Previous Discussion	Next Discussion

Query optimization question... Ok, here's the situation: I'm dealing with a database with several hundred thousand rows. The problem is, one of the queries crashes my editor, and just hangs the web app. Here's the query in question: SELECT COUNT(*) AS TOTRECS FROM V_MY_VIEW VW WHERE VW.THE_ID IN ( SELECT DISTINCT V.THE_ID FROM V_MY_VIEW V, MY_TABLE T WHERE (FIELD_1 LIKE '%%' OR FIELD_2 LIKE '%%') AND V.diff_id = '3' ) I've narrowed the problem to the WHERE...IN clause, because the select inside the IN returns over 100,000 rows. Is there a good way to optimize this query? Perhaps I'm going about this the wrong way. It's been suggested to me that I try joins, or EXISTS instead of IN, but I'm not sure how a JOIN would work with the select, and I'm not sure EXISTS would do what I'm looking for. I'm using Oracle. Started By Monte on Nov 4, 2009 at 7:08:54 AM This message has been edited.

7 Response(s) | Reply

Earlier Replies | Replies 5 to 7 of 7 | Later Replies Goto Page: 1

Monte on Nov 4, 2009 at 7:26:27 AM (# 5) Ok, I think EXISTS will actually do what I want. I'll give it a try and report back... Monte on Nov 4, 2009 at 9:43:34 AM (# 6) Ok, apparently, this didn't work as well as I thought. Now, what it's doing is just duplicating the initial search results, regardless of what criteria I use. Monte on Nov 4, 2009 at 10:36:15 AM (# 7) Another update... I found out I need to modify my query a bit to get it to work right... Here's what I need to do: SELECT * FROM V_MY_VIEW MV WHERE EXISTS (SELECT DISTINCT V.FIELD_NAME FROM MY_VIEW V WHERE (FIELD_1 LIKE '%%' OR FIELD_2 LIKE '%%') AND V.TEST_FIELD = '3' ) and MV.TEST_FIELD = '3' Now I just need to incorporate it into the code, as some of it is dynamically generated...but that shouldn't be too difficult.

Earlier Replies | Replies 5 to 7 of 7 | Later Replies Goto Page: 1

To respond to a discussion, you must first logon.

If you are not registered, please register yourself to become a member of the SiteExperts.community.