Home » SQL & PL/SQL » SQL & PL/SQL » HELP IN NOT LIKE / INSTEAD OF NOT LIKE
HELP IN NOT LIKE / INSTEAD OF NOT LIKE [message #302486] |
Mon, 25 February 2008 21:55  |
kumarvk
Messages: 214 Registered: July 2004
|
Senior Member |
|
|
Dear All,
I have the following sql using NOT LIKE
SELECT DISTINCT b.rno,d.INO,d.IAMT,b.cno,b.eno,b.lic,
a.code,a.dsc
FROM RJOBS a,RHDR b,CCR c,IHDR d,ILINES e
WHERE a.sts = 'I'
AND A.CACC_NO='DT000004'
AND a.rohd_id=b.ID
AND B.MAKE_CODE='T7'
AND b.CARD_ID=c.ID
AND a.ID = e.rojb_id
AND e.inhd_id=d.ID
AND d.sts != 'X'
AND D.INV_NO LIKE '%SAI%'
AND B.ROST_CODE != 'PDI'
and A.eng_dsc NOT LIKE '%SERVICE%'
When I run this script it takes lot of time atleast 30 minutes since the RJOBS table has got more than 500,000 records. Is there anyother method or SQL to get the records in a faster way. Please kindly help.
|
|
|
Re: HELP IN NOT LIKE / INSTEAD OF NOT LIKE [message #302489 is a reply to message #302486] |
Mon, 25 February 2008 22:03   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
SELECT DISTINCT b.rno,d.INO,d.IAMT,b.cno,b.eno,b.lic,a.code,a.dsc
FROM RJOBS a,RHDR b,CCR c,IHDR d
WHERE a.sts = 'I'
AND A.CACC_NO='DT000004'
AND a.rohd_id=b.ID
AND B.MAKE_CODE='T7'
AND b.CARD_ID=c.ID
AND a.ID,d.ID in (select e.rojb_id, e.inhd_id from ILINES e)
AND d.sts != 'X'
AND D.INV_NO LIKE '%SAI%'
AND B.ROST_CODE != 'PDI'
and A.eng_dsc NOT LIKE '%SERVICE%'
|
|
|
|
|
Re: HELP IN NOT LIKE / INSTEAD OF NOT LIKE [message #302526 is a reply to message #302489] |
Tue, 26 February 2008 00:59  |
 |
MarcS
Messages: 312 Registered: March 2007 Location: Antwerp
|
Senior Member |
|
|
anacedent wrote on Tue, 26 February 2008 05:03 |
SELECT DISTINCT b.rno,d.INO,d.IAMT,b.cno,b.eno,b.lic,a.code,a.dsc
FROM RJOBS a,RHDR b,CCR c,IHDR d
WHERE a.sts = 'I'
AND A.CACC_NO='DT000004'
AND a.rohd_id=b.ID
AND B.MAKE_CODE='T7'
AND b.CARD_ID=c.ID
AND (a.ID,d.ID) in (select e.rojb_id, e.inhd_id from ILINES e)
AND d.sts != 'X'
AND D.INV_NO LIKE '%SAI%'
AND B.ROST_CODE != 'PDI'
and A.eng_dsc NOT LIKE '%SERVICE%'
|
anacedent forgot to put braces around the a.ID,d.ID
AND (a.ID,d.ID) in (select e.rojb_id, e.inhd_id from ILINES e)
But could you provide us with the EXPLAIN PLAN of your statement?
If you are familiar with SQL_TRACE & TKPROF: output of the latter would be very welcome as well
We could suggest to add indexes, but maybe they're already there as we don't know how the tables & which indexes have been created.
Sorry Michel, I've been spoonfeeding again
[Updated on: Tue, 26 February 2008 01:01] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Thu Feb 06 10:14:19 CST 2025
|