Re: Query Help..

 Did you try an explain plan on the query ? Is oracle doing a merge join ?
You can always try to force the use of the indexes by using hints for the optimizer.

Michael Joseph wrote in article <342BFB00.ED1942D1_at_capital-re.com>...

>The following query causes our 1Gb TEMP tablespace to overflow with the
>'Unable to extend TEMP tablespace' message and consequently cannot be
>run to completion.
>
>We have analysed it up and down but to no avail.
>
>Here are some other Stats:
>
>The tables are all under 100,000 rows with the exception of the _AW
>table which is about 6,000,000 rows.
>All the joined columns are indexed in single key indexes.
>All the _AW indexes are bitmaps
>Not all the group by columns are indexed but indexing them seems to make
>no difference.
>
>Thanks for any help.
>
>MICHAELJ_at_CAPITAL-RE.COM
>
>
>SELECT "PFM_USER"."PFM_TBL_POLICY"."POLICY_NUM",
> "PFM_USER"."PFM_TBL_CLIENT"."CLIENT_D",
> "PFM_USER"."PFM_TBL_REV_SR"."REV_SR_D",
> "PFM_USER"."PFM_TBL_REV_SR"."MASTER_K",
> "PFM_USER"."PFM_TBL_REV_SR"."NAME_REVS1",
> "PFM_USER"."PFM_TBL_REV_SR"."STATE",
> "PFM_USER"."PFM_TBL_TRNSUM"."ATTACH_DAT",
> "PFM_USER"."PFM_TBL_REV_SR"."NAIC_ID",
> "PFM_USER"."PFM_TBL_REV_SR"."MOODY_RATING",
> "PFM_USER"."PFM_TBL_REV_SR"."SP_RATING",
> "PFM_USER"."PFM_TBL_REV_SR"."REVENUE_TYPE",
> SUM("PFM_USER"."PFM_TBL_DSASSREF_AW"."PAR") PAR,
> SUM("PFM_USER"."PFM_TBL_DSASSREF_AW"."INTEREST") INTEREST
> FROM "PFM_USER"."PFM_TBL_CLIENT",
> "PFM_USER"."PFM_TBL_DSASSREF_AW",
> "PFM_USER"."PFM_TBL_POLICY",
> "PFM_USER"."PFM_TBL_REV_SR",
> "PFM_USER"."PFM_TBL_TRNSUM"
> WHERE ( "PFM_USER"."PFM_TBL_DSASSREF_AW"."REV_SR_K" =
>"PFM_USER"."PFM_TBL_REV_SR"."REV_SR_K" ) and
> ( "PFM_USER"."PFM_TBL_DSASSREF_AW"."SUM_K" =
>"PFM_USER"."PFM_TBL_TRNSUM"."TRNSUM_K" ) and
> ( "PFM_USER"."PFM_TBL_TRNSUM"."POLICY_K" =
>"PFM_USER"."PFM_TBL_POLICY"."POLICY_K" ) and
> ( "PFM_USER"."PFM_TBL_DSASSREF_AW"."ORIGIN_CO" =
>"PFM_USER"."PFM_TBL_CLIENT"."CLIENT_COD" ) and
> ( ( PFM_USER."PFM_TBL_DSASSREF_AW"."DS_DATE" > '30-JUN-97' )
>AND
> ( PFM_USER."PFM_TBL_REV_SR"."REVENUE_TYPE" <> 'NMMORTINS' ) AND
>
> ( PFM_USER."PFM_TBL_DSASSREF_AW"."OWNER_CO" = 99 ) AND
> ( PFM_USER."PFM_TBL_DSASSREF_AW"."ORIGIN_CO" > 100 ) AND
> ( PFM_USER."PFM_TBL_REV_SR"."MASTER_K" = 10000000 ) )
>GROUP BY "PFM_USER"."PFM_TBL_POLICY"."POLICY_NUM",
> "PFM_USER"."PFM_TBL_CLIENT"."CLIENT_D",
> "PFM_USER"."PFM_TBL_REV_SR"."REV_SR_D",
> "PFM_USER"."PFM_TBL_REV_SR"."MASTER_K",
> "PFM_USER"."PFM_TBL_REV_SR"."NAME_REVS1",
> "PFM_USER"."PFM_TBL_REV_SR"."STATE",
> "PFM_USER"."PFM_TBL_TRNSUM"."ATTACH_DAT",
> "PFM_USER"."PFM_TBL_REV_SR"."NAIC_ID",
> "PFM_USER"."PFM_TBL_REV_SR"."MOODY_RATING",
> "PFM_USER"."PFM_TBL_REV_SR"."SP_RATING",
> "PFM_USER"."PFM_TBL_REV_SR"."REVENUE_TYPE"
>
>
Received on Sat Sep 27 1997 - 00:00:00 CEST