Viewing Query Plan
Description
To view a query plan for a CUBRID SQL query, change the value of the optimization level by using the SET OPTIMIZATION statement. You can get the current optimization level value by using the GET OPTIMIZATION statement.
The CUBRID query optimizer determines whether to perform query optimization and output the query plan by referencing the optimization level value set by the user. The query plan is displayed as standard output; the following explanations are based on the assumption that the plan is used in a terminal-based program such as the CSQL Interpreter. In the CSQL query editor, you can view execution plan by executing the ;plan command. See Session Commands. For information on how to view a query plan, see the CUBRID Manager.
Syntax
SET OPTIMIZATION LEVEL opt-level [;]
GET OPTIMIZATION LEVEL [ { TO | INTO } variable ] [;]
- 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
The following example shows how to view query plan by using the example retrieving year when Sim Kwon Ho won medal and metal type.
GET OPTIMIZATION LEVEL
Result
=============
1
SET OPTIMIZATION LEVEL 258;
SELECT a.name, b.host_year, b.medal
FROM athlete a, game b WHERE a.name = 'Sim Kwon Ho' AND a.code = b.athlete_code
Query plan:
Nested loops
Sequential scan(game b)
Index scan(athlete a, pk_athlete_code, a.code=b.athlete_code)
There are no results.
0 rows selected.