I am using Oracle 9.2.0.6 on Windows 2000. I have the following query which was generated from Java. When I try to get an explain plan it is telling me that it is doing a full table scan on uln, even though tpfdd_runid_uln is the primary key. If I hint the index, the cost goes way up but the response time is infinitely faster. Can anyone explain why this is? I enclosed the files, they are html files saved with a .txt extension so that I could upload them.
Code:
SELECT uln_syn.poe_ald_cal AS grp0, SUM ( uln_syn.sumoftotal_stons ) tot0
FROM uln_syn
WHERE EXISTS (
SELECT ca_keys.comp_key
FROM ca_keys
WHERE ca_keys.comp_id = '0x080e00000107235e9e7f0a1428098124'
AND uln_syn.tpfdd_runid_uln = ca_keys.comp_key)
GROUP BY uln_syn.poe_ald_cal;
Your query will ful scan the uln_syn table and test the EXISTS condition for each row. Try this instead:
Code:
SELECT uln_syn.poe_ald_cal AS grp0, SUM ( uln_syn.sumoftotal_stons ) tot0
FROM uln_syn
WHERE tpfdd_runid_uln = (
SELECT ca_keys.comp_key
FROM ca_keys
WHERE ca_keys.comp_id = '0x080e00000107235e9e7f0a1428098124')
GROUP BY uln_syn.poe_ald_cal;
Your query will ful scan the uln_syn table and test the EXISTS condition for each row. Try this instead:
I had to add where rownum < 2 so that I only got back one row, but it works great. Thanks Dave!!!!!!!!
Code:
SELECT uln_syn.poe_ald_cal grp0, SUM ( uln_syn.sumoftotal_stons ) tot0
FROM uln_syn
WHERE tpfdd_runid_uln =
( SELECT ca_keys.comp_key
FROM ca_keys
WHERE ca_keys.comp_id = '0x080e0000010728e3ba1dc046fe878335'
AND ROWNUM < 2 )
GROUP BY uln_syn.poe_ald_cal;