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.

Example 1

The following example shows how to retrieve 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 example shows how to retrieve 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 depends 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.