KR101054026B1 - Index rebuild automation system and method - Google Patents

Abstract

Reduce the time required for rebuilding by automating the index rebuild of the Oracle database using functions and programming functions of the Excel program, and by storing the indexes that do not need to be rebuilt, so as not to rebuild when rebuilding the index. An index rebuild automation system and method are provided. An index rebuild automation system includes an Oracle database having one or more table spaces; Accesses the entire index preliminary capacity in table space units by accessing the Oracle database, compares the disk usage of the index before and after the index rebuild operation based on the index rebuild operation record, and registers the index that does not reduce the disk usage after the index rebuild operation. An index rebuild system for excluding a registered index and performing an index during the index rebuild operation; And an index rebuild operation record in an Excel file format that records the increase and decrease of the index capacity and the index rebuild processing time when performing the index rebuild, wherein the index rebuild system is an EXCEL program having functions and programming functions. Access to perform index rebuild automatically.

Description

System for automatically performing index rebuild, and method for the same}

The present invention relates to index rebuild, and more particularly, to an index rebuild automation system and method for automatically rebuilding an index in an Oracle database.

In a database, indexes build up if there is a lot of data in the table with the index. In other words, when inserting or deleting data, indexes are not deleted and accumulated, but are accumulated unconditionally. Too many of these indexes can slow down search and take up a lot of disk space.

In order to solve this problem, Index Rebuild is performed. When Index Rebuild is performed, only the index that matches the current data remains and the index structure can be optimized. If you do not perform such index rebuild, there is a possibility that the speed of the database unnecessarily decreases. Therefore, index rebuilds are typically performed at intervals of one to two months.

A conventional database program has a function of rebuilding an index, which will be described with reference to FIG. 1.

1 is a flowchart of an index rebuild method according to the related art.

Referring to FIG. 1, the index rebuild method according to the related art first connects to an Oracle database (S10), and inquires a prior index capacity for each case (S20).

Next, write a script for each DB server (S30), and at this time, check whether the script is valid for each DB server (S40). Next, index rebuild is performed (S50).

Index rebuild is performed when it is necessary to free up disk space or improve search speed. However, the administrator cannot determine in advance whether the index needs to be rebuilt or if the index is not needed. However, the administrator can only determine whether or not the index needs to be rebuilt by reducing its capacity.

Therefore, there is a database in a bank that is a financial institution that records deletion rarely or rarely occurs, and even in this case, many indexes exist in one database. Since there is no prior information on whether index rebuild is necessary, rebuild must be performed for all indexes.

In other words, index rebuild is performed when it is necessary to free up disk space or improve search speed. It is usually performed using Oracle DB management software (Orange, Toad, etc.), but disk usage before and after index rebuild processing is performed. For comparison, a separate query statement must be executed after the index rebuild operation, an index rebuild script must be written for each DB server to rebuild the index, and the validity of the script for each index rebuild operation (for example, after the index rebuild operation is added / There is a problem that it is inconvenient to check whether the deleted index is applied) and waste time. In addition, indexes that do not need to be rebuilt during rework (such as indexes used for searching after a batch input) do not have any relevant information, so they have to rebuild the indexes.

SUMMARY OF THE INVENTION The present invention has been made in an effort to provide an index rebuild automation system and method for automating an index rebuild operation using functions and programming functions of an Excel program. .

Another object of the present invention is to provide an index rebuild automation system and method that can shorten the time required by separately storing an index that does not need to be rebuilt so as not to rebuild when rebuilding the index.

Another object of the present invention is to provide an index rebuild automation system and method for automating a script creation process and post-index index inquiry during an index rebuild operation.

As a means for achieving the above-described technical problem, the index rebuild automation system according to the present invention is an index rebuild system for rebuilding an index of an Oracle database, wherein at least one table space, where each table space is at least one data file. An Oracle database comprising a plurality of data files, which can reduce disk input / output (I / O) when using one or more data files; Access the Oracle database and query the entire index pre-capacity by the table space unit, and compare the disk usage of the index before and after the index rebuild operation based on the index rebuild operation record, and exclude the rebuild index that does not reduce the disk usage after the index rebuild operation. An index rebuild system that registers and excludes a rebuild during the index rebuild operation and excludes the registered index and performs the index; And an index rebuild operation record in an Excel file format that records an index capacity increase and decrease when the index rebuild is performed, and an index rebuild processing time. The index rebuild system is an Excel program having a function and a programming function. It is characterized in that the index is rebuilt automatically by accessing the Oracle database.

Here, the index rebuild system automatically performs script creation, index rebuild, and index usage of the index after the index rebuild operation based on the index rebuild work record of the Excel file format.

Here, the index rebuild system records the capacity decrease for the corresponding index by comparing the index capacity before and after the index rebuild, and if the capacity decrease is less than or equal to a predetermined range, the index rebuild registers as an unnecessary index.

Here, the index rebuild system, the index pre-capacity full query unit for accessing the Oracle database to query the dictionary capacity of the entire index in table space units; A rebuild target confirmation unit for confirming a rebuild target for each case; A rebuild exclude table lookup unit for searching a rebuild exclude target table; A rebuild object determination unit determining whether to rebuild; An index rebuild performing unit for performing an index rebuild on the target of rebuilding; Index post-capacity inquiry unit for querying the index post-capacity by case; A rebuild exclusion target determination unit determining that an index rebuild is an unnecessary rebuild exclusion target when the post-retrieved capacity reduction is less than a predetermined range based on a threshold set by a database administrator; A rebuild exclusion target registration unit for registering the rebuild exclusion target in the rebuild exclusion target table; And a rebuild exclusion target table in which the rebuild exclusion target is registered and stored.

As another means for achieving the above-described technical problem, the index rebuild method according to the present invention, in the index rebuild method for rebuilding the index of the Oracle database, a) the prior capacity of the entire index in table space units from the Oracle database Querying; b) checking the rebuild target on a case-by-case basis; c) querying the rebuild exclusion table; d) determining whether to be rebuilt; e) performing an index rebuild on the rebuild target; f) querying the index post capacity on a case-by-case basis; g) determining that the index rebuild is an unnecessary rebuild exclusion target when the post-retrieved capacity decrease is less than a predetermined range based on a threshold set by a database administrator; And h) registering the rebuild exclusion target in the rebuild exclusion target table, wherein the index rebuild is performed automatically by accessing an Oracle database with an EXCEL program having a function and a programming function. do.

Here, the step a) is characterized in that before the index rebuild operation, access to the Oracle database at the time of querying the full capacity of the index.

Here, the rebuild exclusion target of step g) is an index which only frequently searches after inputting data at a time, an index which only inputs and queries frequently without deleting data, or an index whose number is very small even if there is data deletion. It features.

In this case, when the index rebuild is registered as an unnecessary index in step g), when the index rebuild is performed, it is determined whether the index rebuild is a necessary index or an unnecessary index, and the index rebuild is performed on the corresponding index. .

According to the present invention, efficiency and convenience can be improved by automating the index rebuild operation of an Oracle database using functions and programming functions of an Excel program.

According to the present invention, it is possible to shorten the time required by separately storing the index that does not need to be rebuilt so as not to rebuild when rebuilding the index.

According to the present invention, the script creation process and the post index index inquiry are automated during the index rebuild operation, so that the query time can be saved and used as analysis information.

DETAILED DESCRIPTION Hereinafter, exemplary embodiments of the present invention will be described in detail with reference to the accompanying drawings so that those skilled in the art may easily implement the present invention. The present invention may, however, be embodied in many different forms and should not be construed as limited to the embodiments set forth herein. In the drawings, parts irrelevant to the description are omitted in order to clearly describe the present invention, and like reference numerals designate like parts throughout the specification.

Throughout the specification, when a part is "connected" to another part, this includes not only "directly connected" but also "electrically connected" with another element in between. . In addition, when a part is said to "include" a certain component, which means that it may further include other components, except to exclude other components unless otherwise stated.

First, the structure of a general Oracle database will be described with reference to FIG.

2 is a view for explaining the structure of a typical Oracle database.

Referring to FIG. 2, a general Oracle database structure is divided into a database in terms of basic information and an instance in terms of a system. Here, the Oracle database (DB) refers to the relational database management system (RDBMS) of the United States Oracle (ORACLE), the most widely used RDBMS in the Unix environment, and the standard structured query language of the International Organization for Standardization as the language for searching and updating. And PL / SQL are supported.

The Oracle database has a logical layer and a physical layer. The physical layer consists of files on disk, and the logical layer maps data to the physical layer.

Specifically, the physical layer consists of one or more data files and stores the information stored in the database. The physical layer also contains two or more Redo Log Files, which contain information used for recovery in the event of a system failure. At this time, the Redo log file stores a log of all changes made to the database and is necessary for a complete database recovery.

In addition, the physical layer has one or more control files, and includes information that is applied to instance startup, such as the location of the Redo log file. The physical layer also includes a parameter file.

On the other hand, the logical layer consists of one or more table spaces and is a database schema that consists of items such as tables, clusters, views, stored procedures, triggers, sequences, and so on.

A database is divided into one or more logical fragments called table spaces. At this time, the table space is composed of one or more data files, and when using multiple data files, disk I / O can be reduced and performance can be improved.

A database schema is also a collection of logical structured objects called schema objects that define how the data in the database is to be viewed.

A table consists of table names, rows, and columns, and is a basic logical storage unit. A cluster is also a collection of tables that are physically stored as a table that shares common columns. In addition, an index is a structure created to bring data more quickly and effectively. Also, a view is a projection of one or more tables. Stored procedures are SQL queries that are predefined in the data dictionary. A trigger is a procedure that runs automatically when an event occurs.

Meanwhile, as an embodiment of the present invention, an index rebuild automation system and method for automating an index rebuild operation of an Oracle database using functions and programming functions of an Excel program are provided.

3 is a block diagram of an index rebuild automation system of an Oracle database according to an embodiment of the present invention.

Referring to FIG. 3, an index rebuild automation system of an Oracle database according to an embodiment of the present invention includes an index rebuild system 100, an Oracle database 200, and an index rebuild work record 300, wherein the index The rebuild system 100 includes the index pre-capacity full inquiry unit 110, the rebuild target confirmation unit 120, the rebuild exclusion target table inquiry unit 130, the rebuild target determination unit 140, and the index rebuild execution unit 150. , The index post-capacity inquiry unit 160, the rebuild exclusion target determination unit 170, the rebuild exclusion target registration unit 180, and the rebuild exclusion target table 190.

The Oracle database 200 includes one or more table spaces 210, where each of the table spaces 210 includes one or more data files 220, and disk I / O when one or more data files 220 are used. Allows you to reduce I / O.

The index rebuild operation record 300 is a file that separately records processing time and index capacity increase and decrease for each index related to the index rebuild.

The index rebuild system 100 accesses the Oracle database 200 to query the entire index preliminary capacity in units of the table space 210, performs an index rebuild based on the rebuild exclusion target table 190, and indexes. After rebuilding, the index rebuild exclude target is registered in the rebuild exclude target table 190.

In addition, the index rebuild system 100 automatically accesses the Oracle database 200 using an Excel program having a function and a programming function to automatically rebuild the index.

In addition, the index rebuild system 100 automatically performs script writing, index rebuilding, and index rebuilding after the disk usage of the index and the operation time.

Here, the index rebuild system 100 records the capacity decrease for the corresponding index by comparing the index capacity before and after the index rebuild, and registers the index rebuild as an unnecessary index when the capacity decrease is less than a predetermined range.

In detail, the index pre-capacity inquiry unit 110 of the index rebuild system 100 accesses the Oracle database 200 to query the pre-use capacity of all indexes in table space 210 units.

The rebuild target confirmation unit 120 for each case checks the rebuild target for each case, and the rebuild exclusion target table inquiry unit 130 inquires the rebuild exclusion target table 210.

The rebuild target determination unit 140 determines whether the rebuild target is performed, and the index rebuild performer 150 transmits a command to the Oracle database 200 to perform the index rebuild on the rebuild target to perform the index rebuild.

Index post-capacity inquiry unit 160 for each case inquires the index post-capacity for each case, and the rebuild exclusion target determination unit 170 indexes the post-inquiry capacity reduction based on a threshold set by a database administrator, if it is less than a predetermined range. Rebuilds are considered excluded from unnecessary rebuilds. Specifically, rebuild exclusion targets that do not require index rebuilding include indexes that are frequently searched after entering data at once, indexes that are frequently entered and searched without deleting data, or indexes that have very few numbers even if data is deleted. can do.

The rebuild exclusion target registration unit 180 registers the rebuild exclusion target in the rebuild exclusion target table 190. Therefore, when index rebuilding is registered as an unnecessary index, when rebuilding indexes, it may be determined whether index rebuilding is necessary or unnecessary, and index rebuilding may be performed on a corresponding index.

As a result, the index rebuild automation system of the Oracle database according to an embodiment of the present invention automates access to multiple database servers using a single Excel file, and inquires disk usage of the index after writing a script or rebuilding an index. The time-consuming calculation process can be automated, and index rebuild time can be significantly reduced by retrieving the rebuild exclusion property to avoid unnecessary index rebuilds.

4 is an operation flowchart of an index rebuild method in an Oracle database according to an embodiment of the present invention, and FIGS. 5A and 5B are diagrams illustrating an index rebuild operation record according to an embodiment of the present invention.

Referring to FIG. 4, an index rebuilding method in an Oracle database according to an exemplary embodiment of the present invention is an index rebuilding method for rebuilding an index of an Oracle database. First, the pre-use capacity of all indexes in table space units is determined from the Oracle database. Inquiry (S110). In this case, the entire Oracle database is accessed when the entire used capacity of the index is searched before the index rebuild operation.

Next, the rebuild target is checked for each case (S120), and then, the rebuild exclude target table is retrieved (S130).

Next, it is determined whether or not the target is to be rebuilt (S140), and thereafter, index rebuilding is performed on the target to be rebuilt (S150). In this case, when the index rebuild is performed, the script creation process may be automated, thereby saving the script creation time. In this case, the index rebuild automatically accesses the Oracle database using an Excel program having a function and a programming function to automatically perform the index rebuild.

Next, the index post capacity is searched for each case (S160), and accordingly, the post index capacity inquiry is automated, and when the subsequent index rebuild operation is performed, it may be used as post index index time saving time and analysis information.

Next, based on the threshold set by the database manager, if the post-retrieved capacity decrease is less than or equal to a certain range, it is determined that index rebuild is an unnecessary rebuild exclusion target (S170). Here, the object to be rebuilt may be an index that is frequently searched after inputting data at once, an index that is only input and searched without deleting data, or an index whose number is very small even if there is data deletion.

Next, the rebuild exclusion target is registered in the rebuild exclusion target table (S180). In this case, when the index rebuild is registered as an unnecessary index, when rebuilding the index, it may be determined whether the index is required to be rebuilt or unnecessary, and the index rebuild may be performed on the corresponding index.

As a result, the index rebuild automation system and method of the Oracle database according to the embodiment of the present invention improves the work efficiency and productivity of the database manager (DBA) by automating most of manual tasks using Excel's excellent functional and programming functions. You can. In addition, it can be applied not only to financial institutions but also to companies that use Oracle DB in large quantities.

The foregoing description of the present invention is intended for illustration, and it will be understood by those skilled in the art that the present invention may be easily modified in other specific forms without changing the technical spirit or essential features of the present invention. will be. It is therefore to be understood that the above-described embodiments are illustrative in all aspects and not restrictive. For example, each component described as a single type may be implemented in a distributed manner, and similarly, components described as distributed may be implemented in a combined form.

The scope of the present invention is shown by the following claims rather than the above description, and all changes or modifications derived from the meaning and scope of the claims and their equivalents should be construed as being included in the scope of the present invention. do.

1 is a flowchart illustrating a method of rebuilding an index of an Oracle database according to the related art.

2 is a view for explaining the structure of a typical Oracle database.

3 is a block diagram of an index rebuild automation system of an Oracle database according to an embodiment of the present invention.

4 is a flowchart illustrating an index rebuilding method of an Oracle database according to an embodiment of the present invention.

<Brief Description of Drawings>

100: index rebuild system

200: Oracle database

300: index work record (Excel file)

110: index full capacity query section

120: rebuild target confirmation unit by case

130: rebuild exclusion target table lookup unit

140: rebuild target judgment unit

150: index rebuild execution unit

160: index post-capacity inquiry unit

170: Rebuild exclusion target judging unit

180: Rebuild Exclusion Register

190: Rebuild Excluded Table

210: table space

220: data file

Claims (5)

Oracle database with one or more table spaces; The index pre-capacity full query unit for querying the pre-capacity of all indexes in table space units by accessing the Oracle database; Rebuild exclusion target table query unit for inquiring whether or not the confirmed rebuild target is registered, Rebuild target determination unit for determining whether to rebuild based on whether the rebuild target is registered in the rebuild exclusion target table, The rebuild target determined An index rebuild execution unit for instructing the Oracle database to perform an index rebuild on an index, an index post capacity inquiry unit for querying index post capacity by case, and the post-captured post capacity based on a threshold set by a database administrator Rebuild exclusion target determination unit for determining that the index rebuild is an unnecessary rebuild exclusion target, and a rebuild exclusion target registration unit for registering the index determined to be the rebuild exclusion target in the rebuild exclusion target table if the preset capacity is more than a predetermined range. Index rebuild system; Index rebuild automation system comprising a. In the index rebuild method for rebuilding an index of an Oracle database, a) querying a prior capacity of all indexes in table space units from the Oracle database; b) checking the rebuild target on a case-by-case basis; c) querying whether or not the identified rebuild target is registered in the rebuild exclude target table; d) determining whether to rebuild the target or not according to whether the checked rebuild target is registered in the rebuild exclusion target table; e) performing an index rebuild on the index determined to be the target to be rebuilt; f) querying the index post capacity on a case-by-case basis; g) determining that an index rebuild is an unnecessary rebuild exclusion target if the requested post-capacity is more than a predetermined range of the pre-capacity based on a threshold set by a database administrator; And h) registering the rebuild exclusion object in the rebuild exclusion table Index rebuilding method comprising a. The method of claim 2, The index rebuild method is performed automatically by accessing the Oracle database with an Excel program having a function and a programming function. The method of claim 2, The step a) of the index rebuild method, characterized in that to access the Oracle database when the full capacity of the index query before the index rebuild operation. The method of claim 2, The rebuild exclusion target of step g) is an index that only frequently searches after inputting data at a time, an index that only inputs and queries frequently without deleting data, or an index whose number is very small even if data is deleted. How to rebuild an index.

Images