Re: Query tuning

create index fi_agent_upper ( actually any name you want) on table_name UPPER(AgentLastName) tablespace indextablespace;  

rajesh_at_solutionsoftware.com wrote:

>select count(1) from Agent where (Upper(Agent.LastName) like
> 'ADA%' or Upper(Agent.FirstName) like 'ADA%')
>The above query returns me 9 records. What is the syntax to create a
>functional index. I am sorry, but I new to these things. Ans also, if
>we are using the UPPER function and the LIKE clause, would the index
>be of any use to us ?
>Regards,
>Rajesh
>
>mikharakiri_at_yahoo.com (Mikito Harakiri) wrote in message news:<bdf69bdf.0304102115.42d79655_at_posting.google.com>...
>> 8.1.7... Never mind statistics, then.
>>
>> How selective are your predicates? Let's first check if concatenation of
>> range scans of Agent table by the predicate
>>
>> (Upper(Agent.LastName) like
>> 'ADA%' or Upper(Agent.FirstName) like 'ADA%')
>>
>> is a good place to leverage indexing. There are 2 minor tehnicalities for
>> this idea to work:
>>
>> 1. Bulding a functional index on Upper(LastName) and Upper(FirstName)
>> 2. Make sure is that further joins wouldn't explode the result set
>> significantly.
>>
>> I'm also assuming that you run the query in "all rows" mode, because for
>> "first rows" the strategy would be different: a good "first rows" plan has
>> pipelined execution, and driving index would necessarily include "ordered
>> by" columns as well.
>>
>> But, first of all, what does
>>
>> select sum (case (Upper(Agent.LastName) like
>> 'ADA%' or Upper(Agent.FirstName) like 'ADA%') then 1 else 0)/count(1)
>> from Agent
>>
>> returns? (My memory is short: if "case" syntax wasn't in 8.1 yet, please
>> rewrite the above with "decode", or just let us know
>>
>> select count(1) from Agent where (Upper(Agent.LastName) like
>> 'ADA%' or Upper(Agent.FirstName) like 'ADA%')
>>
>> as you told us the total number of records already).
>>
>> <rajesh_at_solutionsoftware.com> wrote in message
>> news:aa8abed4.0304101937.289ba7db_at_posting.google.com...
>> > Here is the explain plan. Actually, I didnot know what statistics we
>> > use to analyze a query. So I couldnot furnish any further information.
>> > I am using ORACLE. 8.1.7.2.1. Please instruct if i need to furnish

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- Received on Fri Apr 11 2003 - 12:10:41 CDT