Browse Prior Art Database

Optimize for SELECT FROM OLD/FINAL TABLE statement

IP.com Disclosure Number: IPCOM000244368D
Publication Date: 2015-Dec-07
Document File: 4 page(s) / 97K

Publishing Venue

The IP.com Prior Art Database

Abstract

SQL Statement ‘SELECT FROM OLD/FINAL TABLE’ is used to update data in specified target table, and then return those rows before/after UPDATE modification to application. But the performance may not be good sometimes, since all rows that modified before/after UPDATE modification are saved into a temporary workfile for fetch operation later. It will be costly to insert or retrieve data from a temporary workfile when the amount of data is tremendous. In this disclosure, I’ll introduce a method to create or avoid building workfile dynamically to enhance the performance of ‘SELECT FROM OLD/FINAL TABLE’.

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

Page 01 of 4

Optimize for SELECT FROM OLD

Optimize for SELECT FROM OLD/

SQL Statement 'SELECT FROM OLD/FINAL TABLE (UPDATE …)' is used to update data in specified target table, and then return those rows before/after UPDATE modification to application. But the performance may not be good sometimes, since all rows that modified before/after UPDATE modification are saved into a temporary workfile for fetch operation later. It will be costly to insert or retrieve data from a temporary workfile when the amount of data is tremendous. In thisdisclosure, I'll introduce a method to create or avoid building workfile dynamically to enhance the performance of 'SELECT FROM OLD/FINAL TABLE (UPDATE …)'.

According to the following factors, decide whether to build a temporary workfile to save all rows updated, or only save the RID (Record ID) of the rows which are updated, or avoid building workfile but only mark those rows updated in target table.

Factors:
1) the percentage that rows updated comparing with total rows in target table
2) the cluster ratio for rows updated in target table
3) the percentage that data length for a record returned comparing with the total data length of a record in target table.

For SELECT FROM OLD/FINAL TABLE statement, there are 3 ways to record data which are modified by UPDATE operation

Method #1: Actually, UPDATE operation will delete the row being modified, and insert a new row with updated VALUE. Thus, only add one bit within each row and mark this bit for rows deleted and inserted, to represent they will be used for SELECT part.

a) Advantage: avoid building workfile could save quite a lot time and space, when the number of modified rows is huge.

b) Disadvantage: if there is no effective index, retrieving data which is marked for SELCT FROM OLD/FINAL, will be costly by relational scanning the base table, especially when the total number of rows in target table is huge, but the number of modified rows is less.

Method #2:Build a workfile to save the rows before/after UPDATE operation.

c) Advantage: if there the number of rows which are modified is less, and the total number of rows in target table is relative huge, and there is no effective index to retrieve those modified data, retrieve them from a workfle will highly improve the performance.


d) Disadvantage: if the number of rows which are updated is huge, saving these rows within workfile will be cost quite a time and space.

Method #3:Build a workfile to only save the RID (Record ID) of rows before/after UPDATE operation. Note that, RID is used to locate record in data page directly.
a) Advantage: the cost on saving RID into workfile is cheap from both of time and space perspective, even if the number of being saved RIDs is huge.

b) Disadvantage: if the value of saved RIDs is not well-distributed, synchronize I/O will be caused frequently, which may affect performance.

Which above method should be finally adopted to record modified data from UPDATE operation? It depends...