| 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: Incorrect SQL execution plan in Star Schema TEMP table creation.
Stephane,
Thanks for your response.
This view is very simple view and has only one equijoin in the view.
Here is the view tables and join condition :
FROM CLAIM_MONTHLY_SNAPSHOT_RELATOR cmsr , CLAIM_MONTHLY_SNAPSHOT_FACT cmsf WHERE cmsf.CLAIM_DAILY_SNAPSHOT_KEY = cmsr.CLAIM_DAILY_SNAPSHOT_KEY
As per oracle documentation, I think we should be okay with this kind of view (because it is not unmerged view).
Documentaion says :
Restrictions on Star Transformation
Star transformation is not supported for tables with any of the following characteristics:
Tables that are really unmerged views, which are not view partitions
Thanks.
Best Regards,
Prasad
860 843 8377
"Stephane Paquette"
<stephane.paquette_at_standa To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
rdlife.ca> cc:
Sent by: root_at_fatcity.com Subject: RE: Incorrect SQL execution plan in Star Schema TEMP table
creation.
04/10/2003 01:29 PM
Please respond to
ORACLE-L
I remember having read that star_transformation doesn't work well with
views.
I'm assuming that V_CLAIM_MONTHLY_SNAPSHOT_FACT is a view.
I'm pretty busy right now I do not have time to check in the documentation.
Stephane.
-----Original Message-----
Prasada.Gunda_at_hartfordlife.com
Sent: Wednesday, April 09, 2003 5:44 PM
To: Multiple recipients of list ORACLE-L
creation.
Hi All,
We are having a problem with one of the SQL query, which is giving
incorrect execution plan.
This is a Data warehouse environment and the star_transformation_enabled
set to true. The following query uses star transformation and it takes long
time to execute. In fact it used up all temp space and failed eventually.
As part of investigation, we came to know that one of the temp table
generation SQL is doing Cartesian product and it is using enormous amount
of temp space.
The main sql :
SELECT
MNTHLY_DURING_DATE.MONTH,
CLAIM_PROCESSING_EXAMINER.PROCESSORS_OFFICE, CLAIM_PROCESSING_EXAMINER.TEAM, CLAIM_PROCESSING_EXAMINER.PROCESSOR_ID, CLAIM_PROCESSING_EXAMINER.PROCESSORS_FULL_NAME,SUM(MONTHLY_SNAPSHOT_DURING.NO_OF_DECISION_TRX) FROM
COVERAGE_PLAN_DIM.COVERAGE_CATEGORY_CODE = 'STD'
AND COVERAGE_PLAN_DIM.COVERAGE_TYPE_CODE IN ('NST', 'STAT', 'SUPP')
AND COVERAGE_PLAN_DIM.MARKET_GROUP = 'Employer Market'
AND COVERAGE_PLAN_DIM.MARKET_SEGMENT IN ('National Accounts',
'Priority Accounts', 'Private Label Direct', 'Private Label Turnkey',
'Regional Accounts')
CLAIM_PROCESSING_EXAMINER.PROCESSORS_OFFICE, CLAIM_PROCESSING_EXAMINER.TEAM, CLAIM_PROCESSING_EXAMINER.PROCESSOR_ID, CLAIM_PROCESSING_EXAMINER.PROCESSORS_FULL_NAME ============================================================================
INSERT INTO "SYS"."ORA_TEMP_1_13FB"
SELECT
/*+ NO_EXPAND SEMIJOIN_DRIVER */ "CMSF"."CLAIM_DAILY_SNAPSHOT_KEY" "C0",
"CMSF"."NO_OF_DECISION_TRX" "C1", "CLAIM_PROCESSING_EXAMINER"."PROCESSORS_FULL_NAME" "C2", "CLAIM_PROCESSING_EXAMINER"."PROCESSOR_ID" "C3", "CLAIM_PROCESSING_EXAMINER"."TEAM" "C4", "CLAIM_PROCESSING_EXAMINER"."PROCESSORS_OFFICE" "C5"FROM
"DISCLM"."EXAMINER_DIM" "CLAIM_PROCESSING_EXAMINER", "DISCLM"."CLAIM_MONTHLY_SNAPSHOT_FACT" "CMSF", "DISCLM"."COVERAGE_PLAN_DIM" "COVERAGE_PLAN_DIM"WHERE
"COVERAGE_PLAN_DIM"."MARKET_SEGMENT"='Priority Accounts' OR "COVERAGE_PLAN_DIM"."MARKET_SEGMENT"='Private Label Direct' OR "COVERAGE_PLAN_DIM"."MARKET_SEGMENT"='Private Label Turnkey' OR "COVERAGE_PLAN_DIM"."MARKET_SEGMENT"='Regional Accounts') AND("COVERAGE_PLAN_DIM"."COVERAGE_TYPE_CODE"='NST' OR "COVERAGE_PLAN_DIM"."COVERAGE_TYPE_CODE"='STAT' OR "COVERAGE_PLAN_DIM"."COVERAGE_TYPE_CODE"='SUPP')) In this sql, 4th line from the bottom is doing a join on cmsf.coverage_plan_skey with the subselect. It is not doing a join on cmsf.coverage_plan_skey with coverage_plan_dim.coverage_plan_skey as an additional condition but it is using coverage_plan_dim as part of "FROM" clause. Due to this, it is doing cartesian product with coverage plan dim.
It is doing correctly for other table (CLAIM_PROCESSING_EXAMINER). One thing to note here is that it is doing a key join between CMSF and CLAIM_PROCESSING_EXAMINER because (I think) we are selecting some of the columns from this table where as none of the columns being selected from COVERAGE_PLAN_DIM. The db version is 8.1.7.4 and it is on HP v11. All the tables and indexes were analyzed.
I appreciate your suggestions/comments.
Thanks.
Best Regards,
Prasad
860 843 8377
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Prasada.Gunda_at_hartfordlife.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: stephane.paquette_at_standardlife.ca 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Prasada.Gunda_at_hartfordlife.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 Thu Apr 10 2003 - 15:48:58 CDT
 |
 |