Re: [Q] Skip scan instead of range scan

digory@xxxxxxx 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@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages