Hi everyone!
I have select that is using in create materialized view statement. Columns id,unitg,unitm,type,rtyp are indexed. I create complex index on (type,rtyp).
SELECT DISTINCT u1.id as unitg, u2.id as unitm
FROM unitrelflat f, units u1, units u2
WHERE f.unitg=u1.id AND
f.unitm=u2.id AND
( (u1.type = 9 and u1.rtyp = 2 and u2.type = 7 and u2.rtyp in (2,4)) OR
(u1.type in (9,14,19,26) and u1.rtyp = 4 and u2.type = u1.type and u2.rtyp = 4 and f.dist > 0) OR
(u1.type in (19,14,26) and u1.rtyp = 4 and u2.type in (15,13,25) and u2.rtyp in (0,4)) OR
(u1.type in (9,19) and u1.rtyp = 4 and u2.type in (8,7,6,13,15) and u2.rtyp = 4) OR
(u1.type in (7,8,13,14,15,19,25,26) and u1.rtyp = 4 and u2.type = u1.type and u2.rtyp = 0 and f.dist = 1) OR
(u1.type = 7 and u1.rtyp=4 and u2.type = 8 and u2.rtyp = 0 and f.dist = 1)) ;
Plan:SQL> explain plan for
SELECT DISTINCT u1.id as unitg, u2.id as unitm
FROM unitrelflat f, units u1, units u2
WHERE f.unitg=u1.id AND
f.unitm=u2.id AND
( (u1.type = 9 and u1.rtyp = 2 and u2.type = 7 and u2.rtyp in (2,4)) OR
(u1.type in (9,14,19,26) and u1.rtyp = 4 and u2.type = u1.type and u2.rtyp = 4 and f.dist > 0) OR
(u1.type in (19,14,26) and u1.rtyp = 4 and u2.type in (15,13,25) and u2.rtyp in (0,4)) OR
(u1.type in (9,19) and u1.rtyp = 4 and u2.type in (8,7,6,13,15) and u2.rtyp = 4) OR
(u1.type in (7,8,13,14,15,19,25,26) and u1.rtyp = 4 and u2.type = u1.type and u2.rtyp = 0 and f.dist = 1) OR
11 (u1.type = 7 and u1.rtyp=4 and u2.type = 8 and u2.rtyp = 0 and f.dist = 1)) ;
Explained.
Elapsed: 00:00:00.04
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3415216610
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58857 | 2241K| | 19451 (1)| 00:03:54 |
| 1 | HASH UNIQUE | | 58857 | 2241K| 6008K| 19451 (1)| 00:03:54 |
|* 2 | HASH JOIN | | 58857 | 2241K| 17M| 18856 (1)| 00:03:47 |
| 3 | TABLE ACCESS FULL | UNITS | 809K| 8693K| | 2960 (1)| 00:00:36 |
|* 4 | HASH JOIN | | 2177K| 58M| 17M| 10858 (2)| 00:02:11 |
| 5 | TABLE ACCESS FULL| UNITS | 809K| 8693K| | 2960 (1)| 00:00:36 |
| 6 | TABLE ACCESS FULL| UNITRELFLAT | 2786K| 45M| | 3149 (2)| 00:00:38 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("F"."UNITM"="U2"."ID")
filter("U1"."TYPE"=9 AND "U1"."RTYP"=2 AND "U2"."TYPE"=7 AND ("U2"."RTYP"=2
OR "U2"."RTYP"=4) OR ("U1"."TYPE"=9 OR "U1"."TYPE"=14 OR "U1"."TYPE"=19 OR
"U1"."TYPE"=26) AND "U1"."RTYP"=4 AND "U2"."TYPE"="U1"."TYPE" AND "U2"."RTYP"=4 AND
"F"."DIST">0 OR ("U1"."TYPE"=14 OR "U1"."TYPE"=19 OR "U1"."TYPE"=26) AND
"U1"."RTYP"=4 AND ("U2"."TYPE"=13 OR "U2"."TYPE"=15 OR "U2"."TYPE"=25) AND
("U2"."RTYP"=0 OR "U2"."RTYP"=4) OR ("U1"."TYPE"=9 OR "U1"."TYPE"=19) AND
"U1"."RTYP"=4 AND ("U2"."TYPE"=6 OR "U2"."TYPE"=7 OR "U2"."TYPE"=8 OR
"U2"."TYPE"=13 OR "U2"."TYPE"=15) AND "U2"."RTYP"=4 OR ("U1"."TYPE"=7 OR
"U1"."TYPE"=8 OR "U1"."TYPE"=13 OR "U1"."TYPE"=14 OR "U1"."TYPE"=15 OR
"U1"."TYPE"=19 OR "U1"."TYPE"=25 OR "U1"."TYPE"=26) AND "U1"."RTYP"=4 AND
"U2"."TYPE"="U1"."TYPE" AND "U2"."RTYP"=0 AND "F"."DIST"=1 OR "U1"."TYPE"=7 AND
"U1"."RTYP"=4 AND "U2"."TYPE"=8 AND "U2"."RTYP"=0 AND "F"."DIST"=1)
4 - access("F"."UNITG"="U1"."ID")
31 rows selected.
Elapsed: 00:00:00.01
Plan with use_nl hint:
SQL> explain plan for
SELECT /*+ use_nl( f u1 f u2)*/ DISTINCT u1.id as unitg, u2.id as unitm
FROM unitrelflat f, units u1, units u2
WHERE f.unitg=u1.id AND
f.unitm=u2.id AND
( (u1.type = 9 and u1.rtyp = 2 and u2.type = 7 and u2.rtyp in (2,4)) OR
(u1.type in (9,14,19,26) and u1.rtyp = 4 and u2.type = u1.type and u2.rtyp = 4 and f.dist > 0) OR
(u1.type in (19,14,26) and u1.rtyp = 4 and u2.type in (15,13,25) and u2.rtyp in (0,4)) OR
(u1.type in (9,19) and u1.rtyp = 4 and u2.type in (8,7,6,13,15) and u2.rtyp = 4) OR
(u1.type in (7,8,13,14,15,19,25,26) and u1.rtyp = 4 and u2.type = u1.type and u2.rtyp = 0 and f.dist = 1) OR
11 (u1.type = 7 and u1.rtyp=4 and u2.type = 8 and u2.rtyp = 0 and f.dist = 1)) ;
Explained.
Elapsed: 00:00:00.16
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 717670011
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58857 | 2241K| | 266K (1)| 00:53:18 |
| 1 | HASH UNIQUE | | 58857 | 2241K| 6368K| 266K (1)| 00:53:18 |
| 2 | CONCATENATION | | | | | | |
| 3 | NESTED LOOPS | | 1 | 39 | | 16 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 2 | 56 | | 12 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | UNITS | 1 | 11 | | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | UNITS_TR_COMPLEX | 1 | | | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | UNITRELFLAT | 2 | 34 | | 8 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | UNITRELFLAT_UNITM | 5 | | | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | UNITS | 1 | 11 | | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | UNITS_UI_ID_TYPE | 1 | | | 1 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1083 | 42237 | | 13367 (1)| 00:02:41 |
| 12 | NESTED LOOPS | | 2168 | 60704 | | 9029 (1)| 00:01:49 |
| 13 | TABLE ACCESS BY INDEX ROWID | UNITS | 1124 | 12364 | | 32 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | UNITS_I_RTYP | 1124 | | | 4 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | UNITRELFLAT | 2 | 34 | | 8 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | UNITRELFLAT_UNITM | 5 | | | 2 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | UNITS | 1 | 11 | | 2 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | UNITS_UI_ID_TYPE | 1 | | | 1 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 42060 | 1601K| | 131K (1)| 00:26:20 |
| 20 | NESTED LOOPS | | 42070 | 1150K| | 47467 (1)| 00:09:30 |
| 21 | INLIST ITERATOR | | | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID| UNITS | 15633 | 167K| | 544 (1)| 00:00:07 |
|* 23 | INDEX RANGE SCAN | UNITS_TR_COMPLEX | 15633 | | | 40 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | UNITRELFLAT | 3 | 51 | | 3 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | UNITRELFLAT_UNITG | 4 | | | 2 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | UNITS | 1 | 11 | | 2 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | UNITS_PK | 1 | | | 1 (0)| 00:00:01 |
| 28 | NESTED LOOPS | | 616 | 24024 | | 10174 (1)| 00:02:03 |
| 29 | NESTED LOOPS | | 3250 | 91000 | | 3670 (1)| 00:00:45 |
| 30 | INLIST ITERATOR | | | | | | |
| 31 | TABLE ACCESS BY INDEX ROWID| UNITS | 1208 | 13288 | | 44 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | UNITS_TR_COMPLEX | 1208 | | | 5 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | UNITRELFLAT | 3 | 51 | | 3 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | UNITRELFLAT_UNITG | 4 | | | 2 (0)| 00:00:01 |
|* 35 | TABLE ACCESS BY INDEX ROWID | UNITS | 1 | 11 | | 2 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | UNITS_PK | 1 | | | 1 (0)| 00:00:01 |
| 37 | NESTED LOOPS | | 21146 | 805K| | 108K (1)| 00:21:37 |
| 38 | NESTED LOOPS | | 30179 | 825K| | 47673 (1)| 00:09:33 |
| 39 | INLIST ITERATOR | | | | | | |
| 40 | TABLE ACCESS BY INDEX ROWID| UNITS | 15700 | 168K| | 547 (0)| 00:00:07 |
|* 41 | INDEX RANGE SCAN | UNITS_TR_COMPLEX | 15700 | | | 42 (0)| 00:00:01 |
|* 42 | TABLE ACCESS BY INDEX ROWID | UNITRELFLAT | 2 | 34 | | 3 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | UNITRELFLAT_UI_UNITG_UNITM | 4 | | | 2 (0)| 00:00:01 |
|* 44 | TABLE ACCESS BY INDEX ROWID | UNITS | 1 | 11 | | 2 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | UNITS_UI_ID_TYPE | 1 | | | 1 (0)| 00:00:01 |
| 46 | NESTED LOOPS | | 992 | 38688 | | 2537 (1)| 00:00:31 |
| 47 | NESTED LOOPS | | 810 | 22680 | | 916 (0)| 00:00:11 |
| 48 | TABLE ACCESS BY INDEX ROWID | UNITS | 301 | 3311 | | 13 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | UNITS_TR_COMPLEX | 301 | | | 3 (0)| 00:00:01 |
| 50 | TABLE ACCESS BY INDEX ROWID | UNITRELFLAT | 3 | 51 | | 3 (0)| 00:00:01 |
|* 51 | INDEX RANGE SCAN | UNITRELFLAT_UNITG | 4 | | | 2 (0)| 00:00:01 |
|* 52 | TABLE ACCESS BY INDEX ROWID | UNITS | 1 | 11 | | 2 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | UNITS_UI_ID_TYPE | 1 | | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("U2"."TYPE"=8 AND "U2"."RTYP"=0)
7 - filter("F"."DIST"=1)
8 - access("F"."UNITM"="U2"."ID")
9 - filter("U1"."RTYP"=4)
10 - access("F"."UNITG"="U1"."ID" AND "U1"."TYPE"=7)
14 - access("U2"."RTYP"=0)
15 - filter("F"."DIST"=1)
16 - access("F"."UNITM"="U2"."ID")
17 - filter("U1"."RTYP"=4 AND (LNNVL("U1"."TYPE"=7) OR LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"=8) OR
LNNVL("U2"."RTYP"=0) OR LNNVL("F"."DIST"=1)))
18 - access("F"."UNITG"="U1"."ID" AND "U2"."TYPE"="U1"."TYPE")
filter("U1"."TYPE"=7 OR "U1"."TYPE"=8 OR "U1"."TYPE"=13 OR "U1"."TYPE"=14 OR "U1"."TYPE"=15 OR
"U1"."TYPE"=19 OR "U1"."TYPE"=25 OR "U1"."TYPE"=26)
23 - access(("U1"."TYPE"=9 OR "U1"."TYPE"=19) AND "U1"."RTYP"=4)
25 - access("F"."UNITG"="U1"."ID")
26 - filter("U2"."RTYP"=4 AND ("U2"."TYPE"=6 OR "U2"."TYPE"=7 OR "U2"."TYPE"=8 OR "U2"."TYPE"=13 OR
"U2"."TYPE"=15) AND (LNNVL("U1"."TYPE"=7) AND LNNVL("U1"."TYPE"=8) AND LNNVL("U1"."TYPE"=13) AND
LNNVL("U1"."TYPE"=14) AND LNNVL("U1"."TYPE"=15) AND LNNVL("U1"."TYPE"=19) AND LNNVL("U1"."TYPE"=25) AND
LNNVL("U1"."TYPE"=26) OR LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"="U1"."TYPE") OR LNNVL("U2"."RTYP"=0) OR
LNNVL("F"."DIST"=1)) AND (LNNVL("U1"."TYPE"=7) OR LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"=8) OR
LNNVL("U2"."RTYP"=0) OR LNNVL("F"."DIST"=1)))
27 - access("F"."UNITM"="U2"."ID")
32 - access(("U1"."TYPE"=14 OR "U1"."TYPE"=19 OR "U1"."TYPE"=26) AND "U1"."RTYP"=4)
34 - access("F"."UNITG"="U1"."ID")
35 - filter(("U2"."TYPE"=13 OR "U2"."TYPE"=15 OR "U2"."TYPE"=25) AND ("U2"."RTYP"=0 OR "U2"."RTYP"=4) AND
(LNNVL("U1"."TYPE"=9) AND LNNVL("U1"."TYPE"=19) OR LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"=6) AND
LNNVL("U2"."TYPE"=7) AND LNNVL("U2"."TYPE"=8) AND LNNVL("U2"."TYPE"=13) AND LNNVL("U2"."TYPE"=15) OR
LNNVL("U2"."RTYP"=4)) AND (LNNVL("U1"."TYPE"=7) AND LNNVL("U1"."TYPE"=8) AND LNNVL("U1"."TYPE"=13) AND
LNNVL("U1"."TYPE"=14) AND LNNVL("U1"."TYPE"=15) AND LNNVL("U1"."TYPE"=19) AND LNNVL("U1"."TYPE"=25) AND
LNNVL("U1"."TYPE"=26) OR LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"="U1"."TYPE") OR LNNVL("U2"."RTYP"=0) OR
LNNVL("F"."DIST"=1)) AND (LNNVL("U1"."TYPE"=7) OR LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"=8) OR
LNNVL("U2"."RTYP"=0) OR LNNVL("F"."DIST"=1)))
36 - access("F"."UNITM"="U2"."ID")
41 - access(("U1"."TYPE"=9 OR "U1"."TYPE"=14 OR "U1"."TYPE"=19 OR "U1"."TYPE"=26) AND "U1"."RTYP"=4)
42 - filter("F"."DIST">0)
43 - access("F"."UNITG"="U1"."ID")
44 - filter("U2"."RTYP"=4 AND (LNNVL("U1"."TYPE"=14) AND LNNVL("U1"."TYPE"=19) AND LNNVL("U1"."TYPE"=26) OR
LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"=13) AND LNNVL("U2"."TYPE"=15) AND LNNVL("U2"."TYPE"=25) OR
LNNVL("U2"."RTYP"=0) AND LNNVL("U2"."RTYP"=4)) AND (LNNVL("U1"."TYPE"=9) AND LNNVL("U1"."TYPE"=19) OR
LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"=6) AND LNNVL("U2"."TYPE"=7) AND LNNVL("U2"."TYPE"=8) AND
LNNVL("U2"."TYPE"=13) AND LNNVL("U2"."TYPE"=15) OR LNNVL("U2"."RTYP"=4)) AND (LNNVL("U1"."TYPE"=7) AND
LNNVL("U1"."TYPE"=8) AND LNNVL("U1"."TYPE"=13) AND LNNVL("U1"."TYPE"=14) AND LNNVL("U1"."TYPE"=15) AND
LNNVL("U1"."TYPE"=19) AND LNNVL("U1"."TYPE"=25) AND LNNVL("U1"."TYPE"=26) OR LNNVL("U1"."RTYP"=4) OR
LNNVL("U2"."TYPE"="U1"."TYPE") OR LNNVL("U2"."RTYP"=0) OR LNNVL("F"."DIST"=1)) AND (LNNVL("U1"."TYPE"=7) OR
LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"=8) OR LNNVL("U2"."RTYP"=0) OR LNNVL("F"."DIST"=1)))
45 - access("F"."UNITM"="U2"."ID" AND "U2"."TYPE"="U1"."TYPE")
49 - access("U1"."TYPE"=9 AND "U1"."RTYP"=2)
51 - access("F"."UNITG"="U1"."ID")
52 - filter(("U2"."RTYP"=2 OR "U2"."RTYP"=4) AND (LNNVL("U1"."TYPE"=9) AND LNNVL("U1"."TYPE"=14) AND
LNNVL("U1"."TYPE"=19) AND LNNVL("U1"."TYPE"=26) OR LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"="U1"."TYPE") OR
LNNVL("U2"."RTYP"=4) OR LNNVL("F"."DIST">0)) AND (LNNVL("U1"."TYPE"=14) AND LNNVL("U1"."TYPE"=19) AND
LNNVL("U1"."TYPE"=26) OR LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"=13) AND LNNVL("U2"."TYPE"=15) AND
LNNVL("U2"."TYPE"=25) OR LNNVL("U2"."RTYP"=0) AND LNNVL("U2"."RTYP"=4)) AND (LNNVL("U1"."TYPE"=9) AND
LNNVL("U1"."TYPE"=19) OR LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"=6) AND LNNVL("U2"."TYPE"=7) AND
LNNVL("U2"."TYPE"=8) AND LNNVL("U2"."TYPE"=13) AND LNNVL("U2"."TYPE"=15) OR LNNVL("U2"."RTYP"=4)) AND
(LNNVL("U1"."TYPE"=7) AND LNNVL("U1"."TYPE"=8) AND LNNVL("U1"."TYPE"=13) AND LNNVL("U1"."TYPE"=14) AND
LNNVL("U1"."TYPE"=15) AND LNNVL("U1"."TYPE"=19) AND LNNVL("U1"."TYPE"=25) AND LNNVL("U1"."TYPE"=26) OR
LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"="U1"."TYPE") OR LNNVL("U2"."RTYP"=0) OR LNNVL("F"."DIST"=1)) AND
(LNNVL("U1"."TYPE"=7) OR LNNVL("U1"."RTYP"=4) OR LNNVL("U2"."TYPE"=8) OR LNNVL("U2"."RTYP"=0) OR
LNNVL("F"."DIST"=1)))
53 - access("F"."UNITM"="U2"."ID" AND "U2"."TYPE"=7)
125 rows selected.
Elapsed: 00:00:00.03
SQL>
NL better that HJ in my query?
Thanks in advance.
Best regards, Pavel.