Indexing on Large table

10 pts.
Tags:
Database
DB2
Oracle
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
ASKED: November 23, 2005  8:08 AM
UPDATED: November 23, 2005  4:37 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Sequential
    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.
    0 pointsBadges:
    report
  • Sequential
    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.
    0 pointsBadges:
    report
  • Craigmullins
    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
    0 pointsBadges:
    report
  • Abhigreen
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/indexing-on-large-table/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following