Re: DWH : Index on Partition Key s
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- Date: Sat, 31 Oct 2009 22:38:04 +0800
Stephane,
Thanks for your response.
Apparently in a partition for a month (uh oh !!
: PARTITION_KEY='200910310000') , *all* the rows have the same
PARTITION_KEY value.
Thus, a Month Partition has the same pseudo-date value stored in all the rows.
So, the index is utterly useless.
As for multi-column indexes, so far (in this design) I've seen them
only for the Primary Key.
Other indexes are single-column indexes.
Not even BitMap indexes on the FACT table --- had they been BitMaps,
Oracle could have been doing an AND on the BitMaps.
This is an absolutely non-Oracle design on an Oracle database. The
database is just a "data store".
Ugh !
Hemant
Hemant K Chitale
http://hemantoracledba.blogspot.com
At 11:48 PM Friday, Stephane Faroult wrote:
Hemant,
I don't see the point if you have any prefixed local index, but if you
have none, if you have one partition by month and most queries hit a
single day or less, then this index *might*, depending on volume, etc,
etc. benefit those queries.
But then perhaps you could begin to question the partition grain ...
By the way a lot of developers seem never to have been told that you
could index several columns at once. Or it's just the developers that
work on the applications I have to audit.
S Faroult
--
//www.freelists.org/webpage/oracle-l
Other related posts:
