Help - Search - Members - Calendar
Full Version: Query ORACLE NOTE EXISTS CLAUSE
Oracle DBA Forums > Oracle > Oracle Forum
vgianni80
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" "" "" "" "" "" ""
Steve
It's a bit hard to read, but let me take a crack at this...do you use bitmap indexes on these tables?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.