-
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,
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|