Re: rage partitioning; engine not picking up partition

hello all & thanks much for all the answers so far. Here's what I found:

1. Indeed Oracle seems to be picking the correct partition (when looking at the explain plan) but no gain in speed when selecting/iterating through just the one half of records from the partitioned table (either from the first partition or the second) vs the non partitioned table. I'm about to stuff in 2 more partitions worth of data, for a total of 4 and will report back if any gain in speed BUT even with a two partitions table, the gain should have been significant, in my oppinion, since only one partition's worth of data is traversed. (P.S. Yes, I can see the data being currently distributed among the two partitions --I thought I may have accidentally stuffed just one partition w/ the entire data but that's not the case. Also, the total memory allocated to Ora is about 1 gb ram and each partition is about 950mb of data so a possible full caching theory is dispelled).
2. When I have a local or global index on the part. table, the scrolling operation goes up from 9 minutes to 18 HOURS (yes, 18 hours) so for now I'm dropping the indexes (or I could probably hint for a full scan).

thx again,

Cosmin

"Oliver Reiniger" <oliver.reiniger_at_isb-ag.de> wrote in message news:<kkf31b.7f4.ln_at_ftp2.intern.isb-ka.de>...
> 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 Wed Jan 29 2003 - 10:14:47 CST