Browse Prior Art Database

Hint Set for Access Path Select

IP.com Disclosure Number: IPCOM000238219D
Publication Date: 2014-Aug-11
Document File: 4 page(s) / 81K

Publishing Venue

The IP.com Prior Art Database

Abstract

Our idea is to issue a hint set for optimizer, if we issue an exclude access path, optimizer would choose access paths among the candidate paths define by hint set.

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

Page 01 of 4

Hint Set for Access Path Select


1. Background:

Currently, optimizer have hint to indicate the access path which should be selected. But in some of our test, we might want to let optimizer choose access paths among some known ones. For example, we would like optimizer to choose index among some known ones. With current hint, we could not attach this purposes.


2. Summary

Our idea is to issue a hint set for optimizer, if we issue an exclude access path, optimizer would choose access paths among the candidate paths define by hint set. This method could be widely used in the access path select, test and so on. For example, when we want to test the access paths is candidates access paths in test, we could use this method to define some of the access paths to process the SQL tuning effectively. We could also use it the programming, and we could not know which two indexes are effective, we could use hint sets to test it.


3. Description:

Users could define a set of the access paths. During access path evaluation, optimizer would choose the access paths among the candidate ones. Currently, the hint could defines only one access path. Our solution allows users to define a set of access paths, and optimizer could choose the best access path from the set --depending on costs and rules as normal.

We could following the process as below:

1



Page 02 of 4


1. Define several candidate access paths


If the users want to apply this feature, several candidate potential access paths should be defined to indicate choosing the best access path from the set.


2. Check whether the candidate access paths are valid

Not all the access paths defined are valid, so optimizer should check whether the access paths are valid. If the access path is invalid, the access path would be dropped.


3. Evaluate the access path by rules and cost


Currently, we have mature method to calculate the access paths costs, and the optimizer would evaluate these valid access paths, and get the a permutation for the query.


4. Choose the best access path


Depending on the rules and costs, optimizer would choose the most efficient access path.

2



Page 03 of 4


5. Drop the access path


If the access path is invalid, optimizer should drop the access path.

The following t...