For example I have a table and I created an index on ID column which generates numbers every time I insert a row. If I only insert 5 rows does index tree start to be created or is there a threshold of pages or rows that should be reached before the index tree is built? (i am using db2 10.5)

closed as unclear what you're asking by Akina, John Eisbrener, mustaccio, LowlyDBA, Gaius Mar 7 at 9:54

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    May I ask why you are wondering? Have you observed something that indicates that an index is not built until a certain number of rows is inserted? – Lennart Mar 5 at 11:17

You don't say what DBMS you're using, so it is possible yours does not. The majors, however, will maintain the table and indexes as completely consistent at all times. As the very first row is written the storage engine will (by-and-large) amend all indexes on that table to match. When the second row is written those indexes will be amended once again. If many rows are written in a single INSERT statement they are processed internally within the DBMS one at a time and the indexes amended one value at a time.

Indeed, in the B+ Tree structure (as used, for example, by SQL Server for a table's clustering index) the leaf level of the tree is the data row. With B+ Trees it is logically impossible for the "index" and the "data" to be inconsistent.

Now, this is a very broad-brush answer to a very general question. What happens within the bowels of any one specific DBMS is nobody's business but its developers'. There are many algorithmic optimizations and run-time considerations which may mean some of these actions may happen out of sequence within the server code. From our point of view as clients of the database service, however, what I have said is true.

There are some structures in some DBMS which are not amended as data arrives. Materialized views in Oracle is one example. These must be refreshed explicitly for changed data to show up. This is explained it that product's documentation. These are different from the plain BTree on plain table to which I'm referring.

A separate concern is whether the optimizer chooses to use the index if only very few rows exist. It may be more work to walk the index then use the pointer to find the row(s) than it would be to step through all the rows of the base table to find the required values. It is the optimizer's role to make these cost-based decisions.

Not the answer you're looking for? Browse other questions tagged or ask your own question.