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

From: SAP BASIS Consultant <basis_consultant_at_hotmail.com>
Date: 17 Jul 2002 11:12:52 -0700
Message-ID: <dd2036f3.0207171012.30fdda89@posting.google.com>

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 Received on Wed Jul 17 2002 - 13:12:52 CDT