Re: Index questions

A copy of this was sent to kirk_at_kaybee.org (if that email address didn't require changing) On 15 Feb 2000 19:43:52 GMT, you wrote:

>First of all, is it necessary to create an index on the primary key
>column of a table (as almost every search I do is on the primary key)?
>

the creation of a primary key will either

• create an index on the primary key columns
• "take over" an existing index on the primary key columns

so -- if you have a primary key -- you have an index.

>Secondly, I have one table where I will regularly do a search like this:
>select * from users where u_name = 'blah' and u_index = 10;
>
>In other words, I will reguarly search for table rows based on those two
>columns together (The two columns, when combined, will be unique, but
>each column by itself will not be unique).
>
>Is this how I create an index to speed up the mentioned queries?
>
>CREATE unique INDEX username_index on users (u_name asc, u_index asc);

Yes -- if u_index is more 'selective' then u_name (eg: if you count(distinct u_index) and count( distinct u_name) and find the count of u_index is higher then u_name ) you could consider creating the index on u_index, u_name instead.

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation