CUBRID 8.4.3用户参考手册:查询优化
查询优化
- 更新统计
- 检查统计信息
- 使用SQL提示
- 查看查询计划
- 使用索引
更新统计
描述
语法
- ALL CLASSES: 如果ALL CLASSES关键字被指定,数据库中存在的所有表的统计将被更新。
检查统计信息
描述
语法
- table_name: Table name to check the statistics Information
例子
使用SQL提示
描述
语法
- hint: 如下提示可被指定。
- USE_NL: 表连接相关,查询优化器通过该提示创建一个嵌套循环连接执行计划。
- USE_MERGE: 表连接相关,查询优化器通过该提示创建一个排序合并连接执行计划。
- ORDERED: 表连接相关,查询优化器通过该提示创建一个连接查询计划,基于FROM子句中指定的表的顺序。FROM子句中左边的表变为外部表,右边的表变为内部表。
- USE_IDX: 索引相关,查询优化器通过该提示为指定表创建一个对应的索引连接执行计划。
- USE_DESC_IDX: 这是一个进行降序索引扫描的提示。更多相关信息,请参照Index Scan in Descending Order.
- NO_DESC_IDX: 这是一个不使用降序索引扫描的提示。
- NO_COVERING_IDX: 这是一个不使用覆盖索引的提示。相关细节,请参照Covering Index.
- NO_STATS: 统计信息相关,查询优化器不更新统计信息。相应查询的查询性能能被提高;因为信息未被更新,所以查询计划未被优化。
- RECOMPILE: 重新编译执行计划。该提示用来删除存储在缓存中的查询执行计划,并且创建一个新的查询执行计划。
- spec_name: 如果spec_name和USE_NL,USE_IDX或USE_MERGE被同时指定,指定的连接方法仅适用于spec_name。如果USE_NL和USE_MERGE被同时指定,给定的提示将被忽视。在某些情况下,查询优化器能够创建基于给定的提示的查询执行计划。例如,USE_NL 指定为右外连接,查询转换为内部左外连接,并且无法保证连接顺序。
例1
例2
查看执行计划
描述
Syntax
- opt-level: A value that specifies the optimization level. It has the following meanings.
- 0: Does not perform query optimization. The query is executed using the simplest query plan. This value is used only for debugging.
- 1: Create a query plan by performing query optimization and executes the query. This is a default value used in CUBRID, and does not have to be changed in most cases.
- 2: Creates a query plan by performing query optimization. However, the query itself is not executed. In generall, this value is not used; it is used together with the following values to be set for viewing query plans.
- 257: Performs query optimization and outputs the created query plan. This value works for displaying the query plan by internally interpreting the value as 256+1 related with the value 1.
- 258: Performs query optimization and outputs the created query plan. The difference from the value 257 is that the query is not executed. That is, this value works for displaying the query plan by internally interpreting the value as 256+2 related with the value 2. This setting is useful to examine the query plan but not to intend to see the query results.
- 513: Performs query optimization and outputs the detailed query plan. This value works for displaying more detailed query plan than the value 257 by internally interpreting the value as 512+1.
- 514: Performs query optimization and outputs the detailed query plan. However, the query is not executed. This value works for displaying more detailed query plan than the value 258 by internally interpreting the value as 512+2.
Example
Using Indexes
相关文章