Home » SQL & PL/SQL » SQL & PL/SQL » Improve efficiency of script (19.2.1.247)
Improve efficiency of script [message #682152] Tue, 06 October 2020 19:02 Go to next message
Dan22
Messages: 2
Registered: October 2020
Junior Member
Hi all

My first post here. Hope someone can help with a basic question.

I have the below script and it just runs for way too long. Is there a way to improve the efficiency?

I don't have privileges to create temp tables or anything.

SELECT DISTINCT s1.ANOTHER_NUM ,s1.LAST_UPD
FROM S_CONTACT s1    
LEFT JOIN S_ASSET_CON s2 ON s2.CONTACT_ID = s1.row_id 
LEFT JOIN s_asset     s3 ON s2.ASSET_ID = s3.row_id   
LEFT JOIN S_PROD_INT  s4 ON s3.row_id = s4.row_id 
WHERE  s1.FIELDA= 'Partially Complete'
AND s1.FIELDB = 'Re-verification Required'
AND s3.NUM IS NOT NULL
AND s1.ANOTHER_NUM IS NOT NULL
Re: Improve efficiency of script [message #682154 is a reply to message #682152] Wed, 07 October 2020 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 21623
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are columns (used to join table) indexed? Did you gather statistics? What does the explain plan say?
Re: Improve efficiency of script [message #682156 is a reply to message #682152] Wed, 07 October 2020 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 67538
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For any performances question, please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.

[Updated on: Wed, 07 October 2020 00:26]

Report message to a moderator

Re: Improve efficiency of script [message #682157 is a reply to message #682152] Wed, 07 October 2020 01:24 Go to previous messageGo to next message
John Watson
Messages: 8424
Registered: January 2010
Location: Global Village
Senior Member
Your use of outer joins is forcing the join order, do you really need them?

As you have the filter
AND s3.NUM IS NOT NULL
that outer join must be pointless: it is generating null rows which you then throw them away.

Is the DISTINCT actually doing anything? Are there any duplicates without it?

Is the join to s4 necessary? You do not project any columns from s4, and as it is (again!) an outer join, it does not filter any rows either.
Re: Improve efficiency of script [message #682158 is a reply to message #682157] Wed, 07 October 2020 01:29 Go to previous messageGo to next message
Dan22
Messages: 2
Registered: October 2020
Junior Member
Hi
Well the s3.num is the account table.
So I am trying to retrieve all S1. Customers who HAVE an account, i.e. not null for S3.

I don't know if there will be any duplicates, just taking precautions.

[Updated on: Wed, 07 October 2020 01:31]

Report message to a moderator

Re: Improve efficiency of script [message #682159 is a reply to message #682158] Wed, 07 October 2020 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67538
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I don't know if there will be any duplicates, just taking precautions.

So:
Michel Cadot wrote on Wed, 07 October 2020 07:25

For any performances question, please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.
Re: Improve efficiency of script [message #682202 is a reply to message #682158] Thu, 08 October 2020 20:19 Go to previous messageGo to next message
flyboy
Messages: 1895
Registered: November 2006
Senior Member
Dan22 wrote on Wed, 07 October 2020 08:29

So I am trying to retrieve all S1. Customers who HAVE an account, i.e. not null for S3.
So, the join is definitely not OUTER. And, as the query shall return values from S1 only, you may use IN or EXISTS subquery for the existence check (the description of expected results explicitly mentions it), something like:
select s1.another_num, s1.last_upd
from s_contact s1
where s1.fielda = 'Partially Complete'
  and s1.fieldb = 'Re-verification Required'
  and s1.another_num is not null
  and s1.row_id in (
    select s2.contact_id from s_asset_con s2
      inner join s_asset s3 on s2.asset_id = s3.row_id
      --left join s_prod_int s4 on s3.row_id = s4.row_id
    where s3.num is not null
  )
Re: Improve efficiency of script [message #682205 is a reply to message #682158] Fri, 09 October 2020 03:30 Go to previous message
John Watson
Messages: 8424
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I don't know if there will be any duplicates, just taking precautions.
A mantra worth trying:

You must understand your data! You must understand your queries!

To write code that is both efficient and fills the business need, you have to know the data with which you are working. In this case, is it actually possible that there will be duplicates? If you investigate you may find that the constraints (existing constraints, or more that you can declare) mean duplicates are not possible. Or perhaps they are possible, but in business terms should not be. Or if they are possible and reasonable, perhaps they should be handled differently.

Use of DISTINCT is one of my pet hates. It so often covers up a poor data model, or lack of a clear problem definition.
Previous Topic: Duplicate delete condition
Next Topic: RANK issue in SQL
Goto Forum:
  


Current Time: Mon Nov 23 16:22:22 CST 2020