Re: star transformation taking too long

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 18 May 2003 23:55:44 -0700
Message-ID: <130ba93a.0305182255.34766bd0@posting.google.com>

I don't see star transformation happening here. So just this step

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

'STAR.FCT_SIGNAL' would kill your performance - you are nested-looping the fact and dimension table without the benefits of an index. Partitioning on the fact table helps you out a bit, still, a lot of rows to go through.

Try these:

1. Remove the unnecessary select from your where clause and rewrite it as 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_NAME = 'Thermometer 5') AND (D.DATE_YYYY = '2003' AND D.DATE_MM = '03' AND D.DATE_DD = '22') AND T.TIME_HH24 = '07'
2. Make sure all bitmap indexes have been created on the foreign keys of the fact table.
3. Fully analyze your tables.

If star transformation happens, it would first bring back 3 bit maps that identify rows from the fact table constrained by each dimension. The optimizer would then apply bit AND to get you the final results. In your explain plan you should see many bitmap related stuff like BITMAP MERGE, BITMAP KEY ITERATION, BITMAP INDEX RANGE SCAN, etc..

• Jusung Yang

"mar" <mar_at_foo.com> wrote in message news:<ba9d1c$qk9ml$3_at_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 Mon May 19 2003 - 01:55:44 CDT