sql query tuning

Hi , I am using Oracle 9.0.4.0.
my issue i have a quey as follows. it's taking 45 mins time. Please help me to increase the performance.I have done Explain Plan and stats.But still I am getting the same problem. Can you please tell me how to use HINTS or any other way to solve this issue.
[code]
SELECT im.location ,
im.item ,
cs.case_size ,
im.ondate ,
im.offdate ,
mas.status
FROM case_size cs,
item im,
master mas,
warehouse wh
WHERE mas.pack_ind = 'N'
AND mas.item_level = mas.trns_level
AND mas.status = 'A'
AND mas.foreind = 'Y'
AND mas.item = im.item
AND im.location_type = 'S'
AND mas.item = cs.item
AND cs.supply_indicator = 'Y'
AND cs.country_indicator = 'Y'
AND im.location =wh.warehose_no
AND NVL(wh.CLOSE_DATE,'04-APR-9999')>=TO_DATE(&versDATE}, 'YYYYMMDD')
[/code]
here CASE is the view name and remaining are tablenames.
the execution plan is as follows
[code]
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15085 Card=1139512 Bytes=134462416)
1 0 HASH JOIN (Cost=15085 Card=1139512 Bytes=134462416)
2 1 HASH JOIN (Cost=580 Card=30772 Bytes=1569372)
3 2 TABLE ACCESS (FULL) OF 'MASTER' (Cost=63 Card=1306 Bytes=18284)
4 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'ITEM' (Cost=2 Card=105311 Bytes=3369952)
5 4 NESTED LOOPS (Cost=86 Card=4296685 Bytes=158977345)
6 5 TABLE ACCESS (FULL) OF 'WAREHOUSE' (Cost=4 Card=41 Bytes=205)
7 5 INDEX (RANGE SCAN) OF 'PK_LOCATION' (UNIQUE) (Cost=1 Card=210622)
8 1 VIEW (Cost=14271 Card=48399 Bytes=3242733)
9 8 SORT (UNIQUE) (Cost=14271 Card=48399 Bytes=6098274)
10 9 HASH JOIN (Cost=992 Card=187614 Bytes=23639364)
11 10 HASH JOIN (Cost=186 Card=7449 Bytes=581022)
12 11 TABLE ACCESS (FULL) OF 'MASTER' (Cost=63 Card=10451 Bytes=156765)
13 11 HASH JOIN (Cost=105 Card=12489 Bytes=786807)
14 13 MERGE JOIN (CARTESIAN) (Cost=40 Card=12489 Bytes=549516)
15 14 MERGE JOIN (CARTESIAN) (Cost=6 Card=1 Bytes=13)
16 15 TABLE ACCESS (FULL) OF 'SYSTEM' (Cost=3 Card=1 Bytes=3)
17 15 BUFFER (SORT) (Cost=3 Card=1 Bytes=10)
18 17 TABLE ACCESS (FULL) OF 'SYSTEM' (Cost=3 Card=1 Bytes=10)
19 14 BUFFER (SORT) (Cost=37 Card=12489 Bytes=387159)
20 19 TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=12489
Bytes=387159)
21 13 TABLE ACCESS (FULL) OF 'SUPPLIER' (Cost=28 Card=24989 Bytes=474791)
22 10 VIEW (Cost=536 Card=172449 Bytes=8277552)
23 22 UNION-ALL
24 23 INDEX (FAST FULL SCAN) OF 'PK_ITEM_SUPPLIER_COUNTRY' (UNIQUE) (Cost=11 Card=
24978 Bytes=324714)
25 23 TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=
399648)
26 23 TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=
374670)
27 23 TABLE ACCESS (FULL) OF 'ITEM_SUPPLIER_COUNTRY' (Cost=34 Card=24978 Bytes=
499560)
28 23 VIEW (Cost=141 Card=24179 Bytes=1039697)
29 28 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507759)
30 29 INDEX (FAST FULL SCAN) OF 'PK_CASE_UPDATES' (UNIQUE) (Cost=14 Card=24179
Bytes=507759)
31 23 VIEW (Cost=141 Card=24179 Bytes=1039697)
32 31 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507759)
33 32 INDEX (FAST FULL SCAN) OF 'PK_CASE_UPDATES' (UNIQUE) (Cost=14 Card=24179
Bytes=507759)
34 23 VIEW (Cost=141 Card=24179 Bytes=1039697)
35 34 SORT (UNIQUE) (Cost=141 Card=24179 Bytes=507759)
36 35 INDEX (FAST FULL SCAN) OF 'PK_CASE_UPDATES' (UNIQUE) (Cost=14 Card=24179
Bytes=507759)

[/code]
after that I have done
[code]
exec dbms_stats.gather_table_stats('owner_name','table_name',cascade=>true);
[/code]
but I am not getting the result.

Thank you.

Are you sure there is some data that matches your where condition ?

Are you getting any error from your query ?

Hi I am getting result from this query. there is no errors in the query. But it is getting result from View and 3 tables. So it's taking 45 mins to retrieve the data.So i need to tune this query...

[QUOTE=debasisdas;28907]Are you sure there is some data that matches your where condition ?

Are you getting any error from your query ?[/QUOTE]



I have used /*+leading(vcs)*/ in the query . Then also it's taking same time as before. is there any other way to resolve this issue?

Thank u

try outer join if necessory in the query

Hi Rao
Did you check the index that beeing used in the query? Please let me know

hi try this query. Kindly place the mandatory condition as the first statement in the where clause. And create the necessary index.
And most important thing is to replace the view. U have select only case size for particular item. So u have to add the particular column of the table in your query.

SELECT im.location ,
im.item ,
cs.case_size ,
im.ondate ,
im.offdate ,
mas.status
FROM case_size cs,
item im,
master mas,
warehouse wh
WHERE
mas.item_level = mas.trns_level

AND im.location =wh.warehose_no
AND mas.item = im.item
AND mas.item = cs.item
AND im.location_type = 'S'
and mas.pack_ind = 'N'
AND cs.supply_indicator = 'Y'
AND cs.country_indicator = 'Y'
AND mas.status = 'A'
AND mas.foreind = 'Y'
AND NVL(wh.CLOSE_DATE,'04-APR-9999')>=TO_DATE(&versDATE}, 'YYYYMMDD')