2010-04-23

Paging before join and aggregation

First test data.


drop table chi purge;

drop table par purge;


create table par
( o varchar2(30)
, t varchar2(30)
, n varchar2(30)
, constraint par_pk primary key(o,t,n))
organization index ;

create table chi
( o varchar2(30)
, t varchar2(30)
, n varchar2(30)
, c varchar2(30)
, constraint chi_pk primary key(o,t,n,c)
, constraint chi_par_fk foreign key(o,t,n) references par(o,t,n))
organization index ;

insert into par select distinct owner, object_type, object_name from dba_objects;

insert into chi(o,t,n,c)
select distinct ta.owner, ob.object_type, ta.table_name, ta.column_name
from all_tab_columns ta
inner join all_objects ob
on ta.owner=ob.owner and TA.TABLE_NAME = ob. object_name
;

commit;

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

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



Query to start with and the info about execution.


SQL> SELECT n,t,o,cou
2 FROM ( SELECT pa.o, pa.t, pa.n, COUNT ( * ) cou
3 FROM par pa LEFT OUTER JOIN chi ch
4 ON pa.o = ch.o and pa.t = ch.t and pa.n = ch.n
5 GROUP BY pa.o, pa.t, pa.n
6 ORDER BY pa.t, pa.o, pa.n)
7 WHERE ROWNUM < 3;

N T O COU
------------------------------ ------------------------------ ------------------------------ ----------
C_COBJ# CLUSTER SYS 1
C_FILE#_BLOCK# CLUSTER SYS 3

Elapsed: 00:00:02.26

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 2 |00:00:02.19 | 2014 |
| 2 | VIEW | | 1 | 112K| 2 |00:00:02.19 | 2014 |
|* 3 | SORT GROUP BY STOPKEY | | 1 | 112K| 2 |00:00:02.19 | 2014 |
| 4 | MERGE JOIN OUTER | | 1 | 112K| 166K|00:00:01.93 | 2014 |
| 5 | SORT JOIN | | 1 | 64558 | 64558 |00:00:00.52 | 666 |
| 6 | INDEX FULL SCAN DESCENDING| PAR_PK | 1 | 64558 | 64558 |00:00:00.06 | 666 |
|* 7 | SORT JOIN | | 64558 | 112K| 112K|00:00:00.87 | 1348 |
| 8 | INDEX FULL SCAN | CHI_PK | 1 | 112K| 112K|00:00:00.11 | 1348 |
----------------------------------------------------------------------------------------------------



The alternative to do paging first and aggregate only the rows to be retrieved.

Paging done in par inline view line 9, order by need to be done on both line 8 for paging and 14 to get the results back in order after joining and aggregating.



SQL> SELECT pap.n,
2 pap.t,
3 pap.o,
4 COUNT ( * ) cou
5 FROM (SELECT *
6 FROM ( SELECT pa.o, pa.t, pa.n
7 FROM par pa
8 ORDER BY pa.t, pa.o, pa.n)
9 WHERE ROWNUM < 3
10 ) pap
11 LEFT OUTER JOIN chi ch
12 ON pap.o = ch.o AND pap.t = ch.t AND pap.n = ch.n
13 GROUP BY pap.o, pap.t, pap.n
14 ORDER BY pap.t, pap.o, pap.n;

N T O COU
------------------------------ ------------------------------ ------------------------------ ----------
C_COBJ# CLUSTER SYS 1
C_FILE#_BLOCK# CLUSTER SYS 3

Elapsed: 00:00:00.20

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 1 | SORT GROUP BY NOSORT | | 1 | 2 | 2 |00:00:00.20 | 673 |
| 2 | NESTED LOOPS OUTER | | 1 | 2 | 4 |00:00:00.20 | 673 |
| 3 | VIEW | | 1 | 2 | 2 |00:00:00.20 | 666 |
|* 4 | COUNT STOPKEY | | 1 | | 2 |00:00:00.20 | 666 |
| 5 | VIEW | | 1 | 64558 | 2 |00:00:00.20 | 666 |
|* 6 | SORT ORDER BY STOPKEY| | 1 | 64558 | 2 |00:00:00.20 | 666 |
| 7 | INDEX FULL SCAN | PAR_PK | 1 | 64558 | 64558 |00:00:00.06 | 666 |
|* 8 | INDEX RANGE SCAN | CHI_PK | 2 | 1 | 4 |00:00:00.01 | 7 |
-----------------------------------------------------------------------------------------------


Notice cou column cannot be in order by condition.
Join needs to be outer.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.