DB2 - Index storage structure




DB2 Index storage structure

Similarly like tables, there is another component that needs to be discussed is index. INDEX is the link to access the data from the database very quickly. this index will decide the rows storing place based on the index type so that the data can be easily accessed without any delay.

CREATE INDEX used to create the INDEX. SYSADM or SYSCTRL authority is required to create the index. INDEX is an ordered set of pointers to the rows of table data. DB2 uses indexes to uniqueness and improve the performance. An index is stored separately from the data in table. Index’s are physically stored in its index space.

The hierarchy of Index can be drawn like below.

Storage Group
	   |
	   |
	   \/
	Database
	   |
	   |
	   \/
	Index Space
	   |
	   |
	   \/
	Index

What is DB2 Index storage structure?

Don’t call them indices in the United States – most American DBAs agree that they are ‘indexes’, though either plural form is grammatically acceptable in American English. Indexes are arguably one of the most important aspects of database design. They’re also something that we can pretty easily change and add to over time. Even overly restrictive vendors tend to allow new non-unique indexes to be added to their vended DB2 databases.

An index is a data structure that exists physically on disk. It consists of one or more columns in order and pointers to rows in the table. It is by definition a partial duplication of the data, but is tied to the data by the DBMS so that an index cannot be different than the data in the table.

Index Creation Options -

Unique Indexes

Unique indexes enforce the uniqueness of a single column or group of columns. In DB2 there is no concept of a unique constraint outside of a unique index. Primary keys are created via CREATE TABLE or ALTER TABLE statements, and may use either a previously existing unique index or create their own unique index.

Clustered Indexes

Clustered indexes are indexes where the actual data in the table is placed at least roughly in order of the index. If a clustered index exists on a table, DB2 will attempt to insert data in the order of the clustering index. Order is not guaranteed, and may degrade over time depending on insert patterns, data volume, and settings controlling the structure of data pages. Tables cannot be REORGed on an index other than the clustering index. Using the CLUSTER option on the create index statement creates a clustering index. Only one clustering index may exist on a table at a time. Clustered indexes are a wholly different concept from tables defined with Multi-Dimensional Clustering(MDC).

Direction of Index Scan

By specifying DISALLOW REVERSE SCANS, index scans can only occur in the order in which the index was defined and not in reverse order. I cannot think of a technical reason you would want to do this – it used to be the default behavior, so I’m in the habit of always specifying ALLOW REVERSE SCANS, though it is not technically required since it is the default.

Partitioned Indexes

When using table partitioning, we can choose to have indexes partitioned along with your data or to have them non-partitioned, at least in DB2 9.7.

DATABASE

A Database is the physical object and it contains the memory area. database name is nothing but the logical name to database where the tables are going to store. database is a set of structures, and structure can contains tables, table spaces and indexes etc, DB2 can control the data by using Database. once the table created in the database, the table will be referred and can be accessed by the Database name.

INDEX SPACE

It is like similar to the Table Space. the Index space was not required to create it separately. whenever the indexes are created the index space for the same will be created automatically. Index space is used store the indexes for the table space. index space can contain more than one index. index space is similar to table space but only contains indexes in it. when index created, index space will be created automatically.