Home -> Mailing Lists -> Oracle-L -> Re: Index Rang Scan - How to Improve?

Re: Index Rang Scan - How to Improve?

From: Tim Gorman <tim_at_evdbt.com>
Date: Tue, 21 Jun 2011 07:16:58 -0600
Message-ID: <4E0099CA.2030001_at_evdbt.com> 



  
    
  
  
    Thiago,

    

    Does the execution plan show TABLE ACCESS BY ROWID to TABLE_Y or
    not?  You've implied that it shouldn't, but the big question is
    whether or not it is happening.

    

    The "clustering factor" is a value that is clearly much closer to
    the number of rows in the table rather than the number of blocks,
    which indicates that each index entry scanned will mean visiting a
    new different table block.  Thus, scanning 1.5m-3.0m million rows
    from TABLE_X during the join to TABLE_Y would clearly imply visiting
    every block in TABLE_Y, so there is little benefit to a range scans
    and instead chooses another plan.  If the execution plan shows
    access to the table, then clustering factor is important, but if as
    you've implied the query is entirely satisfied within INDEX_02 then
    clustering factor would not be an important consideration as there
    should be no access to TABLE_Y.

    

    I can only conclude that the query is not entirely satisfied within
    INDEX_02, and that TABLE_Y is being accessed, and thus that the
    cluster factor is steering the optimizer away from using index range
    scan, which is a wise decision on its part -- as you've proven with
    your own testing of response times.

    

    So, can you post the execution plan chosen by the optimizer?

    

    Hope this helps...

    
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => 13087 West Cedar Drive #225, Lakewood CO 80228
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...


    

    On 6/20/2011 8:54 PM, Thiago Maciel wrote:
    
Folks,
        i need some help/advice. ETL/DW environment, Oracle
        10.2.0.4, AIX 5.3; 28 cpus; 164gb ram. 
        


        

        
Its a simple query that is part of an ETL routine:
          


          

          

            
SELECT    blablabla
                        ,

            
          Y.COLUMN_B,

            
          blablabla

            
     FROM TABLE_X PARTITION (T1)        
                   X,

            
          TABLE_Y         Y

            
 WHERE X.COLUMN_A                =   'N'

            
      AND  NVL(to_number(TRIM(X.COLUMN_B)),1) = Y.COLUMN_A

            


              

            
I need to run this query with 8 different sessions at
              the same time (each one will choose a different
                partition of the table_x, like: session 1 partition
              (t1), session 2 partition (t3),and so on). Table_X is partitioned
                by hash with 8 partitions (each partition will
              vary between 1,5MM and 3MM) , and table_y has
              around 300MM. There is an index on table_y with the
              following stats:

            


            

            

              

                
INDEX_NAME                                        :
                  INDEX_02

                
COLUMNS                                  
                           : 1 ASC   COLUMN_A                      
                      NOT NULL --    299.094.710 NDV

                
                                                  
                              2 ASC   COLUMN_B                      
                      NOT NULL   --  PK of the table_y

                
INDEX_TYPE                                        
                  : NORM                           

                
DEGREE                                            
                    : 1                          

                
BLEVEL                                            
                     : 3                              

                
LEAF_BLOCKS                                     :
                  1.416.590                          

                
DISTINCT_KEYS                                    :
                  295.327.520                             

                
AVG_LEAF_BLOCKS_PER_KEY            : 1            
                              

                
AVG_DATA_BLOCKS_PER_KEY            : 1            
                                 

                
CLUSTERING_FACTOR                          :
                  265.964.260                                

                
BLOCKS_IN_TABLE                               :
                  1.289.581                                            
                                      

                
ROWS_IN_TABLE                                  :
                  300.017.100

                
SIZE                                              
                         : 11gb

              

            

            


            

            
Tests showed to me that full scan on index_02 or on
              table_y is the best scenario regarding response time (even
              with lots of time spent on read by other session). When i
              force Oracle to choose index range scan on index_02 the
              response time is higher than full scan, either: index or
              table.

            


            

            
See that the selectivity is "only" between 0,5% - 1,1%
              (range scan between 1,5MM and 3,5MM), and Oracle does not
              need to touch the table_y.

            


            

            
So my question is: how can i improve the access to the
              index via range scan? Or is there a better solution to
              this issue? For example, create the index with 32kb block
              size (It seems not so good idea: http://richardfoote.wordpress.com/category/index-block-size/)? 

            


            

            
Yes,yes i need to improve a lot more the response time
              of this query running with different sessions at the same
              time.

            


            

            
** response time of the disks are normals, most of the
              time between: 4ms and 16ms.

            
** Index and Tables in ASSM with 8kb block size (i know
              its a small block size to DW environment).

            


            

            
If you need more information, please let me know it.

          

        

        


        

        
Thanks in advance.

        


        

        
Thiago Maciel

      
    

  

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 21 2011 - 08:16:58 CDT












Original text of this message