Re: I am confused with this CBO issuse

From: John P. Higgins <jh33378_at_deere.com>
Date: Tue, 20 Oct 1998 19:14:28 -0500
Message-ID: <362D2764.E1BDE12D@deere.com>

Another possibility:

Due to the extreamly low selectivity of most SAP primary indexes, you may be performing an indexed full table scan! This is much worse than a full table scan. If the cluster ratio is bad, you can wind up retrieving every block of the table multiple times -- up to a factor of 100.

Part of the problem is that SAP puts the predicate values into host variables. This enhances the reuse of cached SQL. But it does not let the optimizer use histograms.

Evan Cao wrote:

scenario:

SELECT NUM_ROWS, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='XXX';

0        SEPT-24-98

select count(*) from xxx;

1310000

There is an unique index on table xxx on column say  index_0 on
xxx(c1,c2);

Observation:

select * from xxx where c1=:a and c2=:b;

Noticed by an SAP monitor, ORACLE is doing a full table read, taking
long time,  which is understandable. what
I am confused is that when I explain this stmt. I see ORACLE using the
index index_0

What's wrong here??? What is the logical explaination here?

BTW. how can we find out ORACLE is doing a full table scan without using
sql trace?

Thx

  ------------------------------------------------------------------------

  Evan Cao <ecao@ti.com>
  SAP/ORACLE Basis Administrator
  Texas Instrument

  Evan Cao
  SAP/ORACLE Basis Administrator  <ecao@ti.com>
  Texas Instrument
                                  Netscape Conference Address
                                  Netscape Conference DLS Server
  Additional Information:
  Last Name      Cao
  First Name     Evan
  Version        2.1

  Received on Tue Oct 20 1998 - 19:14:28 CDT