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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
