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: Gints Plivna <>
Date: Fri, 17 Nov 2006 16:50:19 +0200
Message-ID: <>

FULL SCANs can do completely different things behind the scenes: 1) It can be FULL SCAN without anything more for example select * from big 2) It can be FULL SCAN with some static filter for example select * from big where col1 = 1
3) It can be FULL SCAN with some dynamic filter for example select * from big where my_very_flexible_and_elegant_user_defined_function(col1) = my_very_flexible_and_elegant_user_defined_function2(col1) 4) It can be FULL SCAN with hidden nested loops join in it 5) and so on so on

So here are two query examples of my table big with index on owner and line (which actually is dba_source * 16 :) SELECT count(DISTINCT type)

    FROM big
with following plan
Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3916 Card=1 Bytes=12)    1 0 SORT (GROUP BY)
   2 1 TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564 Bytes=17910768)
SELECT /*+ FULL (a) USE_NL (a b) */ count(DISTINCT a.type)

    FROM big a, big b
    WHERE a.owner = b.owner
      AND a.line = b.line
with following plan
Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1496480 Card=1 Bytes=38)    1 0 SORT (GROUP BY)

   2    1     NESTED LOOPS (Cost=1496480 Card=6036307 Bytes=229379666)
   3    2       TABLE ACCESS (FULL) OF 'BIG' (Cost=3916 Card=1492564
   4    2       INDEX (RANGE SCAN) OF 'BIGIDX' (NON-UNIQUE) (Cost=1
Card=4 Bytes=52)

First query takes about 30 secs, but second more than 30 minutes Both queries show only one full scan in v$session_longops and both have the same number of 72061 blocks in totalwork column But actually THE WORK BEHIND THE SCENES is very different.

I'm almost 100% sure that I've seen the same about hash joins, but haven't time to make a test case just now...

So look on explain plans and/or in 10046 trace output to check your queries are doing something more than just a plain full scan or hash join.

Gints Plivna

2006/11/16, cosmin ioan <>:
> 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 Fri Nov 17 2006 - 08:50:19 CST

Original text of this message