Re: slow full table scanning + partitioning qq on 9.2.0.6

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_at_gmail.com> 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_at_bridge-tech.com> 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

--
http://www.freelists.org/webpage/oracle-l