OraFAQ Forum: Performance Tuning » performance problem while extracting the data

Home » RDBMS Server » Performance Tuning » performance problem while extracting the data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

performance problem while extracting the data [message #170227]

Tue, 02 May 2006 14:52

venkatsp
Messages: 22
Registered: March 2005

Junior Member

have one intermediate table.
iam inserting the rows which are derived from a select statement
The select statement having a where clause which joins a view (created by 5 tables)

The problem is select statement which is getting the data is taking more time

i identified the problems like this

1) The view which is using in the select statement is not indexed---is index is necessary on view ????

2) Because the tables which are used to create a view have already properly indexed

3) while extracting the data it is taking the more time

the below query will extract the data and insert the data in the intermediate table

SELECT 1414 report_time,
2 dt_q,
1 hirearchy_no_q,
p.unique_security_c,
p.source_code_c,
p.customer_specific_security_c user_security_c,
p.par_value par_value, exchange_code_c,
(CASE WHEN p.ASK_PRICE_L IS NOT NULL THEN 1
WHEN p.BID_PRICE_L IS NOT NULL THEN 1
WHEN p.STRIKE_PRICE_L IS NOT NULL THEN 1
WHEN p.VALUATION_PRICE_L IS NOT NULL THEN 1 ELSE 0 END) bill_status,
p.CLASS_C AS CLASS,
p.SUBCLASS_C AS SUBCLASS,
p.AGENT_ADDRESS_LINE1_T AS AGENTADDRESSLINE1,
p.AGENT_ADDRESS_LINE2_T AS AGENTADDRESSLINE2,
p.AGENT_CODE1_T AS AGENTCODE1,
p.AGENT_CODE2_T AS AGENTCODE2,
p.AGENT_NAME_LINE1_T AS AGENTNAMELINE1,
p.AGENT_NAME_LINE2_T AS AGENTNAMELINE2,
p.ASK_PRICE_L AS ASKPRICE,
p.ASK_PRICE_DATE_D AS ASKPRICEDATE,
p.ASSET_CLASS_T AS ASSETCLASS
FROM (SELECT
DISTINCT x.*,m.customer_specific_security_c,m.par_value
FROM
HOLDING_M m JOIN ED_DVTKQS_V x ON
m.unique_security_c = x.unique_security_c AND
m.customer_c = 'CONF100005' AND
m.portfolio_c = 24 AND
m.status_c = 1
WHERE exists
(SELECT 1 FROM ED_DVTKQS_V y
WHERE x.unique_security_c = y.unique_security_c
GROUP BY y.unique_security_c
HAVING MAX(y.trading_volume_l) = x.trading_volume_l)) p

any one please give me the valueble suggestions on the performance

Report message to a moderator

Re: performance problem while extracting the data [message #170318 is a reply to message #170227]

Wed, 03 May 2006 05:13

raji_kb02
Messages: 19
Registered: March 2006

Junior Member

Rewrited the query using analytical function. I assume you have more than 1 record for the same unique security but want to fetch the single record which has the maximum trading volume for the security.

I am not sure why distinct is used. Just verify whether it is needed and check the following query results with your original one.

Next time post your explain plan also


SELECT 1414 report_time, 
	2 dt_q, 
	1 hirearchy_no_q, 
	p.unique_security_c, 
	p.source_code_c, 
	p.customer_specific_security_c user_security_c, 
	p.par_value par_value, exchange_code_c, 
	(CASE WHEN p.ASK_PRICE_L IS NOT NULL THEN 1 
		WHEN p.BID_PRICE_L IS NOT NULL THEN 1 
		WHEN p.STRIKE_PRICE_L IS NOT NULL THEN 1 
		WHEN p.VALUATION_PRICE_L IS NOT NULL THEN 1 ELSE 0 END) bill_status, 
	p.CLASS_C AS CLASS, 
	p.SUBCLASS_C AS SUBCLASS, 
	p.AGENT_ADDRESS_LINE1_T AS AGENTADDRESSLINE1, 
	p.AGENT_ADDRESS_LINE2_T AS AGENTADDRESSLINE2, 
	p.AGENT_CODE1_T AS AGENTCODE1, 
	p.AGENT_CODE2_T AS AGENTCODE2, 
	p.AGENT_NAME_LINE1_T AS AGENTNAMELINE1, 
	p.AGENT_NAME_LINE2_T AS AGENTNAMELINE2, 
	p.ASK_PRICE_L AS ASKPRICE, 
	p.ASK_PRICE_DATE_D AS ASKPRICEDATE, 
	p.ASSET_CLASS_T AS ASSETCLASS
FROM (SELECT DISTINCT x.*, m.customer_specific_security_c, m.par_value, 
	FIRST_VALUE(x.trading_volume_l) over (partition by x.unique_security_c order by x.trading_volume_l DESC)  maxvolume	FROM HOLDING_M m JOIN ED_DVTKQS_V x 
		ON m.unique_security_c = x.unique_security_c
	WHERE 
		m.customer_c = 'CONF100005' AND 
		m.portfolio_c = 24 AND 
		m.status_c = 1) p

WHERE -- to remove the redundant records for the same id
	p.trading_volume_l = p.maxvolume

Report message to a moderator

Re: performance problem while extracting the data [message #170429 is a reply to message #170227]

Wed, 03 May 2006 15:11

venkatsp
Messages: 22
Registered: March 2005

Junior Member

Operation	                                 Object Name	      Rows	Bytes	Cost	

SELECT STATEMENT Optimizer Mode=CHOOSE		                      408 K	 	599606  	 	      	             	 
  VIEW		                                                      408 K	104 M	599606  	 	      	             	 
    SORT UNIQUE		                                              408 K	283 M	599606  	 	      	             	 
      WINDOW SORT		                                      408 K	283 M	599606  	 	      	             	 
        HASH JOIN OUTER		                                      408 K	283 M	12250  	 	      	             	 
          HASH JOIN OUTER		                              160 K	105 M	9382  	 	      	             	 
            HASH JOIN		                                      123 K	68 M	4350  	 	      	             	 
              TABLE ACCESS FULL	CP360.HOLDING_M	                      3 K	186 K	61  	 	      	             	 
              HASH JOIN OUTER		                              113 K	57 M	3557  	 	      	             	 
                HASH JOIN OUTER		                              113 K	30 M	2697  	 	      	             	 
                  TABLE ACCESS FULL	          MFEED.EQUITY_PRICING113 K	17 M	2199  	 	      	             	 
                  TABLE ACCESS FULL	          MFEED.QISS_FEED	1 K	186 K	6  	 	      	             	 
                TABLE ACCESS FULL	          MFEED.DV2_FEED	2 K	617 K	12  	 	      	             	 
            TABLE ACCESS FULL	              MFEED.EQUITY_DESCRIPTIVE	113 K	11 M	2468  	 	      	             	 
          TABLE ACCESS FULL	                  MFEED.SP6_FEED	21 K	762 K	31  	 	      	             	 
i got this information after explain plan th query which is used is 
SELECT 1034 report_time,
2 dt_q, 
1 hirearchy_no_q, 
p.unique_security_c, 
p.source_code_c, 
p.customer_specific_security_c  user_security_c, 
p.par_value  par_value, exchange_code_c, 
(CASE WHEN p.ASK_PRICE_L IS NOT NULL THEN 1 
      WHEN p.BID_PRICE_L IS NOT NULL THEN 1 
      WHEN p.STRIKE_PRICE_L IS NOT NULL THEN 1 
      WHEN p.VALUATION_PRICE_L IS NOT NULL THEN 1  ELSE 0 END) bill_status, 
      p.CLASS_C AS CLASS, 
      p.SUBCLASS_C AS SUBCLASS, 
      p.AGENT_ADDRESS_LINE1_T AS AGENTADDRESSLINE1, 
      p.AGENT_ADDRESS_LINE2_T AS AGENTADDRESSLINE2, 
      p.AGENT_CODE1_T AS AGENTCODE1, 
      p.AGENT_CODE2_T AS AGENTCODE2, 
      p.AGENT_NAME_LINE1_T AS AGENTNAMELINE1, 
      p.AGENT_NAME_LINE2_T AS AGENTNAMELINE2, 
      p.ASK_PRICE_L AS ASKPRICE, 
      p.ASK_PRICE_DATE_D AS ASKPRICEDATE, 
      p.ASSET_CLASS_T AS ASSETCLASS, 
      p.ASSET_TYPE_C AS ASSETTYPE,
	  p.BID_PRICE_L AS BIDPRICE,  
      p.BID_PRICE_DATE_D AS BIDPRICEDATE, '' AS COUPON,  
      p.CURRENCY_CODE_T AS CURRENCYCODE   
FROM  (SELECT  DISTINCT  x.*,m.customer_specific_security_c,m.par_value 
       FROM HOLDING_M m JOIN ED_DVTKQS_V x ON 
	   m.unique_security_c = x.unique_security_c  AND 
	   m.customer_c = 'CONF100005' AND 
	   m.portfolio_c = 24 AND 
	   m.status_c = 1 
WHERE exists 
	      (SELECT 1 FROM ED_DVTKQS_V y 
		     WHERE x.unique_security_c = y.unique_security_c 
			 GROUP BY y.unique_security_c 
			 HAVING MAX(y.trading_volume_l) = x.trading_volume_l))  p

[Updated on: Thu, 04 May 2006 08:48] by Moderator

Report message to a moderator