Home » RDBMS Server » Performance Tuning » Inline View (Oracle 10G)
| Inline View [message #449411] |
Mon, 29 March 2010 07:42  |
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
|
Member |
|
|
Hi,
I have a following query ,
SELECT
mps.SECURTY_ID AS "Sec Id",
mps.SECURTY_TYPE_ID AS "Sec Id Type",
secid_type.NEW_SECURTY_ID_TYPE AS "Valid Sec Id Type",
mps.PRICE_SRC AS "Px Src",
mps.PRICE_DT AS "Px Date",
mps.CRRNCY_CD AS "ISO Currency",
crrncy.NEW_SECURTY_ID_TYPE_CRRNCY_CD AS "Valid ISO Currency",
err.excptn AS "Error Msg",
CASE WHEN (err.excptn is not null)
THEN 'E'
WHEN mps.is_duplicate = 'Y'
THEN 'E'
ELSE 'S'
END AS "StatusCode",
mac.MAC3_SHORT_DESC AS "MAC 3",
mps.BATCH_ID AS "Batch Id",
mps.ask_price AS "Ask Px",
mps.best_price AS "Best Px",
mps.bid_price AS "Bid Px",
mps.close_price AS "Close Px",
mps.high_price AS "High Px",
mps.low_price AS "Low Px",
mps.mid_price AS "Mid Px",
mps.manual_price AS "Manual Px",
mps.yield AS "Yield",
mps.revision_id AS "RevisionId",
mps.record_id AS "PriceRecordId",
mps.is_duplicate AS "Is Duplicate",
mps.cpp_securty_alias_id AS "Security Alias"
FROM gps.manual_price_submit_temp mps,
(SELECT secid.*
FROM gps.manual_price_valdtn_temp secid,
gps.manual_price_submit_temp mps
WHERE secid.record_id = mps.record_id
AND secid.EXCPTN_CD_ID =gps.pkg_gps_constants.c_sec_id_type_excptn_id
AND mps.batch_id=v_batch_id
) secid_type,
(SELECT secid.*
from gps.manual_price_valdtn_temp secid,
gps.manual_price_submit_temp mps
where secid.record_id = mps.record_id
and secid.EXCPTN_CD_ID =gps.pkg_gps_constants.c_currency_excptn_id
and mps.batch_id=v_batch_id
) crrncy,
(SELECT secid.*
from gps.manual_price_valdtn_temp secid,
gps.manual_price_submit_temp mps
where secid.record_id = mps.record_id
and secid.EXCPTN_CD_ID =gps.pkg_gps_constants.c_sec_id_excptn_id
and mps.batch_id=v_batch_id
and secid.validation_status_cd = gps.pkg_gps_constants.c_fail_status_cd
) secid,
(SELECT err.record_id,
wm_concat(err.EXCPTN_CD_ID||'-'||err.EXCPTN_CD_DESC) as excptn
FROM gps.manual_price_valdtn_temp err
WHERE err.batch_id=v_batch_id
AND err.validation_status_cd='FAILED'
GROUP BY err.record_id
) err,
(SELECT DISTINCT g.mac3_short_desc,
s.securty_id ,
ms.record_id
FROM GPS.SECURTY_MASTER g,
gps.gsm_securty_xref s,
gps.manual_price_submit_temp ms
WHERE g.gsm_securty_id = s.gsm_securty_id
AND s.securty_id = ms.securty_id
AND ms.batch_id = v_batch_id
) mac
WHERE mps.batch_id = secid_type.batch_id(+)
AND mps.batch_id = crrncy.batch_id(+)
AND mps.batch_id = secid.batch_id(+)
AND mps.batch_id = v_batch_id
AND mps.record_id = mac.record_id(+)
AND mps.record_id = secid_type.record_id(+)
AND mps.record_id = crrncy.record_id(+)
AND mps.record_id = err.record_id(+)
AND mps.record_id = secid.record_id(+)
AND (mps.securty_type_id IS NULL
OR mps.crrncy_cd IS NULL
OR mps.validation_status_cd = 'FAILED');
I have modified the above qry as follows :
SELECT
mps.securty_id AS "Sec Id",
mps.securty_type_id AS "Sec Id Type",
secid.new_securty_id_type AS "Valid Sec Id Type",
mps.price_src AS "Px Src",
mps.price_dt AS "Px Date",
mps.crrncy_cd AS "ISO Currency",
secid.new_securty_id_type_crrncy_cd AS "Valid ISO Currency",
secid.EXCPTN_CD_ID||'-'||secid.EXCPTN_CD_DESC AS "Error Msg",
CASE WHEN ((secid.EXCPTN_CD_ID||'-'||secid.EXCPTN_CD_DESC) IS NOT NULL)
THEN 'E'
WHEN mps.is_duplicate = 'Y'
THEN 'E'
ELSE 'Y'
END AS "StatusCode",
sm.mac3_short_desc AS "MAC 3",
mps.batch_id AS "Batch Id",
mps.ask_price AS "Ask Px",
mps.best_price AS "Best Px",
mps.bid_price AS "Bid Px",
mps.close_price AS "Close Px",
mps.high_price AS "High Px",
mps.low_price AS "Low Px",
mps.mid_price AS "Mid Px",
mps.manual_price AS "Manual Px",
mps.yield AS "Yield",
mps.revision_id AS "RevisionId",
mps.record_id AS "PriceRecordId",
mps.is_duplicate AS "Is Duplicate",
mps.cpp_securty_alias_id AS "Security Alias"
FROM gps.manual_price_submit_temp mps,
gps.manual_price_valdtn_temp secid,
gps.securty_master sm,
gps.gsm_securty_xref s
WHERE mps.batch_id = secid.batch_id(+)
AND mps.batch_id = v_batch_id
AND mps.record_id = secid.record_id(+)
AND sm.gsm_securty_id = s.gsm_securty_id
AND s.crrncy_cd = mps.crrncy_cd
and s.securty_id_type = mps.securty_type_id
AND s.securty_id = mps.securty_id
AND secid.excptn_cd_id IN (gps.pkg_gps_constants.c_sec_id_type_excptn_id, gps.pkg_gps_constants.c_currency_excptn_id,
gps.pkg_gps_constants.c_sec_id_excptn_id)
AND secid.new_securty_id_type IS NOT NULL
AND (mps.securty_type_id IS NULL
OR mps.crrncy_cd IS NULL
OR mps.validation_status_cd = 'FAILED');
I have removed all the inline views and have joined the table directly for better performance.
Is it correct approach?
Pls suggest if it can have any logical impact.
Best Regards,
Harshal.
CM: Added [code] tags, please do so yourself next time, after 50 posts you ought to know how.
[Updated on: Mon, 29 March 2010 08:56] by Moderator Report message to a moderator |
|
|
|
| Re: Inline View [message #449428 is a reply to message #449411] |
Mon, 29 March 2010 09:01  |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
They are not equivalent.
The 1st one will give all the corresponding data from gps.manual_price_valdtn_temp in one row for each gps.manual_price_submit_temp row.
The 2nd will give the data from gps.manual_price_valdtn_temp in three rows for each gps.manual_price_submit_temp row.
|
|
|
|
| Re: Inline View [message #449535 is a reply to message #449428] |
Tue, 30 March 2010 05:55 |
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
|
Member |
|
|
Thanks.
Is there any alterative option to replace Inline view from above query???
Please suggest what can be done for tuning.
Best Regards,
Harshal
|
|
|
|
| Re: Inline View [message #449538 is a reply to message #449411] |
Tue, 30 March 2010 06:13 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Your second query looks part way right.
You need manual_price_valdtn_temp listed three times in the FROM clause, once for each inline view it's used in:
FROM gps.manual_price_submit_temp mps,
gps.manual_price_valdtn_temp secid_type,
gps.manual_price_valdtn_temp crrncy,
gps.manual_price_valdtn_temp secid,
gps.securty_master sm,
gps.gsm_securty_xref s
WHERE mps.batch_id = secid.batch_id(+)
AND mps.batch_id = v_batch_id
AND mps.record_id = secid.record_id(+)
AND mps.batch_id = secid_type.batch_id(+)
AND mps.record_id = secid_type.record_id(+)
AND mps.batch_id = crrncy.batch_id(+)
AND mps.record_id = crrncy.record_id(+)
AND sm.gsm_securty_id = s.gsm_securty_id
AND s.crrncy_cd = mps.crrncy_cd
and s.securty_id_type = mps.securty_type_id
AND s.securty_id = mps.securty_id
AND secid.excptn_cd_id(+) = gps.pkg_gps_constants.c_fail_status_cd
AND secid_type.EXCPTN_CD_ID(+) =gps.pkg_gps_constants.c_sec_id_type_excptn_id
AND crrncy.EXCPTN_CD_ID(+) =gps.pkg_gps_constants.c_currency_excptn_id
AND secid.new_securty_id_type IS NOT NULL
AND (mps.securty_type_id IS NULL
OR mps.crrncy_cd IS NULL
OR mps.validation_status_cd = 'FAILED');
That'll be closer to the orignal query. I haven't checked if your replacement of the err and mac inline views are correct but you should get the idea from that.
Basically the first three inline views were getting independant sets of data from manual_price_valdtn_temp. To achieve the same without inline views you need a seperate instance of the manual_price_valdtn_temp table in the FROM clause for each set of data.
|
|
|
|
| Re: Inline View [message #449540 is a reply to message #449411] |
Tue, 30 March 2010 06:16 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Bear in mind I can't guarantee that'll be faster. It probably will be since the orignal query was accessing the manual_price_submit_temp more times than was needed but without explain plans it's impossible to tell if that was the major problem.
Try the changes I suggested, make sure you getting the correct results, and if it's still slow post back here an explain plan of the new query.
|
|
|
|
| Re: Inline View [message #449843 is a reply to message #449540] |
Thu, 01 April 2010 06:38 |
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
|
Member |
|
|
Thanks for the Suggestion.
I have modified the query in following way.
SELECT mps.securty_id AS "Sec Id",
mps.securty_type_id AS "Sec Id Type",
secid_type.new_securty_id_type AS "Valid Sec Id Type",
mps.price_src AS "Px Src",
mps.price_dt AS "Px Date",
mps.crrncy_cd AS "ISO Currency",
crrncy.new_securty_id_type_crrncy_cd AS "Valid ISO Currency",
err.excptn AS "Error Msg",
CASE WHEN mps.is_duplicate = gps.pkg_gps_constants.c_duplicate_recrd_ind
THEN gps.pkg_gps_constants.c_error_ind
WHEN (err.excptn IS NOT NULL)
THEN gps.pkg_gps_constants.c_error_ind
ELSE gps.pkg_gps_constants.c_clean_recrd_ind
END AS "StatusCode",
mps.mac3_short_desc AS "MAC 3",
mps.batch_id AS "Batch Id",
mps.ask_price AS "Ask Px",
mps.best_price AS "Best Px",
mps.bid_price AS "Bid Px",
mps.close_price AS "Close Px",
mps.high_price AS "High Px",
mps.low_price AS "Low Px",
mps.mid_price AS "Mid Px",
mps.manual_price AS "Manual Px",
mps.yield AS "Yield",
mps.revision_id AS "RevisionId",
mps.record_id AS "PriceRecordId",
mps.is_duplicate AS "Is Duplicate",
mps.cpp_securty_alias_id AS "Security Alias"
FROM gps.manual_price_submit_temp mps,
gps.manual_price_valdtn_temp secid_type,
gps.manual_price_valdtn_temp crrncy,
gps.manual_price_valdtn_temp secid,
(SELECT err.record_id,
wm_concat(err.excptn_cd_id||'-'||err.excptn_cd_desc) as excptn
FROM gps.manual_price_valdtn_temp err
WHERE err.batch_id = v_batch_id
AND err.validation_status_cd = gps.pkg_gps_constants.c_fail_status_cd
GROUP BY err.record_id ) err
WHERE mps.batch_id = v_batch_id
AND mps.batch_id = secid_type.batch_id(+)
AND mps.record_id = secid_type.record_id(+)
AND secid_type.excptn_cd_id(+) = gps.pkg_gps_constants.c_sec_id_type_excptn_id
AND mps.batch_id = crrncy.batch_id(+)
AND mps.record_id = crrncy.record_id(+)
AND crrncy.excptn_cd_id(+) = gps.pkg_gps_constants.c_currency_excptn_id
AND mps.batch_id = secid.batch_id(+)
AND mps.record_id = secid.record_id(+)
AND secid.excptn_cd_id(+) = gps.pkg_gps_constants.c_sec_id_excptn_id
AND secid.validation_status_cd(+) = gps.pkg_gps_constants.c_fail_status_cd
AND mps.record_id = err.record_id(+)
AND (mps.securty_type_id IS NULL OR
mps.crrncy_cd IS NULL OR
mps.validation_status_cd = gps.pkg_gps_constants.c_fail_status_cd);
However as per the business logic requirement, i kept the err inline view as it is.
wm_concat function concatenates the exception in songle row as it is grouped , which i think can not have alternative option.
After these changes, Explain plan is coparatively improved.
Best regards,
Harshal
|
|
|
|
| Re: Inline View [message #449852 is a reply to message #449411] |
Thu, 01 April 2010 08:02 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
It looks suspiciously like you've run that explain plan against a database with little or no data in it (rows of 1 in all steps). In which case it probably doesn't prove anything. You want to run it against a test instance with a representative amount of data in it - or just run it against production.
And next time you post an explain plan here can you please generate it sqlplus so you can post it here as text rather than an image.
|
|
|
|
Goto Forum:
Current Time: Sun May 12 23:15:53 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
