Browse Prior Art Database

A Method and System to Enhance Query Performance by Autonomically Creating and Deleting Database Indices at Runtime.

IP.com Disclosure Number: IPCOM000019661D
Original Publication Date: 2003-Sep-24
Included in the Prior Art Database: 2003-Sep-24
Document File: 3 page(s) / 22K

Publishing Venue

IBM

Abstract

The following is disclosed: a method and a system is described that can autonomically create and delete indices during runtime taking the query load into account. Whenever a query is issued to the database the query is parsed by the database optimizer module to determine the best access path to execute the query. In this process the query criterion or say query predicates are extracted and populated into a database system table and a reference count is maintained. There is an index scheduler that periodically scans through this table and recognizes the table column predicates in the SQL queries that are frequently used and do not participate in any index, are created as indices. Also if there are any columns that are not referenced frequently and have an index are deleted and reclaimed for space. The scheduler invocation and the determination of reference count etc. are controlled by the parameters in the database configuration file which control the overall database system behavior.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 49% of the total text.

Page 1 of 3

  A Method and System to Enhance Query Performance by Autonomically Creating and Deleting Database Indices at Runtime.

   The query issued to the database is parsed by the "Query Optimizer ". Generally, the role of a query optimizer is to analyze a query statement and choose a best access path from the query access plan and re-formulate the query to do so. When the query optimizer extracts query criterion, the following information must be stored in a database system table INDEX_COLUMNS_REFERENCES_TABLE that contains 5 columns. The 5 columns are: COLUMN_NAME contains values of the form SCHEMANAME.TABLENAME.COLUMNNAME that are extracted from the query, REFERENCE_COUNT contains the number of times the column has been used in queries, INDEX_FLAG is either '1' meaning the column is part of at least on index or '0' meaning the column is not part of an index, INDEX_SCHEDULED_FLAG when set to '1' means to either create or delete an index when scheduled based on the database configuration parameters and INDEX_NAME contains the created index name.

There are 6 database configuration parameters that control the creation and deletion of indices. They are: DYN_NDX_CR_QUERY_COUNT is an integer value that when the number of executed queries on the database exceeds this value the scanning of the INDEX_COLUMNS_REFERENCES_TABLE is started to determine what indices will be created and deleted when scheduled, DYN_NDX_CR_SCHEDULE_TIME is the time interval that is repeated every x units of time for creating and deleting of indices (E.g. every 8 hours or every Friday 10:00 PM etc), DYN_NDX_MAX_REFERENCE_COUNT is an integer value that when the REFERENCE_COUNT column for a COLUMN_NAME is greater than or equal to this value an index will be created when scheduled for this COLUMN_NAME, DYN_NDX_MIN_REFERENCE_COUNT is an integer value that when the REFERENCE_COUNT column for a COLUMN_NAME is less than or equal to this value an index will be deleted when scheduled for this COLUMN_NAME, DYN_NDX_VALID_DURATION is the duration or period of time the values in the INDEX_COLUMNS_REFERENCES_TABLE are updated and taken into account and after this period of time the values are reset to default values and DYN_INDEX_FLAG is set to true or false where true means autonomic indexing occurs and false means autonomic indexing does not occur. The default value for DYN_INDEX_FLAG is false. Figure 1 - System configuration block diagram within the database system

1

Page 2 of 3

An alternative configuration of Figure 1 would be that the "Query Optimizer" and the "Indexing Criteria Extractor" are merged into one program. It is assumed the database system maintains a data structure or stores information in the database itself about how many queries have been issued to the database. This query count parameter is used to determine when the "Index Scheduler" should be invoked based on other database configuration parameters described above.

     The typical program flow consists of a user issuing...