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. For information on how to view a query plan by using the CUBRID Manager, see the Query Execution Plan.

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. Generally, 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 is to display the query plan but not execute a query itself by setting the optimization level to 258, the query is that retrieves the years when Sim Kwon Ho won medals and the types of medals.

GET OPTIMIZATION LEVEL

=== < Result of GET OPTIMIZATION Command in Line 1> ===

              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)

=== < Result of SELECT Command in Line 1> ===

There are no results.

0 rows selected.