Questions on Index

From: yong <yong_at_shell.com>
Date: Mon, 26 Oct 1998 15:13:12 -0600
Message-ID: <3634E5E8.B3DCC50@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]

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. Received on Mon Oct 26 1998 - 15:13:12 CST