Re: I am confused with this CBO issuse

John P. Higgins wrote in message <362E9E9C.471E2926_at_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.

Oh yes. Forgot about that one. I usually try and steer away from compound indexes in Oracle. But then I've only work in OLAP on Oracle and not OLTP. In the OLAP world you can never trust that the user will use the compound index's columns in the right sequence. And neither can you enforce that easily in the query tools they use.

The other problem that you face is that you sometimes need to perform processing via an index and sometimes via a full table scan. And Oracle CBO still fails to read your mind as to what you wanted to have done. Am still waiting to the telepathy patch to the CBO.. :-)

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

Will be interesting to know how it chooses which index to use.

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

Not sure what you mean with an "indexed full table scan". If it means that every single row in the table is accessed using an index because of a bad execution plan, than yes - it sure sounds like a real crappy thing.

CBO takes a different approach. If it thinks (not sure what criteria it uses) that an index range scan will result in the processing of x percentage of the table or something, it will revert to using no indexes and perform a full table scan instead.

>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!)

Yes, I'm aware of that. My comment about bind variables was that the problem with bind variables is much worse in the OLAP environment than in the OLTP environment. IMHO if an OLTP system has been designed properly than bind variables is good for performance as the parsing overhead per every single SQL statement is reduced drastically.

regards,
Billy Received on Thu Oct 22 1998 - 01:57:17 CDT