Re: Incorrect SQL execution plan in Star Schema TEMP table creation.

Hi Stephane,

Thanks for your response.

V_CLAIM_MONTHLY_SNAPSHOT_FACT is a very simple view and nothing complex is going on. It has a equijoin on the fact table with one other table (we call it as relator table). We hide this in a view to avoid the confusion to the business users. Even if it is a view, it suppose to generate correct execution plan. For the sake of testing, I am planning to replace the view with actual tables and see how it generates execution plan. Also we changed the sql (the original sql is generated by Business Objects) for time being, which is taking 15 secs to execute the query. We try not to change the SQL generated by Business objects(our reporting tool) if possible.

Mean while, I opened TAR to find out if there are any known bugs and update the list if I hear anything from tech support.

Thanks for your help again.

Best Regards,
Prasad
860 843 8377

                                                                                                                   
                    Stephane                                                                                       
                    Faroult              To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    <sfaroult_at_orio       cc:                                                                       
                    le.com>              Subject:     Re: Incorrect SQL execution plan in Star Schema TEMP table   
                    Sent by:              creation.                                                                
                    root_at_fatcity.c                                                                                 
                    om                                                                                             
                                                                                                                   
                                                                                                                   
                    04/09/2003                                                                                     
                    06:20 PM                                                                                       
                    Please respond                                                                                 
                    to ORACLE-L                                                                                    
                                                                                                                   
                                                                                                                   

Prasada.Gunda_at_hartfordlife.com wrote:
>
> 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
> EXAMINER_DIM CLAIM_PROCESSING_EXAMINER,
> DAY_DIM MNTHLY_DURING_DATE,
> V_CLAIM_MONTHLY_SNAPSHOT_FACT MONTHLY_SNAPSHOT_DURING,
> COVERAGE_PLAN_DIM
> WHERE
Prasad,

 When I see V_CLAIM_MONTHLY_SNAPSHOT_FACT my first reaction is to say to myself 'Ouch, what does this view hide?'

--
Regards,

Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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: 
  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).