query tunning

[learning_bee]

for some reason the PLANT table was doing full table scan, could someone please give me some advises????

[learning_bee]

below is the plan for this query:



(1) SELECT STATEMENT CHOOSE
Est. Rows: 1 Cost: 516
FILTER
(23) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 504
(19) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 500
(16) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 498
(13) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 496
(10) VIEW (Embedded SQL)
Est. Rows: 1 Cost: 496
(9) NESTED LOOPS OUTER
Est. Rows: 1 Cost: 496
(6) VIEW (Embedded SQL)
Est. Rows: 1 Cost: 493
(5) HASH JOIN
Est. Rows: 1 Cost: 493
(3) TABLE ACCESS BY INDEX ROWID HR.LOAD [Analyzed]
(3) Blocks: 3,708 Est. Rows: 550 of 85,026 Cost: 62
Tablespace: DATA
(2) NON-UNIQUE INDEX RANGE SCAN HR.IDX_RUN_ID2 [Analyzed]
Est. Rows: 1,232 Cost: 5
(4) TABLE ACCESS FULL HR.PLANT [Analyzed]
(4) Blocks: 2,764 Est. Rows: 165,986 of 165,986 Cost: 421
Tablespace: DATA
...................................
.....................