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
andy
Messages: 92
Registered: December 1999
Member


SELECT /*+ 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?

Thanks!

Andy

Report message to a moderator

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.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5059029111432

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:953229842074

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Feb 03 10:45:09 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.