OraFAQ Forum: SQL & PL/SQL » Join or Subquery? Is there another more efficient way of doing this

Home » SQL & PL/SQL » SQL & PL/SQL » Join or Subquery? Is there another more efficient way of doing this

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Join or Subquery? Is there another more efficient way of doing this [message #242277]

Fri, 01 June 2007 08:10

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is correct.

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 06:41:09 CST 2024