| 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.
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).Received on Thu Apr 10 2003 - 12:29:03 CDT
 |
 |