Tables-Primary Key-Sequential read

[kiran_k8]

Hi Folks,

Out of the following which imporves performace?

1.Using all the primary keys of a table in the where clause of a select statement?

2.Using any one or two (not all) primary keys of a table in the where clause of a select statement?

Let me the know the same in the case of using an Secondary index.

3.If we follow the second one,then it will go for a sequential read,how this sequential read mars the performance?

4.How creating an index will affect the database as BASIS guys are not in favour to creating an index.

Thanks,

K.Kiran.

[Former Member]

1.Using all the primary keys of a table in the where clause of a select statement?

2.Using any one or two (not all) primary keys of a table in the where clause of a select statement?

Out of the above 2 first one will give more performance. Coming to primary key or Secondary indexses, anything.. it gives better performance if you give the key fileds in the order of DB declaration.

I mean you are specifying some fields of primary key.. but not in the order .. i mean u have specified key1, key3, key4. It will give less performance than specifying only key1 and key2.

in secondary indexes if you are not specifying all key completely that will take the key up to the order matches. i mean in key1, key3, key4 case.. it will consider only Key1.

In Key1, Key2 case it will consider both.

3.If we follow the second one,then it will go for a sequential read,how this sequential read mars the performance?

4.How creating an index will affect the database as BASIS guys are not in favour to creating an index.

Creating an secondary index will save the table contents in the format of starting with index fields in the DB. So number of indexes on the same table will need to craete more views in database. So leads to poor DB performance. i mean more space unnecesarily for a single table. That's why they will create indexes only for very frequently used fields on tables.

[Former Member]

The use of the key fields will help, but you still have to be careful how you construct the WHERE clause.

Rob