Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: slow full table scanning + partitioning qq on

Re: slow full table scanning + partitioning qq on

From: Dennis Williams <>
Date: Thu, 16 Nov 2006 14:27:07 -0600
Message-ID: <>


QQ1: Solo FTS vs. FTS in a join. For certain types of join, the table scan may be slower because Oracle is performing other tasks while scanning that table. Anyway, I think it is time for you to run it again with a 10046 trace to see what is actually occurring. If you are not getting partion pruning, then you should consider parallel query.

QQ2: Looking just at your last statement, again, probably time to go for the 10046.

Don't forget that repeated execution of the same query can place some blocks in the cache and skew your results.

Apples vs. apples -- Yes definately.

Dennis Williams

On 11/16/06, cosmin ioan <> wrote:
> hey Dennis/All,
> no, there's no partition pruning since the 'where' clause does not contain
> the partition key.
> I really don't care about the join, I think... as the engine did not even
> get to that stage.... In the first step, it was still scanning (FTS) the
> initial table (which on the first test, it was scanning the table a whole
> lot faster).
> Second QQ: this is just index scanning and hash joining, which again,
> I'm noticing hash joining done a lot faster (#blocks/second) at times vs.
> other times when it's painfully slow...
> I probably need to take these tables and run an identical test on
> different hardware, same Oracle version or compare against other versions
> ;-)
> thx for any feedback, gents,
> Cosmin
> *Dennis Williams <>* wrote:
> Cosmin,
> Have you verified that in the join situation that partition pruning is
> still happening?
> What type of join is occurring? Yes, not a surprise that joining two
> tables and doing a FTS on both is potentially much slower than a straight
> query on a single table. Need more details to diagnose.
> QQ2: Your question is not clear. Is this a FTS or indexed access? Usually
> we start with the number of rows in each table (or partition if we can
> perform partitioned access, then the number of rows you must retrieve from
> each table. This can give you a decision on which table you desire Oracle to
> use as the driving table. Then we can get into FTS vs. indexes, etc.
> Dennis Williams
> On 11/16/06, cosmin ioan <> wrote:
> >
> > hi guys,
> > I have a partitioned table, which, when queried on columns not indexed,
> > by itself, scans the table very fast, hundreds of blocks per second, or
> > so...
> >
> > Same partitioned table, joined with another table, the full table scan
> > operation (on that partitioned table) that starts as the first process, is
> > going a whole lot slower, basically low digits blocks per second....
> >
> > Is this indicative of something at the hardware level or data
> > dictionary?
> > I'm not even dealing with indexes here, just full table scans, and tried
> > to reduce a pretty complex problem to just this simplest of tests, so this
> > is where I'm having performance problems.
> >
> > QQ2: joining two partitioned tables, both partitioned by a date field,
> > however, one table, half partitioned by quarter (range), to the most recent
> > quarter, then monthly, from then on, another one, fully partitioned by
> > month, is Oracle going to have an extremely hard time joining the partitions
> > (I do not have parallel set on any table and I'm just doing a full table
> > scan). -- to me, it appears that it does not make any difference but it
> > may make a difference, when querying by a local index which might be
> > hashed/joined by another local index from the other table.... is my
> > assumption correct?
> >
> > thx much,
> > Cosmin
> >

Received on Thu Nov 16 2006 - 14:27:07 CST

Original text of this message