MY ORIGINAL POSTING - Response time was nearly 5 hours
Tables used
-----------
CUST_CRSPD , SET_CNTRL_REC S , SET_CNTRL_REC S2 , BUS_UNIT_OPT_AR , CUSTOMER
, STMT_ITEM_TMP4
Out of which 2 major tables which has close to 6 lakhs records are
cust_crspd and customer.
Indexes created on Customer table
---------------------------------
customer0
---------
name1,setid,custid
customer1
---------
cntct_seq_num,setid,custid
customera
---------
coporate_custid,corporate_setid,custid,setid
customerb
---------
remit_from_custid,remit_from_setid
customerc
---------
crspd_custid,crspd_setid
customerd
----------
crspd_cust_id,crspd_setid,cust_id,setid,cntct_sqe_num
customer(unique)
--------
custid,setid
SELECT
distinct 8134 ,
STMT_ITEM_TMP4.BUSINESS_UNIT ,
CUSTOMER.CUST_ID ,
CUSTOMER.CRSPD_SETID ,
CUSTOMER.CRSPD_CUST_ID ,
S2.SETID ,
BUS_UNIT_OPT_AR.ST_ID ,
S.SETID ,
BUS_UNIT_OPT_AR.BANK_CD ,
BUS_UNIT_OPT_AR.BANK_ACCT_KEY ,
' ' ,
CUSTOMER.SETID ,
CUSTOMER.NAMESHORT ,
CUST_CRSPD.CRSPD_ADDR ,
CUST_CRSPD.CRSPD_CNTCT ,
CUST_CRSPD.LANGUAGE_CD ,
CUST_CRSPD.CNTCT_SEQ_NUM ,
CUST_CRSPD.ADDRESS_SEQ_NUM
FROM
CUST_CRSPD ,
SET_CNTRL_REC S ,
SET_CNTRL_REC S2 ,
BUS_UNIT_OPT_AR ,
CUSTOMER ,
STMT_ITEM_TMP4
WHERE
STMT_ITEM_TMP4.PROCESS_INSTANCE = 8134 AND
RTRIM(SUBSTR(CUSTOMER.SETID,1,5)) LIKE 'GEN' AND
RTRIM(SUBSTR(CUSTOMER.CUST_ID,1,15)) LIKE '%' AND
RTRIM(SUBSTR(CUSTOMER.CRSPD_SETID,1,5)) LIKE 'GEN' AND
RTRIM(SUBSTR(CUSTOMER.CRSPD_CUST_ID,1,15)) LIKE '%' AND
RTRIM(SUBSTR(CUST_CRSPD.ST_GROUP,1,2)) LIKE '%' AND
NOT EXISTS ( SELECT 'X' FROM CUST_CRSPD_REM X WHERE X.SETID =
CUST_CRSPD.SETID AND
X.CUST_ID = CUST_CRSPD.CUST_ID AND
X.EFFDT = CUST_CRSPD.EFFDT AND
X.REMIT_ADD_TYPE = STMT_ITEM_TMP4.ST_ID_TYPE) AND
CUST_CRSPD.ST_ID = ' ' AND
('N' = 'Y' OR CUST_CRSPD.ST_HOLD = 'N' OR CUST_CRSPD.ST_HOLD_DT >
TO_DATE('2002-12-30','YYYY-MM-DD')) AND
CUSTOMER.CUST_ID = STMT_ITEM_TMP4.CUST_ID AND
BUS_UNIT_OPT_AR.SETID =( SELECT SETID FROM SET_CNTRL_REC WHERE RECNAME =
'BUS_UNIT_OPT_AR' AND
SETCNTRLVALUE = STMT_ITEM_TMP4.BUSINESS_UNIT ) AND
S.RECNAME = 'BANK_ACCT_TBL' AND
S.SETCNTRLVALUE = STMT_ITEM_TMP4.BUSINESS_UNIT AND
S2.RECNAME = 'STATEMENT_TBL' AND
S2.SETCNTRLVALUE = STMT_ITEM_TMP4.BUSINESS_UNIT AND
CUST_CRSPD.SETID = CUSTOMER.CRSPD_SETID AND
CUST_CRSPD.CUST_ID = CUSTOMER.CRSPD_CUST_ID AND
CUST_CRSPD.EFFDT = ( SELECT MAX(EFFDT) FROM CUST_CRSPD WHERE EFFDT <=
TO_DATE('2002-12-30','YYYY-MM-DD') AND
EFF_STATUS = 'A' AND
SETID = CUSTOMER.CRSPD_SETID AND
CUST_ID = CUSTOMER.CRSPD_CUST_ID)
Y'day I created following index
-------------------------------
CREATE INDEX FCUSTOMER
ON CUSTOMER( RTRIM(SUBSTR(SETID,1,5)),
RTRIM(SUBSTR(CUST_ID,1,15)),
RTRIM(SUBSTR(CRSPD_SETID,1,5)),
RTRIM(SUBSTR(CRSPD_CUST_ID,1,15)))
TABLESPACE "INDEXES" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 16060K NEXT 500K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1)
LOGGING
Tried executing but same poor performance.
Issued ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE then executed the
query. The response time is 19 sec and the query plan is
Elapsed: 00:00:00.19
>From: Regis Biassala <Regis.[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] RE: Can someone clarify this?...
>Date: Fri, 31 Jan 2003 10:49:55 -0000
>
>Post the query again and the index creation statement...please on the LIST
>
>Regis
>
>
>-----Original Message-----
>From: Geluvu 74 [mailto:[Email Address Removed] Friday, January 31, 2003 10:44 AM
>To: LazyDBA.com Discussion
>Subject: Can someone clarify this?...
>
>
>Hi,
>I had a query which was running for 5 hours. I had posted couple of days
>back. After that I created a function based index, still the query plan was
>not using newly created indexes. The performance was same. Y'day i thought
>I
>
>will just try issuing
>alter session set query_rewrite_enabled = true before executing that query.
>The performance was great & response time came to 19 seconds.
>Infact today morning also i tried it wroked fine. But, now i thought i will
>try once more, so i followed same steps, executed alter session command and
>issued that query. Now again it is taking 6 hours. Between the time it
>executed fine and now I haven't made any changes. Can one someone explain
>me
>
>how is this possible. I referred few docs it says this setting query
>rewrite
>
>enabled is wrt materialized views. Then how come the moment i enabled this
>my query worked fine. I have explainplan output stored for all these
>instance. The query plan which it was using was different morning.
>Thanks
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus
Oracle LazyDBA home page