Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

Using SQL Hint

Description

Using hints can affect the performance of query execution. you can allow the query optimizer to create more efficient execution plan by referring the SQL HINT. The SQL HINTs related tale join, index, and statistics information are provided by CUBRID.

Syntax

CREATE /*+ NO_STATS */ [TABLE | CLASS] ...;

ALTER /*+ NO_STATS */ [TABLE | CLASS] ...;

 

CREATE /*+ NO_STATS */ INDEX ...;

ALTER /*+ NO_STATS */ INDEX ...;

DROP /*+ NO_STATS */ INDEX ...;

 

SELECT /*+ hint [ { hint } ... ] */

SELECT --+ hint [ { hint } ... ]

SELECT //+ hint [ { hint } ... ]

 

hint :

USE_NL[(spec-name[{, spec-name}...])]

USE_IDX[(spec-name[{, spec-name}...])]

USE_MERGE[(spec-name[{, spec-name}...])]

ORDERED

QUERY_CACHE(1)

USE_DESC_IDX

NO_COVERING_IDX

SQL hints are specified by using plus signs and comments. CUBRID interprets this comment as a list of hints separated by blanks. The hint comment must appear after the SELECT, CREATE, or ALTER keyword, and the comment must begin with a plus sign (+), following the comment delimiter.

  • hint : The following hints can be specified.
    • USE_NL : Related to a table join, the query optimizer creates a nested loop join execution plan with this hint.
    • USE_MERGE : Related to a table join, the query optimizer creates a sort merge join execution plan with this hint.
    • ORDERED : Related to a table join, the query optimizer create a join execution plan with this hint, based on the order of tables specified in the FROM clause. The left table in the FROM clause becomes the outer table; the right one becomes the inner table.
    • USE_IDX : Related to a index, the query optimizer creates a index join execution plan corresponding to a specified table with this hint.
    • USE_DESC_IDX : This is a hint for the scan in descending order. For more information, see Index Scan in Descending Order.
    • NO_COVERING_IDX : This is a hint not to use the covering index feature. For more information, see Covering Index.
    • NO_STATS : Related to statistics information, the query optimizer does not update statistics information. Query performance for the corresponding queries can be improved; however, query plan is not optimized because the information is not updated.
  • spec_name : If the spec_name is specified together with USE_NL, USE_IDX or USE_MERGE, the specified join method applies only to the spec_name. If USE_NL and USE_MERGE are specified together, the given hint is ignored. In some cases, the query optimizer cannot create a query execution plan based on the given hint. For example, if USE_NL is specified for a right outer join, the query is converted to a left outer join internally, and the join order may not be guaranteed.
  • QUERY_CACHE(1) : The query cache feature will be applied only to the queries to which this hint is given in the second query cache mode. If you set 2 for the system parameter, query_cache_mode, you can call it the second query cache mode. For more information, see Query/Cache-Related Parameters.
Example 1

The following is an example of retrieving the years when Sim Kwon Ho won medals and the types of medals. Here, a nested loop join execution plan needs to be created which has the athlete table as an outer table and the game table as an inner table. It can be expressed by the following query. The query optimizer creates a nested loop join execution plan that has the game table as an outer table and the athlete table as an inner table.

SELECT /*+ USE_NL ORDERED  */ a.name, b.host_year, b.medal

FROM athlete a, game b WHERE a.name = 'Sim Kwon Ho' AND a.code = b.athlete_code;

  name                    host_year  medal

=========================================================

  'Sim Kwon Ho'                2000  'G'

  'Sim Kwon Ho'                1996  'G'

2 rows selected.

Example 2

The following is an example of viewing query execution time with NO_STAT  hint to improve the functionality of drop partitioned table (before_2008); any data is not stored in the table. Assuming that there are more than 1 million data in the participant2 table. The execution time in the example can differ depending on system performance and database configuration.

-- Not using NO_STATS hint

ALTER TABLE participant2 DROP partition before_2008;

SQL statement execution time: 31.684550 sec

Current transaction has been committed.

1 command(s) successfully processed.

 

-- Using NO_STATS hint

ALTER /*+ NO_STATS */ TABLE participant2 DROP partition before_2008;

SQL statement execution time: 0.025773 sec

Current transaction has been committed.

1 command(s) successfully processed.