Hello! I'm Italian and i'm sorry for my bad english. I'll try to expose my problem.
I Have two tables, a table of Anagrafica (contains all my users), and a table called CURVE_TESTATA, whit a foreing key with ANAGRAFICA on column called POD.
CURVE_TESTATA contains many values for POD, usually a record for month.
I want to see if exists any POD than not have records in CURVE_TESTATA for a month, my query work, but it's too slow. WHY????
This is an example of my query:
SELECT COUNT(DISTINCT POD) FROM ANAGRAFICA_SAP WHERE CODICE_DISTRIBUTORE='IT_E_001'
AND MRU LIKE 'MCCU%' AND NOT EXISTS (SELECT POD FROM CURVE_TESTATA
WHERE DISTRIBUTORE='IT_E_001' AND CURVE_TESTATA.POD=ANAGRAFICA_SAP.POD
AND EXTRACT(MONTH FROM CURVE_TESTATA.MESE_COMPETENZA)=7
AND EXTRACT(YEAR FROM CURVE_TESTATA.MESE_COMPETENZA)=2008)
and this is explane plan:
"Optimizer" "Cost" "Cardinality" "Bytes" "Partition Start" "Partition Stop" "Partition Id" "ACCESS PREDICATES" "FILTER PREDICATES"
"SELECT STATEMENT" "CHOOSE" "100.351956639065" "1" "30" "" "" "" "" ""
"SORT(GROUP BY)" "" "" "1" "30" "" "" "" "" ""
"FILTER" "" "" "" "" "" "" "" "" ""
"TABLE ACCESS(BY INDEX ROWID) EW_ACEA.ANAGRAFICA_SAP" "ANALYZED" "97.5390158767344" "11" "330" "" "" "" "" ""
"BITMAP CONVERSION(TO ROWIDS)" "" "" "" "" "" "" "" "" ""
"BITMAP AND" "" "" "" "" "" "" "" "" ""
"BITMAP CONVERSION(FROM ROWIDS)" "" "" "" "" "" "" "" "" ""
"INDEX(RANGE SCAN) EW_ACEA.ANAGRAFICA_SAP_INDEX4" "ANALYZED" "7" "" "" "" "" "" "" ""
"BITMAP CONVERSION(FROM ROWIDS)" "" "" "" "" "" "" "" "" ""
"SORT(ORDER BY)" "" "" "" "" "" "" "" "" ""
"INDEX(RANGE SCAN) EW_ACEA.ANAGRAFICA_SAP_INDEX5" "ANALYZED" "11" "" "" "" "" "" "" ""
"TABLE ACCESS(BY INDEX ROWID) EW_ACEA.CURVE_TESTATA" "ANALYZED" "2.81294076233022" "1" "32" "" "" "" "" ""
"BITMAP CONVERSION(TO ROWIDS)" "" "" "" "" "" "" "" "" ""
"BITMAP AND" "" "" "" "" "" "" "" "" ""
"BITMAP CONVERSION(FROM ROWIDS)" "" "" "" "" "" "" "" "" ""
"INDEX(RANGE SCAN) EW_ACEA.CURVE_TESTATA_POD" "ANALYZED" "1" "1" "" "" "" "" "" ""
"BITMAP CONVERSION(FROM ROWIDS)" "" "" "" "" "" "" "" "" ""
"INDEX(RANGE SCAN) EW_ACEA.CURVE_TESTATA_DISTRIB" "" "1" "1" "" "" "" "" "" ""