Q
Problem solve Get help with specific problems with your technologies, process and projects.

Speed of indexing on tables with different numbers of fields

Is indexing on five keys of a table containing 100 fields slower than indexing on the same five keys of a table containing 150 fields? Both the tables (one containing 150 fields and the other containing 100 fields) are similar except for the 50 attributes which are redundant.

Is indexing on five keys of a table containing 100 fields slower than indexing on the same five keys of a table containing 150 fields? Both the tables (one containing 150 fields and the other containing 100 fields) are similar except for the 50 attributes which are redundant.

Suppose there are two attributes common to both the tables. Will a query fetching the two attributes run faster on the 100-field table compared to that on the 150-field table? Can you please give the reasons?

The indexes do not know of the total number of columns in a table. So all things being equal except for the number of columns in a table, using an index will be the same for both tables. However, more columns can lead to longer row lengths requiring more blocks of data to store the same number of rows. So retrieving more blocks of data can take longer.

If you query just the attributes in the index, then Oracle will never read the table's contents since it has everything it needs in the index. This means that the total number of columns in the table will not affect the query performance in this example.

That being said, there are many factors that can impact query performance. So your mileage may vary. The above are rules of thumb and there are exceptions to those rules which may give you different results.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close