Oracle8 Partitioning and optimizer intelligence

A challenging problem for the Oracle genius's at large!

I am creating a small datamart that I would like to index using two different columns. Here is my first attempt.

ANID NUMBER(4,0)
ADATE DATE
AVALUE1 NUMBER(13,0)
AVALUE2 NUMBER(13,0) I decided that I wanted to partition using 'anid' and month from 'adate'. I found that Oracle would not allow me to create a partition with high values that did not include a full date including century. This meant that I had to create the following

ANID NUMBER(4,0)
ADATE DATE
AVALUE1 NUMBER(13,0)
AVALUE2 NUMBER(13,0)
MONTH NUMBER(2,0) I can fill the value for month using a trigger, so the application is not affected. My question is, since I am forced to create a non-prefixed local index, and I would never use the 'month' column in a query, will the optimizer really understand when to limit query activity to less than all partitions?

In my case 90% of all queries will stay within a given 'anid' and within 'anid' 90% of all queries will stay within a given month.

Any thoughts, any help would be greatly appreciated.

Thank you,

Joel R. DeRider
IceT Corporation Received on Wed Jun 24 1998 - 23:42:54 CDT