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

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?
>
>
>
>
>Thanks,
>SAP BASIS Consultant

Hello,

histograms (with accurate information) will help the CBO in estimating the cardinality for each column predicate individually.

Without histograms, the CBO probably estimates the cardinality for COL2 to 7 * 13M / 100 = 900k, with accurate histograms, it probably comes close the 250k you mentioned.

If skewness occurs on ACTIVE_FLAG, which I can imagine - 2 values? - histograms might reflect the distribution of values much better.

In general (according to my experience), histograms will not influence the CBO badly. There's no harm to have histograms for all columns, probably with some exceptions to the rule.

The problem you might encounter is that when the CBO combines the selectivity for the multiple columns, it assumes 100% non-correlation.

BTW: I can imagine that there is not a big difference between an index range scan combined with table access by rowid, and a full table scan, because the query selects 0.3% of the table. As a rule of thumb (for big tables only), I divide the size of the table (in bytes) by 64 Kb. This result is approximately the breakeven point (number of rows). If less rows will be selected, indexed access uses probably less resources, if more rows will be selected, than a full table scan is probably more efficient.

Kind Regards,

Herman de Boer
sr consultant
IT Consultancy Group bv Received on Thu Jul 18 2002 - 02:29:12 CDT