Very Computer

I have a table with 2 fields "ID, Name"

I want to be able to search on them both together & individually!

Do I have to create 3 indexes i.e on ID, Name, ID&Name or will just the one
ID&Name work?

Thanks

Adrian HArrison

If you search them individually and it's not the field you defined
first in your create index statement, it will not be used.. try
creating one for each field and see how it goes..

Robert Prendin

Robert Prendin
RP Data Solutions Inc.
Specializing in ORACLE DBA Support Services

You can search all 3 ways by creating 2 indexes as follows

index1 on (col1,co2)
index2 on (col2)

any query on col1 should use index1 (optimizer aside)
any query on col2 will use index2
any query on both columns will use index1

Conan

Quote:>I have a table with 2 fields "ID, Name"
>I want to be able to search on them both together & individually!
>Do I have to create 3 indexes i.e on ID, Name, ID&Name or will just the one
>ID&Name work?

You can search all 3 ways by creating 2 indexes as follows

index1 on (col1,co2), index2 on (col2)
or just
index1 on (col1), index2 on (col2)

(Largely) paraphrased...

An index on (id, name) will help
   a. queries on "id"
   b. queries on ( id, name)
   c. not queries on name

An index on each will help:
   a. queries on id
   b. queries on name
   c. queries on both (but not as well as the index above)

There are a lot of other dependencies on all of this being - format of
your SQL etc etc

Cheers
--
==============================================
Connor McDonald
BHP Information Technology
Perth, Western Australia
"The difference between me and a madman is that I am not mad"
                                - Salvador Dali