hi

This w.r.t type 1 indexes as its outdated, as per doc the leaf pages are broken down to number of subpages and this subpages have index entry, and type 2 indexes don't have subpages, please share how this works and how this is better as compare to type 2 indexes as they don't have subpages,

Please share ur valuable suggestion, as doc don't say much abt this.

regds
Paul

on this page
Index structure
you have an overview...

The main benefit of Type 2 indexes is reducing locking problems in the index.

thks guy and marcus reply

But how this works like type 2 indexes is reducing locking compare to type 1 after elimination of subpages, how this work, could pls give a example, or any more light how this work's

redgs
Paul

as indicated before :
have you looked at the page, pointed by the link ?
have you read the doc - infocenter - pdf files (performance tuning...reference...)
this is not an sql that you can show a example for.. it is a design-architecture... and explained - maybe not in detail..as this is not always relevant to a user..

I don't know the details myself, but one explanation could be this: Physically removing an entry from the index requires some sort of mechanism to close the introduced gap. Closing this gap means you'll have to lock/protect larger parts of the index page. But if you keep the entry where it is and just set a bit/byte to mark the entry as already deleted, you don't have to worry about the mentioned reorganization. Hence, you'll need less locking at that point. Eventually, you'll have to clean up the page at some point in time, but that could be done when multiple deleted index entries are detected and handled together.

Since type 1 indexes are no longer supported in the latest release, and even since 8.1 were automatically converted to type 2 when a reorg was done, I can't imagine why you even care. It is complete waste of time discussing it.