1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
| SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('93jydnwtwakq4',1));
SQL_ID 93jydnwtwakq4, child number 1
-------------------------------------
select count (*) from (SELECT distinct vw23.C1 PIE_ID,
(SELECT SUBSTR(ot00_variante, 1, 26) FROM SCHEMA1.T4 PIE
where PIE.C2 = vw23.C1) VAR_ID_26,
SUBSTR(fpm.C2, 1, 26) FPM_ID_26,
DECODE(C3,NULL,DECODE(C4,NULL,DECODE(V
W22_MESUREDATE,NULL, DECODE(C5,NULL,NULL,C5),C20),C4),C3)
VAL FROM SCHEMA1.T1 rm, SCHEMA1.T2 pv,
SCHEMA1.VUE1 vw23,
SCHEMA1.T3 fpm WHERE 1=1 --AND rm.C2 =
'" + strRmId + "'" AND rm.C2 like '110-1%' AND
pv.C6 = rm.C7 AND pv.C8 = 30 AND
pv.C9 = 29 AND vw23.C10 = pv.C11 AND
vw23.C12 IS NULL AND fpm.C2 = vw23.C13) WHERE
VAL IS NOT NULL ORDER BY PIE_ID
Plan hash value: 584149226
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 36301 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | | |
| 2 | VIEW | | 257K| 3774K| | 36301 (1)| 00:07:16 |
| 3 | TABLE ACCESS BY INDEX ROWID | T4 | 1 | 54 | | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | IDXI_T4 | 1 | | | 2 (0)| 00:00:01 |
| 5 | HASH UNIQUE | | 257K| 44M| | 36301 (1)| 00:07:16 |
| 6 | NESTED LOOPS | | 80 | 14400 | | 36300 (1)| 00:07:16 |
| 7 | NESTED LOOPS | | 80 | 14400 | | 36300 (1)| 00:07:16 |
| 8 | NESTED LOOPS | | 80 | 11840 | | 36195 (1)| 00:07:15 |
| 9 | NESTED LOOPS | | 80 | 11680 | | 36195 (1)| 00:07:15 |
| 10 | NESTED LOOPS | | 80 | 11520 | | 36195 (1)| 00:07:15 |
|* 11 | HASH JOIN | | 2335 | 230K| 2152K| 32247 (1)| 00:06:27 |
|* 12 | HASH JOIN | | 25546 | 1846K| | 31080 (1)| 00:06:13 |
| 13 | NESTED LOOPS | | 25546 | 848K| | 11294 (1)| 00:02:16 |
| 14 | NESTED LOOPS | | 25546 | 848K| | 11294 (1)| 00:02:16 |
| 15 | TABLE ACCESS BY INDEX ROWID | T2 | 2453 | 34342 | | 1476 (1)| 00:00:18 |
| 16 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 17 | BITMAP AND | | | | | | |
| 18 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 19 | INDEX RANGE SCAN | IDXT_T2 | 2453 | | | 215 (1)| 00:00:03 |
| 20 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 21 | SORT ORDER BY | | | | 1240K| | |
|* 22 | INDEX RANGE SCAN | IDX_T2 | 2453 | | | 275 (1)| 00:00:04 |
|* 23 | INDEX RANGE SCAN | IDX_T20_CC | 10 | | | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | T20 | 10 | 200 | | 4 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | T22 | 7113K| 271M| | 19736 (1)| 00:03:57 |
| 26 | INDEX FAST FULL SCAN | IDXO_T3 | 320K| 8454K| | 466 (1)| 00:00:06 |
|* 27 | TABLE ACCESS BY INDEX ROWID | T21 | 1 | 43 | | 2 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | IDXA_T21 | 1 | | | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | IDXA_T23 | 1 | 2 | | 0 (0)| |
|* 30 | INDEX UNIQUE SCAN | IDXA_T24 | 1 | 2 | | 0 (0)| |
|* 31 | INDEX UNIQUE SCAN | IDXA_T1 | 1 | | | 1 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 32 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PIE"."C2"=:B1)
11 - access("FPM"."C2"="A2"."C32")
12 - access("A0"."C6"="A2"."C7")
19 - access("PV"."C9"=29)
22 - access("PV"."C8"=30)
filter("PV"."C8"=30)
23 - access("A0"."ADRVIEW"="PV"."C7")
27 - filter((DECODE("A4"."C3",NULL,DECODE("A4"."C4",NULL,DECODE(INTERNAL_FUNCTION("A4
"."C20"),NULL,DECODE(TO_CHAR("A4"."C5"),NULL,NULL,TO_CHAR("A4"."C5")
),INT
ERNAL_FUNCTION("A4"."C20")),"A4"."C4"),"A4"."C3") IS NOT NULL AND
"A4"."C12" IS NULL))
28 - access("A0"."C30"="A4"."C30")
29 - access("A0"."C31"="A3"."C7")
30 - access("A0"."C8"="A1"."C7")
31 - access("PV"."C6"="RM"."C7")
32 - filter("RM"."C2" LIKE '110-1%')
Note
-----
- cardinality feedback used for this statement
82 ligne(s) sélectionnée(s).
SQL> spool off |
Partager