| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: HELP Query never completes
Make sure Oracle starts with a full table scan of pfm_tbl_dsassref_aw, followed by an index scan (unique?) on pfm_tbl_rev_sr. You can achieve this with hints "full" and "ordered" if you put the tables as in the following example. If you don't like hints, same result could be obtained by putting the tables in the reverse order, and disabling all possibility of index usage on pfm_tbl_dsassref_aw like this:
WHERE ( ref.REV_SR_K + O = rev.REV_SR_K )
and ( ref.SUM_K + 0 = sum.TRNSUM_K )
and ( ref.ORIGIN_CO || "" = cli.CLIENT_COD )
and ( ref.DS_DATE + 0 > '30-JUN-97' )
AND ( ref.OWNER_CO + 0 = 99 )
AND ( ref.ORIGIN_CO + 0 > 100 )
SELECT PFM_TBL_POLICY.POLICY_NUM,
PFM_TBL_CLIENT.CLIENT_D,
PFM_TBL_REV_SR.REV_SR_D,
PFM_TBL_REV_SR.MASTER_K,
PFM_TBL_REV_SR.NAME_REVS1,
PFM_TBL_REV_SR.STATE,
PFM_TBL_TRNSUM.ATTACH_DAT,
PFM_TBL_REV_SR.NAIC_ID,
PFM_TBL_REV_SR.MOODY_RATING,
PFM_TBL_REV_SR.SP_RATING,
PFM_TBL_REV_SR.REVENUE_TYPE,
SUM(PFM_TBL_DSASSREF_AW.PAR) PAR,
SUM(PFM_TBL_DSASSREF_AW.INTEREST) INTEREST
FROM
PFM_TBL_DSASSREF_AW ref,
PFM_TBL_REV_SR rev,
PFM_TBL_CLIENT cli,
PFM_TBL_POLICY pol,
PFM_TBL_TRNSUM sum
WHERE ( ref.REV_SR_K = rev.REV_SR_K )
and ( ref.SUM_K = sum.TRNSUM_K )
and ( ref.ORIGIN_CO = cli.CLIENT_COD )
and ( ref.DS_DATE > '30-JUN-97' )
AND ( ref.OWNER_CO = 99 )
AND ( ref.ORIGIN_CO > 100 )
and ( sum.POLICY_K = pol.POLICY_K )
AND ( rev.MASTER_K = 10000000 )
AND ( rev.REVENUE_TYPE <> 'NMMORTINS' )
GROUP BY PFM_TBL_POLICY.POLICY_NUM,
PFM_TBL_CLIENT.CLIENT_D,
PFM_TBL_REV_SR.REV_SR_D,
PFM_TBL_REV_SR.MASTER_K,
PFM_TBL_REV_SR.NAME_REVS1,
PFM_TBL_REV_SR.STATE,
PFM_TBL_TRNSUM.ATTACH_DAT,
PFM_TBL_REV_SR.NAIC_ID,
PFM_TBL_REV_SR.MOODY_RATING,
PFM_TBL_REV_SR.SP_RATING,
PFM_TBL_REV_SR.REVENUE_TYPE
Normally, it's a pleasure watching Oracle perform a (I say "a") full table scan.
I don't know how an index could help a "group by".
I'd like to know if it's been any help.
-- Kind reGards, Gerard (ghp_at_santens.be; ghp_at_skynet.be) Michael Joseph <michaelJ_at_capital-re.com> schreef in artikel <342BFB28.30A13243_at_capital-re.com>...Received on Sat Sep 27 1997 - 00:00:00 CDT
>
>
> 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"
>
>
>
 |
 |