Oracle-L: RE: slow query help

From: Nicoll, Iain <IAIN.D.NICOLL_at_saic.com>
Date: Wed, 18 Dec 2002 08:04:26 -0800
Message-ID: <F001.0051CE9C.20021218080426@fatcity.com>

Raj,

I thought it would only do the union all if it was able to use an index and all the instr's look as though they'd stop that. Even then I thought it was generally just rule that would do that unless you used the use_concat hint.

I can't see why a full table scan of each wouldn't be fairly efficient unless the result set is only a small fraction of the total possible rows.

I may have missed some e-mails which make the above statements irrevelant, if so my apologies.

Iain

-----Original Message-----
Sent: 18 December 2002 13:55
To: Multiple recipients of list ORACLE-L

Joan,

Here is a suggestion ...
if this is going to be your most used part, I'd look into Intermedia ... you'll have a lot more options to work with and they will work good.

Until then, I'd recommend replacing instr() with appropriate LIKE clause because at-least it will help you use an index. Through a DB trigger ensure that all names (first/middle/last) are always in UPPER so you don't have to pur UPPER or deal with FBI.

Initially I thought of merging all clauses into one, but then I realized, Oracle will end up doing what I un-did (it will replace all OR conditions to UNION ALL anyways).

Happy Holidays Everyone !!
Raj

Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Nicoll, Iain
INET: IAIN.D.NICOLL_at_saic.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Dec 18 2002 - 10:04:26 CST