plsql effiency of exists

[kellytim]

I am having trouble making my sql statement any more effient. I used the explain plan to show the cost of my select and I found that I am doing a table access full. I have to access a database of several million records and this is too costly. My select is written using Dynamic SQL where the fs, ft, and p1t tables could be any table in the database and I am looping through all my previously selected values of state_country_code. I don't understand why I am not using the index where the sql does the Table Access Full. Here is my SQL and explain plan:

SELECT ft.minor_area_code_373 , COUNT(*)
FROM fe_sfaf fs, fe_sfaf ft
WHERE (EXISTS (SELECT * FROM fe_sfaf p1t
WHERE p1t.state_country_code_300='MD'
AND p1t.assignment_id = ft.assignment_id AND
p1t.machine_id = ft.machine_id)) AND
(fs.record_source = 'FRRS' AND fs.record_type = 'PA' AND
fs.assignment_id = ft.assignment_id AND
fs.machine_id = ft.machine_id)
AND ft.minor_area_code_373
IS NOT NULL GROUP BY ft.minor_area_code_373;

Cost Bytes
SELECT STATEMENT, GOAL = CHOOSE 201 1 31
SORT GROUP BY 201 1 31
NESTED LOOPS 199 1 31
HASH JOIN SEMI 198 1 18
TABLE ACCESS FULL SPECTRUM21 FE_SFAF 186 26435 211480
INDEX RANGE SCAN SPECTRUM21 FE_SFAF_300 6 315 3150 1
TABLE ACCESS BY INDEX ROWID SPECTRUM21 FE_SFAF 1 1 13
INDEX UNIQUE SCAN SPECTRUM21 FE_SFAF_PK 1 2


I tried using IN instead of EXISTS and that made the select more expensive. Any help would be greatly appreciated.

 

[sem]

Gather statistics and check the plan again

Regards, Dima

http://seminihin.narod.ru

 

[carp]

Have you tried eliminating the correlated subquery and just running this as a single query joining the three tables? If they don't already exist, you could then add indexes on the join columns.