Re: I am confused with this CBO issuse

From: John P. Higgins <jh33378_at_deere.com>
Date: Wed, 21 Oct 1998 21:55:24 -0500
Message-ID: <362E9E9C.471E2926@deere.com>

You can also get index range scans when there are more columns in the unique index than in the predicates or when the index is not unique.

Here is an example form my SAP system:

select _________ from ____ where AA = :a0 and BB = :a1 and CC = :a2;

There are four indexes on this table. AA is the first key in each index. None of the indexes has BB or CC as its second key.

Via explain, we see that Oracle (rule based) chooses to use an index range scan. How it decided which of the four indexes to use, I don't know. However, since it can only match on the first key, any index would give the same result.

Now the really bad part: the first key has exactly one unique value in the database. Thus the worst performing thing in the world -- an indexed full table scan. I cannot change the SQL, but I am changing some indexes.

I think the rule-based optimizer will always pick an index if any are available (referenced in the predicate). In this release of SAP, only the rule-based optimizer is certified. The cost-based optimizer (ceritied in our next SAP upgrade) might choose a full table scan in this situation or use a different index. But again, the app uses host variables, so the optimizer cannot use the values to determine selectivity.

By the way, SAP is an OLTP system, not a data warehouse. (SAP has a data warehouse, but that is a separate product on a different architecture!)

Billy Verreynne wrote:

John P. Higgins wrote in message <362D2764.E1BDE12D@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 - 21:55:24 CDT