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 10:23:13 -0800 (PST)

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: