Re: Histograms Used to Select Best Index, or Only Between Index and FTS?

On 17 Jul 2002 11:12:52 -0700, basis_consultant_at_hotmail.com (SAP BASIS Consultant) wrote:

>Hello,
>
>
>We are on AIX/Oracle v8.0.6 (Will be upgrading soon:-). There is a
>frequently accessed query on a table with about 13 million rows. The
>query is roughly as
>follows:
>
> Select * from TABLE where COL1 like 'VB%' and ( COL2=A1 or COL2=A2
>or
> COL2=A3 or COL2=A4 or COL2=A5 or COL2=A6 or COL2=A7) AND
> ACTIVE_FLAG= ' '.
> .......
>
>
>COL1 has about 3.3 million distinct values. Of those, about 630,000
>rows start with 'VB'.
>
>COL2 has about 100 distinct values. Of those, about 250,000 rows meet
>the condition (COL2= A1 or A2...or A7). Of the 250,000 rows about
>150,000 meet the
>condition ACTIVE_FLAG = ' '.
>
>The table has two indexes: An index on C1 (IN1) , and an index on C2
>and ACTIVE_FLAG (IN2). IN2 is about 20% smaller in size than IN1.
>
>About 40,000 rows meet all 3 conditions. There are updated statistics
>for the table. The optimizer mode is 'CHOOSE'.
>
>The 'Explain Plan' indicates that the index on C1 (IN1) is chosen for
>the query.
>
>I suggested to the DBA that until we create an index that will meet
>the two main conditions (Likely leaving out ACTIVE_FLAG), creating a
>histogram for
>TABLE will help Oracle choose the better index which is likely to be
>IN2.
>
>The DBA mentioned that histograms will not help Oracle choose between
>indexes, but only between using an index and a FTS. Thus, it will not
>be useful in this case.
>
>
>Is the DBA correct in stating that histograms will not be useful for
>this table?
>
>
>
>

I would suggest the DBA to *proof* this, as there is official documentation up on Metalink to the contrary. The main thing the CBO does using histograms, is trying to determine the *cardinality* of the various access paths, including *all* indexes and the FTS. If the cardinality can't be determined from a histogram, an even distribution is assumed.
The current IN2 index is useless as Oracle doesn't discriminate between an empty string and NULL and NULL always means not to use an index.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jul 17 2002 - 16:12:00 CDT