Re: Questions on Index

1. You will not get any performance advantage (in fact it will decline for Inserts & Updates) if you index a column which appears in the WHERE clause with a function.
2. Depends on how you use the columns;
3. You will get an improvement if you use the columns thus; CREATE INDEX idx_1 ON mytable (col1, col2)
4. SELECT col3, col4 FROM mytable WHERE col1=xyz

Or

ii) SELECT col3, col4 FROM mytable WHERE col1=xyz AND col2=abc

But NOT if you try

iii) SELECT col3, col4 FROM mytable WHERE col2=abc

If you only do i) or iii) then create two indexes (one for each column) If you do i), ii) and iii) then create two indexes one for both columns (as above) and one for the second column (col2).

If you create two indexes (one for each column) then the following statement can only use one of the indexes;

SELECT col3, col4 FROM mytable WHERE col1=xyz AND col2=abc

If you want to improve the performance of you select this;

1. Create another column called say upper_mycol
2. UPDATE mytable SET upper_mycol = UPPER(mycol)
3. Create a trigger to set upper_mycol on Inserts and Updates
4. Create an index on upper_mycol
5. Use upper_mycol instead of UPPER(mycol) in your select!

Stewart Burnett

Please remove 'nospam' from address when replying direct.

yong wrote in message <3634E5E8.B3DCC50_at_shell.com>...
>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';
>
>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]
>
Received on Tue Oct 27 1998 - 03:17:17 CST