| Execution plan question [message #502257] |
Tue, 05 April 2011 08:49  |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
Hello,
I have a big query with the following structure:
SELECT <...>
FROM <...>
WHERE <...>
AND EXISTS
(
SELECT <...> /* 1st subselect*/
UNION ALL
SELECT <...> /* 2nd subselect*/
)
If I use only the first subselect, execution plan estimates the cost of the big query to be = 1466.
If I use only the second subselect, execution plan estimates the cost of the big query to be = 68.
In both cases there are no full table scans, and queries execute in a second.
However if I use both subselects, execution plan estimates the cost of big query to be = 84299 with lots of full table scans and unacceptable execution time.
I would expect execution plan to be with the cost of 1466+68=1534, why does it grow that big?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Execution plan question [message #502380 is a reply to message #502348] |
Wed, 06 April 2011 02:57  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Cost is estimated amount of work not time and that isn't independent accross the two as both include the outer select.
I would expect it to be closer to A+B than A^B though.
|
|
|
|
|
|
| Re: Execution plan question [message #502417 is a reply to message #502408] |
Wed, 06 April 2011 04:24 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
It'd help if you ran the explain plans in sqlplus as below then pasted the results directly into the thread in code tags. It'll be a lot easier for us to read, plus some people are allergic to attachments:
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
|
|
|
|
| Re: Execution plan question [message #502423 is a reply to message #502417] |
Wed, 06 April 2011 04:39 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Seems your two sub-selects are actually the same, difference being which column from the main query you match them to.
Try moving the sub-select into a with clause ie.
WITH tmp as (sub-select)
SELECT ....
FROM ....
WHERE (PAD.ID in (select ATASK_PADALINYS_ID from tmp)
OR KOMIS_GAVEJAS.ID in (select ATASK_PADALINYS_ID from tmp)
)
.....
Also sub-queries for with INs don't need distinct clauses, that'll just make them do an unecessary sort.
|
|
|
|
| Re: Execution plan question [message #502431 is a reply to message #502423] |
Wed, 06 April 2011 04:57 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Also when doing an exists query I'd generally do SELECT NULL rather than SELECT * or <columns> as it doesn't matter what you select so if you select null it'd increase chances of using an index if it makes sense. Won't make a difference in this case but I'd get into the habit.
Also you appear to be combining a group by and a distinct, that really shouldn't be necessary.
|
|
|
|
| Re: Execution plan question [message #502438 is a reply to message #502431] |
Wed, 06 April 2011 05:44 |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
Thanks for the good suggestions. Now the big query executes 3 times faster. However that strange effect described in my first post remains. If I use only
PAD.ID in (select ATASK_PADALINYS_ID from tmp)
big query executes in 1.1sec (1135 rows).
If I use only
KOMIS_GAVEJAS.ID in (select ATASK_PADALINYS_ID from tmp)
big query executes in 0.2sec (0 rows).
If I use both
(
PAD.ID in (select ATASK_PADALINYS_ID from tmp)
OR
KOMIS_GAVEJAS.ID in (select ATASK_PADALINYS_ID from tmp)
)
big query executes in 22sec (previously was ~60sec), which is still ~15times more than expected.
|
|
|
|
|
|
| Re: Execution plan question [message #502445 is a reply to message #502440] |
Wed, 06 April 2011 06:18 |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
The big query now looks like that:
SELECT
*
FROM
(
WITH TMP AS ( SELECT
GRAFO_BRIAUNOS.ATASK_PADALINYS_ID
FROM
DIS_VARTOTOJAS.TIES_ATASK_PADALINIAI_T GRAFO_BRIAUNOS,
DIS_VARTOTOJAS.PADALINYS_T GRAFO_VIRSUNES
WHERE
GRAFO_VIRSUNES.ID = GRAFO_BRIAUNOS.PADALINYS_ID
AND LEVEL > 0
START WITH
GRAFO_VIRSUNES.ID = 57425653
CONNECT BY NOCYCLE PRIOR
GRAFO_BRIAUNOS.ATASK_PADALINYS_ID = GRAFO_BRIAUNOS.PADALINYS_ID
UNION
SELECT
57425653
FROM
DUAL )
SELECT
POL.ID AS POL_ID,
PAD.ID AS PAD_ID,
PAD.REGIONAS_ID AS PAD_REGIONAS_ID,
KOMIS_GAVEJAS.ID,
CASE WHEN POR.STATUSAS = 1 AND POR.PERDAVIMO_DATA < SYSDATE THEN POR.PERDAVIMO_DATA ELSE NULL END AS PERDAVIMO_DATA
,CASE WHEN POR.STATUSAS = 1 AND POR.PERDAVIMO_DATA < SYSDATE THEN KOMIS_GAVEJAS.KODAS ELSE PAD.KODAS END AS KOMIS_GAVEJAS_KODAS
,PAD.KODAS AS PAD_KODAS
,MAX(CASE WHEN POR.STATUSAS = 1 AND POR.PERDAVIMO_DATA < SYSDATE THEN POR.PERDAVIMO_DATA ELSE NULL END) OVER (PARTITION BY POL.ID) AS MAX_PERDAVIMO_DATA
,CASE WHEN POR.STATUSAS = 1 AND POR.PERDAVIMO_DATA < SYSDATE THEN KOMIS_GAVEJAS.ID ELSE PAD.ID END AS KOMIS_GAVEJAS_ID
FROM
DR_OBJEKTAS.PARD_VIENETO_VER_T PVV,
DR_OBJEKTAS.PARD_VIENETAS_T PV,
KONTRAHENTAS.KONTRAHENTAS_T KONT,
DIS_VARTOTOJAS.PADALINYS_T PAD,
DIS_VARTOTOJAS.REGIONAS_T REG,
DIS_VARTOTOJAS.REGIONAS_T ATS,
VALIUTA.VALIUTA_T VL,
DIS_VARTOTOJAS.PADALINIO_TIPAS_T PT,
POLISAS.POLISO_POZYMIU_REGISTRAS_T POZ,
POLISAS.POLISAS_T POL,
POLISAS.APDRAUSTAS_PRODUKTAS_T APDR
,POLISAS.PORTFELIS_T POR
,POLISAS.PORTFELIO_APDR_PRODUKTAI_T PAP
, DIS_VARTOTOJAS.PADALINYS_T KOMIS_GAVEJAS
WHERE
POL.PARD_VIEN_VER_ID = PVV.ID AND
POL.PARD_VIEN_ID = PV.ID AND
POL.DRAUDEJAS_ID = KONT.ID AND
PAD.ID = POL.ATSAKINGAS_PAD_ID AND
PT.ID = PAD.PADALINIO_TIPAS_ID AND
ATS.ID = PAD.REGIONAS_ID AND
REG.ID = ATS.PARENT_ID AND
APDR.POLISAS_ID = POL.ID AND
VL.ID = APDR.VALIUTA_ID AND
POL.PARD_VIEN_VER_ID IN (
SELECT
PARD_VIEN_VER_ID
FROM
DR_OBJEKTAS.PVV_VEDEJAS_T VEDEJAI
WHERE
VEDEJAI.PADALINYS_ID = 57425653 ) AND
POL.ISRASYTAS >= TO_DATE('2011.01.01','YYYY.MM.DD') AND
POL.ISRASYTAS <= TO_DATE('2011.04.01','YYYY.MM.DD') AND
POL.STATUSAS = 1 AND
POL.POZYMIS_ID = POZ.ID(+)
AND KOMIS_GAVEJAS.ID(+) = POR.GAVEJAS_PAD_ID AND
POR.ID(+) = PAP.PORTFELIS_ID AND
PAP.AP_ID(+) = APDR.ID AND
(
PAD.ID in (select ATASK_PADALINYS_ID from tmp)
OR
KOMIS_GAVEJAS.ID in (select ATASK_PADALINYS_ID from tmp)
)
GROUP BY
POL.ID,
PAD.ID ,
PAD.REGIONAS_ID ,
KOMIS_GAVEJAS.ID,
POR.STATUSAS,
POR.PERDAVIMO_DATA,
KOMIS_GAVEJAS.KODAS ,
PAD.KODAS
)
WHERE
(PERDAVIMO_DATA = MAX_PERDAVIMO_DATA OR MAX_PERDAVIMO_DATA IS NULL) ;
Execution plan of the big query:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 836 | 89452 | | 47500 (1)|
| 1 | VIEW | | 836 | 89452 | | 47500 (1)|
| 2 | TEMP TABLE TRANSFORMATION | | | | | |
| 3 | LOAD AS SELECT | | | | | |
| 4 | SORT UNIQUE | | 4 | 63 | | 6 (67)|
| 5 | UNION-ALL | | | | | |
| 6 | FILTER | | | | | |
| 7 | CONNECT BY WITH FILTERING | | | | | |
| 8 | NESTED LOOPS | | 3 | 63 | | 3 (0)|
| 9 | INDEX UNIQUE SCAN | PK_PADALINYS | 1 | 7 | | 1 (0)|
| 10 | TABLE ACCESS BY INDEX ROWID | TIES_ATASK_PADALINIAI_T | 3 | 42 | | 2 (0)|
| 11 | INDEX RANGE SCAN | IX_TIES_ATASK_PADALINYS_ID | 3 | | | 1 (0)|
| 12 | NESTED LOOPS | | 3 | 63 | | 2 (0)|
| 13 | NESTED LOOPS | | | | | |
| 14 | CONNECT BY PUMP | | | | | |
| 15 | TABLE ACCESS BY INDEX ROWID| TIES_ATASK_PADALINIAI_T | 3 | 42 | | 2 (0)|
| 16 | INDEX RANGE SCAN | IX_TIES_ATASK_PADALINYS_ID | 3 | | | 1 (0)|
| 17 | INDEX UNIQUE SCAN | PK_PADALINYS | 1 | 7 | | 0 (0)|
| 18 | FAST DUAL | | 1 | | | 2 (0)|
| 19 | WINDOW BUFFER | | 836 | 166K| | 47494 (1)|
| 20 | SORT GROUP BY | | 836 | 166K| 1816K| 47494 (1)|
| 21 | FILTER | | | | | |
| 22 | HASH JOIN | | 8578 | 1708K| | 47109 (1)|
| 23 | VIEW | index$_join$_012 | 235 | 3290 | | 3 (34)|
| 24 | HASH JOIN | | | | | |
| 25 | INDEX FAST FULL SCAN | IX_REGIONAS_PARENT_ID | 235 | 3290 | | 1 (0)|
| 26 | INDEX FAST FULL SCAN | PK_REGIONAS | 235 | 3290 | | 1 (0)|
| 27 | HASH JOIN | | 8823 | 1637K| | 47106 (1)|
| 28 | TABLE ACCESS FULL | PVV_VEDEJAS_T | 136 | 1904 | | 43 (0)|
| 29 | HASH JOIN RIGHT OUTER | | 9879 | 1697K| | 47063 (1)|
| 30 | TABLE ACCESS FULL | PADALINYS_T | 8501 | 207K| | 24 (0)|
| 31 | HASH JOIN | | 101K| 14M| | 47038 (1)|
| 32 | TABLE ACCESS FULL | PADALINYS_T | 8501 | 323K| | 24 (0)|
| 33 | HASH JOIN RIGHT OUTER | | 101K| 10M| | 47013 (1)|
| 34 | TABLE ACCESS FULL | PORTFELIS_T | 1 | 25 | | 3 (0)|
| 35 | HASH JOIN RIGHT OUTER | | 101K| 8608K| | 47009 (1)|
| 36 | VIEW | index$_join$_019 | 10 | 140 | | 3 (34)|
| 37 | HASH JOIN | | | | | |
| 38 | INDEX FAST FULL SCAN | IXFK_PORTFELIO_AP_ID | 10 | 140 | | 1 (0)|
| 39 | INDEX FAST FULL SCAN | IXFK_PORTFELIO_ID | 10 | 140 | | 1 (0)|
| 40 | NESTED LOOPS OUTER | | 101K| 7223K| | 47006 (1)|
| 41 | HASH JOIN | | 101K| 6530K| 5224K| 47006 (1)|
| 42 | TABLE ACCESS FULL | POLISAS_T | 87630 | 4193K| | 27483 (1)|
| 43 | TABLE ACCESS FULL | APDRAUSTAS_PRODUKTAS_T | 3304K| 53M| | 14718 (1)|
| 44 | INDEX UNIQUE SCAN | PK_POLISO_POZYMIU_REGISTRAS | 1 | 7 | | 0 (0)|
| 45 | VIEW | | 4 | 52 | | 2 (0)|
| 46 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6615_5752FD0A | 4 | 52 | | 2 (0)|
| 47 | VIEW | | 4 | 52 | | 2 (0)|
| 48 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6615_5752FD0A | 4 | 52 | | 2 (0)|
-----------------------------------------------------------------------------------------------------------------
Execution plan of query using only PAD.ID in (select ATASK_PADALINYS_ID from tmp):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41 | 4387 | 1572 (1)|
| 1 | VIEW | | 41 | 4387 | 1572 (1)|
| 2 | WINDOW BUFFER | | 41 | 8897 | 1572 (1)|
| 3 | SORT GROUP BY | | 41 | 8897 | 1572 (1)|
| 4 | HASH JOIN OUTER | | 41 | 8897 | 1571 (1)|
| 5 | HASH JOIN OUTER | | 41 | 7872 | 1547 (1)|
| 6 | NESTED LOOPS OUTER | | 41 | 6847 | 1543 (1)|
| 7 | NESTED LOOPS | | 41 | 6273 | 1542 (1)|
| 8 | HASH JOIN | | 36 | 4896 | 1434 (1)|
| 9 | NESTED LOOPS OUTER | | 40 | 4880 | 1390 (1)|
| 10 | NESTED LOOPS | | 40 | 4600 | 1390 (1)|
| 11 | HASH JOIN | | 4 | 264 | 13 (24)|
| 12 | NESTED LOOPS | | 4 | 208 | 10 (20)|
| 13 | VIEW | | 4 | 52 | 6 (34)|
| 14 | SORT UNIQUE | | 4 | 63 | 6 (67)|
| 15 | UNION-ALL | | | | |
| 16 | FILTER | | | | |
| 17 | CONNECT BY WITH FILTERING | | | | |
| 18 | NESTED LOOPS | | 3 | 63 | 3 (0)|
| 19 | INDEX UNIQUE SCAN | PK_PADALINYS | 1 | 7 | 1 (0)|
| 20 | TABLE ACCESS BY INDEX ROWID | TIES_ATASK_PADALINIAI_T | 3 | 42 | 2 (0)|
| 21 | INDEX RANGE SCAN | IX_TIES_ATASK_PADALINYS_ID | 3 | | 1 (0)|
| 22 | NESTED LOOPS | | 3 | 63 | 2 (0)|
| 23 | NESTED LOOPS | | | | |
| 24 | CONNECT BY PUMP | | | | |
| 25 | TABLE ACCESS BY INDEX ROWID| TIES_ATASK_PADALINIAI_T | 3 | 42 | 2 (0)|
| 26 | INDEX RANGE SCAN | IX_TIES_ATASK_PADALINYS_ID | 3 | | 1 (0)|
| 27 | INDEX UNIQUE SCAN | PK_PADALINYS | 1 | 7 | 0 (0)|
| 28 | FAST DUAL | | 1 | | 2 (0)|
| 29 | TABLE ACCESS BY INDEX ROWID | PADALINYS_T | 1 | 39 | 1 (0)|
| 30 | INDEX UNIQUE SCAN | PK_PADALINYS | 1 | | 0 (0)|
| 31 | VIEW | index$_join$_012 | 235 | 3290 | 3 (34)|
| 32 | HASH JOIN | | | | |
| 33 | INDEX FAST FULL SCAN | IX_REGIONAS_PARENT_ID | 235 | 3290 | 1 (0)|
| 34 | INDEX FAST FULL SCAN | PK_REGIONAS | 235 | 3290 | 1 (0)|
| 35 | TABLE ACCESS BY INDEX ROWID | POLISAS_T | 10 | 490 | 1356 (0)|
| 36 | INDEX RANGE SCAN | IX_POL_ATSAK_PAD | 1939 | | 7 (0)|
| 37 | INDEX UNIQUE SCAN | PK_POLISO_POZYMIU_REGISTRAS | 1 | 7 | 0 (0)|
| 38 | TABLE ACCESS FULL | PVV_VEDEJAS_T | 136 | 1904 | 43 (0)|
| 39 | TABLE ACCESS BY INDEX ROWID | APDRAUSTAS_PRODUKTAS_T | 1 | 17 | 3 (0)|
| 40 | INDEX RANGE SCAN | IX_AP_POL | 1 | | 2 (0)|
| 41 | TABLE ACCESS BY INDEX ROWID | PORTFELIO_APDR_PRODUKTAI_T | 1 | 14 | 1 (0)|
| 42 | INDEX RANGE SCAN | IXFK_PORTFELIO_AP_ID | 1 | | 0 (0)|
| 43 | TABLE ACCESS FULL | PORTFELIS_T | 1 | 25 | 3 (0)|
| 44 | TABLE ACCESS FULL | PADALINYS_T | 8501 | 207K| 24 (0)|
-------------------------------------------------------------------------------------------------------------------
ExeExecution plan of query using only KOMIS_GAVEJAS.ID in (select ATASK_PADALINYS_ID from tmp):
-------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 62 (7)|
| 1 | VIEW | | 1 | 107 | 62 (7)|
| 2 | WINDOW BUFFER | | 1 | 217 | 62 (7)|
| 3 | SORT GROUP BY | | 1 | 217 | 62 (7)|
| 4 | HASH JOIN | | 1 | 217 | 61 (5)|
| 5 | NESTED LOOPS | | 1 | 203 | 18 (17)|
| 6 | NESTED LOOPS | | 1 | 189 | 17 (18)|
| 7 | NESTED LOOPS OUTER | | 1 | 150 | 16 (19)|
| 8 | NESTED LOOPS | | 1 | 143 | 16 (19)|
| 9 | NESTED LOOPS | | 1 | 94 | 14 (22)|
| 10 | NESTED LOOPS | | 1 | 77 | 12 (25)|
| 11 | HASH JOIN | | 1 | 63 | 11 (28)|
| 12 | NESTED LOOPS | | 1 | 50 | 4 (0)|
| 13 | TABLE ACCESS FULL | PORTFELIS_T | 1 | 25 | 3 (0)|
| 14 | TABLE ACCESS BY INDEX ROWID | PADALINYS_T | 1 | 25 | 1 (0)|
| 15 | INDEX UNIQUE SCAN | PK_PADALINYS | 1 | | 0 (0)|
| 16 | VIEW | | 4 | 52 | 6 (34)|
| 17 | SORT UNIQUE | | 4 | 63 | 6 (67)|
| 18 | UNION-ALL | | | | |
| 19 | FILTER | | | | |
| 20 | CONNECT BY WITH FILTERING | | | | |
| 21 | NESTED LOOPS | | 3 | 63 | 3 (0)|
| 22 | INDEX UNIQUE SCAN | PK_PADALINYS | 1 | 7 | 1 (0)|
| 23 | TABLE ACCESS BY INDEX ROWID | TIES_ATASK_PADALINIAI_T | 3 | 42 | 2 (0)|
| 24 | INDEX RANGE SCAN | IX_TIES_ATASK_PADALINYS_ID | 3 | | 1 (0)|
| 25 | NESTED LOOPS | | 3 | 63 | 2 (0)|
| 26 | NESTED LOOPS | | | | |
| 27 | CONNECT BY PUMP | | | | |
| 28 | TABLE ACCESS BY INDEX ROWID| TIES_ATASK_PADALINIAI_T | 3 | 42 | 2 (0)|
| 29 | INDEX RANGE SCAN | IX_TIES_ATASK_PADALINYS_ID | 3 | | 1 (0)|
| 30 | INDEX UNIQUE SCAN | PK_PADALINYS | 1 | 7 | 0 (0)|
| 31 | FAST DUAL | | 1 | | 2 (0)|
| 32 | TABLE ACCESS BY INDEX ROWID | PORTFELIO_APDR_PRODUKTAI_T | 10 | 140 | 1 (0)|
| 33 | INDEX RANGE SCAN | IXFK_PORTFELIO_ID | 10 | | 0 (0)|
| 34 | TABLE ACCESS BY INDEX ROWID | APDRAUSTAS_PRODUKTAS_T | 1 | 17 | 2 (0)|
| 35 | INDEX UNIQUE SCAN | PK_APDR_PROD | 1 | | 1 (0)|
| 36 | TABLE ACCESS BY INDEX ROWID | POLISAS_T | 1 | 49 | 2 (0)|
| 37 | INDEX UNIQUE SCAN | PK_POLISAS | 1 | | 1 (0)|
| 38 | INDEX UNIQUE SCAN | PK_POLISO_POZYMIU_REGISTRAS | 1 | 7 | 0 (0)|
| 39 | TABLE ACCESS BY INDEX ROWID | PADALINYS_T | 1 | 39 | 1 (0)|
| 40 | INDEX UNIQUE SCAN | PK_PADALINYS | 1 | | 0 (0)|
| 41 | TABLE ACCESS BY INDEX ROWID | REGIONAS_T | 1 | 14 | 1 (0)|
| 42 | INDEX UNIQUE SCAN | PK_REGIONAS | 1 | | 0 (0)|
| 43 | TABLE ACCESS FULL | PVV_VEDEJAS_T | 136 | 1904 | 43 (0)|
------------------------------------------------------------------------------------------------------------------ [Updated on: Wed, 06 April 2011 06:24] Report message to a moderator |
|
|
|
| Re: Execution plan question [message #502450 is a reply to message #502445] |
Wed, 06 April 2011 07:10 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
What indexes have you got on POLISAS_T and APDRAUSTAS_PRODUKTAS_T?
You might also want to try unioning your two test selects instead of combining them with the or.
|
|
|
|
| Re: Execution plan question [message #502456 is a reply to message #502450] |
Wed, 06 April 2011 08:08 |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
SELECT AIC.INDEX_NAME, AIC.COLUMN_NAME FROM ALL_IND_COLUMNS AIC WHERE AIC.TABLE_NAME = 'POLISAS_T';
INDEX_NAME COLUMN_NAME
IX_PLANNED_ATSTATYMO_DATA PLANNED_ATSTATYMO_DATA_LAIKAS
IX_POLISAS_ATSTATYMAS_ID POLISAS_ATSTATYMAS_ID
IX_ATSTATYMO_DATA ATSTATYMO_DATA
IX_POL_APSKAITOS_DATA APSKAITOS_DATA
IX_POL_NUTR_APSKAITOS_DATA NUTR_SUST_APSK_DATA
PK_POLISAS ID
IXU_FIN_ID FIN_ID
IX_POL_REMIAMAS REMIAMAS
IX_POLISAS_MOKETOJAS_ID MOKETOJAS_ID
IX_POL_PIRMO_KONTR_PRADZIA_AGR PIRMO_KONTRAKTO_PRADZIA_AGR
IX_POL_NUM_SERIJA NUMERIS
IX_POL_NUM_SERIJA SERIJA
IX_POL_ATSAK_PAD ATSAKINGAS_PAD_ID
IX_POL_BAIGIASI BAIGIASI
IX_POL_DRAUDEJAS DRAUDEJAS_ID
IX_POL_ISRASYTAS ISRASYTAS
IX_POL_NUTRAUKTA NUTRAUKTA
IX_POL_PRASIDEDA PRASIDEDA
IX_POL_PV PARD_VIEN_ID
IX_POL_PVV PARD_VIEN_VER_ID
IX_POL_DOK DOKUMENTAS_ID
IX_NUMERIS NUMERIS
IX_POLISAS_PAPILDYTO_POL_ID PAPILDYTO_POL_ID
IX_PERRASYMO_ID PERRASYMO_ID
IX_PAP_REGISTRAS_ID PAP_REGISTRAS_ID
IX_POLISAS_SUNUS_ID SUNUS_ID
IX_PRATESIMAS_REG TEVAS_ID
POL_ISRASYTAS_PERIOD SYS_NC00063$
IX_ILGALAIKIS_POLISAS ILGALAIKIS_POLISAS_ID
IX_POLISO_XML_ID POLISO_XML_ID
IX_POLISO_NUTRAUKIMO_XML_ID POLISO_NUTRAUKIMO_XML_ID
SELECT AIC.INDEX_NAME, AIC.COLUMN_NAME FROM ALL_IND_COLUMNS AIC WHERE AIC.TABLE_NAME = 'APDRAUSTAS_PRODUKTAS_T';
INDEX_NAME COLUMN_NAME
IXPK_AP_PARD_VIENETAS PARD_VIENETAS_ID
IXPK_ATSAKINGAS_PAD ATSAKINGAS_PAD_ID
IX_ATNAUJINIMAS_VAIKAS ATNAUJINIMAS_VAIKAS_ID
IX_ATNAUJINIMAS_TEVAS ATNAUJINIMAS_TEVAS_ID
IXU_APDR_PROD DR_PRODUKTAS_ID
IXU_APDR_PROD POLISAS_ID
IX_AP_DR_PROD DR_PRODUKTAS_ID
IX_AP_KUMULIS KUMULIS_ID
IX_AP_POL POLISAS_ID
IX_AP_VALIUTA VALIUTA_ID
IX_111 DR_PRODUKTAS_ID
IX_111 ATSAKINGAS_PAD_ID
IX_111 ID
IX_APDR_PROD_OBJ_ID DRAUDIMO_OBJEKTAS_ID
IX_AP_DR_PR_VER DR_PRODUKTO_VER_ID
IX_133 PRASIDEDA
IX_134 BAIGIASI
IX_135 NUTRAUKTA
PK_APDR_PROD ID
IX_PROD_VER_PAP_INF_ID PROD_VER_PAP_INF_ID
If I replace
(
PAD.ID in (select ATASK_PADALINYS_ID from tmp)
OR
KOMIS_GAVEJAS.ID in (select ATASK_PADALINYS_ID from tmp)
)
with
(
EXISTS (select ATASK_PADALINYS_ID from tmp WHERE ATASK_PADALINYS_ID = PAD.ID
UNION ALL
select ATASK_PADALINYS_ID from tmp WHERE ATASK_PADALINYS_ID = KOMIS_GAVEJAS.ID)
)
it makes no changes to execution plan.
Interestingly if I copy big query and union-all it with itself, and leave one OR condition in each of them, then I get the desired result. However copy/pasting is a weird way to optimize queries I guess. 
[Updated on: Wed, 06 April 2011 08:21] Report message to a moderator |
|
|
|
| Re: Execution plan question [message #502461 is a reply to message #502456] |
Wed, 06 April 2011 08:29 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The index list for POLISAS_T looks like the source of the problem. Composite indexes can be really useful for complex queries.
Try putting a single index accross the following columns:
PARD_VIEN_VER_ID
PARD_VIEN_ID
DRAUDEJAS_ID
ATSAKINGAS_PAD_ID
ID
ISRASYTAS
STATUSAS
POZYMIS_ID
If any of them are particular unselective (small number of distinct values) you can try leaving them out.
Buchas wrote on Wed, 06 April 2011 14:08
Interestingly if I copy big query and union-all it with itself, and leave one OR condition in each of them, then I get the desired result. However copy/pasting is a weird way to optimize queries I guess.
That's what I meant for you to try. If you look at the plans for the sub-set queries they're using different indexes/join orders/access methods. As such they can't be combined. And with the considerable difference it means it's likely that what is efficient for one is really quite painful for the other. Hence oracle went with a completely different plan again for the big query.
2 ways to get round that:
1) Do the above union all so each section can use it's own access path.
2) Add a new index or two so that a better path becomes available.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
