Browse Prior Art Database

Merge Enhancement depending on relationship between tables

IP.com Disclosure Number: IPCOM000244367D
Publication Date: 2015-Dec-07
Document File: 5 page(s) / 105K

Publishing Venue

The IP.com Prior Art Database

Abstract

Merge statement is important in the daily database management. Currently, the optimizer would decide the access path depending on the cost without consideration the following operation on the target table such as update, delete or insert. But usually, these statement might affect the performance much more than the join part. We would like to introduce a method to enhance the optimizer chosen. Usually, the data management statement arrive in the order of the data sequence just as the cluster index. Higher cluster ratio indicates that the physical order of MERGE-JOIN operation result is much closer with the physical order of records in MERGE-target table. When the ratio is greater than a threshold, optimizer would give a proper reduction against MERGE cost. Furthermore, optimizer could make decision that whether to add a sort plan dynamically against the MERGE-JOIN operation result to maximum the cluster ratio of RID.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 53% of the total text.

Page 01 of 5

Merge Enhancement depending on relationship between tables

Merge statement is important in the daily database management. Currently, the optimizer would decide the access path depending on the cost without consideration the following operation on the target table such as update, delete or insert. But usually, these statement might affect the performance much more than the join part.

We would like to introduce a method to enhance the optimizer chosen. Usually, the data management statement arrive in the order of the data sequence just as the cluster index. Higher cluster ratio indicates that the physicalorder of MERGE-JOIN operation result is much closer with the physical order of records in MERGE-target table. When the ratio is greater than a threshold, optimizer would give a proper reduction against MERGE cost. Furthermore, optimizer could make decision that whetherto add a sort plan dynamically against the MERGE-JOIN operation result to maximum the cluster ratio of RID.

The following is the detail information:

1



Page 02 of 5

2



Page 03 of 5


1.Optimizer decide whether the size for target table


The optimizer should estimate the size of the target table depending on the stats.


2. Result set is big


If the result set it not quite big, there is no need to check the new method to follow normal process; or else, try to apply the new method.


3. Build up the access path candidate set


The optimizer would build up the potential access path candidate set.


4. Check the order class relationship with target table


Depending on the join predicates, we could build up the relationship with source table and target table. The optimizer analyzes the ON predicate of MERGE: Determine whether the RID of MERGE-Join result is full clustered

5. Set reduction ratio for the sort access path


Depending on the relationship between source table and target table. If the result set could be fixed full clustered, we could give the suitable reduction ratio.

MERGE cost = Join cost + UDI cost * full reduction factor


6. Order access path is the lowest one

The system would check the candidate result set, the ordered result set is the low one, we could choose current access path; or else we might try to add sort to the result set.


7. Choose current access path


The ordered result set is the low one, we could choose this access path.


8. Apply the chosen access path


The system should choose the access path with lowest cost one.


9. Compare the lowest path with sort


If the result set is not in the order, the optimi...