RE: Skip scans
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <barb.baker@xxxxxxxxx>, <mgogala@xxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 29 Dec 2004 21:49:11 +0100
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
Other related posts:
