When using where clause like % error is given ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Toad for Oracle
1

Hi,
I am running following SQL and getting error ORA-01652: unable to extend temp
segment by 128 in tablespace TEMP.
One thing I know is that when join with like
(CRM_Event_Legacy.Legacy_SFDC_Opportunity_ID(+) LIKE
SFDC_RawInquiry_Stg.EP_Event_ID || ‘%’) was added then it gave this error.
Before it was equijoin (CRM_Event_Legacy.Legacy_SFDC_Opportunity_ID(+) =
SFDC_RawInquiry_Stg.EP_Event_ID) and worked fine. I tried to use hints, but did
not work either. Any suggestions?
Thank you in advance

SELECT /*+ use_hash(SFDC_RAWINQUIRY_STG) use_hash(CRM_OPPORTUNITY)
use_hash(CRM_PROSPECT) use_hash(CRM_EVENT_LEGACY) /
SFDC_RAWINQUIRY_STG.RAW_INQUIRY_ID, SFDC_RAWINQUIRY_STG.LEAD_ID,
SFDC_RAWINQUIRY_STG.BRAND, SFDC_RAWINQUIRY_STG.GRAD_LEVEL,
SFDC_RAWINQUIRY_STG.CHANNEL, SFDC_RAWINQUIRY_STG.VENDOR_CODE,
SFDC_RAWINQUIRY_STG.VENDOR_TIMESTAMP,
SFDC_RAWINQUIRY_STG.VENDOR_TRACKING_NUMBER, SFDC_RAWINQUIRY_STG.ADDRESS_STREET,
SFDC_RAWINQUIRY_STG.ADDRESS_CITY, SFDC_RAWINQUIRY_STG.ADDRESS_STATE,
SFDC_RAWINQUIRY_STG.ADDRESS_POSTAL_CODE, SFDC_RAWINQUIRY_STG.ADDRESS_COUNTRY,
SFDC_RAWINQUIRY_STG.EMAIL, SFDC_RAWINQUIRY_STG.PHONE,
SFDC_RAWINQUIRY_STG.EP_PRESENTER_DSI, SFDC_RAWINQUIRY_STG.REP_DSI,
SFDC_RAWINQUIRY_STG.LEGACY_SFID, SFDC_RAWINQUIRY_STG.RAW_INQUIRY_DELETED,
SFDC_RAWINQUIRY_STG.SFDC_INQUIRY_CREATE_DATE, SFDC_RAWINQUIRY_STG.OWNER_ID,
SFDC_RAWINQUIRY_STG.SFDC_INQUIRY_UPDATE_DATE,
SFDC_RAWINQUIRY_STG.PROGRAM_OF_INTEREST,
SFDC_RAWINQUIRY_STG.LOCATION_PREFERENCE, SFDC_RAWINQUIRY_STG.INQUIRY_STATUS,
SFDC_RAWINQUIRY_STG.PROSPECT_TYPE, SFDC_RAWINQUIRY_STG.EP_SCORE_EXPLICIT,
SFDC_RAWINQUIRY_STG.EP_SCORE_IMPLICIT, SFDC_RAWINQUIRY_STG.CAMPAIGN_ID,
SFDC_RAWINQUIRY_STG.DUPLICATE, SFDC_RAWINQUIRY_STG.GRADUATION_DATE,
SFDC_RAWINQUIRY_STG.GRADUATION_TYPE, SFDC_RAWINQUIRY_STG.ORIGINAL_LOCATION_CD,
SFDC_RAWINQUIRY_STG.INQUIRY_SCORE, SFDC_RAWINQUIRY_STG.ELOQUA_TIMESTAMP,
SFDC_RAWINQUIRY_STG.EQUIFAX_TIMESTAMP, SFDC_RAWINQUIRY_STG.ASSIGN_KCCL,
SFDC_RAWINQUIRY_STG.ASSIGN_MILITARY, SFDC_RAWINQUIRY_STG.LEAD_STATUS,
SFDC_RAWINQUIRY_STG.LEAD_CREATE_DATE, SFDC_RAWINQUIRY_STG.LEAD_DELETED,
SFDC_RAWINQUIRY_STG.LEAD_MASTER_RECORD_ID, SFDC_RAWINQUIRY_STG.LEGACY_LEAD_ID,
SFDC_RAWINQUIRY_STG.ASSIGN_INTERNATIONAL,
SFDC_RAWINQUIRY_STG.CONTACT_ID, SFDC_RAWINQUIRY_STG.EQUIFAX_TRANSITION_ID,
SFDC_RAWINQUIRY_STG.EP_EVENT_ID,
CRM_OPPORTUNITY.CRM_OPPORTUNITY_ID, CRM_OPPORTUNITY.CRM_PROSPECT_ID,
CRM_OPPORTUNITY.CRM_CAMPAIGN_ID, CRM_OPPORTUNITY.CRM_OWNER_USER_ID,
CRM_OPPORTUNITY.CRM_ORIGINAL_USER_ID, CRM_OPPORTUNITY.STAGE,
CRM_OPPORTUNITY.BRAND, CRM_OPPORTUNITY.GRAD_LEVEL, CRM_OPPORTUNITY.CHANNEL,
CRM_OPPORTUNITY.VENDOR_CODE, CRM_OPPORTUNITY.VENDOR_TIMESTAMP,
CRM_OPPORTUNITY.VENDOR_TRACKING_NUMBER, CRM_OPPORTUNITY.LOCATION_PREFERENCE,
CRM_OPPORTUNITY.EP_SCORE_EXPLICIT, CRM_OPPORTUNITY.EP_SCORE_IMPLICIT,
CRM_OPPORTUNITY.LEAD_STATUS, CRM_OPPORTUNITY.ADVISOR_DSI,
CRM_OPPORTUNITY.EP_PRESENTER_DSI, CRM_OPPORTUNITY.REP_DSI,
CRM_OPPORTUNITY.LEGACY_SFID, CRM_OPPORTUNITY.CLOSED_REASON,
CRM_OPPORTUNITY.CLOSED_DETAIL, CRM_OPPORTUNITY.APPLICATION_DATE,
CRM_OPPORTUNITY.REGISTRATION_DATE, CRM_OPPORTUNITY.ATTENDED_DATE,
CRM_OPPORTUNITY.SFDC_RAW_INQUIRY_ID, CRM_OPPORTUNITY.SFDC_LEAD_ID,
CRM_OPPORTUNITY.SFDC_OPPORTUNITY_ID, CRM_OPPORTUNITY.DW_ORGINAL_LOCATION_ID,
CRM_OPPORTUNITY.ADDRESS_STREET_INQUIRY, CRM_OPPORTUNITY.ADDRESS_CITY_INQUIRY,
CRM_OPPORTUNITY.ADDRESS_STATE_INQUIRY,
CRM_OPPORTUNITY.ADDRESS_POSTAL_CODE_INQUIRY,
CRM_OPPORTUNITY.ADDRESS_COUNTRY_INQUIRY, CRM_OPPORTUNITY.EMAIL_INQUIRY,
CRM_OPPORTUNITY.PHONE_INQUIRY, CRM_OPPORTUNITY.SFDC_OWNER_USER_ID,
CRM_OPPORTUNITY.SFDC_CAMPAIGN_ID, CRM_OPPORTUNITY.SFDC_INQUIRY_DATE,
CRM_OPPORTUNITY.SFDC_INQUIRY_DATE_RPT, CRM_OPPORTUNITY.ATTRITION_DATE,
CRM_OPPORTUNITY.ORIGINAL_PROGRAM_CD, CRM_OPPORTUNITY.CURRENT_PROGRAM_CD,
CRM_OPPORTUNITY.ORIGINAL_SESSION_CD, CRM_OPPORTUNITY.CURRENT_SESSION_CD,
CRM_OPPORTUNITY.ORIGINAL_LOCATION_CD, CRM_OPPORTUNITY.CURRENT_LOCATION_CD,
CRM_OPPORTUNITY.DUPLICATE_INQUIRY, CRM_OPPORTUNITY.GRADUATION_DATE,
CRM_OPPORTUNITY.GRADUATION_TYPE, CRM_OPPORTUNITY.PROSPECT_TYPE,
CRM_OPPORTUNITY.DW_CURRENT_LOCATION_ID, CRM_OPPORTUNITY.ENROLLMENT_STATUS,
CRM_OPPORTUNITY.ENROLLMENT_SUBSTATUS, CRM_OPPORTUNITY.DELETED_RAWINQUIRY,
CRM_OPPORTUNITY.DELETED_OPPORTUNITY, CRM_OPPORTUNITY.DELETED_LEAD,
CRM_OPPORTUNITY.INQUIRY_STATUS, CRM_OPPORTUNITY.LEAD_CREATE_DATE,
CRM_OPPORTUNITY.LEAD_CLOSE_DATE, CRM_OPPORTUNITY.INQUIRY_LOCATION,
CRM_OPPORTUNITY.KCCL_IND, CRM_OPPORTUNITY.SFDC_ORIGINAL_USER_ID,
CRM_OPPORTUNITY.SFDC_CONTACT_ID, CRM_OPPORTUNITY.MILITARY_IND,
CRM_OPPORTUNITY.ORIG_INTENDED_START_DATE, CRM_OPPORTUNITY.SFDC_ACCOUNT_ID,
CRM_OPPORTUNITY.REPORTING_DIVISION, CRM_OPPORTUNITY.INQUIRY_COUNT,
CRM_OPPORTUNITY.APPLICATION_COUNT, CRM_OPPORTUNITY.CURRENT_SESSION_START_DATE,
CRM_OPPORTUNITY.DELIVERY_LOCATION_CD, CRM_OPPORTUNITY.INQUIRY_SCORE,
CRM_OPPORTUNITY.ATTRITION_CODE, CRM_OPPORTUNITY.NEW_STUDENT_COUNT,
CRM_OPPORTUNITY.SFDC_MASTER_LEAD_ID, CRM_OPPORTUNITY.CALL_CENTER_PARTNER_NAME,
CRM_OPPORTUNITY.CALL_CENTER_PROGRAM, CRM_OPPORTUNITY.CALL_CENTER_DATE,
CRM_OPPORTUNITY.ELOQUA_TIMESTAMP, CRM_OPPORTUNITY.EQUIFAX_TIMESTAMP,
CRM_OPPORTUNITY.ORIGINAL_BRAND, CRM_OPPORTUNITY.ORIGINAL_GRAD_LEVEL,
CRM_OPPORTUNITY.ORIGINAL_CHANNEL ,
CRM_OPPORTUNITY.INTERNATIONAL_IND, CRM_OPPORTUNITY.NON_MATRIC_INQUIRY,
CRM_OPPORTUNITY.APPLICATION_REGION, CRM_OPPORTUNITY.EQUIFAX_TRANSITION_ID,
CRM_OPPORTUNITY.RPT_DIV_GRAD_LEVEL, CRM_OPPORTUNITY.SFDC_ORIGINAL_LEAD_ID,
CRM_OPPORTUNITY.ROUTING_LOCATION_CD, CRM_OPPORTUNITY.CRM_ROUTING_LOCATION_ID,
CRM_OPPORTUNITY.ORIGINAL_LEAD_CREATE_DATE,
CRM_OPPORTUNITY.EP_EVENT_ID, CRM_OPPORTUNITY.CRM_EP_EVENT_LEGACY_ID,
CRM_OPPORTUNITY.CRM_EP_OWNER_LEGACY_ID,
CRM_OPPORTUNITY.CRM_EP_ACCOUNT_LEGACY_ID,
nvl(CRM_PROSPECT.CRM_PROSPECT_ID, -1),
nvl(CRM_EVENT_LEGACY.CRM_EVENT_LEGACY_ID, -1),
nvl(CRM_EVENT_LEGACY.CRM_ACCOUNT_LEGACY_ID,-1),
nvl(CRM_EVENT_LEGACY.CRM_OWNER_LEGACY_ID,-1)
FROM
SFDC_RAWINQUIRY_STG, CRM_OPPORTUNITY, CRM_PROSPECT, CRM_EVENT_LEGACY
WHERE
SFDC_RAWINQUIRY_STG.RAW_INQUIRY_ID=CRM_OPPORTUNITY.SFDC_RAW_INQUIRY_ID(+)
AND
SFDC_RAWINQUIRY_STG.LEAD_ID=CRM_PROSPECT.SFDC_LEAD_ID(+)
AND
SFDC_RAWINQUIRY_STG.CONTACT_ID IS NULL
AND
CRM_Event_Legacy.Legacy_SFDC_Opportunity_ID(+) LIKE
SFDC_RawInquiry_Stg.EP_Event_ID || ‘%’
AND
CRM_Event_Legacy.DW_Closed_Date(+) = to_date(‘01012500’,‘mmddyyyy’)
UNION
SELECT /+ use_hash(SFDC_RAWINQUIRY_STG) use_hash(CRM_OPPORTUNITY)
use_hash(CRM_PROSPECT) use_hash(CRM_EVENT_LEGACY) */
SFDC_RAWINQUIRY_STG.RAW_INQUIRY_ID, SFDC_RAWINQUIRY_STG.LEAD_ID,
SFDC_RAWINQUIRY_STG.BRAND, SFDC_RAWINQUIRY_STG.GRAD_LEVEL,
SFDC_RAWINQUIRY_STG.CHANNEL, SFDC_RAWINQUIRY_STG.VENDOR_CODE,
SFDC_RAWINQUIRY_STG.VENDOR_TIMESTAMP,
SFDC_RAWINQUIRY_STG.VENDOR_TRACKING_NUMBER, SFDC_RAWINQUIRY_STG.ADDRESS_STREET,
SFDC_RAWINQUIRY_STG.ADDRESS_CITY, SFDC_RAWINQUIRY_STG.ADDRESS_STATE,
SFDC_RAWINQUIRY_STG.ADDRESS_POSTAL_CODE, SFDC_RAWINQUIRY_STG.ADDRESS_COUNTRY,
SFDC_RAWINQUIRY_STG.EMAIL, SFDC_RAWINQUIRY_STG.PHONE,
SFDC_RAWINQUIRY_STG.EP_PRESENTER_DSI, SFDC_RAWINQUIRY_STG.REP_DSI,
SFDC_RAWINQUIRY_STG.LEGACY_SFID, SFDC_RAWINQUIRY_STG.RAW_INQUIRY_DELETED,
SFDC_RAWINQUIRY_STG.SFDC_INQUIRY_CREATE_DATE, SFDC_RAWINQUIRY_STG.OWNER_ID,
SFDC_RAWINQUIRY_STG.SFDC_INQUIRY_UPDATE_DATE,
SFDC_RAWINQUIRY_STG.PROGRAM_OF_INTEREST,
SFDC_RAWINQUIRY_STG.LOCATION_PREFERENCE, SFDC_RAWINQUIRY_STG.INQUIRY_STATUS,
SFDC_RAWINQUIRY_STG.PROSPECT_TYPE, SFDC_RAWINQUIRY_STG.EP_SCORE_EXPLICIT,
SFDC_RAWINQUIRY_STG.EP_SCORE_IMPLICIT, SFDC_RAWINQUIRY_STG.CAMPAIGN_ID,
SFDC_RAWINQUIRY_STG.DUPLICATE, SFDC_RAWINQUIRY_STG.GRADUATION_DATE,
SFDC_RAWINQUIRY_STG.GRADUATION_TYPE, SFDC_RAWINQUIRY_STG.ORIGINAL_LOCATION_CD,
SFDC_RAWINQUIRY_STG.INQUIRY_SCORE, SFDC_RAWINQUIRY_STG.ELOQUA_TIMESTAMP,
SFDC_RAWINQUIRY_STG.EQUIFAX_TIMESTAMP, SFDC_RAWINQUIRY_STG.ASSIGN_KCCL,
SFDC_RAWINQUIRY_STG.ASSIGN_MILITARY, SFDC_RAWINQUIRY_STG.LEAD_STATUS,
SFDC_RAWINQUIRY_STG.LEAD_CREATE_DATE, SFDC_RAWINQUIRY_STG.LEAD_DELETED,
SFDC_RAWINQUIRY_STG.LEAD_MASTER_RECORD_ID, SFDC_RAWINQUIRY_STG.LEGACY_LEAD_ID,
SFDC_RAWINQUIRY_STG.ASSIGN_INTERNATIONAL,
SFDC_RAWINQUIRY_STG.CONTACT_ID, SFDC_RAWINQUIRY_STG.EQUIFAX_TRANSITION_ID,
SFDC_RAWINQUIRY_STG.EP_EVENT_ID,
CRM_OPPORTUNITY.CRM_OPPORTUNITY_ID, CRM_OPPORTUNITY.CRM_PROSPECT_ID,
CRM_OPPORTUNITY.CRM_CAMPAIGN_ID, CRM_OPPORTUNITY.CRM_OWNER_USER_ID,
CRM_OPPORTUNITY.CRM_ORIGINAL_USER_ID, CRM_OPPORTUNITY.STAGE,
CRM_OPPORTUNITY.BRAND, CRM_OPPORTUNITY.GRAD_LEVEL, CRM_OPPORTUNITY.CHANNEL,
CRM_OPPORTUNITY.VENDOR_CODE, CRM_OPPORTUNITY.VENDOR_TIMESTAMP,
CRM_OPPORTUNITY.VENDOR_TRACKING_NUMBER, CRM_OPPORTUNITY.LOCATION_PREFERENCE,
CRM_OPPORTUNITY.EP_SCORE_EXPLICIT, CRM_OPPORTUNITY.EP_SCORE_IMPLICIT,
CRM_OPPORTUNITY.LEAD_STATUS, CRM_OPPORTUNITY.ADVISOR_DSI,
CRM_OPPORTUNITY.EP_PRESENTER_DSI, CRM_OPPORTUNITY.REP_DSI,
CRM_OPPORTUNITY.LEGACY_SFID, CRM_OPPORTUNITY.CLOSED_REASON,
CRM_OPPORTUNITY.CLOSED_DETAIL, CRM_OPPORTUNITY.APPLICATION_DATE,
CRM_OPPORTUNITY.REGISTRATION_DATE, CRM_OPPORTUNITY.ATTENDED_DATE,
CRM_OPPORTUNITY.SFDC_RAW_INQUIRY_ID, CRM_OPPORTUNITY.SFDC_LEAD_ID,
CRM_OPPORTUNITY.SFDC_OPPORTUNITY_ID, CRM_OPPORTUNITY.DW_ORGINAL_LOCATION_ID,
CRM_OPPORTUNITY.ADDRESS_STREET_INQUIRY, CRM_OPPORTUNITY.ADDRESS_CITY_INQUIRY,
CRM_OPPORTUNITY.ADDRESS_STATE_INQUIRY,
CRM_OPPORTUNITY.ADDRESS_POSTAL_CODE_INQUIRY,
CRM_OPPORTUNITY.ADDRESS_COUNTRY_INQUIRY, CRM_OPPORTUNITY.EMAIL_INQUIRY,
CRM_OPPORTUNITY.PHONE_INQUIRY, CRM_OPPORTUNITY.SFDC_OWNER_USER_ID,
CRM_OPPORTUNITY.SFDC_CAMPAIGN_ID, CRM_OPPORTUNITY.SFDC_INQUIRY_DATE,
CRM_OPPORTUNITY.SFDC_INQUIRY_DATE_RPT, CRM_OPPORTUNITY.ATTRITION_DATE,
CRM_OPPORTUNITY.ORIGINAL_PROGRAM_CD, CRM_OPPORTUNITY.CURRENT_PROGRAM_CD,
CRM_OPPORTUNITY.ORIGINAL_SESSION_CD, CRM_OPPORTUNITY.CURRENT_SESSION_CD,
CRM_OPPORTUNITY.ORIGINAL_LOCATION_CD, CRM_OPPORTUNITY.CURRENT_LOCATION_CD,
CRM_OPPORTUNITY.DUPLICATE_INQUIRY, CRM_OPPORTUNITY.GRADUATION_DATE,
CRM_OPPORTUNITY.GRADUATION_TYPE, CRM_OPPORTUNITY.PROSPECT_TYPE,
CRM_OPPORTUNITY.DW_CURRENT_LOCATION_ID, CRM_OPPORTUNITY.ENROLLMENT_STATUS,
CRM_OPPORTUNITY.ENROLLMENT_SUBSTATUS, CRM_OPPORTUNITY.DELETED_RAWINQUIRY,
CRM_OPPORTUNITY.DELETED_OPPORTUNITY, CRM_OPPORTUNITY.DELETED_LEAD,
CRM_OPPORTUNITY.INQUIRY_STATUS, CRM_OPPORTUNITY.LEAD_CREATE_DATE,
CRM_OPPORTUNITY.LEAD_CLOSE_DATE, CRM_OPPORTUNITY.INQUIRY_LOCATION,
CRM_OPPORTUNITY.KCCL_IND, CRM_OPPORTUNITY.SFDC_ORIGINAL_USER_ID,
CRM_OPPORTUNITY.SFDC_CONTACT_ID, CRM_OPPORTUNITY.MILITARY_IND,
CRM_OPPORTUNITY.ORIG_INTENDED_START_DATE, CRM_OPPORTUNITY.SFDC_ACCOUNT_ID,
CRM_OPPORTUNITY.REPORTING_DIVISION, CRM_OPPORTUNITY.INQUIRY_COUNT,
CRM_OPPORTUNITY.APPLICATION_COUNT, CRM_OPPORTUNITY.CURRENT_SESSION_START_DATE,
CRM_OPPORTUNITY.DELIVERY_LOCATION_CD, CRM_OPPORTUNITY.INQUIRY_SCORE,
CRM_OPPORTUNITY.ATTRITION_CODE, CRM_OPPORTUNITY.NEW_STUDENT_COUNT,
CRM_OPPORTUNITY.SFDC_MASTER_LEAD_ID, CRM_OPPORTUNITY.CALL_CENTER_PARTNER_NAME,
CRM_OPPORTUNITY.CALL_CENTER_PROGRAM, CRM_OPPORTUNITY.CALL_CENTER_DATE,
CRM_OPPORTUNITY.ELOQUA_TIMESTAMP, CRM_OPPORTUNITY.EQUIFAX_TIMESTAMP,
CRM_OPPORTUNITY.ORIGINAL_BRAND, CRM_OPPORTUNITY.ORIGINAL_GRAD_LEVEL,
CRM_OPPORTUNITY.ORIGINAL_CHANNEL ,
CRM_OPPORTUNITY.INTERNATIONAL_IND, CRM_OPPORTUNITY.NON_MATRIC_INQUIRY,
CRM_OPPORTUNITY.APPLICATION_REGION, CRM_OPPORTUNITY.EQUIFAX_TRANSITION_ID,
CRM_OPPORTUNITY.RPT_DIV_GRAD_LEVEL, CRM_OPPORTUNITY.SFDC_ORIGINAL_LEAD_ID,
CRM_OPPORTUNITY.ROUTING_LOCATION_CD, CRM_OPPORTUNITY.CRM_ROUTING_LOCATION_ID,
CRM_OPPORTUNITY.ORIGINAL_LEAD_CREATE_DATE,
CRM_OPPORTUNITY.EP_EVENT_ID, CRM_OPPORTUNITY.CRM_EP_EVENT_LEGACY_ID,
CRM_OPPORTUNITY.CRM_EP_OWNER_LEGACY_ID,
CRM_OPPORTUNITY.CRM_EP_ACCOUNT_LEGACY_ID,
nvl(CRM_PROSPECT.CRM_PROSPECT_ID,-1),
nvl(CRM_EVENT_LEGACY.CRM_EVENT_LEGACY_ID, -1),
nvl(CRM_EVENT_LEGACY.CRM_ACCOUNT_LEGACY_ID,-1),
nvl(CRM_EVENT_LEGACY.CRM_OWNER_LEGACY_ID,-1)
FROM
SFDC_RAWINQUIRY_STG, CRM_OPPORTUNITY, CRM_PROSPECT, CRM_EVENT_LEGACY
WHERE
SFDC_RAWINQUIRY_STG.RAW_INQUIRY_ID=CRM_OPPORTUNITY.SFDC_RAW_INQUIRY_ID(+)
AND
SFDC_RAWINQUIRY_STG.CONTACT_ID=CRM_PROSPECT.SFDC_CONTACT_ID(+)
AND
SFDC_RAWINQUIRY_STG.CONTACT_ID IS NOT NULL
AND
CRM_Event_Legacy.Legacy_SFDC_Opportunity_ID(+) LIKE
SFDC_RawInquiry_Stg.EP_Event_ID || ‘%’
AND
CRM_Event_Legacy.DW_Closed_Date(+) = to_date(‘01012500’,‘mmddyyyy’)

2

Any feedback will be appreciated.
Thank you

3

This has usually been a problem for me if I am creating a very large result set
that possibly needs to be sorted.

I noticed you had a UNION

A UNION is actually a UNION ALL with an invisible outer SELECT that does a
DISTINCT – that is it requires sorting.

Here are some things to try (assuming non of the objects in the FROM clauses are
views)

4

Anybody any clues? Would an index on SFDC_RawInquiry_Stg.EP_Event_ID work? or
any other hint?
thank you

5

Did you try the suggestion I sent? I think it is a sorting issue and no amount
of indexes or hints will help.

6

Reduce the columns to one column. Keep the tables and joins.

Then run it once with the old join and once with the new and check the rows returned. See if more rows are returned. You may have hit a max on temp space. Also do row counts on the two separate parts.

7

Well, we know that all the overhead associated with the LIKE is the culprit. If
one or both of the IDs in the offending join is numeric, try to_charring them. I
don’t know if the outer join will fail with the addition of an index, but
I’m thinking to save the additional overhead for an on-the-fly type
conversion. Grasping at straws here.

This paragraph is food for thought, but maybe you can fool around with creating
in-line views to obtain subsets of data that can be matched in a way that
eliminates the LIKE clause in your outer join.

Again, just ideas that might be immediate throw-aways, but perhaps it’ll
spark something.

Best regards,
Mike

Michael S. Zarzycki, MTS | Manager of Engineering IT | Sensata Technologies,
Inc. | voice: 508-236-1015 | fax: 508-236-3701 | www.sensata.com | The World
Depends on Sensors and Controls

8

Thank you for your response.
Answers below…

9

Hi Henry,

  1. If the UNION ALL works then go see a DBA Already
    talked to DBA. Will not do anything. They are saying
    tablespace is large enough. They believe the SQL needs fine tuning.

  1. Do you know how big the TEMP tablespace currently is by any chance?

  2. What does Toad tell you the explain plan will be for the statement
    with the UNION? In the editor, press CTRL+E and the output area will
    give you many details.

Look through the details for the various sizes - especially those being
input to the sort phase(s). I suspect that one or more of these is
bigger than the current size of the TEMP tablespace.

If your DBA persists in telling you that the tablespace is big enough
already, show them the explain pan details and say that that is how much
is required.

If the DBA then persists in saying that you still need to optimise the
query, tell him/her to do it for you because you are unable to optimise
it any further.

After a while, I suspect you may well find yourself with a (much) bigger
TEMP tablespace! :wink:

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

10

Two last things to try.

See if you can get the DBAs to temporarily give you rights to set the
sort_area_size to the max and then you may need as much temp space.

The other thing to try is materialize each query. Hat is create a table of each
half. The UNION them together in a query. If that croaks on temp space then you
have proof you need more space because a strait union of two tables can’t
be optimized any further.

And one more thing. I presume the query you are running is needed. Someone needs
the results. You are not doing this because you have nothing better to do. In
that case tell your boss to fight the DBA. Someone has to have more clout.

He should be prepared to change it temporarily to see if that is the problem.

11

Thank you all for your responses.

I went through each query separated by the UNION, compared against each other
and they are distinct already. Hence, no UNION is needed but UNION ALL and the
result set comes really fast. However, our Data Architect needs to work on this
join proposed, but that is another story.