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