Yes, it looks like..
CREATE INDEX name ON table (Col1,Col2);
You could also do something like:
CREATE INDEX name ON table (Col1||Col2);
(same as last_name+first_name sort of)
>1) is it more efficient to use a composite index or a separate index on each field
Depends on the SQL statement you are using.
But seperate a key concept(s) in indexes are
- to keep them as compact as possible (less rows need to be scanned off disk and less memory needs to be used.)
- if the index does not have many different values i.e. only 0 or 1 use a bitmap index.
If you do statements like..
update table set col1=2167,col2=212 where col1=1 and col2=2;
Then use seperate indexes..
If you do something like...
update table set col1=2167,col2=212 where col1||col2=1||2;
Use the composite method.
Let me just say the the || is highly ineffiecient I can't see why you would ever need to use and many DBA's would crap in their pants if they saw this coding style. Maybe a view would be appropriate. That being said I only index on key fields or fields which are more or less unique and are used extensively in where clauses.
>2) if the composite index is more efficient, can you create a composite index on numeric fields w/o converting them to strings first?
I don't think so you need to convert to strings.
As I said depends on what you are trying to do.
HTH
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement