Oracle Database Discussions

Hello Experts,
i have two table (master and detail give as below)
1st table:
CREATE TABLE STR_MRN_M
(
MRN_DIV_CODE VARCHAR2(6 BYTE),
MRN_NUM VARCHAR2(18 BYTE),
MRN_TYPE VARCHAR2(4 BYTE) NOT NULL,
MRN_DATE DATE,
MRN_FROM_CODE VARCHAR2(6 BYTE),
MRN_CHLN_NO VARCHAR2(35 BYTE)
)

primary key constraints( MRN_DIV_CODE AND MRN_NUM)

2nd table:
CREATE TABLE NEWTON_A.STR_MRN_D
(
MRN_DIV_CODE VARCHAR2(6 BYTE),
MRN_NUM VARCHAR2(18 BYTE),
MRN_MODL_CODE VARCHAR2(11 BYTE),
MRN_ITEM_CODE VARCHAR2(18 BYTE),
MRN_ITEM_NE VARCHAR2(1 BYTE),
MRN_CHLN_QTY NUMBER(20,4),
)
index1 on str_mrn_d( MRN_DIV_CODE )
index2 on str_mrn_d( MRN_NUM )

note: there is no foreign key is implemented in both table. also there are other column too which i did not mention.

now i am running the query1 for records:
Query1:
SELECT mrn_num,mrn_date,MRN_GE_NUM,MRN_PO_NUM1,MRN_CHLN_NO,MRN_PASS_AMNT,c.party_party_name,mrn_from_code
FROM str_mrn_m m,cor_party_m c
WHERE mrn_div_code=400001
AND mrn_type ='2401'
and substr(mrn_num,1,8)=20122013
AND mrn_from_code=party_party_code
AND EXISTS (
SELECT 1
FROM str_mrn_d d
WHERE mrn_div_code=m.mrn_div_code
AND mrn_num =m.mrn_num
AND iqc_pndg_qty>0
)
order by mrn_date DESC

query1 taking too much time in running.

but if i remove the index1 from str_mrn_d table, same query running in mili seconds.

my concern: as index are used to increase performance then why index1 creating problem.

also i try with index1 and query2, output is again come in mili seconds.
query2:
SELECT distinct m.mrn_num,m.mrn_date,m.MRN_GE_NUM,m.MRN_PO_NUM1,m.MRN_CHLN_NO,m.MRN_PASS_AMNT,
c.party_party_name,m.mrn_from_code
FROM str_mrn_m m,str_mrn_d d,cor_party_m c
WHERE m.mrn_div_code=d.mrn_div_code
and m.mrn_num =d.mrn_num
and m.mrn_from_code=c.party_party_code
AND m.mrn_div_code=400001
and substr(m.mrn_num,1,8)=20122013
AND m.mrn_type ='2401'
and d.iqc_pndg_qty>0


please suggest me some tips while writing query.

thanks
yash

Edited by: yash_08031983 on Jul 18, 2012 11:32 PM