Qs Why the larger
values of disk=45 , query = 525 when using Global indexes & Non-prefixed
local indexes
VERSUS Local Prefixed
indexes where dis = 0 & query = 0 in the CASES below ?
Qs How significantly can
this affect the performance thruput ?
CASE : Comparison with
global partitioned index
INDEX: on sol_id,
tran_date, gl_sub_Head_code and crncy_code, globally partitioned on range of
tran_date
TABLE: GST table with 10
million rows, with 2 equal partitions on range of tran_date
Query: Select queries with
key, returning 500 rows.
********************************************************************************
select crncy_code into
:b0
from
gst_part where
(((sol_id=:b1 and tran_date=:b2) and gl_sub_head_code=:b3)
and
crncy_code=:b0)
call
count cpu
elapsed disk
query current
rows
------- ------
-------- ---------- ---------- ---------- ----------
----------
Parse
1 0.12
0.35 46
526
0
0
Execute
500 0.04
0.03
0
0
0
0
Fetch
500 0.01
0.01
16
2000
0 500
------- ------
-------- ---------- ---------- ---------- ----------
----------
total
1001 0.17
0.40
62
2526
0 500
Misses in library cache
during parse: 1
Optimizer goal:
CHOOSE
Parsing user id: 5
(SYSTEM)
Rows
Row Source Operation
-------
---------------------------------------------------
500 PARTITION RANGE SINGLE PARTITION: KEY KEY
(cr=2000 r=16 w=0 time=10503 us)
500 INDEX UNIQUE SCAN OBJ#(26318) PARTITION: KEY KEY (cr=2000 r=16
w=0 time=9188 us)(object id 26318)
Rows
Execution Plan
-------
---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
500 PARTITION RANGE (SINGLE)
PARTITION:KEYKEY
500 INDEX (UNIQUE SCAN) OF 'IDX_GL_SUB_HEAD_TRAN_PART'
(UNIQUE)
PARTITION:KEYKEY
********************************************************************************
CASE 1: Comparison with
non-prefixed index
INDEX: Non-prefixed on
sol_id, tran_date, gl_sub_head_code and crncy_code
TABLE: GST table with 10
million rows with 2 equal partitions on range of tran_date
********************************************************************************
select crncy_code into
:b0
from
gst_part where
(((sol_id=:b1 and tran_date=:b2) and gl_sub_head_code=:b3)
and
crncy_code=:b0)
call
count cpu
elapsed disk
query current
rows
------- ------
-------- ---------- ---------- ---------- ----------
----------
Parse
1 0.12
0.34 42
422
0
0
Execute
500 0.02
0.03
0
0
0
0
Fetch
500 0.03
0.07
14
2000
0 500
------- ------
-------- ---------- ---------- ---------- ----------
----------
total
1001 0.17
0.45
56
2422
0 500
Misses in library cache
during parse: 1
Optimizer goal:
CHOOSE
Parsing user id: 5
(SYSTEM)
Rows
Row Source Operation
-------
---------------------------------------------------
500 PARTITION RANGE SINGLE PARTITION: KEY KEY
(cr=2000 r=14 w=0 time=70754 us)
500 INDEX UNIQUE SCAN OBJ#(26279) PARTITION: KEY KEY (cr=2000 r=14
w=0 time=69432 us)(object id 26279)
Rows
Execution Plan
-------
---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
500 PARTITION RANGE (SINGLE)
PARTITION:KEYKEY
500 INDEX (UNIQUE SCAN) OF 'IDX_GL_SUB_HEAD_TRAN_PART'
(UNIQUE)
PARTITION:KEYKEY
********************************************************************************
CASE 2: Comparison with
prefixed index
INDEX: on tran_date,sol_id,
gl_sub_head_code and crncy_code
TABLE: GST table with 10
million rows with 2 equal partitions, on range of tran_date
********************************************************************************
select crncy_code into
:b0
from
gst_part where
(((sol_id=:b1 and tran_date=:b2) and gl_sub_head_code=:b3)
and
crncy_code=:b0)
call
count cpu
elapsed disk
query current
rows
------- ------
-------- ---------- ---------- ---------- ----------
----------
Parse
1 0.00
0.00 0
0
0
0
Execute
500 0.02
0.02
0
0
0
0
Fetch
500 0.02
0.04
16
2000
0 500
------- ------
-------- ---------- ---------- ---------- ----------
----------
total
1001 0.04
0.07
16
2000
0 500
Misses in library cache
during parse: 1
Optimizer goal:
CHOOSE
Parsing user id: 5
(SYSTEM)
Rows
Row Source Operation
-------
---------------------------------------------------
500 INDEX UNIQUE SCAN OBJ#(26298) (cr=2000 r=16 w=0 time=36529
us)(object id 26298)
Rows
Execution Plan
-------
---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
500 INDEX (UNIQUE SCAN) OF 'IDX_GL_SUB_HEAD_TRAN_PART'
(UNIQUE)
********************************************************************************