Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query tuning

Re: Query tuning

From: <rajesh_at_solutionsoftware.com>
Date: 11 Apr 2003 09:37:45 -0700
Message-ID: <aa8abed4.0304110837.36e9f45b@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US