Re: Questions on Index

In article <3634E5E8.B3DCC50_at_shell.com>, yong <yong_at_shell.com> wrote:
>Question1: "Tuning SQL statements" says don't index a column that only
>appears in the WHERE clause with functions (except functions MAX, MIN)
>or operators. Does this mean I won't have a performance advantage if I
>index mycol and I say
>
>select * from mytable where upper(mycol)='TEST';

Sorry, using a function (like upper(), nvl(), decode(), etc) on the database column in the where clause means Oracle has to read the value, then perform the function to see if the condition is met... Hence no index is used. (The optimizer is irrelevant.)

>Question 2: If I have an index on mycol1 and another on mycol2, will the
>performance not be as good as if I have one index on a composite index
>on mycol1 and mycol2 together? Why?[note]

Because if both queried columns are in one composite index, then Oracle only has to read one index to resolve it. If there are two separate indexes exist, it must either: 1) read both indexes or 2) read one index and then scan the results for the second criteria (the optimizer IS relevant).

>For both questions, assume all columns have good selectivity (if that's
>relevant). Thanks for advice.
>Yong Huang
>
>___________
>[note] If you're interested, here's my real-world problem. First I try
>select * from mytable where col1='a' and col2='b'; if not successful
>(not found), try select * from mytable where col1='a' and col3='c'. For
>the sake of argument, I omit upper() here. My question 1 is very
>important to me.

Why not:

select *

   from MYTABLE
 where COL1 = 'a'

    and COL2 in ('b','c');

This may return more than one record though, depending on your data. Received on Tue Oct 27 1998 - 18:20:20 CST