No More spoolspace error

Tools & Utilities
Enthusiast

No More spoolspace error

Hi,

I have the below query...

SELECT
AL5.SUB_ACCT_NO_SBB SUB_ACCT_NO, AL3.NAME OLD_REC, AL3.STATUS_END_DT STATUS_END_DT
FROM
OFFER_EQUIPMENT_VIEWS.EQ_ACCOUNT_HISTORY_VW AL6,
OFFER_EQUIPMENT_VIEWS.EQ_DETAILS_HISTORY_VW AL3,
VANTAGE.SBB_BASE AL5
WHERE
AL5.SID_SBB = AL6.SID_SBB
AND (AL3.name LIKE '%522%' OR AL3.name LIKE '%625%'
OR AL3.name LIKE '%1022%' OR AL3.name LIKE '%VIP%')
AND AL3.STATUS_CD = 'DISABL'

When I execute it, it is resulting in "no more spoolspace" error.

Can someone suggest me how to re-write the query to avoid this error?

General suggestions will be fine.

Thanks
Rajiv
3 REPLIES
Junior Contributor

Re: No More spoolspace error

Hi Rajiv,
it's three tables, but only one join condition join -> cross join

Dieter
Enthusiast

Re: No More spoolspace error

Hi Rajiv,

In the following query...

SELECT
AL5.SUB_ACCT_NO_SBB SUB_ACCT_NO, AL3.NAME OLD_REC, AL3.STATUS_END_DT STATUS_END_DT
FROM
OFFER_EQUIPMENT_VIEWS.EQ_ACCOUNT_HISTORY_VW AL6,
OFFER_EQUIPMENT_VIEWS.EQ_DETAILS_HISTORY_VW AL3,
VANTAGE.SBB_BASE AL5
WHERE
AL5.SID_SBB = AL6.SID_SBB
AND (AL3.name LIKE '%522%' OR AL3.name LIKE '%625%'
OR AL3.name LIKE '%1022%' OR AL3.name LIKE '%VIP%')
AND AL3.STATUS_CD = 'DISABL'

the culprit is the OR condition, try using union all rather then OR condition's. This reduces your execution time as well as spool space problem.

i mean to say that rewrite the query as follows:

SELECT
AL5.SUB_ACCT_NO_SBB SUB_ACCT_NO, AL3.NAME OLD_REC, AL3.STATUS_END_DT STATUS_END_DT
FROM
OFFER_EQUIPMENT_VIEWS.EQ_ACCOUNT_HISTORY_VW AL6,
OFFER_EQUIPMENT_VIEWS.EQ_DETAILS_HISTORY_VW AL3,
VANTAGE.SBB_BASE AL5
WHERE
AL5.SID_SBB = AL6.SID_SBB
AND AL3.STATUS_CD = 'DISABL'
AND AL3.name LIKE '%522%'

UNION ALL
SELECT
AL5.SUB_ACCT_NO_SBB SUB_ACCT_NO, AL3.NAME OLD_REC, AL3.STATUS_END_DT STATUS_END_DT
FROM
OFFER_EQUIPMENT_VIEWS.EQ_ACCOUNT_HISTORY_VW AL6,
OFFER_EQUIPMENT_VIEWS.EQ_DETAILS_HISTORY_VW AL3,
VANTAGE.SBB_BASE AL5
WHERE
AL5.SID_SBB = AL6.SID_SBB
AND AL3.STATUS_CD = 'DISABL'
AND AL3.name LIKE '%625%'

UNION ALL
SELECT
AL5.SUB_ACCT_NO_SBB SUB_ACCT_NO, AL3.NAME OLD_REC, AL3.STATUS_END_DT STATUS_END_DT
FROM
OFFER_EQUIPMENT_VIEWS.EQ_ACCOUNT_HISTORY_VW AL6,
OFFER_EQUIPMENT_VIEWS.EQ_DETAILS_HISTORY_VW AL3,
VANTAGE.SBB_BASE AL5
WHERE
AL5.SID_SBB = AL6.SID_SBB
AND AL3.STATUS_CD = 'DISABL'
AND AL3.name LIKE '%1022%'

UNION ALL
SELECT
AL5.SUB_ACCT_NO_SBB SUB_ACCT_NO, AL3.NAME OLD_REC, AL3.STATUS_END_DT STATUS_END_DT
FROM
OFFER_EQUIPMENT_VIEWS.EQ_ACCOUNT_HISTORY_VW AL6,
OFFER_EQUIPMENT_VIEWS.EQ_DETAILS_HISTORY_VW AL3,
VANTAGE.SBB_BASE AL5
WHERE
AL5.SID_SBB = AL6.SID_SBB
AND AL3.STATUS_CD = 'DISABL'
AND AL3.name LIKE '%VIP%';

even though the query looks more in number of lines but it will be executed much quicker.

Hope this solution may help you

thanks
Jagdish

Enthusiast

Re: No More spoolspace error

Hi Jagdish,

Thanks for the suggestion made. Even though Dieter's suggestion worked for me, by using join condition between other two tables, your suggestion seems to make the query run faster.

Thanks
Rajiv