Re: slow full table scanning + partitioning qq on 9.2.0.6

  • From: "Dennis Williams" <oracledba.williams@xxxxxxxxx>
  • To: cosmini@xxxxxxxxxxxxxxx
  • Date: Thu, 16 Nov 2006 13:26:29 -0600

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 <cosmini@xxxxxxxxxxxxxxx> 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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2006