Re: rage partitioning; engine not picking up partition

Hi Cosmin,

the question is: Are there any indexes as well? To understand what oracle is doing: It selects the access method for the rows by determining the "most selective" criteria. Sometimes, (no statisitcs available, init.ora setting like index_cost_adjust, etc.) the datatbase thinks not the partition-criteria but another one is most selective and chooses that one for access.

Regards
Oliver

"cosmin ioan" <cosmini_at_yahoo.com> schrieb im Newsbeitrag news:80fb0771.0301261412.470f4781_at_posting.google.com...
> 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 Mon Jan 27 2003 - 08:27:31 CST