star transformation taking too long

From: mar <mar_at_foo.com>
Date: Mon, 19 May 2003 03:45:21 +0200
Message-ID: <ba9d1c$qk9ml$3@ID-174077.news.dfncis.de>

Hi group,
As always, newbie working with 8.1.7 EE on Windows 2000.

I would kindly ask for some advice on the star query below. It simply takes too much time to execute. I would expect instantaneous reaction from those mighty Oracle Bitmap Indexes and that powerful Oracle Star Transformation.

Yes I have STAR_TRANSFORMATION_ENABLED = true.

The query:

SELECT

    D.DATE_YYYY AS MyYear,
    D.DATE_MM AS MyMonth,
    D.DATE_DD AS MyDay,
    T.TIME_HH24 AS MyHour,
    T.TIME_MI AS MyMinute
    S.SOURCE_NAME AS MySource
    F.FCT_VALUE AS MyFact,

    ST.STATUS_TYPE AS StatusType
FROM

    DIM_DATE D,
    DIM_TIME T,
    DIM_SOURCE S,
    DIM_STATUS ST,
    FCT_SIGNAL F

WHERE

    F.DATE_ID = D.DATE_ID AND
    F.TIME_ID = T.TIME_ID AND
    F.SOURCE_ID = S.SOURCE_ID AND
    F.STATUS_ID = ST.STATUS_ID AND
    S.SOURCE_ID IN (SELECT SOURCE_ID FROM DIM_SOURCE WHERE SOURCE_NAME =

'Thermometer 5') AND

    D.DATE_ID IN (SELECT DATE_ID FROM DIM_DATE WHERE DATE_YYYY = '2003' AND DATE_MM = '03' AND DATE_DD = '22') AND
    T.TIME_ID IN (SELECT TIME_ID FROM DIM_TIME WHERE TIME_HH24 = '07') Explain plan for this query (parentheses contain "Expected rows"):

SELECT STATEMENT (594,322,589)
    HASH JOIN (594,322,589)

        TABLE ACCESS (BY INDEX ROWID) OF 'STAR.DIM_TIME' (583)
            INDEX (RANGE_SCAN) OF 'STAR.DIM_TIME_COLS' (UNIQUE) (583)
        HASH JOIN (101,942,125)
            TABLE ACCESS (FULL) OF 'STAR.DIM_TIME' (58238)
            HASH JOIN (175,044)
                TABLE ACCESS (FULL) OF 'STAR.DIM_SOURCE' (13)
                NESTED LOOPS (1,346,491)
                    HASH JOIN (109,828)
                        TABLE_ACCESS (BY INDEX ROWID) OF 'STAR.DIM_STATUS'


(82)

                        HASH JOIN (133,937)
                            TABLE ACCESS (BY INDEX ROWID) OF 'STAR.DIM_DATE'


(1)

                                INDEX (RANGE SCAN) OF 'STAR.DIM_DATE_COLS'


(UNIQUE) (1)

                            NESTED LOOPS (13,393,695)
                                TABLE ACCESS (FULL) OF 'STAR.DIM_DATE' (164)
                                PARTITION RANGE (ITERATOR)
                                    TABLE ACCESS (FULL) OF 'STAR.FCT_SIGNAL'


(8,166,887)

                    INDEX (UNIQUE SCAN) OF 'STAR.DIM_PWQUELLE_ID' (UNIQUE)

Received on Sun May 18 2003 - 20:45:21 CDT