rage partitioning; engine not picking up partition

hello all,
I'm working on Oracle 9i R2 on WinXP Pro and I was wondering why the engine is not picking up the right partition in a table range partitioned.

I've created a sample table of 3 Number[5] columns (first column having just two distinct values used as the two range values for two partitions) w/ 100 million rows and then I'm running something simple like the following loop:

DECLARE
   CURSOR c
   IS SELECT * FROM TABLEA WHERE COLA = 100; (--basically simulating retrieving one half records from the first partition)

   i PLS_INTEGER;
BEGIN
   i := 0;
   FOR cr IN c
   LOOP
      i := i + 1;
   END LOOP;
END; It is my understanding that Oracle should pick up the correct partition even without any kind of index (local or global) and that partitioning is mostly helpful when doing full table scans such as in this operation. The plan shows a full scan but no partition selection and basically the operation takes the same amount of time as an identical non partitioned table. I've even enabled parallelism on the table and in the session, still no luck. Am I missing something here?  On more complex tables where the 'where' clause contains more fields as well as the partitioned-on field, would I normally get the benefit of correct automatic partition selection or do I have to index all fields involved?

thank you,
Cosmin Received on Sun Jan 26 2003 - 16:12:40 CST