| Hierarchical queries with rownum pseudocolumn in 10g vs. 9i [message #206035] |
Tue, 28 November 2006 07:00  |
orafaqer
Messages: 48
Registered: July 2006
|
Member |
|
|
Good day everybody!
I have a simple case of hierarchical query with rownum:
SQL>set timing off
SQL>set autotrace off
SQL>
SQL>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected.
SQL>
SQL>drop table t;
Table dropped.
SQL>-- create a parent-child table with additional column
SQL>create table t as
2 select rownum object_id, rownum parent_id, rpad('x', 3900, 'x') name from dual connect by l
evel<10000 union all
3 select 0, 0, null from dual;
Table created.
SQL>
SQL>update t set parent_id = (mod(abs(dbms_random.random), 10000));
10000 rows updated.
SQL>
SQL>create unique index t_indx on t(object_id);
Index created.
SQL>create index t_fk_indx on t(parent_id);
Index created.
SQL>alter table t add constraint t_pk primary key (object_id) using index t_indx;
Table altered.
SQL>alter table t add constraint t_fk foreign key (parent_id) references t(object_id);
Table altered.
SQL>
SQL>set serveroutput on
SQL>exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T', cascade=>true);
PL/SQL procedure successfully completed.
SQL>
SQL>-- now run a simple query with rownum & connect by
SQL>set autotrace on
SQL>set timing on
SQL>show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL>
SQL>select *
2 from (select object_id, level from t
3 start with object_id = 123
4 connect by parent_id = prior object_id
5 order by 2)
6 where rownum = 1;
OBJECT_ID LEVEL
---------- ----------
123 1
1 row selected.
Elapsed: 00:00:17.54
Execution Plan
----------------------------------------------------------
Plan hash value: 1992107542
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | | 2235 (1)| 00:00:27 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 10000 | 253K| | 2235 (1)| 00:00:27 |
|* 3 | SORT ORDER BY STOPKEY | | 10000 | 80000 | 328K| 2235 (1)| 00:00:27 |
|* 4 | CONNECT BY WITH FILTERING | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T | | | | | |
|* 6 | INDEX UNIQUE SCAN | T_INDX | 1 | 4 | | 1 (0)| 00:00:01 |
|* 7 | HASH JOIN | | | | | | |
| 8 | CONNECT BY PUMP | | | | | | |
| 9 | TABLE ACCESS FULL | T | 10000 | 80000 | | 2196 (1)| 00:00:27 |
| 10 | TABLE ACCESS FULL | T | 10000 | 80000 | | 2196 (1)| 00:00:27 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
4 - filter("OBJECT_ID"=123)
6 - access("OBJECT_ID"=123)
7 - access("PARENT_ID"=NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
110102 consistent gets
98042 physical reads
0 redo size
471 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL>-- set 9.2 CBO environment
SQL>alter session set "optimizer_features_enable"= '9.2.0';
Session altered.
Elapsed: 00:00:00.04
SQL>show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string CHOOSE
optimizer_secure_view_merging boolean TRUE
SQL>
SQL>select *
2 from (select object_id, level from t
3 start with object_id = 123
4 connect by parent_id = prior object_id
5 order by 2)
6 where rownum = 1;
OBJECT_ID LEVEL
---------- ----------
123 1
1 row selected.
Elapsed: 00:00:00.20
Execution Plan
----------------------------------------------------------
Plan hash value: 2613299882
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 5 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 2 | 52 | 5 |
|* 3 | SORT ORDER BY STOPKEY | | 2 | 16 | 5 |
|* 4 | CONNECT BY WITH FILTERING | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | T | | | |
|* 6 | INDEX UNIQUE SCAN | T_INDX | 1 | 4 | 1 |
| 7 | NESTED LOOPS | | | | |
| 8 | BUFFER SORT | | | | |
| 9 | CONNECT BY PUMP | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T | 2 | 16 | 3 |
|* 11 | INDEX RANGE SCAN | T_FK_INDX | 2 | | 1 |
|* 12 | TABLE ACCESS FULL | T | 2 | 16 | 3 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
4 - filter("OBJECT_ID"=123)
6 - access("OBJECT_ID"=123)
11 - access("PARENT_ID"=NULL)
12 - access("PARENT_ID"=NULL)
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
67 consistent gets
20 physical reads
0 redo size
471 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
24 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL>-- set 10.2 CBO environment and one strange parameter
SQL>alter session set "optimizer_features_enable"= '10.2.0.1';
Session altered.
Elapsed: 00:00:00.01
SQL>alter session set "_optimizer_rownum_pred_based_fkr"= false;
Session altered.
Elapsed: 00:00:00.00
SQL>show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_rownum_pred_based_fkr boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL>
SQL>select *
2 from (select object_id, level from t
3 start with object_id = 123
4 connect by parent_id = prior object_id
5 order by 2)
6 where rownum = 1;
OBJECT_ID LEVEL
---------- ----------
123 1
1 row selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2613299882
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (25)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 2 | 52 | 4 (25)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY | | 2 | 16 | 4 (25)| 00:00:01 |
|* 4 | CONNECT BY WITH FILTERING | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | T | | | | |
|* 6 | INDEX UNIQUE SCAN | T_INDX | 1 | 4 | 1 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | | | | |
| 8 | BUFFER SORT | | | | | |
| 9 | CONNECT BY PUMP | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T | 2 | 16 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | T_FK_INDX | 2 | | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | T | 2 | 16 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
4 - filter("OBJECT_ID"=123)
6 - access("OBJECT_ID"=123)
11 - access("PARENT_ID"=NULL)
12 - access("PARENT_ID"=NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
67 consistent gets
0 physical reads
0 redo size
471 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
24 sorts (memory)
0 sorts (disk)
1 rows processed
Lets look at the plan on 9.2.0.6:
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 |
| 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 2 | 52 | 3 |
| 3 | SORT ORDER BY STOPKEY | | 2 | 16 | 3 |
| 4 | CONNECT BY WITH FILTERING | | | | |
| 5 | NESTED LOOPS | | | | |
| 6 | INDEX UNIQUE SCAN | T_INDX | 1 | 4 | 1 |
| 7 | TABLE ACCESS BY USER ROWID | T | | | |
| 8 | NESTED LOOPS | | | | |
| 9 | BUFFER SORT | | 2 | 16 | 3 |
| 10 | CONNECT BY PUMP | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID| T | 2 | 16 | 1 |
| 12 | INDEX RANGE SCAN | T_FK_INDX | 2 | | 1 |
--------------------------------------------------------------------------------
So, thats enough. Looks simple, but I can't understand:
1. (General question about hierarchical queries) "Connect by" queries in 10g always have a FTS in plan (look at the 12th step in the second explain plan) while 9i has no such operation in plan at all. Also it seems like this FTS is not performed.
2. (Regarding rownum & connect by) First query shows hash join using FTS (which is performed, as you can see in statistics) instead of nested loops. Strange parameter "optimizer_rownum_pred_based_fkr" helps.
I would like you to share with people any thoughts on this.
Thanks.
PS. I know that /*+ first_rows*/ also helps to get rid of hash join for query above. It cannot help in this one:
select *
from (select object_id, rownum from t
start with object_id = 123
connect by parent_id = prior object_id);
-
Attachment: test.sql
(Size: 1.66KB, Downloaded 1726 times)
|
|
|
|
| Re: Hierarchical queries with rownum pseudocolumn in 10g vs. 9i [message #206126 is a reply to message #206035] |
Tue, 28 November 2006 20:30  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I too have seen the third step in CONNECT BY queries and wondered. When you run it through TK*Prof, you notice that it does not have any rows against it, so it is not being used.
Logically, a CONNECT BY can have only two table-access steps: one to pick up the START-WITH rows, and another to iterate through the children.
Although one of your plans may contain a clue. Notice the access on step 12 - access("PARENT_ID"=NULL).
I wonder what would happen if you had a row with null PARENT_ID, and another row with null OBJECT_ID? You would not think that parent_id = PRIOR OBJECT_ID would work with NULLs, but what is the reason for that access note.
Maybe, just maybe, the third step is an alternate path for NULL lookups. I'm just guessing, but it seems unlikely.
It could also have something to do with the other 10g CONNECT BY features, such as ISLEAF, ROOT, and NOCYCLE.
Why do you want it to use the index? I've found the 10g method of Hash/FTS for CONNECT BY to be heaps more efficient. Perhaps you could try an /*+INDEX(t)*/ hint.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
