Re: Re: Access and Filter Predicate on same execution plan line

Hi Mladen,
Indexes are no mere decoration to a table. They improve the access to the data. Thus, they way indexes should be build depends on the application and the users requirements. The idea that just one rule, wheter or not it works occaisionally, should define how indexes are designed best, is rather naive at least. Your refernce to an exotic concept like the index merge does not make this any better. I know that you can construct cases where this concept does work, but yet this is not the point. I can easily construct cases where it does not work. Since the days of Karl Popper we know that y good theory must withstand falsification. That positive prove is not possible. Thus, speaking of proven rule is bad science. Bury the silver bullets for good.
Thanks
Lothar
----Ursprüngliche Nachricht----

Von : gogala.mladen_at_gmail.com
Datum : 18/08/2021 - 20:30 (MS)
An : oracle-l_at_freelists.org
Betreff : Re: Access and Filter Predicate on same execution plan line 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:  

Range scan (between, <=, >=) on the leading column(s) of the index  

The exact equality scan on all columns of the index 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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 20 2021 - 14:48:37 CEST