Re: Query tuning

OK, you have rather selective predicate -- 9 records out of 5000. Let Agent be the driving table, then. Next steps:

1. Create 2 functional indexes on Upper(Agent.LastName) and on Upper(Agent.FirstName). 2. set autotrace on select --+index(a index1) index(a index2) count(1) from Agent a where (Upper(Agent.LastName) like 'ADA%' or Upper(Agent.FirstName) like 'ADA%')

you would expect no more than couple dozen buffer gets here. This way you doublecheck that your index access path is working.

3. Make sure you have join indexes -- otherwise full table scan on other tables would ruin the performance.
4. Test the full query with hints: ordered, use_nl, index 5. Gather statistics, remove hints and make sure the plan is still OK.

You may skip step #4 and go directly to #5 and hope for the best, but you would have to reconsider #4 if #5 is not working.

<rajesh_at_solutionsoftware.com> wrote in message news:aa8abed4.0304110837.36e9f45b_at_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 - 12:06:27 CDT