Re: slow full table scanning + partitioning qq on 9.2.0.6
- From: cosmin ioan <cosmini@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 16 Nov 2006 11:56:24 -0800 (PST)
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 <oracledba.williams@xxxxxxxxx> 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 <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:
