KR101152852B1 - The database management system using a snap entity model - Google Patents

Abstract

  The present invention relates to a database management system and a database management method using a snap object model. More specifically, database management system and database management using a snap object model consisting of a code contrast section that stores data that can easily identify the upper and lower structures only by a new sequential column (eg p_sq), and a self-circulation relationship section that stores identifiers of upper data information. It is about a method.

 The present invention relates to a database management system using a snap object model as a first aspect, comprising: a code comparison unit for storing data easily identifying upper and lower structures only by a new sequential column (for example, p_sq) and a identifier for storing identifiers of upper data information. A method for managing a database using a snap object model, comprising a snap object database composed of a snap object model composed of a circular relation part, is presented.

Snap Object Model, Code Contrast, Circular Relations, Self-Circulating Entity

Description

The database management system using a snap entity model}

The present invention relates to a database management system and a database management method using a snap object model. More specifically, database management system and database management using a snap object model consisting of a code contrast section that stores data that can easily identify the upper and lower structures only by a new sequential column (eg p_sq), and a self-circulation relationship section that stores identifiers of upper data information. It is about a method.

Currently, the recursive structure including department management technique that applied the cyclic relationship when constructing IT-based system and the management method of bill of material (BOM) structure in manufacturing industry has evolved from the code preparation method to the self recursive structure. . However, the new self-circulating structure technique is a management technique that compensates for many of the problems produced in the past, but it generates a lot of performance loads.

Also, if the developers who implement the system do not have sufficient ability to use SQL, the reality is that they will produce wrong results. In fact, there are a lot of intermediate people do not know. So, with the desire to escape from this environment, we implemented a system to manage data using the Snap Entity Model. The Snap Entity Model is a way to solve this problem by combining the advantages of code contrast and self-circulation.

The code preparation method is a technique used in department management in the past, and has inherited the concept of department code (eg, ID). For example, when a department was moved, the department had to be assigned a new department code, so the department code had to be changed and all the departments below it had to be reorganized into a new department code system. Therefore, the reality was that all database department codes using departments had to be changed to new department codes when departments were reorganized.

The so-called relationship between two commonly used entities (1: M) was solved as an entity by having inheritance relations between IDs. The advantage is that the execution speed is fast and the structure is easy to identify with only the identifier. It is also quite simple when developing a system. Disadvantages incur significant risks due to the change of identifiers and sub-identifiers when restructuring, and the identifiers of all past entities must be converted to changed identifiers. 1 is a construction example using a code comparison method.

The self recursive structure is a theory that solves the shortcomings of the code preparation method and manages the upper and lower departments in one record so that only the upper department is changed when the department is reorganized. 2 is an example of construction using a self recursive structure. This structure eliminates the shortcomings of the code comparison method. However, due to the inherent nature of this structure, it has produced a large number of performance loads with internally continuous joins.

And to use this structure in SQL, a new SQL called CONNECT BY was born, which soon required a great deal of SQL skills for developers. In reality, due to the problem of the ability to use SQL, it was inevitable to produce unintended results in the system and to extend the development period. Due to these problems, it is a practical concern for all companies to have significant problems in system development. Advantageously, even if the structure is changed, the identifier does not change, and the application of change data is simple, and the flexibility of the data makes it easy to manage. Disadvantages are that the internal mechanism is carried out mainly on joins, so that the speed is slowed down, considerable technical skills are required in development, and it is inconvenient to use, resulting in unintended consequences. And most of these entities were the ancestors and were the main culprit of mass production because they were based on all entities. So it's more expensive to tune in.

In order to solve the problems of the prior art as described above, an object of the present invention is to provide a database management system using a snap object model composed of a code preparation unit and a self-circulation relationship unit and a database management method using a snap object model.

As a technical idea for achieving the above object, In the database management system using a snap object model as a first aspect, Code comparison unit for storing data that is easy to grasp the structure of the upper and lower structure only by a new sequential column; A database management system using a snap object model is provided that includes a snap object database composed of a snap object model composed of a self-circulation relationship unit storing identifiers of higher data information.

In the first aspect,

The snap object database further includes a self-circulating entity for storing basic data while having a column for storing identifiers of higher data information, and the data is also generated in the snap object model when a change is made to the basic data. Features,

In the first aspect,

The snap object model is characterized in that the new sequential column in the order of displaying the top and bottom,

In the first aspect,

The snap object model is characterized by using a hint using a new sequential column when sorting to sort the data,

In the first aspect,

The snap object model is characterized in that the upper and lower data is generated in the order of reorganization date of the upper and lower data.

In a second aspect, there is provided a database management method using a snap object model, comprising the steps of: generating data in a self-circulating entity; A data moving means includes two steps of generating data in the snap object model based on the data generated in the self-circulating entity, wherein the snap object model stores data in which the upper and lower structures can be easily identified only by a new sequential column; A database management method using a snap object model is provided, which comprises a contrasting unit and a self-circulating relationship unit storing identifiers of upper data information.

In the second aspect,

The snap object model is characterized in that the upper and lower data is generated in the order of reorganization date of the upper and lower data.

Due to the highest efficiency of the Hit Ratio due to the application of the clustering factor, the performance of all SQLs is increased, and the convenience of development is possible due to the non-use of the CONNECT BY statement. have.

In addition, it is possible to quickly create a snap entity, join with other entities without an inline view, and reduce development time. Since the new sequential column (eg p_sq) expands the organization chart and all organizations are created by reorganization date, various queries are possible, many indexes required for modeling are unnecessary, and sorting load is reduced.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Hereinafter, configurations and operations of embodiments of the present invention will be described in detail with reference to the accompanying drawings.

The database management technique using the snap entity model is a model designed to solve many of the shortcomings of the current management technique. It uses both the code contrast method and the SELF RECURSIVE technique to take advantage of the two advantages simultaneously. As a structure to compensate for the shortcomings, the essence is a self-recursive entity, and the entity that exists as a snap is a snap entity model using code contrast.

 The actual basic data is managed by a self recursive entity. At the same time, the data of the snap entity model is newly generated by a stored procedure or trigger when the basic data is changed.

In other words, the concept of history is applied (it is also possible for a snap model to include all basic data).

Therefore, all system developers, modelers, and project managers use SQL in the snap entity model as if they were actually using simple entities. Approach it and use it. This shortens the effect of the connect by SQL, which eliminates internal joins, which greatly improves performance and allows developers to easily use SQL. Where connect by is used only at the moment of converting a self recursive entity into a snap entity (eg reorganization).

Another advantage is that the Snap Entity model has a pretty good Hit Ratio. This is distinguished from the entity using the conventional code comparison method, and has similar performance to the cluster (usually more than 10 times). This is because the snap entity model creates new data at the same instant (date) with the current valid data as soon as the self recursive entity changes. Since they all exist in consecutive spaces, the effect is to increase performance even further.

And since all of these entities have the characteristics of eponymous entities, they have a significant ripple effect on many entities such as main entities, active entities, and generate processed information of these entities. This makes SQL easier to use. If there is an intrinsic change in the data, it is certain that it is one of the most recent data, so in such a case, delete the data afterwards and regenerate it. This results in a fairly fast Insert performance.

only. Note that when creating a new sequential column (e.g. p_sq) in consideration of scalability, it should be generated based on the type code + sequential (sq) or sequential (sq) in consideration of scalability. Division, BB: Headquarters…) It is reasonable to expand the character spacing between levels (LEVLE) in consideration of the scalability by using two or more character prefixes. For example, if level 1 consists of AA001, AA010, AA020 .., and level 2 consists of BB001, BB010, BB020, etc., there will be no problem in scalability, stability, and reusability in the future.

3 is an example of a database using a snap entity model. A database management system using a snap entity model uses a snap object model that consists of a code counterpart (eg p_sq) and a self-circulating relationship part. The code contrast unit stores data that is easy to grasp upper and lower structures only with a new sequential column (for example, p_sq). The self-circulating relationship unit is a concept including higher data information. Here, p_sq corresponds to the code comparison unit, and ID (department code) and P_ID (upper department code) correspond to the self-circulation relationship part.

4 illustrates an example in which the hit ratio and the clustering factor are maximized when the snap object model is used. The performance is improved by increasing the Hit Ratio, which is the ratio of accessing data to the cache memory. Since the amount of data is also constant, it can be converted into a cluster.

5 is an example in which the system performance is maximized by using various processing columns.

An example will be described to describe a database management system using a Snap Entity Model. Department reorganization refers to the case where a series of cases occur by division creation, department movement, and department closure by type. For example, as of September 10, 2008, a company named XXX has a department named b as its parent and a department c as its lower. When a department moves to a subordinate department of d and the organization is reorganized, only the currently reorganized organization updates or inserts the parent organization to manage the data. Once the behavior of the reorganization is reflected in the entity, all the organizations at the time of the reorganization with the reorganization date are connected in sequence using the connect by statement, and all aspects at the time of reorganization correspond to the new entity. As a concept used in the code contrast method, new sequential data is created in the code contrast part (eg p_sq) and stored in a snap entity model. Therefore, the example of using connect by statement is only used to extract the snap entity model when reorganizing. Therefore, when joining or supplier subqueries or resolver subqueries related to all organizations, all SQLs use the simple entity model for the snap entity model and maximize I / O efficiency and performance.

For example, when the organization chart is drawn later, it is difficult to expand the exact organization chart, the SQL is difficult, and the clear difference with the SQL used in the snap entity model can be seen at a glance.

The bill of material (BOM) structure used by manufacturers will further require this management technique. The advantage of the Snap Entity model is that it has a clustering factor, but it is also possible to designate a cluster from the beginning. And since the update will rarely happen and all will be Insert, it will continue to maintain the Clustering Factor even without using Cluster.

If you have to revise the reorganization information by mistake of your recent data, you can use the Snap Entity model to delete and insert. This is done using the SYS_PATH function, which retrieves the hierarchy information provided by Oracle9i. And the reorganization of departmental information in the past requires the same attention in the existing self-circulation structure.

After that, let's take a closer look at SQL that creates Snap Entity model for department reorganization. Below is the SQL to create the Snap Entity model and the SQL applied to the BOM structure.

only . Since retrieval date from the snap model is one thing, we use a function. The function to be used is, for example, F_ID_YMD (XX).

-insert into snap_entity

SELECT uid (pk), replace (Sys_connect_by_path (a.sq, '-'), '-', '') p_sq, a. *

FROM department table a

where: v3 between a.sta_ymd and a.end_ymd

and a.Company Code =: v1

CONNECT BY PRIOR a.org_cd = a.super_org_cd

and: v3 between a.sta_ymd and a.end_ymd

and a.Company code =: v1

START WITH a.parent department code =: v2

and: v3 between a.sta_ymd and a.end_ymd

and a.Company code =: v1

Application: BOM Structure

insert into snap_entity

SELECT uid (pk), replace (Sys_connect_by_path (a.sq, '-'), '-', '') p_sq, replace (Sys_connect_by_path (a.quantity, '-'), '-', '') qty, a. *

FROM Material Table a

where… ..

Next, let's take a look at the specific application of SQL using the self-circulating structure and the Snap Entity Model model. The following shows query SQL using self-circulating structure and Query SQL using Snap Entity Model model.

-Query SQL by Self-Circulation Structure

select level lvl, a. *

from Department table a

where: v3 (view date) between a.revision start date and a.revision end date

and a.Company Code =: v2 (Company Code)

connect by prior a.Department code = a.Top department code

and: v3 (view date) between a.revision start date and a.revision end date

and a.Company Code =: v2 (Company Code)

start with parent code =: v1 (parent code)

and: v3 (view date) between a.revision start date and a.revision end date

and a.Company Code =: v2 (Company Code)

-Query SQL using Snap Entity Model

select / * + index_asc (a idx_psq) * /

       a. *

from snap_entity a

where a.Revision date = F_ID_YMD (XX)

and a.p_sq> ''

and a.Company Code =: v2 (Company Code)

Comparing the above two queries, the self-circulating lookup SQL (first query) generates a lot of self joins internally, and the more data, the worse the clustering factor, and the speed decreases exponentially. That is, the data loading speed slows down due to the movement of many disk arms. It is also very difficult to display the sequence by the same level. However, the query SQL (second query) using the Snap Entity Model ensures that the subquery reads just one index directly and that speed is guaranteed due to the good disk clustering factor, which solves this problem at once.

Shown below is the SQL to find the name of the parent organization by the self-circulation structure and the SQL to find the name of the parent organization using the Snap Entity Model.

-SQL to find parent organization name by self-circulation structure

select a.parent signature

from Department table a

where a.Department code = (select b.Top department code

                     from department table b

                     where b.Department code =: v1

                and: v3 (view date) between b.restart date and b.rename end date

                     and b.Company Code =: v2 (Company Code))

and: v3 (view date) between a.revision start date and a.revision end date

and a.Company Code =: v2 (Company Code)

SQL to find parent organization name using Snap Entity Model

select / * + index_desc (a idx_psq) * /

         a.Top Signature

from snap_entity a

where a.p_sq <: v4 (the value of the new sequential column from the screen)

and a.Company Code =: v2 (Company Code)

and a.Revision date = F_ID_YMD (XX)

and a.rownum = 1

The following shows when to find all of the parent departments. Below are other examples of SQL searching for the parent organization name using self-circulating structure and SQL for finding the parent organization name using the Snap Entity Model model.

-SQL to find parent organization name by self-circulation structure

select a. *

from Department table a

where: v3 (view date) between a.revision start date and a.revision end date

and a.Company Code =: v2 (Company Code)

connect by prior a.Department Code = a.Department Code

and: v3 (view date) between a.revision start date and a.revision end date

and a.Company Code =: v2 (Company Code)

start with department code =: v1 (department code)

and: v3 (view date) between a.revision start date and a.revision end date

and a.Company Code =: v2 (Company Code)

-SQL to find parent organization name using Snap Entity Model model

select a. *

from snap_entity a

where a.p_sq between: v5 (Top-sequential column)

          and: v6 (new sequential column corresponding to that department code)

and a.Company Code =: v2 (Company Code)

and a.Revision date = F_ID_YMD (XX)

Below are the SQL searching for the subordinate departments using self-circulating structure and the SQL searching for the subordinate department using the Snap Entity Model model.

-SQL searching for subdepartment by self-circulation structure

select a. *

from Department table a

where a.company code =: v2 (company code)

and: v3 (view date) between a.revision start date and a.revision end date

connect by prior a.Department code = a.Top department code

and a.Company Code =: v2 (Company Code)

and: v3 (view date) between a.revision start date and a.revision end date

start with department code =: v1 (department code)

and a.Company Code =: v2 (Company Code)

and: v3 (view date) between a.revision start date and a.revision end date

SQL finding subdepartments using the Snap Entity Model model

select a. *

from snap_entity a

where a.p_sq like: v5 (new sequential column for corresponding department code) || ‘%’

and a.Company Code =: v2 (Company Code)

and a.Revision date = F_ID_YMD (XX)

FIG. 6 compares the data reading order and the SQL statement when using a system for managing a database using the existing self-circulating structure and the present invention's Snap Entity model. In the self-circulating structure, data is not arranged in a line on the disk. Therefore, data must be read while moving the disk. It is arranged so that data can be read without moving the disk.

FIG. 7 illustrates the SQL statement when a subgroup is not expanded and a specific row is not extracted when using a system for managing a database using an existing self-circulation structure and the present invention, the Snap Entity model. Are comparing. The SQL statement of the system that manages the database using the present Snap Entity model does not use the connect by statement, which is superior in performance.

FIG. 8 is an example of application to the present invention when applying the Snap Entity model to a Bill of Material (BOM) structure used by a manufacturer.

In the current Bill of Material structure

FOR m IN (select id, level lvl, qty

                from bom

                where ....

                connect by prior id = p_id

                and ....

                start with p_id = *

                and ....)

               ) LOOP

If the SQL in the FOR statement uses connect by, but the snap object model is applied to it, the SQL in the FOR statement below does not use connect by, which is excellent in terms of performance.

FOR m IN (select id, level lvl, qty

                from bom

                where p_sq like 'AA001%'

                and ...)

               ) LOOP

Figure 9 shows an example of the project management when using the system for managing a database using the present invention Snap Entity (Snap Entity) model. As a conclusion based on FIG. 8, when comparing the SQL application examples, developers have no reason to consider SQL adequacy while simplifying SQL, and only the accuracy of the data is considered. This is a very convenient development environment. If so, it will be clear which way to proceed. Applying this Snap Entity model to the BOM structure will provide a very convenient basis for quantity calculations.

Although the embodiments of the present invention have been described above with reference to the accompanying drawings, those skilled in the art to which the present invention pertains may be embodied in other specific forms without changing the technical spirit or essential features of the present invention. You will understand that. The embodiments described above are therefore to be considered in all respects as illustrative and not restrictive.

1 is a construction example using a code comparison method.

2 is an example of construction using a self recursive structure.

3 is an example of a database using a snap entity model.

4 illustrates an example in which the hit ratio and the clustering factor are maximized when the snap object model is used.

5 is an example in which the system performance is maximized by using various processing columns.

FIG. 6 compares the data reading order and the SQL statement when using a system for managing a database using the existing self-circulating structure and the present invention's Snap Entity model.

FIG. 7 illustrates the SQL statement when a subgroup is not expanded and a specific row is not extracted when using a system for managing a database using an existing self-circulating structure and the present invention, the Snap Entity model. Are comparing.

FIG. 8 is an example of application to the present invention when applying the Snap Entity model to a Bill of Material (BOM) structure used by a manufacturer.

Figure 9 shows an example of the project management when using the system for managing a database using the present invention Snap Entity (Snap Entity) model.

<Description of Major Symbols in Drawing>

Claims (7)

In the database management system using the snap object model, A code preparation unit for generating and storing a new sequential column p_sq based on the type code and the sequence sq or the sequence sq; A database management system using a snap object model, characterized in that it comprises a snap object database consisting of a snap object model consisting of a self-circulation relationship unit for storing identifiers of upper data information. The method according to claim 1, The snap object database, It further comprises a self-circulating entity for storing the basic data while having a column for storing the identifier of the upper data information, And a data is also generated in the snap object model as soon as a change is made to the basic data. The method according to claim 1 or 2, The snap object model, A database management system using a snap object model, characterized in that the new sequential column is in the order of displaying the top and bottom. The method according to claim 1 or 2, The snap object model, A database management system using a snap object model that uses hints using new sequential columns when sorting to sort data. The method according to claim 1 or 2, The snap object model, A database management system using a snap object model, characterized in that the upper and lower data is generated in the order of reorganization date of the upper and lower data. In the database management method using the snap object model, Generating data in the self-circulating entity; And a data moving means for generating data in the snap object model based on the data generated in the self-circulating entity. The snap object model includes a code comparison unit for generating and storing a new sequential column (p_sq) based on a type code and a sequence (sq) or a sequence (sq) and a self-circulation relation unit for storing identifiers of upper data information. Database management method using a snap object model characterized in that the configuration. The method of claim 6, The snap object model, Database management method using a snap object model, characterized in that the upper and lower data is generated in the order of reorganization date of the upper and lower data.

Images