Thanks for the response.It's very useful.My earlier question perhaps was not clear so I'm rewriting the questions.

question 2 : Can we do analyze of a partition while other partition is being loaded(I mean simultaneously).I thought one of it will fail.

question 3 : Pls correct me if i'm wrong,In a datawarehouse we should go with local indexes rather than with global indexes.

One more quick Q.I've one column in this partitioned table "xyz" say code_type(has values say a,b,c,d,e,f,g,h)As I'm loading this table with multiple threads , I'm thinking to create a bitmap index on this column.Is it ok ?Thanks in advance.

Tom --does the load timing will be impacted even the bitmap index is local to partitions.?If yes -- then should we set those indexes unusable before the load and then load .?

I think that this confirms what I have always used as guideline: if an index on a range-partitioned table INCLUDES the partitioning key, then make it a local index, otherwise make it a global index. Is that correct?

I agree about understanding the specifics in each case. Here is a test I used to verify conditions in a real database:

The DSSMART.PRODUCT_EVENTS table is range partitioned on START_DT. All of the indexes are currently LOCAL indexes.The index PRODUCT_EVENTS_IDX11 is on column ORD_TYPE_CD.

Some examples:

SELECT COUNT(*) FROM DSSMART.PRODUCT_EVENTS WHERE START_DT > SYSDATE - 2;

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Hint=CHOOSE 1 600
SORT AGGREGATE 1 8
PARTITION RANGE ITERATOR KEY 44
PARTITION HASH ALL 1 4
INDEX RANGE SCAN PRODUCT_EVENTS_IDX1 1 M 12 M 600 KEY 176

This uses partition elimination as expected since START_DT is the partitioning key and is also part of the primary key index.


Let's add another predicate:

SELECT COUNT(*) FROM DSSMART.PRODUCT_EVENTS WHERE START_DT > SYSDATE - 2 AND ORD_TYPE_CD = 1;

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Hint=CHOOSE 1 600
SORT AGGREGATE 1 12
PARTITION RANGE ITERATOR KEY 44
PARTITION HASH ALL 1 4
INDEX RANGE SCAN PRODUCT_EVENTS_IDX1 124 K 1 M 600 KEY 176

So far, so good - including START_DT still gives us partition elimination.


Now let's lose the partitioning key from the query:

SELECT COUNT(*) FROM DSSMART.PRODUCT_EVENTS WHERE ORD_TYPE_CD = 1;

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Hint=CHOOSE 1 1251
SORT AGGREGATE 1 4
PARTITION RANGE ALL 1 44
PARTITION HASH ALL 1 4
INDEX FAST FULL SCAN PRODUCT_EVENTS_IDX11 2 M 9 M 1251 1 176

We now have PARTITION RANGE ALL, which means that even though it can use index PRODUCT_EVENTS_IDX11, it will still have to do an index scan on every partition of this local index.

For our sins my group has "inherited" a DWH with loads of range partitioned aggregate tables (8.1.7) Most data is parititioned by week, i.e. for week level summaries each partition has a single date value.

We have found that the designers have created bitmap indexes on each of the dimension columns of the fact summary INCLUDING the time column - surely this is not needed as there will only be 1 value referenced in each index partition. Unless any of you know otherwise ;-}

My thinking is that the data dictionary knows which partition contains which week, and the bitmap index is not giving us any value even for date predicate queries.

Sadly, no
Star transforms are not enabled on this DB.

That is one area we are going to fix after a scheduled upgrade to 9.2

Are you saying that STAR transforms also require a bitmap index on the partition keys

Star transforms were a little buggy in early 8.1.7 releases, but 8.1.7.4 was good. you might like to give them a go prior the the 9i upgrade -- they gave us a great performance boost.

Aside from the star tansformation issue, Oracle doesn't know for sure that there is a single value per partition (aside from column histogram information), only that any values fall within a particular range. So if you wanted to place a predicate on that column that stated "where week_date ='01-jan-2003'" it would still be beneficial to have that bitmap index there so that it could be bitmap-AND'ed with other predicated colum bitmaps

That was most helpful. The DB was devloped in 8.1.4 i.e before STAR Transforms worked well.

We will certainly give this a try on our test system - just have to find some time to put in the dimensions !

referring to this partition by range..can i partition with more than one column...

say col1,col2 range x,y
col1, col2 range z,k
col1, col2 range f,h

for the star transform the docs say:

There is no need to create many concatenated indexes where the different column orders match different patterns of constrained dimensions in different queries.

so:

does this imply that for star queries without bitmap indexes, that we must have many different concatenated indexes to match the different patterns of constrained dimensions in the different queries?

I've a partitioned table and i've created local partitions on some columns and have indexed the partitioning key (TIME).

I've several similar queries that would benefit immensely if they'd use the index on the partitioning key.

1. How would i tell oracle that this is the way to go ?? My queries are dynamically created and i cannot readily change the front end program to make use of the hints.

2. Would it be better to drop the index ??

3. how would i force oracle to full scan the partition instead ? I think block selectivity or block distribution of my data are evenly distributed hence the benefit of full partition scans.

SELECT /*+INDEX (rtbetelcalldetailrecords,CDR_TIME) */ count(*)
FROM rtbetelephony.rtbetelcalldetailrecords
WHERE groupid = 2741
and (TIME BETWEEN 1062219600487 AND 1062305999487)
5 /

COUNT(*)
--------------
59679


1 row selected.

Elapsed: 00:03:209.62

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=68612 Card=1 Bytes=13)


1 0
SORT (AGGREGATE)


2 1
TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'RTBETELCALLDETAILRECORDS' (Cost=6861
2 Card=1636 Bytes=21268)


3 2
INDEX (RANGE SCAN) OF 'CDR_TIME' (NON-UNIQUE) (Cost=268 Card=1636)








Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
126521 consistent gets
10888 physical reads
0 redo size
205 bytes sent via SQL*Net to client
316 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed


1 SELECT count(*)
2 FROM rtbetelephony.rtbetelcalldetailrecords
3 WHERE groupid = 2741
4* and (TIME BETWEEN 1062219600487 AND 1062305999487)

COUNT(*)
--------------
59679


1 row selected.

Elapsed: 00:20:1251.90

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=33806 Card=1 Bytes=13)


1 0
SORT (AGGREGATE)


2 1
TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'RTBETELCALLDETAILRECORDS' (Cost=3380
6 Card=1636 Bytes=21268)


3 2
INDEX (RANGE SCAN) OF 'CDR_PART_GRPID' (NON-UNIQUE) (Cost=248 Card=1636)








Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
389986 consistent gets
214864 physical reads
0 redo size
205 bytes sent via SQL*Net to client
316 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

I have a table partitioned by day. One partition for every day. Is there any reason ( I can't think of one) why I would want to bitmap index the date field?

Hi Tom,

I have a range partitioned table, partitioned by a date field, which holds data from year 2000 onwards. My question is - Is it possible to create a btree or bitmap composite index(even though bitmap composite indexes are not that common) only for a few of the table partitions - say for partitions which contains the data for year 2003 and 2004. Since each of the partition contains many millions of rows, a btree composite index on the full table will be really big and most of the old data will be very rarely used.Most of the queries access only the current year and previous year's data. Currently we have local, single column bitmap indexes in place for this table. Is it possible to create a composite index(either local or global, btree or bitmap) only for 1 or 2 partitions of the table ? Ours is a data warehouse which uses range partitioned tables and the queries on these tables use bitmap index/star transformation

Thanks, Tom.

I had in fact seen this article in Oracle magazine and on the net,some time back, but was searching for 'condtional index' instead of 'selective index' and thus unable to find that, of late.

Thanks again

was searching for 'conditional index' not 'condtional index'