index question
samir BADAOUI asked whether the primary key index on tab1 could be dropped when a composite index idx1 on (col1,col2) exists. Art S. Kagel said no: the engine would just rename the index, and a composite index cannot enforce primary key uniqueness. Jim Lowry added the key is also used when joining tables. San advised dropping constraints for performance, using indexes and stored procedures instead.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi all, I know that if we have a index idx1 of a tabale tab1 on col1,col2 we dont need to add a index idx2 on tab1(col1) (because idx2 can be used when we used somethings like: where col1=..) Now my question is : if we have a index idx1 on tab1(col1,col2). Can I drop The primery ker tab1_pk of tab1 on col1 or it can be usefull. thanks ! ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
samir BADAOUI wrote: > > Hi all, > I know that if we have a index idx1 of a tabale tab1 on col1,col2 > we dont need to add a index idx2 on tab1(col1) (because idx2 can be used > when we used somethings like: where col1=..) > Now my question is : if we have a index idx1 on tab1(col1,col2). Can I drop > The primery ker tab1_pk of tab1 on col1 or it can be usefull. > thanks ! No you cannot. If you do the engine will simply rename the index into one of those unfathomable unusable index names and the index will still be there. Besides PRIMARY KEYs must be unique, by definition, so that composite index cannot be used to verify that a primary key is unique which is the purpose of the primary key index. Art S. Kagel
Art S. Kagel wrote: > > samir BADAOUI wrote: > > > > Hi all, > > I know that if we have a index idx1 of a tabale tab1 on col1,col2 > > we dont need to add a index idx2 on tab1(col1) (because idx2 can be used > > when we used somethings like: where col1=..) > > Now my question is : if we have a index idx1 on tab1(col1,col2). Can I drop > > The primery ker tab1_pk of tab1 on col1 or it can be usefull. > > thanks ! > > No you cannot. If you do the engine will simply rename the index into one > of those unfathomable unusable index names and the index will still be > there. Besides PRIMARY KEYs must be unique, by definition, so that > composite index cannot be used to verify that a primary key is unique > which is the purpose of the primary key index. > > Art S. Kagel Is it (the primary key) useful? YES... not just for what Art said, but it is used when going from one table to another.
If you want achieve good perfomance you should remove any constraints from your database. Use only indexes and stored procedures to ensure data consistensy. -- Andrew Svikhnushin Inist Ltd. E-mail: san@inist.ru samir BADAOUI <samir_badaoui@hotmail.com> wrote in message news:7os9aj$3co$1@news.xmission.com... > > Hi all, > I know that if we have a index idx1 of a tabale tab1 on col1,col2 > we dont need to add a index idx2 on tab1(col1) (because idx2 can be used > when we used somethings like: where col1=..) > Now my question is : if we have a index idx1 on tab1(col1,col2). Can I drop > The primery ker tab1_pk of tab1 on col1 or it can be usefull. > thanks ! > > > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com