Hi, I have a problem.. I can not really see why the hint MATERIALIZE changes the execution plan if I use a VIEW in the Query.
Environment:
1 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2 PL/SQL Release 11.2.0.4.0 - Production
3 CORE 11.2.0.4.0 Production
4 TNS for Linux: Version 11.2.0.4.0 - Production
5 NLSRTL Version 11.2.0.4.0 - Production
Workaround:
DROP TABLE TABLE_A PURGE;
DROP TABLE TABLE_B PURGE;
DROP TABLE TABLE_C PURGE;
DROP VIEW VIEW_B;
CREATE TABLE TABLE_A (id NUMBER, text VARCHAR2(10)) TABLESPACE USERS;
CREATE TABLE TABLE_B (id NUMBER, num NUMBER, text VARCHAR2(10)) TABLESPACE USERS;
CREATE TABLE TABLE_C (num NUMBER) TABLESPACE GLOB_DATA;
CREATE OR REPLACE VIEW VIEW_B AS SELECT * FROM TABLE_B;
INSERT INTO TABLE_A (id, text) VALUES (1, 'a');
INSERT INTO TABLE_A (id, text) VALUES (2, 'b');
INSERT INTO TABLE_A (id, text) VALUES (3, 'c');
INSERT INTO TABLE_A (id, text) VALUES (4, 'd');
INSERT INTO TABLE_B (id, num, text) VALUES (2, 5, 'A');
INSERT INTO TABLE_B (id, num, text) VALUES (5, 5, 'E');
INSERT INTO TABLE_B (id, num, text) VALUES (9, 0, 'M');
INSERT INTO TABLE_C (num) VALUES (5);
COMMIT;
CREATE INDEX B_IND_NUM ON TABLE_B (num) TABLESPACE USERS;
CASE 1. -- I use the MATERIALIZE Hint with the combination of the VIEW -> INDEX won't be used!
======
WITH TMP_TEST
AS (SELECT /*+ MATERIALIZE
cardinality(A, 10000000) cardinality(B, 100000000) cardinality(C, 1)
LEADING(C,B,A) USE_NL(B, A) USE_NL(C, B) INDEX(B B_IND_NUM) */
A.id
, B.text
FROM
TABLE_A A
LEFT OUTER
JOIN VIEW_B B --> I Use here a VIEW!
ON A.id = B.id
CROSS
JOIN TABLE_C C
WHERE
B.num = C.num
)
SELECT * FROM TMP_TEST;
Execution Plan:
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7500K| 143M| 11338 (1)| 00:02:39 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D71D6_1C7B45C3 | | | | |
| 3 | NESTED LOOPS | | 7500K| 422M| 9 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 3 | 138 | 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TABLE_C | 1 | 13 | 2 (0)| 00:00:01 |
|* 6 | VIEW | VIEW_B | 3 | 99 | 4 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | TABLE_B | 3 | 99 | 4 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | TABLE_A | 2500K| 30M| 1 (0)| 00:00:01 |
| 9 | VIEW | | 7500K| 143M| 11329 (1)| 00:02:39 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D71D6_1C7B45C3 | 7500K| 143M| 11329 (1)| 00:02:39 |
-----------------------------------------------------------------------------------------------------------
As it can be seen - Index won't be used... But I force it..
CASE 2 - I change the view to the Table
======
WITH TMP_TEST
AS (SELECT /*+ MATERIALIZE
cardinality(A, 10000000) cardinality(B, 100000000) cardinality(C, 1)
LEADING(C,B,A) USE_NL(B, A) USE_NL(C, B) INDEX(B B_IND_NUM) */
A.id
, B.text
FROM
TABLE_A A
LEFT OUTER
JOIN TABLE_B --> I Use here the TABLE!
ON A.id = B.id
CROSS
JOIN TABLE_C C
WHERE
B.num = C.num
)
SELECT * FROM TMP_TEST;
Then you can see the index will be used:
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 188G(100)| | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | LOAD AS SELECT | | | | | | 270K| 270K| 1/0/0|
| 3 | NESTED LOOPS | | 125T| 6707T| 18M (1)| 73:13:58 | | | |
| 4 | NESTED LOOPS | | 50M| 2193M| 3 (0)| 00:00:01 | | | |
| 5 | TABLE ACCESS FULL | TABLE_C | 1 | 13 | 2 (0)| 00:00:01 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| TABLE_B | 50M| 1573M| 1 (0)| 00:00:01 | | | |
|* 7 | INDEX RANGE SCAN | B_IND_NUM | 82 | | 0 (0)| | | | |
|* 8 | TABLE ACCESS FULL | TABLE_A | 2500K| 30M| 0 (0)| | | | |
| 9 | VIEW | | 125T| 2273T| 188G (1)|999:59:59 | | | |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D71D2_1C7B45C3 | 125T| 2273T| 188G (1)|999:59:59 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
CASE 3 - I run the first query WITHOUT the hint MATERIALIZE BUT with the VIEW!:
======
WITH TMP_TEST
AS (SELECT /*+
cardinality(A, 10000000) cardinality(B, 100000000) cardinality(C, 1)
LEADING(C,B,A) USE_NL(B, A) USE_NL(C, B) INDEX(B B_IND_NUM) */
A.id
, B.text
FROM
TABLE_A A
LEFT OUTER
JOIN VIEW_B B --> I use here the VIEW again but without hint MATERIALIZE!
ON A.id = B.id
CROSS
JOIN TABLE_C C
WHERE
B.num = C.num
)
SELECT * FROM TMP_TEST;
I get the result that the index will be used:
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 125T| 6707T| 93M (1)|365:15:29 |
| 1 | NESTED LOOPS | | 125T| 6707T| 93M (1)|365:15:29 |
| 2 | NESTED LOOPS | | 50M| 2193M| 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TABLE_C | 1 | 13 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TABLE_B | 50M| 1573M| 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | B_IND_NUM | 82 | | 0 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | TABLE_A | 2500K| 30M| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
My question: Why does the usage of the hint MATERIALIZE change the execution plan of the query?
Aim: How could I achieve the following:
- Usage of the hint MATERIALIZE
- Usage of the View not the Table
- Usage of the Index?
Thanks in advance!