RE: Access and Filter Predicate on same execution plan line

1. The only strategic goals of index design are to optimally support integrity constraints (including no index if there is a cheaper way to do that in a particular case, including maintenance and possible lock generation) and optimally supporting the queries and you know will take place and guess might take place.
2. I am not allowed to reveal whether or not I know the number and name of the last version of Oracle. It is barely allowed for me to reveal outside of DTSS and Oracle there is an agreement I am not allowed to reveal anything I know about Oracle that is not already public information. That dates from when Larry was worth less than half a billion. DTSS is history, but my word on that agreement survives as long as Oracle exists, even if no one has a copy of it. Very probably I don't know anything anymore that remains confidential, but to my knowledge they have not revealed what you ask. And release dates and names of releases were something specific in the agreement, since some lawyer told Larry something to the effect that they could be construed revealing financial information during a quiet period. And more importantly, they could trigger a counter ad campaign by SYBASE and/or INFORMIX.

Indexes are NOT a part of the relational model. (And if someone mentions index organized table, please notice that index is an adjective about structure and the object is a table.)

Likewise, a primary key is not an index, although it may be supported by a primary key index.

An excellent question of 42 is whether or not the menu at the restaurant at the end of the universe will need an index. I suspect not. I suspect by then the thought speed database will exist and the optimizer will nearly always work. Slight failures may resemble what someone told me is like a bad trip on LSD.

All the best,

mwf

-----Original Message-----
From: Mladen Gogala [mailto:gogala.mladen_at_gmail.com] Sent: Tuesday, August 17, 2021 9:43 PM
To: Mark W. Farnham; oracle-l_at_freelists.org Subject: Re: Access and Filter Predicate on same execution plan line

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.

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




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