Home » SQL & PL/SQL » SQL & PL/SQL » Join or Subquery? Is there another more efficient way of doing this
Join or Subquery? Is there another more efficient way of doing this [message #242277] Fri, 01 June 2007 08:10 Go to next message
fmrock
Messages: 45
Registered: December 2006
Member
Is there a more efficent way of doing this instead of a using an exists

This table would be called tblComments. And basicly, I want to find all accounts that do not have a the text "XYZ". They could have many comments, and if "XYZ" is in any one of them, I dont want it to show on the report.

I have this created already with an exists statement, but looking to possible make it more efficient with a join.

The query before checking the comments table has around 45K accounts that match.

AND NOT exists(
SELECT distinct r.location, r.accountnum 
FROM tblComments mr 
WHERE d.location = r.location 
AND d.accountnum = r.accountnum 
AND r.comment LIKE '%XYZ%'
)


This report does work, but it takes a while to run as you can imagine.

[Updated on: Fri, 01 June 2007 08:25] by Moderator

Report message to a moderator

Re: Join or Subquery? Is there another more efficient way of doing this [message #242279 is a reply to message #242277] Fri, 01 June 2007 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select clause (distinct r.location, r.accountnum ) is useless change it to just NULL.

Then gather the statistics and let Oracle chooses the good plan.

Regards
Michel
Re: Join or Subquery? Is there another more efficient way of doing this [message #242281 is a reply to message #242279] Fri, 01 June 2007 08:19 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Michel Cadot wrote on Fri, 01 June 2007 09:15
select clause (distinct r.location, r.accountnum ) is useless change it to just NULL.

Then gather the statistics and let Oracle chooses the good plan.

Regards
Michel




So something like this would be better?

AND NOT exists(
SELECT NULL 
FROM tblComments mr 
WHERE d.location = r.location 
AND d.accountnum = r.accountnum AND r.comment LIKE '%XYZ%'
)

[Updated on: Fri, 01 June 2007 08:26] by Moderator

Report message to a moderator

Re: Join or Subquery? Is there another more efficient way of doing this [message #242284 is a reply to message #242281] Fri, 01 June 2007 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Break your lines I don't see the end.
Quote:
d.location = r.location AND d.accountnum = r.accountnum AND r.comment LIKE '%XYZ%'

This part has nothing to do in the subquery, it is not related to the table in it.

Regards
Michel
Re: Join or Subquery? Is there another more efficient way of doing this [message #242288 is a reply to message #242277] Fri, 01 June 2007 08:33 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
AND NOT exists(
SELECT NULL 
FROM tblComments r 
WHERE d.location = r.location 
AND d.accountnum = r.accountnum 
AND r.comment LIKE '%XYZ%'
)


Sorry I think that was a typo when creating the example for the post.

I am a bit confused, but thanks for your help
Re: Join or Subquery? Is there another more efficient way of doing this [message #242294 is a reply to message #242288] Fri, 01 June 2007 08:58 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is correct.

Regards
Michel
Previous Topic: to make entry in log table
Next Topic: PLS-00553: character set name is not recognized
Goto Forum:
  


Current Time: Sat Dec 03 08:20:37 CST 2016

Total time taken to generate the page: 0.13046 seconds