Query takes hours and hours

[rajan1]

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,