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@xxxxxxxxx
Datum : 18/08/2021 - 20:30 (MS)
An : oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com -- ;
//www.freelists.org/webpage/oracle-l
