Query takes hours and hours
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: Query takes hours and hours

  1. #1
    Join Date
    Oct 2002
    Posts
    25

    Query takes hours and hours

    The SQL attached takes hours and hours to execute,

    Here is the explain plan for the SQL,

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37730 Card=22539 Byt
    es=3268155)

    1 0 SORT (GROUP BY) (Cost=37730 Card=22539 Bytes=3268155)
    2 1 HASH JOIN (Cost=37460 Card=22539 Bytes=3268155)
    3 2 TABLE ACCESS (FULL) OF 'IW_GEOGRAPHY_DIMENSIONS' (Cost
    =20 Card=3815 Bytes=156415)

    4 2 HASH JOIN (Cost=37438 Card=22539 Bytes=2344056)
    5 4 TABLE ACCESS (FULL) OF 'IW_RETAIL_DIMENSIONS' (Cost=
    5270 Card=37383 Bytes=822426)

    6 4 NESTED LOOPS (Cost=32148 Card=234311 Bytes=19213502)
    7 6 HASH JOIN (Cost=32148 Card=234311 Bytes=18510569)
    8 7 TABLE ACCESS (FULL) OF 'IW_PRODUCT_DIMENSIONS' (
    Cost=52 Card=1071 Bytes=44982)

    9 7 NESTED LOOPS (Cost=32094 Card=234311 Bytes=86695
    07)

    10 9 TABLE ACCESS (BY INDEX ROWID) OF 'IW_TIME_DIME
    NSIONS' (Cost=32 Card=1 Bytes=20)

    11 10 BITMAP CONVERSION (TO ROWIDS)
    12 11 BITMAP AND
    13 12 BITMAP INDEX (SINGLE VALUE) OF 'IWTD_IX2
    _BIT'

    14 12 BITMAP INDEX (SINGLE VALUE) OF 'IWTD_IX1
    _BIT'

    15 9 TABLE ACCESS (BY INDEX ROWID) OF 'IW_SLD_VOL_A
    GG4' (Cost=32094 Card=93724288 Bytes=1593312896)

    16 15 BITMAP CONVERSION (TO ROWIDS)
    17 16 BITMAP INDEX (RANGE SCAN) OF 'IWSVA4_IWTD_
    FK_BIT'

    18 6 INDEX (UNIQUE SCAN) OF 'IWGD_PK' (UNIQUE)

    Please help me to optimize this SQL,the goal is to make it run much faster.

    I hope experts can help me.

    Thanks,
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width