Home » RDBMS Server » Performance Tuning » How to reduce cost of query ...Give Indexing on FT Scan (Oracle 10g)
| How to reduce cost of query ...Give Indexing on FT Scan [message #580169] |
Thu, 21 March 2013 01:52  |
swapnil_naik
Messages: 269
Registered: December 2009
Location: Mumbai
|
Senior Member |
|
|
Hi Friend,
I have Following wjich takes some minutes to executes i want to be tune
so this query Executes fast.
Query :
SELECT a.CHDR_EXCH_CD ,TMHR_EXCH_TM_CD,'S' Sec_type,
round(SUM (Decode(csdt_Depo_Typ,'I',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty* (CSDT_HAIRCUT/ 100) ),0)),2)im_AMT,
Round(SUM (Decode(csdt_Depo_Typ,'A',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty* (CSDT_HAIRCUT/ 100) ),0)),2)Aim_AMT,
round(SUM (Decode(csdt_Depo_Typ,'M',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty* (CSDT_HAIRCUT/ 100) ),0)),2)Mtm_AMT,
Round(SUM (Decode(csdt_Depo_Typ,'T',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty* (CSDT_HAIRCUT/ 100) ),0)),2)AMTM_AMT,
0 REFUND_AMT, 0 wITHDRAWL_AMT
FROM MG_COLL_HDR a, mS_TM_HCUT_LINK C ,MG_COLL_SCP_DTL B
WHERE A.CHDR_CLNT_CD=C.tmhr_tm_cd
AND C.tmhr_tm_cd = B.CSDT_CHDR_CLNT_CD
AND a.CHDR_TRANS_NUM = B.CSDT_CHDR_TRANS_NUM
AND a.CHDR_CLNT_CD=DECODE(:P_TM_CD,'ALL', A.CHDR_CLNT_CD, :P_TM_CD)
AND a.CHDR_EXCH_CD=decode(:P_EXCH,'ALL',a.CHDR_EXCH_CD,:P_exch)
AND a.CHDR_TRANS_DATE <=:P_DT_FR
GROUP BY a.CHDR_EXCH_CD,'S',TMHR_EXCH_TM_CD
Explain Plan Result :
Plan
SELECT STATEMENT ALL_ROWS
Cost: 1,669 Bytes: 67 Cardinality: 1
15 HASH GROUP BY
Bytes: 67 Cardinality: 1
14 CONCATENATION
6 FILTER
5 HASH JOIN
Cost: 1,647 Bytes: 1,139 Cardinality: 17
3 HASH JOIN
Cost: 167 Bytes: 88,040 Cardinality: 2,840
1 TABLE ACCESS FULL TABLE TM.MS_TM_HCUT_LINK Object Instance: 2
Cost: 4 Bytes: 10,406 Cardinality: 946
2 INDEX FAST FULL SCAN INDEX TM.CHDR_MTM
Cost: 163 Bytes: 56,620 Cardinality: 2,831
4 TABLE ACCESS FULL TABLE TM.MG_COLL_SCP_DTL Object Instance: 3
Cost: 1,477 Bytes: 14,554,044 Cardinality: 404,279
13 FILTER
12 TABLE ACCESS BY INDEX ROWID TABLE TM.MG_COLL_SCP_DTL Object Instance: 3
Cost: 3 Bytes: 36 Cardinality: 1
11 NESTED LOOPS
Cost: 21 Bytes: 67 Cardinality: 1
9 HASH JOIN
Cost: 9 Bytes: 124 Cardinality: 4
7 INDEX RANGE SCAN INDEX TM.CHDR_MTM
Cost: 4 Bytes: 80 Cardinality: 4
8 TABLE ACCESS FULL TABLE TM.MS_TM_HCUT_LINK Object Instance: 2
Cost: 4 Bytes: 10,406 Cardinality: 946
10 INDEX RANGE SCAN INDEX (UNIQUE) TM.CSDT_PK
Cost: 2 Cardinality: 1
After i see result , no 4 in explain plan result gives TABLE ACCESS FULL . i want to be indexing on that how to do this..
This table MG_COLL_SCP_DTL have index like this
CREATE UNIQUE INDEX CSDT_PK ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM, CSDT_PROD_TYP, CSDT_TRAN_SR_NO, CSDT_CHDR_CDTL_COLL_TYP, CSDT_CHDR_CDTL_COLL_TYP_CD,
CSDT_STSC_CD, CSDT_CHDR_CLNT_CD, CSDT_CHDR_CLNT_TM_CD)
CREATE INDEX INDX_CSDT_VW ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM, CSDT_STATUS, CSDT_PROD_TYP, CSDT_STSC_CD)
CREATE INDEX INDX_TRANS_NUM ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM)
CREATE INDEX INDX_VIEW_MTM ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM, CSDT_PROD_TYP, CSDT_CHDR_CDTL_COLL_TYP, CSDT_CHDR_CDTL_COLL_TYP_CD, CSDT_STSC_CD,
CSDT_STATUS)
CREATE INDEX MG_COLL_SCP_DTL_I_CSDT_STSC_CD ON MG_COLL_SCP_DTL
(CSDT_STSC_CD)
How to Reduce cost ???
|
|
|
|
| Re: How to reduce cost of query ...Give Indexing on FT Scan [message #580191 is a reply to message #580169] |
Thu, 21 March 2013 03:46  |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Use the following method to generate explain plans, the output is easier to read:
[code]
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
If you remove all the functions from the select clause, how long does it take to run?
|
|
|
|
|
|
|
|
| Re: How to reduce cost of query ...Give Indexing on FT Scan [message #580332 is a reply to message #580169] |
Fri, 22 March 2013 16:10 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. As cookiemonster already asked - What happens when you remove call to Cal_Scheme_Rate function from select?
2. The query contains:
...AND a.CHDR_CLNT_CD=DECODE(:P_TM_CD,'ALL', A.CHDR_CLNT_CD, :P_TM_CD)
AND a.CHDR_EXCH_CD=decode(:P_EXCH,'ALL',a.CHDR_EXCH_CD,:P_exch)
as the same column(s) appear on both sides of = operator(a.CHDR_CLNT_CD,a.CHDR_EXCH_CD ) - optimizer has difficulties using index, so
it may use full table scan (even if you are apssing values for both parameters).
Try using dynamic SQL or a number of different SQL statements.
HTH
|
|
|
|
Goto Forum:
Current Time: Wed Dec 18 00:40:14 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
