Browse Prior Art Database

Database Cost Based Insert Optimizer

IP.com Disclosure Number: IPCOM000237299D
Publication Date: 2014-Jun-12
Document File: 5 page(s) / 102K

Publishing Venue

The IP.com Prior Art Database

Abstract

Provide new database component called insert optimizer. When Database evaluates a insert sql for execution several steps are taken to ensure the best performance given the information Database has in the form of table statistics. It finds the optimal insert evaluation plan for the underlying insert statement. An insert evaluation plan is a procedural process for the available space searching pattern and locking/latching mechanism and some other facts in the underlying insert statement. For any given insert SQL statement, there are many insert paths to consider. The database estimates the costs of each path and chooses what it thinks is the fastest. This selection process must be done in a reasonable amount of time..

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

Page 01 of 5

Database Cost Based Insert Optimizer

Insert efficiency in Database is settled at very beginning when designing the whole database structure.

At most time, insert performance is permanent if you do not change any database design or change your application behavior. This is quite unacceptable if user hits insert performance problem. Because database is not smart enough to tune the insert performance and user has to change their database design or application.

Database itself cannot decide the database design or insert pattern. All the insert related behaviors are controlled by user themselves. So the better insert performance is the more professional database skill it requests. Database just performs exactly the same as the plan user designs.

Known solutions and their problems

Currently, database always considers the insert algorithm is under internal control. Customer can only control the insert algorithm by changing their database or insert application and workload behavior.

Prior Arts: N/A

Summary of Invention:

Provide new database component called insert optimizer.

When Database evaluates a insert sql for execution several steps are taken to ensure the best performance given the information Database has in the form of table statistics. It finds the optimal insert evaluation plan for the underlying insert statement

An insert evaluation plan is a procedural process for the available space searching pattern and locking/latching mechanism and some other facts in the underlying insert statement. For any given insert SQL statement, there are many insert paths to consider. The database estimates the costs of each path and chooses what it thinks is the fastest. This selection process must be done in a reasonable amount of time..

The goal of optimization is faster execution and itis the speed that allows the database optimizer tobalance cost (in terms of execution time, CPU timeand DASD usage) of

1



Page 02 of 5

different insert paths to determine how a insert statement should be executed.

An insert path describes:

-

the locking level

-

for table, how to find the available space

-

for each index, how to find the available space

-

free space reservation control

-

concu...