Recursive WITH clause - to implement Connect By features

ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause. This blog entry shows how to implement all CONNECT BY features in Recursive WITH clause.

CONNECT BY clause to show all employees starting with KING (who has no manager [mgr IS NULL]):

rajesh@ORA11G> select empno,ename,mgr
  2  from emp
  3  start with mgr is null
  4  connect by prior empno = mgr;

     EMPNO ENAME             MGR
---------- ---------- ----------
      7839 KING
      7566 JONES            7839
      7788 SCOTT            7566
      7876 ADAMS            7788
      7902 FORD             7566
      7369 SMITH            7902
      7698 BLAKE            7839
      7499 ALLEN            7698
      7521 WARD             7698
      7654 MARTIN           7698
      7844 TURNER           7698
      7900 JAMES            7698
      7782 CLARK            7839
      7934 MILLER           7782

14 rows selected.

Elapsed: 00:00:00.01
rajesh@ORA11G>

Same result using the recursive WITH clause

rajesh@ORA11G> with r(empno,ename,mgr) as
  2  ( select empno,ename,mgr
  3    from emp
  4    where mgr is null
  5    union all
  6    select e.empno,e.ename,r.empno
  7    from emp e, r
  8    where e.mgr = r.empno )
  9  select * from r;

     EMPNO ENAME             MGR
---------- ---------- ----------
      7839 KING
      7566 JONES            7839
      7698 BLAKE            7839
      7782 CLARK            7839
      7499 ALLEN            7698
      7521 WARD             7698
      7654 MARTIN           7698
      7788 SCOTT            7566
      7844 TURNER           7698
      7900 JAMES            7698
      7902 FORD             7566
      7934 MILLER           7782
      7369 SMITH            7902
      7876 ADAMS            7788

14 rows selected.

Elapsed: 00:00:00.01
rajesh@ORA11G>

The first query block (anchor member) of the recursive WITH clause defines the root(s) of the hierarchy, the second the recursion. In the second block, we can see a join between the anchor member and the emp table which is pretty much the same as the CONNECT BY clause in the traditional approach.

The CONNECT BY clause has a dedicated ORDER BY clause (ORDER SIBLINGS BY) to sort the elements of a hierarchy.

rajesh@ORA11G> select level lvl, rpad(' ',2*level)||ename enames,empno,mgr
  2  from emp
  3  start with mgr is null
  4  connect by prior empno = mgr
  5  order siblings by ename;

       LVL ENAMES                    EMPNO        MGR
---------- -------------------- ---------- ----------
         1   KING                     7839
         2     BLAKE                  7698       7839
         3       ALLEN                7499       7698
         3       JAMES                7900       7698
         3       MARTIN               7654       7698
         3       TURNER               7844       7698
         3       WARD                 7521       7698
         2     CLARK                  7782       7839
         3       MILLER               7934       7782
         2     JONES                  7566       7839
         3       FORD                 7902       7566
         4         SMITH              7369       7902
         3       SCOTT                7788       7566
         4         ADAMS              7876       7788

14 rows selected.

Elapsed: 00:00:00.01
rajesh@ORA11G>

Same result can be achieved using the recursive WITH clause with the SEARCH clause

rajesh@ORA11G>
rajesh@ORA11G> with r(empno,ename,mgr,lvl) as
  2  ( select empno,ename,mgr,1 as lvl
  3    from emp
  4    where mgr is null
  5    union all
  6    select e.empno, e.ename, e.mgr, lvl+1
  7    from emp e, r
  8    where e.mgr = r.empno
  9  )
 10  search depth first by ename set ord1
 11  select lvl, rpad(' ',2*lvl)||ename as enames, empno,mgr
 12  from r
 13  order by ord1;

       LVL ENAMES                    EMPNO        MGR
---------- -------------------- ---------- ----------
         1   KING                     7839
         2     BLAKE                  7698       7839
         3       ALLEN                7499       7698
         3       JAMES                7900       7698
         3       MARTIN               7654       7698
         3       TURNER               7844       7698
         3       WARD                 7521       7698
         2     CLARK                  7782       7839
         3       MILLER               7934       7782
         2     JONES                  7566       7839
         3       FORD                 7902       7566
         4         SMITH              7369       7902
         3       SCOTT                7788       7566
         4         ADAMS              7876       7788

14 rows selected.

Elapsed: 00:00:00.02
rajesh@ORA11G>

SEARCH DEPTH FIRST will show the children before the siblings whereas SEARCH BREADTH FIRST would show the siblings before the children.

CONNECT BY clause knows the CONNECT_BY_ROOT operator which returns the root(s) of a hierarchy. Furthermore the SYS_CONNECT_BY_PATH function may be used to get a path from the root to the current element within the hierarchy.

rajesh@ORA11G> select ename, empno, mgr,
  2     sys_connect_by_path(ename,'/') path,
  3     connect_by_root ename
  4  from emp
  5  start with mgr is null
  6  connect by prior empno = mgr ;

ENAME           EMPNO        MGR PATH                           CONNECT_BY
---------- ---------- ---------- ------------------------------ ----------
KING             7839            /KING                          KING
JONES            7566       7839 /KING/JONES                    KING
SCOTT            7788       7566 /KING/JONES/SCOTT              KING
ADAMS            7876       7788 /KING/JONES/SCOTT/ADAMS        KING
FORD             7902       7566 /KING/JONES/FORD               KING
SMITH            7369       7902 /KING/JONES/FORD/SMITH         KING
BLAKE            7698       7839 /KING/BLAKE                    KING
ALLEN            7499       7698 /KING/BLAKE/ALLEN              KING
WARD             7521       7698 /KING/BLAKE/WARD               KING
MARTIN           7654       7698 /KING/BLAKE/MARTIN             KING
TURNER           7844       7698 /KING/BLAKE/TURNER             KING
JAMES            7900       7698 /KING/BLAKE/JAMES              KING
CLARK            7782       7839 /KING/CLARK                    KING
MILLER           7934       7782 /KING/CLARK/MILLER             KING

14 rows selected.

Elapsed: 00:00:00.01
rajesh@ORA11G>

Same result using the recursive WITH clause

rajesh@ORA11G>
rajesh@ORA11G> with r(ename,empno,mgr,path,by_root) as
  2  ( select ename,empno,mgr,
  3     '/'||ename as path,
  4     ename as by_root
  5    from emp
  6    where mgr is null
  7    union all
  8    select e.ename, e.empno, e.mgr,
  9      r.path||'/'||e.ename as path,
 10      r.by_root
 11    from emp e, r
 12    where e.mgr = r.empno )
 13  select ename,empno,mgr,path,by_root from r ;

ENAME           EMPNO        MGR PATH                           BY_ROOT
---------- ---------- ---------- ------------------------------ ----------
KING             7839            /KING                          KING
JONES            7566       7839 /KING/JONES                    KING
BLAKE            7698       7839 /KING/BLAKE                    KING
CLARK            7782       7839 /KING/CLARK                    KING
ALLEN            7499       7698 /KING/BLAKE/ALLEN              KING
WARD             7521       7698 /KING/BLAKE/WARD               KING
MARTIN           7654       7698 /KING/BLAKE/MARTIN             KING
SCOTT            7788       7566 /KING/JONES/SCOTT              KING
TURNER           7844       7698 /KING/BLAKE/TURNER             KING
JAMES            7900       7698 /KING/BLAKE/JAMES              KING
FORD             7902       7566 /KING/JONES/FORD               KING
MILLER           7934       7782 /KING/CLARK/MILLER             KING
SMITH            7369       7902 /KING/JONES/FORD/SMITH         KING
ADAMS            7876       7788 /KING/JONES/SCOTT/ADAMS        KING

14 rows selected.

Elapsed: 00:00:00.03
rajesh@ORA11G>

If the data contains a cylce the query would run indefinitely. ORACLE detects these situations and lets the query fail.

rajesh@ORA11G>
rajesh@ORA11G> UPDATE emp
  2  SET mgr = 7499
  3  WHERE empno = 7839 ;

1 row updated.

Elapsed: 00:00:00.00
rajesh@ORA11G>
rajesh@ORA11G> select level, empno,ename
  2  from emp
  3  start with empno = 7839
  4  connect by prior empno = mgr;
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

Elapsed: 00:00:00.01
rajesh@ORA11G>

Recursive suquery factoring throws a different error code with a similar message

rajesh@ORA11G>
rajesh@ORA11G> with r(lvl,empno,ename) as
  2  ( select 1 as lvl, empno, ename
  3    from emp
  4    where empno = 7839
  5    union all
  6    select lvl+1, e.empno, e.ename
  7    from emp e, r
  8    where e.mgr = r.empno )
  9  select * from r ;
ERROR:
ORA-32044: cycle detected while executing recursive WITH query



no rows selected

Elapsed: 00:00:00.01
rajesh@ORA11G>


Since ORACLE 10g the CONNECT BY clause knows the NOCYLCE attribute as well as the CONNECT_BY_ISCYCLE pseudo column to ignore cycles 

rajesh@ORA11G> select level, empno,ename,mgr,connect_by_iscycle,
  2     sys_connect_by_path(ename,'/') path
  3  from emp
  4  start with empno = 7839
  5  connect by nocycle prior empno = mgr;

     LEVEL      EMPNO ENAME             MGR CONNECT_BY_ISCYCLE PATH
---------- ---------- ---------- ---------- ------------------ -----------------------------
         1       7839 KING             7499                  0 /KING
         2       7566 JONES            7839                  0 /KING/JONES
         3       7788 SCOTT            7566                  0 /KING/JONES/SCOTT
         4       7876 ADAMS            7788                  0 /KING/JONES/SCOTT/ADAMS
         3       7902 FORD             7566                  0 /KING/JONES/FORD
         4       7369 SMITH            7902                  0 /KING/JONES/FORD/SMITH
         2       7698 BLAKE            7839                  0 /KING/BLAKE
         3       7499 ALLEN            7698                  1 /KING/BLAKE/ALLEN
         3       7521 WARD             7698                  0 /KING/BLAKE/WARD
         3       7654 MARTIN           7698                  0 /KING/BLAKE/MARTIN
         3       7844 TURNER           7698                  0 /KING/BLAKE/TURNER
         3       7900 JAMES            7698                  0 /KING/BLAKE/JAMES
         2       7782 CLARK            7839                  0 /KING/CLARK
         3       7934 MILLER           7782                  0 /KING/CLARK/MILLER

14 rows selected.

Elapsed: 00:00:00.00
rajesh@ORA11G>
The Recursive WITH clause also offers clause to handle this way.

rajesh@ORA11G>
rajesh@ORA11G> with r(lvl,empno,ename,mgr,path) as
  2  ( select 1 as lvl, empno, ename,
  3     mgr,'/'||ename as path
  4    from emp
  5    where empno = 7839
  6    union all
  7    select lvl+1, e.empno, e.ename,
  8     e.mgr,r.path||'/'||e.ename
  9    from emp e, r
 10    where e.mgr = r.empno )
 11  search depth first by ename set ord1
 12  cycle empno set y_cycle to 1 default 0
 13  select lvl,empno,ename,mgr,path,y_cycle from r ;

       LVL      EMPNO ENAME             MGR PATH                           Y
---------- ---------- ---------- ---------- ------------------------------ -
         1       7839 KING             7499 /KING                          0
         2       7698 BLAKE            7839 /KING/BLAKE                    0
         3       7499 ALLEN            7698 /KING/BLAKE/ALLEN              0
         4       7839 KING             7499 /KING/BLAKE/ALLEN/KING         1
         3       7900 JAMES            7698 /KING/BLAKE/JAMES              0
         3       7654 MARTIN           7698 /KING/BLAKE/MARTIN             0
         3       7844 TURNER           7698 /KING/BLAKE/TURNER             0
         3       7521 WARD             7698 /KING/BLAKE/WARD               0
         2       7782 CLARK            7839 /KING/CLARK                    0
         3       7934 MILLER           7782 /KING/CLARK/MILLER             0
         2       7566 JONES            7839 /KING/JONES                    0
         3       7902 FORD             7566 /KING/JONES/FORD               0
         4       7369 SMITH            7902 /KING/JONES/FORD/SMITH         0
         3       7788 SCOTT            7566 /KING/JONES/SCOTT              0
         4       7876 ADAMS            7788 /KING/JONES/SCOTT/ADAMS        0

15 rows selected.

Elapsed: 00:00:00.03
rajesh@ORA11G>

The difference between CONNECT BY and RECURSIVE WITH cycle detection is that with RECURSIVE WITH the cycle is decteted after the next recursion level wass processed. The erroneous node is repeated and the cycle flag is set one level lower than the CONNECT_BY_ISCYCLE pseudo column.

Reduced consistent gets - II

This will be the prolongation of the previous post.

create table t
(x  int primary key,
 y  varchar2(80) )
 /

insert into t(x,y)
select level, rpad('*',80,'*')
from dual
connect by level <= 1000
/
commit;

exec dbms_stats.gather_table_stats(user,'T');

create or replace function get_stats
return number as
 l_val number ;
begin
 select s2.value
 into l_val
 from v$sysstat s1,
 v$mystat s2
 where s1.statistic# = s2.statistic#
 and s1.name like 'consistent gets' ;

 return l_val;
end;
/

Lets do a simple select and measure the consistent gets in both 10g and 11g database.

rajesh@ORA10G> declare
  2   l_stats number;
  3  begin
  4   l_stats := get_stats ;
  5   for x in ( select x from t, (select mod(level,1000)+1 as l
  6      from dual
  7      connect by level <= 100000 ) t1
  8      where t.x = t1.l )
  9   loop
 10    null;
 11   end loop;
 12 
 13   dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats )  );
 14  end;
 15  /
 Total Consistent gets = 101001

PL/SQL procedure successfully completed.

rajesh@ORA11G> declare
  2   l_stats number;
  3  begin
  4   l_stats := get_stats ;
  5   for x in ( select x from t, (select mod(level,1000)+1 as l
  6      from dual
  7      connect by level <= 100000 ) t1
  8      where t.x = t1.l )
  9   loop
 10    null;
 11   end loop;
 12 
 13   dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats )  );
 14  end;
 15  /
 Total Consistent gets = 2602

PL/SQL procedure successfully completed.

This is some what an expected behaviour, since oracle 11g decided to cache block after each lookup in the hope it re-uses its again. Instead of hitting the buffer cache again.

As soon as we start reading the data randomly, this effect disappered.

rajesh@ORA10G> /* Reading PK Randomly */
rajesh@ORA10G> declare
  2   l_stats number;
  3  begin
  4   l_stats := get_stats ;
  5   for x in ( select x from t, (select trunc(dbms_random.value(1,1000)) as l
  6      from dual
  7      connect by level <= 100000 ) t1
  8      where t.x = t1.l )
  9   loop
 10    null;
 11   end loop;
 12 
 13   dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats )  );
 14  end;
 15  /
Total Consistent gets = 101001

PL/SQL procedure successfully completed.

rajesh@ORA11G> /* Reading PK Randomly */
rajesh@ORA11G> declare
  2   l_stats number;
  3  begin
  4   l_stats := get_stats ;
  5   for x in ( select x from t, (select trunc(dbms_random.value(1,1000)) as l
  6      from dual
  7      connect by level <= 100000 ) t1
  8      where t.x = t1.l )
  9   loop
 10    null;
 11   end loop;
 12 
 13   dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats )  );
 14  end;
 15  /

Total Consistent gets = 102262

PL/SQL procedure successfully completed.
Table Access by Index Rowid

rajesh@ORA10G>
rajesh@ORA10G> declare
  2   l_stats number;
  3  begin
  4   l_stats := get_stats ;
  5   for x in ( select Y from t, (select mod(level,1000)+1 as l
  6      from dual
  7      connect by level <= 100000 ) t1
  8      where t.x = t1.l )
  9   loop
 10    null;
 11   end loop;
 12 
 13   dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats )  );
 14  end;
 15  /
Total Consistent gets = 201001

PL/SQL procedure successfully completed.

What we are doing here is reading data from table itself (not from the index). We have to do an Index unique scan followed by an Table access by rowid, resulting in Two logical IO's per row. Now lets see what happens in Oracle 11g.

rajesh@ORA11G>
rajesh@ORA11G> declare
  2   l_stats number;
  3  begin
  4   l_stats := get_stats ;
  5   for x in ( select Y from t, (select mod(level,1000)+1 as l
  6      from dual
  7      connect by level <= 100000 ) t1
  8      where t.x = t1.l )
  9   loop
 10    null;
 11   end loop;
 12 
 13   dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats )  );
 14  end;
 15  /
Total Consistent gets = 102602

PL/SQL procedure successfully completed.

you can see we got only single IO per row. In other words Index acess were neutralized. Optimization still works for index even though we acess tables inbetween them.

Table Self Join

When we add the same table twice in join, it adds up in Oracle 10g but not in Oracle 11g.

rajesh@ORA10G> /* How about Same Table Twice */
rajesh@ORA10G> declare
  2   l_stats number;
  3  begin
  4   l_stats := get_stats ;
  5   for x in ( select t.x from t, (select mod(level,1000)+1 as l
  6      from dual
  7      connect by level <= 100000 ) t1, t t3
  8      where t.x = t1.l
  9      and t3.x = t1.l )
 10   loop
 11    null;
 12   end loop;
 13 
 14   dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats )  );
 15  end;
 16  /
Total Consistent gets = 202002

PL/SQL procedure successfully completed.

rajesh@ORA11G> /* How about Same Table Twice */
rajesh@ORA11G> declare
  2   l_stats number;
  3  begin
  4   l_stats := get_stats ;
  5   for x in ( select t.x from t, (select mod(level,1000)+1 as l
  6      from dual
  7      connect by level <= 100000 ) t1, t t3
  8      where t.x = t1.l
  9      and t3.x = t1.l )
 10   loop
 11    null;
 12   end loop;
 13 
 14   dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats )  );
 15  end;
 16  /
Total Consistent gets = 2602

PL/SQL procedure successfully completed.
Its still equivalent to having table ( 'T' ) only once in the Self join.


Across Fetches

But this optimization doesn't spanned across different fetches.

rajesh@ORA11G> /* Reading same value across fetches */
rajesh@ORA11G> declare
  2   l_stats number;
  3  begin
  4   l_stats := get_stats ;
  5   for x in ( select x from t, (select 1 as l
  6      from dual
  7      connect by level <= 100000 ) t1
  8      where t.x = t1.l )
  9   loop
 10    null;
 11   end loop;
 12 
 13   dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats )  );
 14  end;
 15  /
Total Consistent gets = 1008

PL/SQL procedure successfully completed.

Here we join the data set 'T1' with table 'T'  for the value x = 1 for 100 K iterations.  but the join become sucess for 1000 iterations. so we get consistent gets close to that.

By default cursor for loop does implicit array fetch of 100 rows and looks like consistent gets cannot operate across fetches.


In overall it looks like a nice feature and it seems Oracle 11g have introduced these little tricks and features all over the place
 

Reduced consistent gets in 11g

Oracle database 11g has as Optimization with consistent gets involved in Nested loop joins.

drop table t1 purge;
drop table t2 purge;

create table t1
nologging as
select a.*,rownum as id
from all_objects a
where rownum <= 10000;

create table t2
nologging as
select * from t1;

begin
 dbms_stats.gather_table_stats(user,'T1');
 dbms_stats.gather_table_stats(user,'T2');
end;
/

create index t1_ind on t1(id);
select /*+ use_nl(t2,t1) */ count(*)
from t1, t2
where t1.id = t2.id
/

Tkprof from Oracle 10g database shows me this.


********************************************************************************
select /*+ use_nl(t2,t1) */ count(*)
from t1, t2
where t1.id = t2.id
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.02          0          0          0           0
Fetch        2      0.04       0.04          0      10157          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.06          0      10157          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=10157 pr=0 pw=0 time=46412 us)
  10000   NESTED LOOPS  (cr=10157 pr=0 pw=0 time=50094 us)
  10000    TABLE ACCESS FULL T2 (cr=135 pr=0 pw=0 time=77 us)
  10000    INDEX RANGE SCAN T1_IND (cr=10022 pr=0 pw=0 time=36338 us)(object id 53690)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

Where as Tkprof from Oracle 11g database shows me this.


********************************************************************************
select /*+ use_nl(t2,t1) */ count(*)
from t1, t2
where t1.id = t2.id
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.02          0        288          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.02          0        288          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=288 pr=0 pw=0 time=0 us)
  10000   NESTED LOOPS  (cr=288 pr=0 pw=0 time=26199 us cost=10047 size=80000 card=10000)
  10000    TABLE ACCESS FULL T2 (cr=140 pr=0 pw=0 time=2657 us cost=42 size=40000 card=10000)
  10000    INDEX RANGE SCAN T1_IND (cr=148 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 74845)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

We are seeing a reduced consistent gets in Oracle 11g because the dedicated server decide in keeping the index block after each lookup in the hopes it could use it again right away. Instead of reading and re-reading the block time after time. The dedicated server is caching the block in your pga, so that it doesn't have to get and re-get from buffer cache.


drop table t1 purge;
drop table t2 purge;

create table t1
nologging as
select *
from (
select a.*,rownum as id
from all_objects a
where rownum <= 10000
  )
order by dbms_random.random;

create table t2
nologging as
select * from t1;

begin
 dbms_stats.gather_table_stats(user,'T1');
 dbms_stats.gather_table_stats(user,'T2');
end;
/

create index t1_ind on t1(id);

********************************************************************************
select /*+ use_nl(t2,t1) */ count(*)
from t1, t2
where t1.id = t2.id
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.03          0      10162          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.03          0      10162          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=10162 pr=0 pw=0 time=0 us)
  10000   NESTED LOOPS  (cr=10162 pr=0 pw=0 time=33540 us cost=10047 size=80000 card=10000)
  10000    TABLE ACCESS FULL T2 (cr=140 pr=0 pw=0 time=2911 us cost=42 size=40000 card=10000)
  10000    INDEX RANGE SCAN T1_IND (cr=10022 pr=0 pw=0 time=0 us cost=1 size=4 card=1)(object id 74848)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

As soon as we start reading the index in random fashion the effect well disappered. Thus far optimization seems to be kicking in only if we repeaditly accessing the same block over and over again

PCT on Join Dependent expression

Let me start explaining about what is a Join dependent expression?

An expression consisting of columns from tables directly or indirectly joined through equijoins to the partitioned detail tables on the partitioning key and which is either a dimensional attribute or a dimension hierarchical parent of the joining key is called a join dependent expression and the set of table on path to the detailed table are called join dependent table.

Here is the snippet of code to demonstrate how oracle does rewrite against MV if PCT is enabled through Join dependent expression.

rajesh@ORA11R2> create table t1 as
  2  select * from scott.dept;

Table created.

Elapsed: 00:00:00.57
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t2
  2  partition by list(deptno)
  3  ( partition p1 values (10),
  4    partition p2 values (20),
  5    partition p3 values (30),
  6    partition pmax values (default) )
  7  as select * from scott.emp;

Table created.

Elapsed: 00:00:00.10
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t1 add constraint t1_pk
  2  primary key(deptno);

Table altered.

Elapsed: 00:00:00.23
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t2 add constraint t2_fk
  2  foreign key(deptno) references t1;

Table altered.

Elapsed: 00:00:00.15
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t2 modify deptno not null;

Table altered.

Elapsed: 00:00:00.21
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(user,'T1');
  3     dbms_stats.gather_table_stats(user,'T2');
  4  end;
  5  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.34
rajesh@ORA11GR2>
rajesh@ORA11GR2> create materialized view t1_t2_mv
  2  build immediate
  3  refresh on demand
  4  enable query rewrite as
  5  select t1.deptno,count(*)
  6  from t1, t2
  7  where t1.deptno = t2.deptno
  8  group by t1.deptno;

Materialized view created.

Elapsed: 00:00:01.65
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T1_T2_MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>

Here the table 'T1' is joined to partitioned detail table 'T2' on the partioned key column. so PCT is enabled on this MV through join dependent table (T1). you can see this clearly as below.

rajesh@ORA11GR2> select capability_name,possible,
  2  case when related_text is not null then
  3    '( '||related_text||') '||msgtxt
  4    else msgtxt end as related_text
  5  from table ( explain_mview('T1_T2_MV'));

CAPABILITY_NAME                P RELATED_TEXT
------------------------------ - ------------------------------------------------------------
PCT                            T
REFRESH_COMPLETE               T
REFRESH_FAST                   T
REWRITE                        T
PCT_TABLE                      F ( T1) relation is not a partitioned table
PCT_TABLE                      T ( T2)
REFRESH_FAST_AFTER_INSERT      F ( RAJESH.T1) the detail table does not have a materialized v
                                 iew log
REFRESH_FAST_AFTER_INSERT      F ( RAJESH.T2) the detail table does not have a materialized v
                                 iew log
REFRESH_FAST_AFTER_ONETAB_DML  F see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML     F see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               T
REWRITE_FULL_TEXT_MATCH        T
REWRITE_PARTIAL_TEXT_MATCH     T
REWRITE_GENERAL                T
REWRITE_PCT                    F general rewrite is not possible or PCT is not possible on an
                                 y of the detail tables
PCT_TABLE_REWRITE              F ( T1) relation is not a partitioned table
PCT_TABLE_REWRITE              F ( T2) PCT is enabled through a join dependency

17 rows selected.

Elapsed: 00:00:00.96
rajesh@ORA11GR2> select detailobj_name, detail_partition_name, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='T1_T2_MV';

DETAILOBJ_NAME                 DETAIL_PARTITION_NAME          FRESH
------------------------------ ------------------------------ -----
T2                             P1                             FRESH
T2                             P2                             FRESH
T2                             P3                             FRESH
T2                             PMAX                           FRESH

Elapsed: 00:00:00.01
rajesh@ORA11GR2>  

rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select count(*) from t2 where deptno = 10;

Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2337769562
------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |          |     1 |     6 |            |          |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV |     1 |     6 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1_T2_MV"."DEPTNO"=10)

rajesh@ORA11GR2> select count(*) from t2 where deptno = 30;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2337769562
------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |          |     1 |     6 |            |          |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV |     1 |     6 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1_T2_MV"."DEPTNO"=30)

rajesh@ORA11GR2> select count(*) from t2 where deptno = 20;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2337769562
------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |          |     1 |     6 |            |          |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| T1_T2_MV |     1 |     6 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1_T2_MV"."DEPTNO"=20)

rajesh@ORA11GR2> set autotrace off;

so far everything looks fine. But what happens when a particular partition becomes STALE in this Mview ?

rajesh@ORA11GR2> delete from t2 where deptno = 20
  2  and rownum = 1;

1 row deleted.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> select detailobj_name, detail_partition_name, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='T1_T2_MV';

DETAILOBJ_NAME                 DETAIL_PARTITION_NAME          FRESH
------------------------------ ------------------------------ -----
T2                             P1                             FRESH
T2                             P2                             STALE
T2                             P3                             FRESH
T2                             PMAX                           FRESH

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select count(*) from t2 where deptno = 10;
Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
Plan hash value: 1232830859
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |     3 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |     3 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |     3 |     9 |     3   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | T2   |     3 |     9 |     3   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------

rajesh@ORA11GR2> select count(*) from t2 where deptno = 30;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1232830859
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |     3 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |     3 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |     6 |    18 |     3   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | T2   |     6 |    18 |     3   (0)| 00:00:01 |     3 |     3 |
-----------------------------------------------------------------------------------------------

rajesh@ORA11GR2> select count(*) from t2 where deptno = 20;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1232830859
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |     3 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |     3 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |     5 |    15 |     3   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | T2   |     5 |    15 |     3   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>

Oracle does not rewrite against partial stale materialized views if partition change tracking on the changed table is enabled by the presence of join dependent expression in the materialized view.

this is clearly mentioned in product docs, so take care when defining MView with PCT enabled by Join dependent tracking 

Partitioned Plans in Oracle 11GR2

Oracle database 11GR2 is designed extensively for having different access paths for each partition wise data access. What does it means is that starting with oracle 11g you can index some partitions and not others.  If you have index for some partitions oracle may pickup Index range scan for those partitions and full partition table acess for others.

 rajesh@ORA11GR2> create table t
  2  partition by range(dt)
  3  ( partition p1 values less than ( to_date('01-JAN-2011','dd-mon-yyyy') ),
  4    partition p2 values less than ( to_date('01-JAN-2012','dd-mon-yyyy') )
  5  ) as
  6  select a.*,
  7   to_date('01-JAN-2010','dd-mon-yyyy') + mod(rownum,350) as dt,
  8   rownum as id
  9  from all_objects a;

Table created.

Elapsed: 00:00:01.81
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert /*+ append */ into t
  2  select a.*,
  3  to_date('01-JAN-2011','dd-mon-yyyy') + mod(rownum,350) as dt,
  4  rownum as id
  5  from all_objects a;

71230 rows created.

Elapsed: 00:00:01.48
rajesh@ORA11GR2>
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

We have two partitions P1 and P2 both loaded with some datas. Next we will create a Local Unusable index, meaning this index will exist in data dictionary but wont actually populated with data. The index will *exist* but not consume any storage.

rajesh@ORA11GR2> create index t_ind on
  2  t(id) local unusable;

Index created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats
  3     ( ownname=>user,
  4       tabname=>'T',
  5       estimate_percent=>dbms_stats.auto_sample_size,
  6       cascade=>false );
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.42
rajesh@ORA11GR2> SELECT segment_name, partition_name, segment_type
  2  FROM user_segments
  3  where segment_name ='T_IND';

no rows selected

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

Now we will have only one acess path for queries, that is Full partition access.


rajesh@ORA11GR2> variable x number;
rajesh@ORA11GR2> exec :x := 42;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select * from t where id = :x;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3557914527
--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     2 |   220 |   645   (1)| 00:00:08 |       |       |
|   1 |  PARTITION RANGE ALL|      |     2 |   220 |   645   (1)| 00:00:08 |     1 |     2 |
|*  2 |   TABLE ACCESS FULL | T    |     2 |   220 |   645   (1)| 00:00:08 |     1 |     2 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=TO_NUMBER(:X))
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;

Now, if we rebuild the index on the partion P2, then plan changes like this.

rajesh@ORA11GR2> alter index t_ind rebuild
  2  partition p2 nologging;

Index altered.

Elapsed: 00:00:00.18
rajesh@ORA11GR2>
rajesh@ORA11GR2> SELECT segment_name, partition_name, segment_type
  2  FROM user_segments
  3  where segment_name ='T_IND';

SEGMENT_NAME                   PARTITION_NAME                 SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
T_IND                          P2                             INDEX PARTITION

Elapsed: 00:00:00.01
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats
  3     ( ownname=>user,
  4       tabname=>'T',
  5       estimate_percent=>dbms_stats.auto_sample_size,
  6       cascade=>false,
  7      method_opt=>'for all indexed columns size 254',
  8      partname=>'P2');
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.04
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select * from t where id = 42;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3602643409
---------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | Pstart | Pstop |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |     2 |        |       |
|   1 |  VIEW                                | VW_TE_2 |     2 |        |       |
|   2 |   UNION-ALL                          |         |       |        |       |
|   3 |    PARTITION RANGE SINGLE            |         |     1 |      2 |     2 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T       |     1 |      2 |     2 |
|*  5 |      INDEX RANGE SCAN                | T_IND   |     1 |      2 |     2 |
|   6 |    PARTITION RANGE SINGLE            |         |     1 |      1 |     1 |
|*  7 |     TABLE ACCESS FULL                | T       |     1 |      1 |     1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("ID"=42)
   7 - filter("ID"=42)

rajesh@ORA11GR2>

 This demonstrates that the optimizer has the ability to develop a query plan with different methods for accessing different partitions, Index range scan for partition P2 and Full partition scan for partition P1.

Incremental statistics does work with locked statistics

when testing this scheme, I found that after locking the statistics on several of the older partitions and applying DML activity, dbms_stats.gather_table_stats ignores the fact that the incremental preference for the table is set to TRUE, and gathers global stats via full table scan.

rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t
  2  partition by range(dt)
  3  (  partition p1 values less than ( to_date('01-FEB-2012','dd-mon-yyyy') ) ,
  4     partition p2 values less than ( to_date('01-MAR-2012','dd-mon-yyyy') ) ,
  5     partition p3 values less than ( to_date('01-APR-2012','dd-mon-yyyy') ) ,
  6     partition p4 values less than ( to_date('01-MAY-2012','dd-mon-yyyy') ) ,
  7     partition p5 values less than ( to_date('01-JUN-2012','dd-mon-yyyy') ) ,
  8     partition p6 values less than ( to_date('01-JUL-2012','dd-mon-yyyy') ) ,
  9     partition p7 values less than ( to_date('01-AUG-2012','dd-mon-yyyy') ) ,
 10     partition p8 values less than ( to_date('01-SEP-2012','dd-mon-yyyy') ) ,
 11     partition p9 values less than ( to_date('01-OCT-2012','dd-mon-yyyy') ) ,
 12     partition p10 values less than ( to_date('01-NOV-2012','dd-mon-yyyy') ) ,
 13     partition p11 values less than ( to_date('01-DEC-2012','dd-mon-yyyy') ) ,
 14     partition p12 values less than ( to_date('01-JAN-2013','dd-mon-yyyy') ) ,
 15     partition pmax values less than ( maxvalue )
 16  ) as
 17  select a.*, add_months( to_date('05-JAN-2012','dd-mon-yyyy'), mod(rownum,12) ) dt
 18  from all_objects a;

Table created.

Elapsed: 00:00:02.32
rajesh@ORA11GR2> begin
  2     dbms_stats.set_table_prefs
  3     (ownname=>user,
  4      tabname=>'T',
  5      pname=>'INCREMENTAL',
  6      pvalue=>'TRUE');
  7
  8     dbms_stats.set_table_prefs
  9     (ownname=>user,
 10      tabname=>'T',
 11      pname=>'GRANULARITY',
 12      pvalue=>'AUTO');
 13
 14     dbms_stats.set_table_prefs
 15     (ownname=>user,
 16      tabname=>'T',
 17      pname=>'PUBLISH',
 18      pvalue=>'TRUE');
 19  end;
 20  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.75
rajesh@ORA11GR2>
rajesh@ORA11GR2> column incremental format a20;
rajesh@ORA11GR2> column granularity format a20;
rajesh@ORA11GR2> column publish format a20;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select  dbms_stats.get_prefs('INCREMENTAL',user,'T') as incremental,
  2             dbms_stats.get_prefs('GRANULARITY',user,'T') as granularity,
  3             dbms_stats.get_prefs('PUBLISH',user,'T') as publish
  4  from dual;

INCREMENTAL          GRANULARITY          PUBLISH
-------------------- -------------------- --------------------
TRUE                 AUTO                 TRUE

Elapsed: 00:00:00.82
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.37
rajesh@ORA11GR2>
rajesh@ORA11GR2> select partition_name, stattype_locked, last_analyzed, num_rows
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by last_analyzed desc;

PARTITION_NAME                 STATT LAST_ANALYZED          NUM_ROWS
------------------------------ ----- -------------------- ----------
                                     09-apr-2012 12:55:31      82804
P3                                   09-apr-2012 12:55:30       6901
P9                                   09-apr-2012 12:55:30       6900
P6                                   09-apr-2012 12:55:30       6900
P12                                  09-apr-2012 12:55:29       6900
P2                                   09-apr-2012 12:55:29       6901
P5                                   09-apr-2012 12:55:29       6901
P11                                  09-apr-2012 12:55:29       6900
P8                                   09-apr-2012 12:55:29       6900
P4                                   09-apr-2012 12:55:29       6901
P1                                   09-apr-2012 12:55:29       6900
P7                                   09-apr-2012 12:55:28       6900
P10                                  09-apr-2012 12:55:28       6900
PMAX                                 09-apr-2012 12:55:28          0

14 rows selected.

Elapsed: 00:00:00.82
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2    dbms_stats.lock_partition_stats
  3    ( ownname=>user,
  4      tabname=>'T',
  5      partname=>'P3');
  6
  7    dbms_stats.lock_partition_stats
  8    ( ownname=>user,
  9      tabname=>'T',
 10      partname=>'P4');
 11  end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56
rajesh@ORA11GR2>
rajesh@ORA11GR2> delete from t partition (p3);

6901 rows deleted.

Elapsed: 00:00:00.67
rajesh@ORA11GR2>
rajesh@ORA11GR2> delete from t partition (p7);

6900 rows deleted.

Elapsed: 00:00:00.67
rajesh@ORA11GR2>
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.54
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.48
rajesh@ORA11GR2> select partition_name, stattype_locked, last_analyzed, num_rows
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by last_analyzed desc
  5  /
PARTITION_NAME                 STATT LAST_ANALYZED          NUM_ROWS
------------------------------ ----- -------------------- ----------
                                     09-apr-2012 12:56:19      69003
P9                                   09-apr-2012 12:56:19       6900
PMAX                                 09-apr-2012 12:56:19          0
P8                                   09-apr-2012 12:56:19       6900
P7                                   09-apr-2012 12:56:18          0
P6                                   09-apr-2012 12:56:18       6900
P2                                   09-apr-2012 12:56:17       6901
P5                                   09-apr-2012 12:56:17       6901
P11                                  09-apr-2012 12:56:16       6900
P12                                  09-apr-2012 12:56:16       6900
P1                                   09-apr-2012 12:56:15       6900
P10                                  09-apr-2012 12:56:15       6900
P3                             ALL   09-apr-2012 12:55:30       6901
P4                             ALL   09-apr-2012 12:55:29       6901

14 rows selected.

Elapsed: 00:00:01.79
rajesh@ORA11GR2>

As you can see that oracle has scanned all the partitions by ignoring the fact that the Incremental preference for this table is set to True.