|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Materialize hint giving different results [message #505550 is a reply to message #505523] |
Tue, 03 May 2011 13:07   |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
jbarril wrote on Tue, 03 May 2011 06:31
Thanks Barbara, however can you try one more thing - add another criteria on the where clause to filter the results (say where EMPNO > 7800). According to the article, when the temp table transformation happens, some of the criteria get lost, which is what was happening to me also (I had WHERE H_UNIV_ID LIKE 'E4%'). Of course when I remove it I get the same results with or without the hint.
SCOTT@orcl_11gR2> SELECT * FROM v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
5 rows selected.
SCOTT@orcl_11gR2> CREATE TABLE test_tab AS
2 SELECT empno, mgr
3 FROM emp
4 /
Table created.
SCOTT@orcl_11gR2> CREATE INDEX test_tab_empno_mgr_idx ON test_tab (empno, mgr)
2 /
Index created.
SCOTT@orcl_11gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TEST_TAB')
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11gR2> WITH
2 managers AS
3 (SELECT a.empno
4 FROM test_tab a
5 WHERE empno > 7800
6 CONNECT BY PRIOR a.empno = a.mgr
7 START WITH a.empno = 7839)
8 SELECT * FROM managers
9 /
EMPNO
----------
7839
7876
7902
7844
7900
7934
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 735014586
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 182 | 2 (50)| 00:00:01 |
| 1 | VIEW | | 14 | 182 | 2 (50)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 4 | INDEX FULL SCAN | TEST_TAB_EMPNO_MGR_IDX | 14 | 112 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."EMPNO">7800)
3 - access("A"."MGR"=PRIOR "A"."EMPNO")
filter("A"."EMPNO"=7839)
SCOTT@orcl_11gR2> WITH
2 managers AS
3 (SELECT /*+materialize*/ a.empno
4 FROM test_tab a
5 WHERE empno > 7800
6 CONNECT BY PRIOR a.empno = a.mgr
7 START WITH a.empno = 7839)
8 SELECT * FROM managers
9 /
EMPNO
----------
7839
7876
7902
7844
7900
7934
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1653721301
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 39 | 4 (25)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6606_3DFAEF7 | | | | |
|* 3 | FILTER | | | | | |
|* 4 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 5 | INDEX FULL SCAN | TEST_TAB_EMPNO_MGR_IDX | 14 | 112 | 1 (0)| 00:00:01 |
| 6 | VIEW | | 3 | 39 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_3DFAEF7 | 3 | 12 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."EMPNO">7800)
4 - access("A"."MGR"=PRIOR "A"."EMPNO")
filter("A"."EMPNO"=7839)
SCOTT@orcl_11gR2> WITH
2 managers AS
3 (SELECT /*+materialize*/ a.empno
4 FROM test_tab a
5 WHERE empno > 7800
6 CONNECT BY PRIOR a.empno = a.mgr
7 START WITH a.empno = 7839)
8 SELECT COUNT (*) FROM managers
9 /
COUNT(*)
----------
6
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1884793425
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (25)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6608_3DFAEF7 | | | | |
|* 3 | FILTER | | | | | |
|* 4 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 5 | INDEX FULL SCAN | TEST_TAB_EMPNO_MGR_IDX | 14 | 112 | 1 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | | | |
| 7 | VIEW | | 3 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_3DFAEF7 | 3 | 12 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."EMPNO">7800)
4 - access("A"."MGR"=PRIOR "A"."EMPNO")
filter("A"."EMPNO"=7839)
SCOTT@orcl_11gR2>
|
|
|
|
|
|
| Re: Materialize hint giving different results [message #505552 is a reply to message #505551] |
Tue, 03 May 2011 13:29 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is the test script, in case anyone else wants to run it on their system:
SET LINESIZE 130
SELECT * FROM v$version
/
CREATE TABLE test_tab AS
SELECT empno, mgr
FROM emp
/
CREATE INDEX test_tab_empno_mgr_idx ON test_tab (empno, mgr)
/
EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TEST_TAB')
SET AUTOTRACE ON EXPLAIN
WITH
managers AS
(SELECT a.empno
FROM test_tab a
WHERE empno > 7800
CONNECT BY PRIOR a.empno = a.mgr
START WITH a.empno = 7839)
SELECT * FROM managers
/
WITH
managers AS
(SELECT /*+materialize*/ a.empno
FROM test_tab a
WHERE empno > 7800
CONNECT BY PRIOR a.empno = a.mgr
START WITH a.empno = 7839)
SELECT * FROM managers
/
WITH
managers AS
(SELECT /*+materialize*/ a.empno
FROM test_tab a
WHERE empno > 7800
CONNECT BY PRIOR a.empno = a.mgr
START WITH a.empno = 7839)
SELECT COUNT (*) FROM managers
/
SET AUTOTRACE OFF
drop table test_tab
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
