Re: Skip scans
- From: Barbara Baker <barb.baker@xxxxxxxxx>
- To: mgogala@xxxxxxxxxxxxxxxxxxxx
- Date: Wed, 29 Dec 2004 13:26:39 -0700
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
Other related posts:
