Automated real-time index management
Automated Index Management entails automated monitoring of query workload in a DBMS to determine a set of higher load queries to use to evaluate new potential indexes. Without the need of user approval or action, the potential indexes are automatically created, evaluated and tested, and then made available for system wide use for executing queries issued by end users. Indexes created by Automated Index Management are referred to herein as auto indexes.
1. A method, comprising:
a database management system (DBMS) selecting a working set of queries by monitoring queries that are executed by said DBMS;
monitoring column usage of said working set of queries, wherein said column usage includes use of a column in one or more of a filtering predicate, a join predicate, a group-by operation, and an order-by operation by said set of working queries;
based on said column usage, selecting a certain set of one or more auto indexes;
making said certain set of one or more auto indexes available for executing queries not in said working set of queries; and
after making said certain set of one or more auto indexes available:
performing one or more executions of a particular query, each execution of said one or more executions using a particular auto index of said certain set of one or more auto indexes; and
based on execution performance of said one or more executions, locking down said particular auto index for said particular query.
2. The method of claim 1 , wherein selecting the certain set of one or more auto indexes includes:
based on said column usage, creating a first set of meta-only auto indexes;
compiling at least some of said working set of queries; and
determining a second set of meta-only auto indexes selected by compiling at least some of said working set of queries.
3. The method of claim 2 , wherein before compiling said at least some of said working set of queries, generating index statistics for said first set of meta-only auto indexes.
4. The method of claim 1 , wherein locking down said particular auto index for said particular query includes generating a SQL management object that causes said particular auto index to not be used for generating an execution plan.
5. The method of claim 1 , further including:
monitoring usage of said certain set of one or more auto indexes; and
deactivating at least one of said certain set of one or more auto indexes based on said usage of said certain set of one or more auto indexes.
6. A method, comprising:
a database management system (DBMS) selecting a working set of queries by monitoring queries that are executed by said DBMS;
monitoring column usage of said working set of queries, wherein said column usage includes use of a column in one or more of a filtering predicate, a join predicate, a group-by operation, and an order-by operation by said set of working queries;
based on said column usage, selecting a certain set of auto indexes that includes a plurality of auto indexes;
in response to selecting said certain set of auto indexes, materializing said certain set of auto indexes, wherein said materializing said certain set of auto indexes includes performing a single index creation scan of a particular table to materialize said plurality of auto indexes; and
making said certain set of one or more auto indexes available for executing queries not in said working set of queries.
7. The method of claim 6 , further including:
after making said certain set of one or more auto indexes available:
compiling a particular query not in the working set of queries thereby generating a first execution plan, said first execution plan using a particular auto index of said certain set of one or more auto indexes;
monitoring query execution performance of said first execution plan;
based on said query execution performance of said first execution plan, locking down said particular auto index for said particular query.
8. The method of claim 7 , wherein locking down said particular auto index for said particular query includes generating a SQL management object that causes said particular auto index to not be used for generating an execution plan.
9. The method of claim 6 , wherein selecting the certain set of one or more auto indexes includes:
based on said column usage, creating a first set of meta-only auto indexes;
compiling at least some of said working set of queries; and
determining a second set of meta-only auto indexes selected by compiling at least some of said working set of queries.
10. One or more non-transitory computer-readable media storing sequences of instructions which, when executed by one or more processors, cause:
a database management system (DBMS) selecting a working set of queries by monitoring queries that are executed by said DBMS;
monitoring column usage of said working set of queries, wherein said column usage includes use of a column in one or more of a filtering predicate, a join predicate, a group-by operation, and an order-by operation by said set of working queries;
based on said column usage, selecting a certain set of one or more auto indexes;
making said certain set of one or more auto indexes available for executing queries not in said working set of queries; and
after making said certain set of one or more auto indexes available:
performing one or more executions of a particular query, each execution of said one or more executions using a particular auto index of said certain set of one or more auto indexes; and
based on execution performance of said one or more executions, locking down said particular auto index for said particular query.
11. The one or more non-transitory computer-readable media of claim 10 , wherein selecting the certain set of one or more auto indexes includes:
based on said column usage, creating a first set of meta-only auto indexes;
compiling at least some of said working set of queries; and
determining a second set of meta-only auto indexes selected by compiling at least some of said working set of queries.
12. The one or more non-transitory computer-readable media of claim 11 , wherein the sequences of instructions include instructions that, when executed by said one or more processers, cause before compiling said at least some of said working set of queries, generating index statistics for said first set of meta-only auto indexes.
13. The one or more non-transitory computer-readable media of claim 10 , wherein locking down said particular auto index for said particular query includes generating a SQL management object that causes said particular auto index to not be used for generating an execution plan.
14. The one or more non-transitory computer-readable media of claim 10 , wherein the sequences of instructions include instructions that, when executed by said one or more processers, cause:
monitoring usage of said certain set of one or more auto indexes; and
deactivating at least one of said certain set of one or more auto indexes based on said usage of said certain set of one or more auto indexes.
15. One or more non-transitory computer-readable media storing sequences of instructions which, when executed by one or more processors, cause:
a database management system (DBMS) selecting a working set of queries by monitoring queries that are executed by said DBMS;
monitoring column usage of said working set of queries, wherein said column usage includes use of a column in one or more of a filtering predicate, a join predicate a group-by operation, and an order-by operation by said set of working queries;
based on said column usage, selecting a certain set of auto indexes that includes a plurality of auto indexes;
in response to selecting said certain set of auto indexes, materializing said certain set of auto indexes, wherein said materializing said certain set of auto indexes includes performing a single index creation scan of a particular table to materialize said plurality of auto indexes; and
making said certain set of one or more auto indexes available for executing queries not in said working set of queries.
16. The one or more non-transitory computer-readable media of claim 15 , wherein the sequences of instructions include instructions that, when executed by said one or more processers, cause:
after making said certain set of one or more auto indexes available:
compiling a particular query not in the working set of queries thereby generating a first execution plan, said first execution plan using a particular auto index of said certain set of one or more auto indexes;
monitoring query execution performance of said first execution plan;
based on said query execution performance of said first execution plan, locking down said particular auto index for said particular query.
17. The one or more non-transitory computer-readable media of claim 16 , wherein locking down said particular auto index for said particular query includes generating a SQL management object that causes said particular auto index to not be used for generating an execution plan.
18. The one or more non-transitory computer-readable media of claim 15 , wherein selecting the certain set of one or more auto indexes includes:
based on said column usage, creating a first set of meta-only auto indexes;
compiling at least some of said working set of queries; and
determining a second set of meta-only auto indexes selected by compiling at least some of said working set of queries.