Indexing on Large table
Hi,
Is indexing on 5 keys of a table containing 100 fields more slower than indexing on same 5 keys of a table containing 150 fields.
Both the tables (one containing 150 fields and other containing 100 fields) are similar except the 50 attributes which are redundant.
Suppose there are 2 attributes common to both the tables.
Will a query fetching the 2 attributes run faster on 100 field table compared to that on 150 field table?
Can you please give the reasons?
Thanks
Abhi
Software/Hardware used:
Software/Hardware used:
ASKED:
November 23, 2005 8:08 AM
UPDATED:
November 23, 2005 4:37 PM
Answer Wiki:
Everything else being equal, the response times for querying these two tables individually should be nearly identical. The difference would only be whatever time it takes to package the additional 50 fields for routing. That will take some time, probably uncalculable per row; But if you are returning 1,000,000 or so rows, that might be significant. For similar queries on these two tables, the execution plans should be indentical. If the queries are different, or if one table has indexes the other does not, all bets are off.
Now if you need to join these two tables, you could create a CLUSTER based on the 2 common columns. That way, the data from the two tables would be physically stored together in the database files, which would drastically reduce the physical read times for such queries. This adds a small overhead for DML on these tables, but this is much more than made up for on queries.
To see all answers submitted to the Answer Wiki: View Answer History.
Once you access a table via an index you are using a rowid. So the access to the individual rows will take pretty much the same amount of resources. But the returned amount of information maybe the only difference between the tables, with the extra columns you may see an increase in time to return rows even with no data in them. Row headers and what not cold impact this if the row count returned is large enough.
Once you access a table via an index you are using a rowid. So the access to the individual rows will take pretty much the same amount of resources. But the returned amount of information maybe the only difference between the tables, with the extra columns you may see an increase in time to return rows even with no data in them. Row headers and what not cold impact this if the row count returned is large enough.
You also need to factor in the I/O requests that are required to return your data. The DBMS will perform I/O at the block (or page) level – this is so whether you return one row or millions of rows. For multi-row results, accessing data from the table with the wider row (more columns) will usually perform worse. This is so because fewer rows will exist on each page (the row with 100 columns is smaller than the row with 150 columns so more rows can reside in a single, pre-sized block/page). The bigger the result set, the more pronounced the performance degradation can be (because more physical I/Os are required to retrieve the data).
Think about it this way. Is it faster to pull smaller peaches out of a basket than bigger peaches? Say you want 100 peaches. Small peaches fit 25 per basket; big peaches fit ten per basket. To get 100 small peaches you’d need to pull 4 baskets from the shelf. To get 100 big peaches you’d need to pull 10 baskets from the shelf. The second task will clearly take more time.
Good luck,
Craig S. Mullins
Director, Product Strategy
Embarcadero Technologies
http://www.embarcadero.com