Home » SQL & PL/SQL » SQL & PL/SQL » Query Re-structure
Query Re-structure [message #439201] Thu, 14 January 2010 20:24 Go to next message
decci_7
Messages: 68
Registered: March 2006
Member
Hi,

I want to restructure the below query and remove all the "AND tskid IN" filters.

(SELECT tskid 
    FROM   tskident 
    WHERE  tskid IN (SELECT tskid 
                     FROM   tskident 
                     WHERE  idcode = 6 
                            AND fieldnbr = 1 
                            AND (fieldvalue) = '111111') 
           AND tskid IN (SELECT tskid 
                         FROM   tskident 
                         WHERE  idcode = 6 
                                AND fieldnbr = 1 
                                AND (fieldvalue) = '300700') 
           AND tskid IN (SELECT tskid 
                         FROM   tskident 
                         WHERE  idcode = 6 
                                AND fieldnbr = 1 
                                AND (fieldvalue) = '800601') 
           AND tskid IN (SELECT tskid 
                         FROM   tskident 
                         WHERE  idcode = 8 
                                AND fieldnbr = 1 
                                AND (fieldvalue) = '1')) 


But in my original query, below particular portion is based on the fetch from a dynamic query. So more the number of idcode/fieldnbr/fieldvalue combinations, more will be the number of "AND tskid IN" clauses and more time this query is going to take.

AND tskid IN (SELECT tskid 
                         FROM   tskident 
                         WHERE  idcode = 6 
                                AND fieldnbr = 1 
                                AND (fieldvalue) = '800601') 


Is there any way to restructure this query to improve performance?

thanks!
Re: Query Re-structure [message #439202 is a reply to message #439201] Thu, 14 January 2010 20:35 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
>Is there any way to restructure this query to improve performance?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Query Re-structure [message #439203 is a reply to message #439201] Thu, 14 January 2010 21:36 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Hi Blackswan,

I'm not looking for an exact solution here. I just need a hint what can I use to eliminate the multiple "AND" clauses. That's why I didn't include the DMLs.

Forget about the dynamic SQLs invloved. If there any way I can eliminate multiple "AND" clauses, it will be more than handful.

With all due respect, I'm aware of all the rules on the forum. If you still want me to include the DMLs, please let me know. I'll do that.

Thanks!!

Re: Query Re-structure [message #439204 is a reply to message #439201] Thu, 14 January 2010 21:50 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Try :-

(SELECT tskid 
    FROM   tskident 
    WHERE  exists  (SELECT 1
                     FROM   tskident 
                     WHERE ( idcode = 6 AND  idcode = 8)  
                            AND fieldnbr = 1 
                            AND ( fieldvalue =  '111111'  and fieldvalue = '300700'  and fieldvalue = '800601' and fieldvalue = '1') )



you might want to correlate the query with tskid

-Rahul

[Updated on: Thu, 14 January 2010 21:58]

Report message to a moderator

Re: Query Re-structure [message #439206 is a reply to message #439204] Thu, 14 January 2010 22:01 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Hi Rahul,

Thanks for the suggestion. But I forgot and it would be worth mentioning here that I have already tried the WHERE EXISTS and it doesn't make much difference to the performance.

SELECT tskid 
    FROM   tskident ti
    WHERE  EXISTS (SELECT 1
                     FROM   tskident 
                     WHERE  idcode = 6 
                            AND fieldnbr = 1 
                            AND tskid = ti.tskid
                            AND (fieldvalue) = '111111') 


And my problem is that the "AND tskid" clause is not fixed. It depends on how many idcode/fieldnbr/fieldvalue combinations (like in above its 1 combination of 6,1,111111) are there.

Also, I would know prior to jumping to this query, how many combinations are there and what exactly combinations are.

thanks!
Re: Query Re-structure [message #439231 is a reply to message #439206] Fri, 15 January 2010 02:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
YOu can replace the whole query with a set of INTERSECTS:
 SELECT tskid 
 FROM   tskident 
 WHERE  idcode = 6 
 AND    fieldnbr = 1 
 AND    fieldvalue = '111111'
INTERSECT
 SELECT tskid 
 FROM   tskident 
 WHERE  idcode = 6 
 AND    fieldnbr = 1 
 AND    fieldvalue = '300700'
INTERSECT
 SELECT tskid 
 FROM   tskident 
 WHERE  idcode = 6 
 AND    fieldnbr = 1 
 AND    fieldvalue = '800601'
INTERSECT
 SELECT tskid 
 FROM   tskident 
 WHERE  idcode = 8 
 AND    fieldnbr = 1 
 AND    fieldvalue = '1')


Or you could do a grouping operation, and select all the tskids that occur for each set of conditions:
SELECT tskid,count(distinct idcode||':'||fieldnbr||':'||fieldvalue)
FROM   tskident
WHERE  (idcode = 6 AND fieldnbr = 1 AND fieldvalue = '111111')
OR     (idcode = 6 AND fieldnbr = 1 AND fieldvalue = '300700')
OR...
GROUP BY tskid
HAVING count(distinct idcode||':'||fieldnbr||':'||fieldvalue) = N

where N is the number of different OR clauses that you have.
Re: Query Re-structure [message #439237 is a reply to message #439204] Fri, 15 January 2010 03:53 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
rahulvb wrote on Fri, 15 January 2010 03:50
Try :-

(SELECT tskid 
    FROM   tskident 
    WHERE  exists  (SELECT 1
                     FROM   tskident 
                     WHERE ( idcode = 6 AND  idcode = 8)  
                            AND fieldnbr = 1 
                            AND ( fieldvalue =  '111111'  and fieldvalue = '300700'  and fieldvalue = '800601' and fieldvalue = '1') )



you might want to correlate the query with tskid

-Rahul


That's never going to return any data. Some of those ANDs need to be ORs.
Re: Query Re-structure [message #439238 is a reply to message #439204] Fri, 15 January 2010 03:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Try :-

(SELECT tskid 
    FROM   tskident 
    WHERE  exists  (SELECT 1
                     FROM   tskident 
                     WHERE ( idcode = 6 AND  idcode = 8)  
                            AND fieldnbr = 1 
                            AND ( fieldvalue =  '111111'  and fieldvalue = '300700'  and fieldvalue = '800601' and fieldvalue = '1') )



you might want to correlate the query with tskid
This won't return anything like the same rows.
The original query would return all the TSKID values that existed for each combination - what your query will do (if you convert some of the ANDs into ORs is return all the TSKIDs that exists for ANY combination.
Re: Query Re-structure [message #439445 is a reply to message #439238] Sat, 16 January 2010 22:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could create a GLOBAL TEMPORARY TABLE to store the valid values:

CREATE GLOBAL TEMPORARY TABLE temp_tskident AS
SELECT idcode, fieldnbr, fieldvalue
FROM   tskid
WHERE 1=0


Fill it up with the values you are after, then construct a query that ensures all values in the GTT are present for a given tskid.

There's probably a few ways of doing this, but one quite elegant one is with a Partitioned Outer Join
SELECT ti.tskid
FROM   tskident ti PARTITION BY (tskid)
RIGHT OUTER JOIN temp_tskident tmp
ON tmp.idcode = ti.idcode
AND tmp.fieldnbr = ti.fieldnbr
AND tmp.fieldvalue = ti.fieldvalue
GROUP BY tskid
HAVING COUNT(ti.rowid) = COUNT(*)


Ross Leishman
Re: Query Re-structure [message #440363 is a reply to message #439201] Fri, 22 January 2010 16:01 Go to previous message
decci_7
Messages: 68
Registered: March 2006
Member
Thanks Ross!!

Tried INTERSECT as suggested by JRowbottom and it perfectly fits the bill.
Multiple ANDs on tskid were trying to do the same thing as INTERSECT i.e. finding a taskid which is common for all the combinations.

Previous Topic: Command to add constraints
Next Topic: creating a new table from another table
Goto Forum:
  


Current Time: Tue Sep 27 05:58:39 CDT 2016

Total time taken to generate the page: 0.24754 seconds