Home -> Mailing Lists -> Oracle-L -> Re: Access and Filter Predicate on same execution plan line

Re: Access and Filter Predicate on same execution plan line

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 18 Aug 2021 14:30:01 -0400
Message-ID: <1a95626f-4cc0-30b3-3695-cd045fc32e90_at_gmail.com> 





  
    
  
  
    
Thanks Lothar!

    
I posted a question on the Richard's blog. The myth is real and
      the cause of the myth was an expensive index merge operation in
      previous versions of Oracle. Index merge is much cheaper as of the
      late versions of Oracle 7i. The whole lore came into being because
      the people were trying to solve 2  problems with one index:

    
    
      
  1. Range scan (between, <=, >=) on the leading column(s) of
        the index
    2. 
      
  2. The exact equality scan on all columns of the index
    3. 
    

    
The logical solution would be to have several indexes but since
      the index merge operation was so expensive and the index
      maintenance cost were having more of an impact at the time when 1
      IO request took 20ms to complete, people were creating
      multi-column indexes. The reason for having the column with the
      most values as the leading column were usually range scans of that
      particular column.

    
Regards

    

    
On 8/18/21 2:29 AM, Lothar Flatz wrote:

    

    
Mladen,
      this is no basic rule but rather some kind of myth. I like to know
      who has proven it, where and when.
      

https://richardfoote.wordpress.com/2018/06/04/index-column-order-impact-on-index-branch-blocks-part-i-day-in-day-out/
      

      

      Regards
      

      

      Lothar
      

      

      Am 18.08.2021 um 03:43 schrieb Mladen Gogala:
      

      
Mark, one of the basic rules of the
        database design is to put the most selective column first in a
        multi-column index. Putting a column with very few values as the
        first column of a multi-column index goes against the time
        tested design rules and is guaranteed to cause problems. I have
        always treated skip scan as a performance problem.
        

        

        As for the 42 billions, I do agree that the answer to the
        question of the indexes, databases and everything is 42. I
        believe that's in one of the Jonathan's books, along with the
        SEP field of the tablespace headers. BTW, I wonder what will be
        the Oracle version at the end of the universe?
        

        

        Regards
        

        

        On 8/17/21 8:51 PM, Mark W. Farnham wrote:
        

        
If your first column is just two values,
          say,  either Y or N, then a skip scan filtering on the second
          in each of Y and N will be the good polar case of skip scan.
          (Well, actually all values equal would be even better, but
          that would also be a silly column to have and index).
          

          

          If you have 42 billion different values for the first column
          only a few of which have the desired value in the second
          column, that will be toward the polar bad case.
          

        

        

      

      

      --
      

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

      

      

    

    
-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com


  

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 18 2021 - 20:30:01 CEST












Original text of this message