Using SQL Hints

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 1

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

hint :
 USE_NL[(spec-name[{, spec-name}...])]
 USE_IDX[(spec-name[{, spec-name}...])]
 USE_MERGE[(spec-name[{, spec-name}...])]
 ORDERED

Syntax 2

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

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

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.

• 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.

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;

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

  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.