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).
Received on Wed Apr 09 2003 - 17:20:00 CDT
