Re: I am confused with this CBO issuse

John P. Higgins wrote in message <362D2764.E1BDE12D_at_deere.com>...

> 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.

I assume you're meaning an index range scan? You get those when you do something like this on a column that's indexed:

SELECT * FROM foo WHERE c1 BETWEEN '290000000' AND '290999999'

Reason why I did not think it a possibility was that the poster said he used to the following SQL:
select * from xxx where c1=:a and c2=:b;

Where both c1 and c2 are indexes. Oracle should not be using an index range scan in this instance.

AFAIK the results of the EXPLAIN PLAN tells you whether or not Oracle will make use of an index range scan.

>Part of the problem is that SAP puts the predicate values into host
variables.
>This enhances the reuse of cached SQL.

Yep. This is especially a problem in data warehousing. Bind variables in a SQL statement is a great idea for OLTP but can be a real headache in OLAP. One way to prevent this is to reduce the SQL cache size. The better way is for the front-end OLAP and query tools to use normal SQL and not bind variables (one of the reasons I have a gripe with Cognos for example).

regards,
Billy Received on Wed Oct 21 1998 - 05:18:50 CDT