the danger with index skip scanning is that (afaik) some heuristics are involved. it is very complicated for the CBO to base decisions purely on statistics; in other words, the decisions are also based upon some "reasonable" guesswork... additions/corrections welcome, Lex. ---------------------------------------------------------------- Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html ---------------------------------------------------------------- -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Barbara Baker Sent: Wednesday, December 29, 2004 21:27 To: mgogala@xxxxxxxxxxxxxxxxxxxx Cc: Oracle-L (E-mail) Subject: Re: Skip scans Hmmm I wasn't really aware that you could turn them off. Thought you "got them" starting with 9i. I had a 9.2.0.4 database that had optimizer_features set to 8.0.4 When I removed the optimizer_features param, I had a query go from 5 minutes to 6 hours. It started using a skip scan. The problem was that the developer went view-crazy. The query went something like "select * from tab1 minus select * from tab2". But tab1 and tab2 were really views, and one of those contained yet another view. Under 8.0 everything was date-driven, which worked. But in 9i Oracle decided to outsmart itself and use another index. Oracle could not see the "big picture" because rather than a nice query that it could optimize, everything was hidden in the views. Only time I've seen a problem, but it was a biggie. Others have raved about skip scans. I just always seem to get the flaky queries . . . I'd say go for it, but watch for the sucker-punch. Barb On Wed, 29 Dec 2004 13:47:12 -0500, Mladen Gogala <mgogala@xxxxxxxxxxxxxxxxxxxx> 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? > > -- > Mladen Gogala > Oracle DBA > Ext. 121 > > -- > //www.freelists.org/webpage/oracle-l > -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l