Re: Query tuning

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 - 00:15:30 CDT