Skip to Content
author's profile photo Former Member
Former Member

Indexes

Why indexes is not possible in pooled and clustered tables?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on May 20, 2008 at 11:54 AM

    Hi,

    U can't create secondary INDEX on pooled and cluster table.

    If u open the table in se11 in change mode the push button Indexs will be disabled. So u can't do that.

    This is because for many tables in Data dictionary there will be only one table in data base for pooled or cluster tables called as table pool/Table cluster. Secondary index will be based on fields specified in Index for one table. So if u create Index for one pooled/cluster table the same fields may or may not be there in other tables in table pool/Cluster. So there will be inconsistency occurs for the data base optimizer when u write a select query. That is why it is not possible to create secondary Index for pooled and cluster table.

    In case of Transaparent tables it will be one to one relationship i.e One table in DD and one table in Data base. Also field names and table name in DD and data base is same in case of transaparent table but is different in case of pooled and cluster tbale..

    Reward If Helpfull,

    Naresh.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 20, 2008 at 12:07 PM

    Hi

    Understanding clustering index behavior on partitioned tables

    Clustering indexes offer the same benefits for partitioned tables as they do for regular tables. However, care must be taken in choosing a clustering index with regards to the table partitioning key definitions.

    You can create clustering indexes on a partitioned table using any clustering key. The database server attempts to use the clustering index to cluster data locally within each data partition. During a clustered insert, a lookup is done in the index to look for a suitable row identifier (RID). This RID is used as a starting point when looking for space in the table to insert the record. To achieve well-maintained clustering with good performance, there should be a correlation between the index columns and the table partitioning key columns. One way to ensure such correlation is to prefix the index columns by the table partitioning key columns, as shown in the following example :

    PARTITION BY RANGE (Month, Region)

    CREATE INDEX ...(Month, Region, Department) CLUSTER

    Although the database server does not enforce this correlation, there is an expectation that all keys in the index are grouped together by partition IDs to achieve good clustering. For example, if a table is partitioned on quarter, and a clustering index is defined on date, because the relation between quarter and date exists, optimal clustering of the data with good performance can be achieved because all keys of any data partition are grouped together within the index.

    Figure 103. The possible effects of a clustered index on a partitioned table. In the first figure, data is both globally and locally clustered.

    As Figure 103 shows, given the layout of the index and data in each example, optimal scan performance is achieved when the clustering correlates to the table partitioning key. When clustering is not correlated to the table partitioning key it is unlikely that the index will be locally clustered. Because a correlation between the table partitioning columns and index columns is expected, a perfect locally clustered scenerio is highly unlikely.

    Benefits of clustering include:

    Within each data partition, rows are in key order.

    Clustering indexes improve the performance of scans that traverse the table in the order of the keys. This is because the scan fetches the first row of the first page, then each row in that same page until it has fetched all of the rows for that page and moves on to the next. This means that only one page of the table needs to be in the buffer pool at any given time. In contrast, if the table is not clustered, then each row fetched is likely to be from a different page. Unless there is room in the buffer pool to hold the entire table, this will result in each page being fetched more than once, greatly slowing the scan.

    For partitioned tables, the ideal case of fetching each page only once during the scan can be guaranteed only if the table partitioning key is a prefix of the clustering key (see first figure in Figure 103). However, if the clustering key is not correlated to the table partitioning key as described previously, and the data is locally clustered, you can still achieve the full benefit of the clustered index if there is enough space to hold one page of each data partition in the buffer pool. This is because each row fetched for a given data partition is near the previous row fetched for that same data partition. (see the second figure of Figure 103). As previously mentioned, the clustering may not be well maintained in the case where the clustering key is unrelated to the table partitioning key, but if you do not expect a high level of insert, update and delete activity on your table this approach should be beneficial.

    Even if there is not sufficient space for a page of every data partition to be held in the buffer pool, there is still some benefit to be gained from defining a clustered index.

    publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/c0021580.htm

    Regards

    Lakshman

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.