Home » RDBMS Server » Performance Tuning » Speeding up this query?
Speeding up this query? [message #65438] Tue, 21 September 2004 16:18 Go to next message
Messages: 92
Registered: December 1999

/*+ ordered */
DISTINCT nds.Field1 a, nds.Field2 b, nds.Field3 c, nds.Field4 d
       FROM db1.tableA nd, db1.tableB nds
    WHERE nds.Field1 LIKE in_param1 || '%'
         AND nds.Field1 = nd.Field1
         AND nd.statuscode = 'A'
         AND (UPPER(nds.Datastreamtype) IN ('I', 'P'))
         AND nd.tni = UPPER(in_param2)
         AND nds.Field1 NOT IN  (SELECT pdw.Field1
                                                           FROM db2.Profile_Ds_Weights pdw
                                                        WHERE Profile_Data_SourceId = in_param3)
         AND rownum <= 200
         ORDER BY a, b;

tableA and tableB have approximately 12 million records each. Field1 is indexed on both tables. The query is running really slowly. Any suggestions on how to speed it up?


Re: Speeding up this query? [message #65439 is a reply to message #65438] Tue, 21 September 2004 16:45 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Some things you could try (apart from the regular indexing, stats gathering and tkprof / sqlplus autotrace):

1.) remove the distinct unless really necessary
2.) try changing the not in to not exists
3.) nds.Field1 >= nvl(in_param1, chr(0))
4.) AND (nds.Datastreamtype IN ('I', 'P', 'i', 'p'))
5.) remove the hint
6.) try re-writing the NOT IN as a QRY1 minus QRY2
7.) try re-writing the NOT IN as an outer join

(6) and (7) aren't likely to be efficient. Watch out that nulls in your data don't change the results when you re-write the query.


Previous Topic: Testing performance of queries (caching)
Next Topic: Speed becomes slow after some days
Goto Forum:

Current Time: Sat Feb 24 17:31:02 CST 2024