Select statement performance enhancement

[Former Member]

Hi Experts

Appreciate your comments/answers for my question-

Suppose a table has 11 key fields, and I extract data from it using a select statement and in where condition I use 3 fields (all are key fields in source table)

Now ,

Q:---If I somehow manage to include all 11 key fields of the source table in the where condition (the number of records selected remaining same),           will it give me any performance improvement for the select statement.

If yes, why/how?

If not, why not?

Thanks

Sumanto

[Former Member]

Hi,

the select without the complete primary key will be  slower.

That's because the DB has to do at least "Index Range Scan" and not a "Index Unique Scan".

So it has to do at least 2 read actions.

If the used Select-Fields are suboptimal (not the first fields of the index) your DB may also change to coffee-time-modus and do a "Full Table Scan".

It´s not relevant if it´s an primary key or a secondary index. Only that the primary key ensures (normaly) uniqueness of the datasets. For performance both will work fine.

An example with two key fields

Field1 / Field2

A / 1

A / 2

B / 1

C / 1

If you fire a SELECT with Field1 = B and Field2 = 1, the DB can use its Index completely and will find the correct entry with one hit. AND because you used the complete key, the DB knows that there won´t be any more entries (in the case that the db-table is unique).

If you fire a SELECT with FIELD1 = B, the DB can jump directly to the first correct entry (B / 1) but because the key is not complete it has to do check if there are any more entries with B / ???.

And if you want to use only parts of the index, make sure that you use at least the FIRST entries. If not the index is useless and the DB may change to the lovely "Full table scan".

If the table won´t be changed to much, it may be wise to think about a secondary index.

I work primary with an Oracle DB, so it may be that some DB´s behave differently.

Warm regards

Jan

[Former Member]

Thanks Jan