Re: [Q] Skip scan instead of range scan

digory_at_gmx.net wrote:
> Hi. I have the following query:
>
> SELECT /*+ INDEX (w myindex) */
> COUNT(*)
> FROM
> mytable w,
> (
> SELECT
> rkat, rkey
> FROM
> othertable
> WHERE
> kat = 'LINKPOOL'
> AND typ = 'POLL'
> START WITH
> pcode = 3
> CONNECT BY
> PRIOR icode = pcode
> UNION
> SELECT
> 'USER' AS rkat, 'ZZCISAMB' AS rkey
> FROM DUAL
> ) m
> WHERE
> w.rkat = m.rkat
> AND w.rkey = m.rkey
> AND w.folder = 'MYFOLDER'
> AND w.stopdat = to_date ('01.01.3000', 'DD.MM.YYYY')
> AND w.startdat > to_date ('01.01.1900', 'DD.MM.YYYY')
>
> I have also an index over rkat, rkey, folder, stopdat, startdat.
>
> The problem is, Oracle does not use rkat and rkey of the index.
> Instead, it does a skip scan, skipping rkat and rkey. As rkey is the
> most selective column of the table, the query runs slowly.
>
> I tried to use WHERE (rkat, rkey) IN (SELECT... instead of a join, but
> to no avail. Oracle's still doing skip scan.
>
> Any ideas?

Post the DDL and explain plan created using DBMS_XPLAN.

Then try it after hinting the inline view.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org