Home » RDBMS Server » Performance Tuning » Hierarchical queries with rownum pseudocolumn in 10g vs. 9i
Hierarchical queries with rownum pseudocolumn in 10g vs. 9i [message #206035] Tue, 28 November 2006 07:00 Go to next message
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 296 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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: TKPROF & Statspack question
Next Topic: INDEX column combination
Goto Forum:
  


Current Time: Sat Dec 03 20:11:33 CST 2016

Total time taken to generate the page: 0.09895 seconds