| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tuning Answer (Short)
Following Stephane's advice, I rewrote the query to use underlying tables rather than the view.
The execution plan looks much better and the elapsed time came down appreciably.
My Question is , Since I was simply selecting from the view, eliminating a lot of columns and adding a few more predicates, why did Oracle not merge the view in the main query? (Oracle version 8.1.7.0.0)
I was of the opinion, that Oracle will merge the view into the main query, since the main query was a simple select from a view with a few predicates.
But I see the whole execution plan getting changed when I use the underlying tables directly rather than the view.
Maybe, someone can enlighten me on this.
Am interacting with the developers to see if the number of columns in the view can be brought down, or the query be used to select from the tables rather than the view itself.
Following are the queries and the explain plan. If someone can study them and answer my question as to why view was not merged in the main query, I shall be thankful to him.
I know the details are long and tedious. I can't help the formatting, Yahoo! spoils the formatting
I rewrote the query
MPAI_NAV_MODS, MPAI_SYS_NO, MPAI_PAI_SYS_NO, MPAI_AS_OF_DATE,
MPAI_AS_OF_DATE IN (to_date('03/31/2003','MM/DD/YYYY') ) and
PRODUCT_INSTR_ID in (1321,1339,1341,1340) and
CUR_SYS_NO in (200,226)
---
To
---
SELECT
MEPAI.MPAI_NAV_MOD MPAI_NAV_MOD,
MEPAI.MPAI_NAV_MODS MPAI_NAV_MODS,
MEPAI.MPAI_SYS_NO MPAI_SYS_NO,
MEPAI.MPAI_AS_OF_DATE MPAI_AS_OF_DATE,
MEPAI.MPAI_PAI_SYS_NO MPAI_PAI_SYS_NO,
PRODUCTS.ISS_INSTR_ID PRODUCT_INSTR_ID,
CUR.CUR_CURRENCY_CODE CURRENCY_CODE,
CUR.CUR_CURRENCY_NAME CURRENCY_NAME,
CUR.CUR_SYS_NO CUR_SYS_NO
FROM
EPR_CURRENCIES CUR,
EPR_GEOGRAPHIES GEO,
EPR_PRODUCTS PRODUCTS,
MOD_EPR_PRICING_ASSET_INFO MEPAI
WHERE
MEPAI.MPAI_ISS_SYS_NO = PRODUCTS.ISS_SYS_NO
AND MEPAI.MPAI_GEO_SYS_NO = GEO.GEO_SYS_NO
AND MEPAI.MPAI_CUR_SYS_NO = CUR.CUR_SYS_NO
AND MEPAI.MPAI_AS_OF_DATE IN (TO_DATE('03/31/2003','MM/DD/YYYY') )
AND PRODUCTS.ISS_INSTR_ID IN (1321,1339,1341,1340)
AND CUR.CUR_SYS_NO IN (200,226)
-----------------------------
The query of the view being:
-----------------------------
SELECT
PRODUCTS.ISS_SYS_NO PRODUCT_ISS_SYS_NO,
PRODUCTS.ISS_INSTR_ID PRODUCT_INSTR_ID,
PRODUCTS.ISS_SHARE_CLASS SHARE_CLASS,
PRODUCTS.ISS_DISPLAY_SHARE_CLASS DISPLAY_SHARE_CLASS,
PRODUCTS.ISS_EFFECTIVE_START_DATE PRODUCT_START_DATE,
PRODUCTS.ISS_EFFECTIVE_END_DATE PRODUCT_END_DATE,
PRODUCTS.ISS_PRODUCT_NAME_DEFAULT PRODUCT_DEFAULT_NAME,
GEO.GEO_SYS_NO GEO_SYS_NO,
GEO.GEO_EFFECTIVE_START_DATE GEO_START_DATE,
GEO.GEO_EFFECTIVE_END_DATE GEO_END_DATE,
GEO.GEO_GEOGRAPHY_CODE GEOGRAPHY_CODE,
GEO.GEO_GEOGRAPHY_ISO_CODE GEOGRAPHY_ISO_CODE,
GEO.GEO_GEOGRAPHY_TYPE GEOGRAPHY_TYPE,
GEO.GEO_GEOGRAPHY_NAME GEOGRAPHY_NAME,
GEO.GEO_GEOGRAPHY_DESCRIPTION GEOGRAPHY_DESCRIPTION,
GEO.GEO_LEAD_LANGUAGE_CODE LEAD_LANGUAGE_CODE,
GEO.GEO_LEAD_CURRENCY_CODE LEAD_CURRENCY_CODE,
GEO.GEO_DEFAULT_DATE_FORMAT DEFAULT_DATE_FORMAT,
GEO.GEO_DEFAULT_NUMBER_FORMAT DEFAULT_NUMBER_FORMAT,
GEO.GEO_COUNTRY_FLAG_URL COUNTRY_FLAG_URL,
CUR.CUR_SYS_NO CUR_SYS_NO,
CUR.CUR_EFFECTIVE_START_DATE CUR_EFFECTIVE_START_DATE,
CUR.CUR_EFFECTIVE_END_DATE CUR_EFFECTIVE_END_DATE,
CUR.CUR_CURRENCY_CODE CURRENCY_CODE,
CUR.CUR_ISO_CURRENCY_CODE ISO_CURRENCY_CODE,
CUR.CUR_CURRENCY_NAME CURRENCY_NAME,
MEPAI.MPAI_SYS_NO MPAI_SYS_NO,
MEPAI.MPAI_AS_OF_DATE MPAI_AS_OF_DATE,
MEPAI.MPAI_STATUS MPAI_STATUS,
MEPAI.MPAI_SOURCE_SYSTEM MPAI_SOURCE_SYSTEM,
MEPAI.MPAI_TOTAL_NET_ASSETS_SOR MPAI_TOTAL_NET_ASSETS_SOR,
MEPAI.MPAI_TOTAL_NET_ASSETS_MOD MPAI_TOTAL_NET_ASSETS_MOD,
MEPAI.MPAI_TOTAL_NET_ASSETS_MODS MPAI_TOTAL_NET_ASSETS_MODS,
MEPAI.MPAI_MANDATE_ASSETS_SOR MPAI_MANDATE_ASSETS_SOR,
MEPAI.MPAI_MANDATE_ASSETS_MOD MPAI_MANDATE_ASSETS_MOD,
MEPAI.MPAI_MANDATE_ASSETS_MODS MPAI_MANDATE_ASSETS_MODS,
MEPAI.MPAI_NAV_SOR MPAI_NAV_SOR,
MEPAI.MPAI_NAV_MOD MPAI_NAV_MOD,
MEPAI.MPAI_NAV_MODS MPAI_NAV_MODS,
MEPAI.MPAI_POP_SOR MPAI_POP_SOR,
MEPAI.MPAI_POP_MOD MPAI_POP_MOD,
MEPAI.MPAI_POP_MODS MPAI_POP_MODS,
MEPAI.MPAI_HIGH_NAV_CALENDAR_SOR MPAI_HIGH_NAV_CALENDAR_SOR,
MEPAI.MPAI_HIGH_NAV_CALENDAR_MOD MPAI_HIGH_NAV_CALENDAR_MOD,
MEPAI.MPAI_HIGH_NAV_CALENDAR_MODS MPAI_HIGH_NAV_CALENDAR_MODS,
MEPAI.MPAI_HIGH_NAV_ROLLING_SOR MPAI_HIGH_NAV_ROLLING_SOR,
MEPAI.MPAI_HIGH_NAV_ROLLING_MOD MPAI_HIGH_NAV_ROLLING_MOD,
MEPAI.MPAI_HIGH_NAV_ROLLING_MODS MPAI_HIGH_NAV_ROLLING_MODS,
MEPAI.MPAI_HIGH_NAV_SINCE_INCEP_SOR MPAI_HIGH_NAV_SINCE_INCEP_SOR,
MEPAI.MPAI_HIGH_NAV_SINCE_INCEP_MOD MPAI_HIGH_NAV_SINCE_INCEP_MOD,
MEPAI.MPAI_HIGH_NAV_SINCE_INCEP_MODS MPAI_HIGH_NAV_SINCE_INCEP_MODS,
MEPAI.MPAI_LOW_NAV_CALENDER_SOR MPAI_LOW_NAV_CALENDER_SOR,
MEPAI.MPAI_LOW_NAV_CALENDER_MOD MPAI_LOW_NAV_CALENDER_MOD,
MEPAI.MPAI_LOW_NAV_CALENDER_MODS MPAI_LOW_NAV_CALENDER_MODS,
MEPAI.MPAI_LOW_NAV_ROLLING_SOR MPAI_LOW_NAV_ROLLING_SOR,
MEPAI.MPAI_LOW_NAV_ROLLING_MOD MPAI_LOW_NAV_ROLLING_MOD,
MEPAI.MPAI_LOW_NAV_ROLLING_MODS MPAI_LOW_NAV_ROLLING_MODS,
MEPAI.MPAI_LOW_NAV_SINCE_INCEP_SOR MPAI_LOW_NAV_SINCE_INCEP_SOR,
MEPAI.MPAI_LOW_NAV_SINCE_INCEP_MOD MPAI_LOW_NAV_SINCE_INCEP_MOD,
MEPAI.MPAI_LOW_NAV_SINCE_INCEP_MODS MPAI_LOW_NAV_SINCE_INCEP_MODS,
MEPAI.MPAI_MARKET_PRICE_SOR MPAI_MARKET_PRICE_SOR,
MEPAI.MPAI_MARKET_PRICE_MOD MPAI_MARKET_PRICE_MOD,
MEPAI.MPAI_MARKET_PRICE_MODS MPAI_MARKET_PRICE_MODS,
MEPAI.MPAI_CENDPERF_PREM_DISC_SOR MPAI_CENDPERF_PREM_DISC_SOR,
MEPAI.MPAI_CENDPERF_PREM_DISC_MOD MPAI_CENDPERF_PREM_DISC_MOD,
MEPAI.MPAI_CENDPERF_PREM_DISC_MODS MPAI_CENDPERF_PREM_DISC_MODS,
MEPAI.MPAI_PAI_SYS_NO MPAI_PAI_SYS_NO,
MEPAI.MPAI_EPR_ORIG_LOAD_DATE MPAI_EPR_ORIG_LOAD_DATE
FROM EPR_PRODUCTS PRODUCTS,
EPR_GEOGRAPHIES GEO,
EPR_CURRENCIES CUR,
MOD_EPR_PRICING_ASSET_INFO MEPAI
WHERE MEPAI.MPAI_ISS_SYS_NO = PRODUCTS.ISS_SYS_NO
AND MEPAI.MPAI_GEO_SYS_NO = GEO.GEO_SYS_NO
AND MEPAI.MPAI_CUR_SYS_NO = CUR.CUR_SYS_NO
The execution plan and the time take came down drastically.
-------------------
Old Execution Plan
-------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=482 Card=1 Bytes=68)
1 0 SORT (UNIQUE) (Cost=471 Card=1 Bytes=68)
2 1 CONCATENATION
3 2 NESTED LOOPS (Cost=230 Card=4 Bytes=272)
4 3 NESTED LOOPS (Cost=230 Card=4 Bytes=260)
5 4 NESTED LOOPS (Cost=218 Card=12 Bytes=684)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES' (Cost=1 Card=1 Bytes=21)
7 6 INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE)
8 5 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MOD_EPR_PRICING_ASSET_INFO' (Cost=217 Card=244 Bytes=8784)
9 8 INDEX (RANGE SCAN) OF 'MPAI_CUR_FK_I' (NON-UNIQUE) (Cost=216 Card=244)
10 4 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS' (Cost=1 Card=9 Bytes=72)
11 10 INDEX (UNIQUE SCAN) OF 'ISS_PK' (UNIQUE)
12 3 INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)
13 2 NESTED LOOPS (Cost=230 Card=4 Bytes=272)
14 13 NESTED LOOPS (Cost=230 Card=4 Bytes=260)
15 14 NESTED LOOPS (Cost=218 Card=12 Bytes=684)
16 15 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES' (Cost=1 Card=1 Bytes=21)
17 16 INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE)
18 15 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MOD_EPR_PRICING_ASSET_INFO' (Cost=217 Card=244 Bytes=8784)
19 18 INDEX (RANGE SCAN) OF 'MPAI_CUR_FK_I' (NON-UNIQUE) (Cost=216 Card=244)
20 14 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS' (Cost=1 Card=9 Bytes=72)
21 20 INDEX (UNIQUE SCAN) OF 'ISS_PK' (UNIQUE)
22 13 INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)
-------------------
New Execution Plan
-------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2551 Card=1 Bytes=68)
1 0 NESTED LOOPS (Cost=2551 Card=1 Bytes=68)
2 1 NESTED LOOPS (Cost=2551 Card=1 Bytes=65)
3 2 NESTED LOOPS (Cost=2541 Card=10 Bytes=440)
4 3 INLIST ITERATOR
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS' (Cost=3 Card=9 Bytes=72)
6 5 INDEX (RANGE SCAN) OF 'ISS_ISS_INSTR_ID' (NON-UNIQUE) (Cost=2 Card=9)
7 3 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MOD_EPR_PRICING_ASSET_INFO' (Cost=282 Card=244 Bytes=8784)
8 7 INDEX (RANGE SCAN) OF 'MPAI_ISS_FK_I' (NON-UNIQUE)(Cost=277 Card=244)
9 2 INLIST ITERATOR
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES' (Cost=1 Card=2 Bytes=42)
11 10 INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE)
12 1 INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)
Regards
New DBA
Stephane Faroult <sfaroult_at_oriolecorp.com> wrote:
Getting rid of views is quite often the first step on the path to Enlightenment. Compare the number of columns you want to be returned to the number of columns in the view: is it reasonable? Obviously not. If you absolutely want a view for security reasons, create a specific one for this query, which only returns what is needed.
I must say that I am feeling a bit lazy to delve deeply into something which I see very poorly formatted. But first check whence (from which TABLE) come the columns you want to be returned, as well as those which much match the search criteria you are providing. Check whether you can join the required tables together; if not add the required tables to do the links to the stew. Stir. Determine which is the most selective of values from your input. If it really is selective, check that it is properly indexed. Check also that your joins can execute relatively fast. If you have a join on poorly selective columns, read about hash joins.
I am not pretending that by then you'll have a fast running query, but you'll have a sounder base for further tuning.
Regards,
Stephane Faroult
Oriole
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: sfaroult_at_oriolecorp.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing)
---------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: New DBA
INET: new_dba_on_the_block_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Sep 19 2003 - 02:19:47 CDT
 |
 |