|
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)
********************************************************************************
|
