Global Index vs Nonprefix Local Index in Oracle 8.

Hello Oracle Gurus,

I am a newbie in Oracle and need your help urgently.

I have a very big sales table which has around 300 million records. This table is range partitioned by region, year and month. The table has data for 5 years for 4 regions. So each partition has roughly 300 / (5 * 4 * 12) = 1.2 million records. Region code is CHAR(2), Year is NUMBER(4) and MONTH is NUMBER(2).

I also have a customer table, where there are 12 thousand customers for each region. Customer code is char(10). Region + Customer code is primary key for Customer table.

I want to make an index on Sales table for Region and Customer code so that queries which have Region & Customer code in where clause return results faster. Some queries have only Region + Customer code in the where clause, while others also have Year and Month.

Should I make a global unpartitioned index on Region + Customer code? Or should I make a local Index on Customer code only (because the table is already partitioned by Region, Year and Month.). The performance of the query is more important to me, even if index creation takes long time.

If I should make global index on Region + Customer code, what should be the order in the index - Region + Customer code or Customer code + Region??

I am using Oracle 8i. If you need more information, please reply to this message.

Thanks a lot for your expert opinion.

With Regards,

Rohit Chhabra. Received on Thu Mar 21 2002 - 21:33:09 CST