| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> High disk , query values with Global & non-prefixed Local indexes
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)
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: VIVEK_SHARMA_at_infosys.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Jul 30 2003 - 03:14:23 CDT
 |
 |