| Slow Query [message #142128] |
Thu, 13 October 2005 09:25  |
abc123
Messages: 24
Registered: March 2005
Location: sa
|
Junior Member |
|
|
Hi,
I have the follwing query in java code which takes hugh time to execute:
Select
i.client_cd,i.settlement_type,i.settlement_no,i.exchng_cd,i.form_type,i.dep_
ac_no,s.long_nm,
i.series_prefix,i.serial_no,i.cm_bp_id,i.execution_dt,id.isin_cd,id.q
ty,id.dpm_no
From Isin s, Instruction i, Instruction_detail id
Where i.depos_cd = id.depos_cd
And i.dp_id = id.dp_id
And i.client_cd = id.client_cd
And i.instruction_no = id.instruction_no
And id.isin_cd = s.isin_cd
And id.stat != 'D'
And i.depos_cd = ?
And i.dp_id = ?
And i.dep_ac_no = nvl( ? , i.dep_ac_no)
And i.execution_dt between nvl( ? , i.execution_dt) and nvl( ? ,
i.execution_dt)
And id.entry_dt = nvl( ? , id.entry_dt)
And id.bbo_id = 'SPEEDE'
And id.entry_dt = ?
order by i.dep_ac_no;
The tables have following indexes:
1.isin
PK_ISIN(isin_cd)
2.instruction
PK_INSTRUCTION(DP_ID,CLIENT_CD,INSTRUCTION_NO)
IDX_INSTRUNCTION_1(DEPOS_CD, DP_ID, FORM_TYPE, SERIES_PREFIX, SERIAL_NO)
3. instruction_detail
PK_INSTRUCTION_DETAIL(DEPOS_CD, DP_ID, TRAN_TYPE, TRAN_SUB_TYPE, DPM_REF_NO)
IDX_INSTRUCTION_DETAIL_2(DEPOS_CD, DP_ID, DPM_REF_NO)
Also table have following no of records:
1.isin=33482
2.instruction=2338420
3.instruction_detail=2515900
The execution plan is as follows:
SELECT STATEMENT Optimizer=CHOOSE
SORT (ORDER BY)
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF 'INSTRUCTION'
INDEX (RANGE SCAN) OF 'IDX_INSTRUNCTION_1' (NON-NIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'INSTRUCTION_DETAIL'
INDEX (RANGE SCAN) OF 'PK_INSTRUCTION_DETAIL' (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'ISIN'
INDEX (UNIQUE SCAN) OF 'PK_ISIN' (UNIQUE)
I am using oracle 8.
Please suggest to make it faster.
|
|
|
|
| Re: Slow Query [message #142222 is a reply to message #142128] |
Thu, 13 October 2005 18:39  |
cybotto
Messages: 12
Registered: October 2005
|
Junior Member |
|
|
|
NVL joining to itself in comparision. When you use JAVA can't you simply elimate that lines where ? is NULL. When its null don't use that line.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
