Consider below request:
We need to get the 900 to 1000 records after ordered by time.
We call this kind of request as "paging". Because in most case we encounter this kind of request in web page's redirect buttons: page 1, page 2, page 3.....
We can jump to any page via clicking the conrrespoding buttion.
There are two kinds of paging method in oracle: Rownum paging and Rowid paging.
Almost all the documents which i read all announce that rowid is a better method then rownum for paging.
They said:
For the first a few pages, two methods have similiar performance.But as long as the page number become bigger and bigger, rowid paging perform better and better then rownum paging.
Pay attention to the red part. Is that really true? Let's make a experiment.
First test in 11g. Create test table as below:
11gR2 >create table test(id number,status VARCHAR2(7),type VARCHAR2(19),created date);
Table created.
11gR2 >insert into test select OBJECT_ID,STATUS,OBJECT_TYPE,CREATED from dba_objects;
12926 rows created.
11gR2 >alter table test modify created not null;
Table altered.
11gR2 >create index test_ind1 on test(CREATED);
Index created.
11gR2 >ANALYZE TABLE TEST compute statistics;
Table analyzed.
We are going to read row 900-1000 from table test order by created.
Test 11g paging with rownum method：
11gR2 >select *
from (
select rownum rn,t.*
from
(select id,status,type,created from test order by created) t
where rownum<1000)
where rn >900;
99 rows selected.
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 50949 | 9 (0)| 00:00:01 |
|* 1 | VIEW | | 999 | 50949 | 9 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 999 | 37962 | 9 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST | 12926 | 277K| 9 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | TEST_IND1 | 999 | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
3703 bytes sent via SQL*Net to client
590 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
Test 11g paging with rowid method：
11gR2 >select /*+ ordered use_nl(p s) */ *
from (
select rownum rn,rd
from (select rowid rd from test order by created)
t where rownum<1000) p,
test s
where rn>900 and p.rd=s.rowid; 2 3 4 5 6 7
99 rows selected.
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 52947 | 1003 (0)| 00:00:13 |
| 1 | NESTED LOOPS | | 999 | 52947 | 1003 (0)| 00:00:13 |
|* 2 | VIEW | | 999 | 24975 | 4 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 999 | 11988 | 4 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | TEST_IND1 | 12926 | 239K| 4 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY USER ROWID| TEST | 1 | 28 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
5450 bytes sent via SQL*Net to client
590 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
From the red part we can see rowid paging(19 logic read) do better then rownum paging(22 logic read).
The reason is rowid method reduce the "TABLE ACCESS BY ROWID" for first 900 rows.
Let's check in 10g:
Build the Test table as excatly the same with 11g, created the index and gathered stats.
Test 11g paging with rownum method：
10gR2 >select *
from (
select rownum rn,t.*
from
(select id,status,type,created from test order by created) t
where rownum<1000)
where rn >900;
99 rows selected.
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 50949 | 17 (0)| 00:00:01 |
|* 1 | VIEW | | 999 | 50949 | 17 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 999 | 37962 | 17 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST | 45620 | 1113K| 17 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | TEST_IND1 | 999 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
3842 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
19 logic read, very close to 11g. It is a normal value and close to our expected.
Test 11g paging with rowid method：
10gR2 >select /*+ ordered use_nl(p s) */ *
from (
select rownum rn,rd
from (select rowid rd from test order by created)
t where rownum<200) p,
test s
where rn>100 and p.rd=s.rowid;
99 rows selected.
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 199 | 11144 | 261 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 199 | 11144 | 261 (1)| 00:00:04 |
|* 2 | VIEW | | 199 | 4975 | 62 (2)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 45620 | 534K| 62 (2)| 00:00:01 |
| 5 | INDEX FULL SCAN | TEST_IND1 | 45620 | 846K| 62 (2)| 00:00:01 |
| 6 | TABLE ACCESS BY USER ROWID| TEST | 1 | 31 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
108 consistent gets
0 physical reads
0 redo size
5252 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
wow, why logic turn to 108? The execution plan is same with 11g, but why so big difference in logic read?
rownum rowid
11g: 22 19
10g: 19 108
Why?
Below is the Reason:
11gR2 >select rowid,id from test where rowid in ('AAADSHAABAAAH3hAAA','AAADSHAABAAAH3hAAB','AAADSHAABAAAH3hAAC','AAADSHAABAAAH3hAAD','AAADSHAABAAAH3hAAE','AAADSHAABAAAH3hAAF','AAADSHAABAAAH3hAAG','AAADSHAABAAAH3hAAH','AAADSHAABAAAH3hAAI','AAADSHAABAAAH3hAAJ');
ROWID ID
------------------ ----------
AAADSHAABAAAH3hAAA 20
AAADSHAABAAAH3hAAB 46
AAADSHAABAAAH3hAAC 28
AAADSHAABAAAH3hAAD 15
AAADSHAABAAAH3hAAE 29
AAADSHAABAAAH3hAAF 3
AAADSHAABAAAH3hAAG 25
AAADSHAABAAAH3hAAH 41
AAADSHAABAAAH3hAAI 54
AAADSHAABAAAH3hAAJ 40
10 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
875 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
10gR2 >select rowid,id from test where rowid in ('AAAzo5AABAAAKnWAAA','AAAzo5AABAAAKnWAAB','AAAzo5AABAAAKnWAAC','AAAzo5AABAAAKnWAAD','AAAzo5AABAAAKnWAAE','AAAzo5AABAAAKnWAAF','AAAzo5AABAAAKnWAAG','AAAzo5AABAAAKnWAAH','AAAzo5AABAAAKnWAAI','AAAzo5AABAAAKnWAAJ');
ROWID ID
------------------ ----------
AAAzo5AABAAAKnWAAA 30
AAAzo5AABAAAKnWAAB 8
AAAzo5AABAAAKnWAAC 14
AAAzo5AABAAAKnWAAD 34
AAAzo5AABAAAKnWAAE 45
AAAzo5AABAAAKnWAAF 39
AAAzo5AABAAAKnWAAG 47
AAAzo5AABAAAKnWAAH 51
AAAzo5AABAAAKnWAAI 11
AAAzo5AABAAAKnWAAJ 48
10 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
861 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
A Rowid is made by: OOOOOOFFFBBBBBBRRR
O is object id，F is file id，B is block id，R is row number.
From above we can see, read 10 rows from same block in 11g cost 2 block read while in 10g cost 10 block read.
Further research also confirmed in 10g, every rowid will gernerate an logic read, even all the rowid are come from same block.
But in 11g some new machanism are introduced, if next rowid is in the same block with current row, then current block can be reuse.
GE, AGFA. experienced in 9i/10g/11g/12c, AdvanceReplication, Stream, GoldenGate, RAC, DataGuard, ODA, EXADATA, GridControl. In my career I have always been assigned to handle most complicated and critical oracle issues without solution online, and I hope to record some of them via this blog.
April 24, 2012
rowid paging benefit from 11g new feature
Tags：
rowid rownum paging 11g
Subscribe to:
Post Comments (Atom)
1 Comments:
Thanks a lot Kevin....this one really helps.
Is there a way to speed up the count query (to know the total records) for such pagination query ?
Post a Comment