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
Received on Fri Apr 11 2003 - 11:37:45 CDT
