[Q] Skip scan instead of range scan

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? Received on Tue Jul 31 2007 - 02:50:53 CDT