| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: star transformation taking too long
You are not getting a star_transformation path. This may be the result of the 'IN' clauses that appear in the query.
Unless there is a subtle reason (which you have removed for purposes of clarity) for the construction of you query, you may find that rewriting the 'WHERE' clause as follows will help:
WHERE
F.DATE_ID = D.DATE_ID AND
DATE_YYYY = '2003' AND DATE_MM = '03' AND DATE_DD = '22'
F.TIME_ID = T.TIME_ID AND
TIME_HH24 = '07'
F.SOURCE_ID = S.SOURCE_ID AND
SOURCE_NAME = 'Thermometer 5' AND
F.STATUS_ID = ST.STATUS_ID
You also need to ensure that each of the dimension tables has the appropriate primary key defined for the closing join-back.
I have published a couple of articles on
on bitmap indexes which give further clues
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html ____Denmark__May 21-23rd ____Sweden___June ____Australia__June 28th (Canberra - CBO) ____Finland__September ____Norway___September Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK_(Manchester)_May x 2 ____Estonia___June 4th - 6th ____Australia_June 18th - 20th (Perth) ____Australia_June 23rd - 25th (Melbourne) ____USA_(CA, TX)_August ____UK___September The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "mar" <mar_at_foo.com> wrote in message news:ba9d1c$qk9ml$3_at_ID-174077.news.dfncis.de...Received on Mon May 19 2003 - 03:30:56 CDT
>
> 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)
>
>
>
>
>
>
 |
 |