Oracle Standard Index vs Reverse-key Index Comparison Testing
Reverse-Key Index:
reverses the key value before entering it in the index.
e.g
1000 -> 0001
2054 -> 4502
3012 -> 2103
When inserting, values are spread across the whole index structure and contention for index blocks can be reduced.
However, when querying data within a range, performance can be degraded.
-- Make two exactly same-structured tables as follows:
create table test_tab_A
(idno number(10),
datetime timestamp
) initrans 100;
create table test_tab_B
(idno number(10),
datetime timestamp
) initrans 100;
-- In the first table, create a primary key using standard b-tree index
alter table test_tab_A add constraint PK_TEST_TAB_A
primary key(idno) using index;
-- In the second table, create a primary key using reverse-key index
alter table test_tab_B add constraint PK_R_TEST_TAB_B
primary key(idno) using index reverse;
-- get current session SPID, which will be needed to look for the trace file.
col spid format a10
select p.spid as spid,s.sid as sid,s.serial# as serial#
from v$session s,v$process p
where s.audsid = userenv('sessionid') and s.paddr = p.addr;
SPID SID SERIAL#
---------- ---------- ----------
8915 488 26211
alter session set events '10046 trace name context forever, level 12';
/********************/
/* Test SQL Insert */
/********************/
declare
v_datetime timestamp default to_timestamp('2001-01-01','yyyy-mm-dd');
begin
for i in 1..100000 loop
insert into test_tab_A(idno,datetime)
values(i,v_datetime);
v_datetime:=v_datetime+1/100;
end loop;
commit;
end;
/
declare
v_datetime timestamp default to_timestamp('2001-01-01','yyyy-mm-dd');
begin
for i in 1..100000 loop
insert into test_tab_B(idno,datetime)
values(i,v_datetime);
v_datetime:=v_datetime+1/100;
end loop;
commit;
end;
/
exec dbms_stats.gather_table_stats(null,'TEST_TAB_A',method_opt=>'FOR ALL COLUMNS SIZE 10',cascade => TRUE);
exec dbms_stats.gather_table_stats(null,'TEST_TAB_B',method_opt=>'FOR ALL COLUMNS SIZE 10',cascade => TRUE);
/********************/
/* Test Range Query */
/********************/
select * from test_tab_A where idno between 5000 and 5100;
select * from test_tab_B where idno between 5000 and 5100;
alter session set events '10046 trace name context off';
/* Check Result */
$ tkprof testdb_ora_8915.trc report.txt
$ cat report.txt
###########################
declare
v_datetime timestamp default to_timestamp('2001-01-01','yyyy-mm-dd');
begin
for i in 1..100000 loop
insert into test_tab_A(idno,datetime)
values(i,v_datetime);
v_datetime:=v_datetime+1/100;
end loop;
commit;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 4.09 4.14 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 4.09 4.14 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.01 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.01 0.01
********************************************************************************
SQL ID: 0vr7mtnh3haxy Plan Hash: 0
INSERT INTO TEST_TAB_A(IDNO,DATETIME)
VALUES
(:B2 ,:B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 9.48 11.22 2973 1661 314256 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100001 9.48 11.22 2973 1661 314256 100000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=2 pr=5 pw=0 time=22948 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 2 0.00 0.00
db file sequential read 2973 0.07 1.64
log file switch completion 2 0.04 0.05
log buffer space 1 0.03 0.03
********************************************************************************
declare
v_datetime timestamp default to_timestamp('2001-01-01','yyyy-mm-dd');
begin
for i in 1..100000 loop
insert into test_tab_B(idno,datetime)
values(i,v_datetime);
v_datetime:=v_datetime+1/100;
end loop;
commit;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 4.04 4.11 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 4.04 4.11 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.01 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.02 0.02
********************************************************************************
SQL ID: 7f7859v9dqbku Plan Hash: 0
INSERT INTO TEST_TAB_B(IDNO,DATETIME)
VALUES
(:B2 ,:B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 9.55 11.21 3191 2227 315764 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100001 9.55 11.21 3191 2227 315764 100000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=2 pr=5 pw=0 time=1603 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 3191 0.04 1.70
log file switch completion 1 0.01 0.01
********************************************************************************
SQL ID: 40gqct8ddyd9c Plan Hash: 2608084040
select *
from
test_tab_A where idno between :"SYS_B_0" and :"SYS_B_1"
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.00 0.00 0 5 0 101
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 5 0 101
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
101 101 101 FILTER (cr=5 pr=0 pw=0 time=458 us)
101 101 101 TABLE ACCESS BY INDEX ROWID TEST_TAB_A (cr=5 pr=0 pw=0 time=352 us cost=3 size=1648 card=103)
101 101 101 INDEX RANGE SCAN PK_TEST_TAB_A (cr=3 pr=0 pw=0 time=138 us cost=2 size=0 card=103)(object id 89108)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.72 0.80
********************************************************************************
SQL ID: 8czwrgwdas5wy Plan Hash: 215152163
select *
from
test_tab_B where idno between :"SYS_B_0" and :"SYS_B_1"
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.00 0.00 0 383 1 101
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 383 1 101
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
101 101 101 FILTER (cr=383 pr=0 pw=0 time=448 us)
101 101 101 TABLE ACCESS FULL TEST_TAB_B (cr=383 pr=0 pw=0 time=344 us cost=103 size=1680 card=105)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 1.44 1.44
********************************************************************************
留言列表
{{ article.title }}