Home » RDBMS Server » Performance Tuning » Performance tuning in View (Oracle)
Performance tuning in View [message #541239] Mon, 30 January 2012 03:20 Go to next message
bamugan81
Messages: 4
Registered: December 2008
Location: INDIA - CHENNAI
Junior Member
Hi i am using the following view

CREATE OR REPLACE VIEW V_STO_SAM_SKO_TXN_LOG_REPR
AS
(SELECT TXN_CTL_NO , TRANS_TYPE , DOC_NO , DOC_DATE , MATL_CODE , QTY , RATE , SOH_BEF_TRANS , WT_AVG_BEF_TRANS , PO_NO , PO_AMND_NO , VENDOR_CD , DEPT_CODE , SECTION_CD , DR_ACNT_CD , CR_ACNT_CD , REP_FLAG , CONSUM_DATE , TXN_DATE , VALUE , VALUE_BEF_TRANS , QTY_CUM , WT_AVG_AFTER_TRANS , DR_CC_CODE , CR_CC_CODE , CR_PRJ_CODE , DR_PRJ_CODE , DR_EQPT_CODE , CR_EQPT_CODE , REF_TRN_TYPE , REF_DOC_NO , REF_DOC_DATE , WRITE_OFF ,TAG ,O_TRN_TYPE,O_DOC_NO,O_DOC_DATE, TXN_CTL_NO REF_NO FROM t_sto_sko_txn_log WHERE DOC_DATE <= '31-AUG-2010'
UNION
SELECT TXN_CTL_NO , TRANS_TYPE , DOC_NO , DOC_DATE , MATL_CODE , QTY , RATE , SOH_BEF_TRANS , WT_AVG_BEF_TRANS , PO_NO , PO_AMND_NO , VENDOR_CD , DEPT_CODE , SECTION_CD , DR_ACNT_CD , CR_ACNT_CD , REP_FLAG , CONSUM_DATE , TXN_DATE , VALUE , VALUE_BEF_TRANS , QTY_CUM , WT_AVG_AFTER_TRANS , DR_CC_CODE , CR_CC_CODE , CR_PRJ_CODE , DR_PRJ_CODE , DR_EQPT_CODE , CR_EQPT_CODE , REF_TRN_TYPE , REF_DOC_NO , REF_DOC_DATE , WRITE_OFF , FA_TAG ,null O_TRN_TYPE,null O_DOC_NO ,null O_DOC_DATE , ORG_TXN_CTL_NO REF_NO
FROM T_STO_SAM_SKO_TXN_LOG_REPR where DOC_DATE > '31-AUG-2010'
) order by 1 ,2


Table Name
t_sto_sko_txn_log -- PK is TRANS_TYPE , DOC_NO , DOC_DATE
Other index is TXN_CTL_NO
T_STO_SAM_SKO_TXN_LOG_REPR -- PK is TXN_CTL_NO
No Index on table

While quering the view using an condition it will take long time for execution.

Please Help me in tuning the view.
Thanks
Re: Performance tuning in View [message #541263 is a reply to message #541239] Mon, 30 January 2012 04:07 Go to previous message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Please read and follow How to use [code] tags and make your code easier to read?
2) Please read How to tune SQL or Identify Performance Problem and Bottleneck and supply the requested information
Previous Topic: scripts on tablespaces,Indexes and DBJobs
Next Topic: How to find Literal SQL statement
Goto Forum:
  


Current Time: Fri Nov 21 19:51:32 CST 2014

Total time taken to generate the page: 0.14621 seconds