Re: Skip scan instead of range scan

From: <digory_at_gmx.net>
Date: Tue, 31 Jul 2007 01:17:34 -0700
Message-ID: <1185869854.762290.183540@22g2000hsm.googlegroups.com>

Just figured it out myself. The UNION was the problem.

SELECT c1 + c2
FROM (
  SELECT /*+ INDEX (w myindex) */
    COUNT(*) c1
  FROM
    mytable w,
    (

            SELECT
                    'POOL' AS rkat, fkey AS rkey
            FROM
                    othertable
            WHERE
                    kat = 'LINKPOOL'
                    AND typ = 'POLL'
            START WITH
                    pcode = 3
            CONNECT BY
                    PRIOR icode = pcode

    ) 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')

) t1,

(
  SELECT /*+ INDEX (w myindex) */
    COUNT(*) c2
  FROM
    mytable w
  WHERE
    w.rkat = 'USER'

    AND w.rkey = 'ZZCISAMB'
    AND w.folder = 'INBOX'
    AND w.stopdat = to_date ('01.01.3000', 'DD.MM.YYYY')
    AND w.startdat > to_date ('01.01.1900', 'DD.MM.YYYY')

) t2
Received on Tue Jul 31 2007 - 03:17:34 CDT