Re: Skip scans

Mladen Gogala mgogala_at_allegientsystems.com wrote:

>Is anybody using index skip scan feature? What would be a
>situation in which the feature should be used? I have a SQL
>statement, part of ETL process that I have to tune. The
>initial reuslts are encouraging although not
>dramatic. Does anybody else have any type of practical
>experience with skip scans and index_ss hint?

Assume you've seen metalink docs 212391.1 and 3354231.8 .

I'm dealing with a system ETL'd from a commercial ERP system (still RBO). Naturally, in the commercial system, every key is going to be prefixed with a company and division code. So queries in the ETL'd system are naturals for skip scans.

The CBO figures this out, of course. What I ran into was the above bug combined with differences in plans generated by the different ways of generating statistics. Oddly, the statistics generated from the imp (coming from an 817 RBO exp into a 9204 CBO) were the only ones that correctly costed the skip scan. Since I needed to do things faster than with an imp (eventually settling on CTAS over a link, with associated truncations, index creations and so forth), I had to change the (10gAS Portal) code to hint range scan, and was never able to equal the lowest cost, even by hinting index_ss. I was happy just to get the report to be able to run in a reasonable time, again.

It was a real PITA having to go through this while discovering the different methods of generating stats give different results (as Lewis, McDonald et al have posted somewhere more recently). And that's not even counting whether explain plan really does. Customer was not happy I had to spend so much time just to make a report work that had worked with imp.

ONE WOULD THINK ORACLE WOULD HAVE CBO FIGURED OUT BY NOW. Ahem, excuse me. I've been saying that since 7.1.

So my question is, what is it that imp does correctly for stats that nothing else does? (Granted, it could be the stats from imp could be one of those situations where they are "completely wrong" yet give the best performance. It did not occur to me at the time to make something bound to be completely wrong the outline. My bad. The "statistics" in the original RBO db are from Feb. '03, which I'm guessing is when the instance was changed from choose to rule after an imp.) I see things like ANALYZE TABLE ... ESTIMATE STATISTICS in the exp file, yet doing that from SQL seems different...

Joel Garry
http://www.garry.to

--
http://www.freelists.org/webpage/oracle-l