2. Altibase SQL Basics#

This chapter describes the basics for using Altibase's SQL.

Comments#

In Altibase, the following two types of comment delimiters can be used in SQL statements:

/* */
In the same way as using the same syntax as in C, the start of a comment is indicated with '/*' and the end with '*/'. This kind of comment can occupy multiple lines.
--
Use two hyphen '-' characters to indicate the start of a single-line comment.

Altibase Objects#

Altibase provides the following data objects that are either schema objects or non-schema objects:

Schema Objects#

Constraint
Index
Sequence
Synonym
Table
Stored procedure
View
Trigger
Database link

Non-Schema Objects#

User
Replication
Tablespace
Directory

Rules for Object Names#

Object Names#

Database objects are named using identifiers that follow these rules:

The maximum length of an identifier is 40 bytes.
Users can choose to enclose an identifier in double quotation marks (") or not. If users create 40 SQL Reference an object and specify its name in double quotation marks, it must always be referenced that way.
A nonquoted identifier is not surrounded by delimiters and is case insensitive. Altibase internally changes names to upper-case letters. Quoted identifiers are case sensitive.
```
employees, EMPLOYEES, "EMPLOYEES"
```
Nonquoted identifiers can contain upper and lowercases letters of the alphabet, numeric character between 0 ~ 9, the underscore character ('_'), the dollar-sign character ("$") and the pound-sign character ('#').
An identifier must start with either a letter or the underscore character('_'). Nonquoted identified cannot begin with 'V$', 'X$, or 'D$'.
Quoted identifiers can contain characters, punctuation marks and spaces, but not double quotation marks.
Altibase reserved words cannot be used as object names (A list of reserved words in Altibase is provided below.).
Objects should not share the same name within the same namespace.
- The following schema objects share one namespace:
  - Tables
  - Views
  - Sequences
  - Synonyms
  - Stored procedures
- The following schema objects have their own namespace:
  - Constraints
  - Indexes
  - Triggers
  - Database link objects
- A table and view in the same schema cannot share the same name because tables and views share the same namespace. However, a table and index can share the same name in the same namespace because tables and indexes exist in different namespaces.
- The following non-schema objects also have their own namespace:
  Users, Replication objects, Tablespaces, Directory objects.

For more detailed information about Altibase objects, please refer to the Administrator's Manual.

Passwords#

Altibase uses password authentication. This means that the user has to enter a password when logging into the database.

The password the user uses to connect to Altibase also has similar constraints as the object name: characters for passwords are A~Z, a~z, 0~9, _, and $. In addition, Altibase's reserved words cannot be in passwords. The first character must be a letter or _. The maximum length of the password is 40 bytes.

Altibase automatically converts lowercase passwords to uppercase by default. However, you can create a case sensitive password by setting the value of CASE_SENSITIVE_PASSWORD to 1, and then enclosing the password in quotation marks, when creating a user with the CREATE USER statement. If you omit the quotation marks, the database will convert the string to uppercase, even if the value of CASE_SENSITIVE_PASSWORD is 1.

Reserved Words#

The following words are reserved words in Altibase, and cannot be used as database object names or passwords. The (O) indicates that the keyword cannot be used as a database object name but can be used as a column name. You should keep this in mind when creating database objects and writing SQL statements.

_PROWID	FIFO(O)	PRIMARY
ACCESS(O)	FIXED(O)	PRIOR
ADD	FLASHBACK(O)	PROCEDURE
AFTER(O)	FLUSH(O)	PURGE
AGER(O)	FLUSHER(O)	QUEUE
ALL	FOLLOWING(O)	RAISE
ALTER	FOR	READ
AND	FOREIGN	REBUILD
ANY	FROM	RECOVER
APPLY	FULL(O)	REMOVE
ARCHIVE(O)	FUNCTION(O)	RENAME
ARCHIVELOG(O)	GOTO(O)	REPLACE
AS	GRANT	RETURN
ASC	GROUP	RETURNING
AT(O)	HAVING	REVOKE
AUDIT(O)	IF(O)	RIGHT
AUTOEXTEND(O)	IN	ROLLBACK
BACKUP(O)	INDEX	ROLLUP
BEFORE(O)	INITRANS(O)	ROW
BEGIN(O)	INNER(O)	ROWCOUNT
BETWEEN	INSERT	ROWNUM
BODY(O)	INSTEAD	ROWTYPE
BULK	INTERSECT	SAVEPOINT
BY	INTO	SEGMENT
CASCADE(O)	IS	SELECT
CASE	ISOLATION(O)	SEQUENCE
CAST(O)	JOIN(O)	SESSION
CHECKPOINT(O)	KEY(O)	SET
CLOSE(O)	LANGUAGE(O)	SHARD
COALESCE(O)	LATERAL	SOME
COLUMN	LEFT(O)	SPLIT
COMMENT(O)	LESS(O)	SQLCODE
COMMIT(O)	LEVEL	SQLERRM
COMPILE(O)	LIBRARY(O)	START
COMPRESS	LIFO(O)	STEP
COMPRESSED(O)	LIKE	STORAGE
CONJOIN(O)	LIMIT(O)	STORE
CONNECT	LINK	SYNONYM
CONSTANT	LINKER	TABLE
CONSTRAINTS(O)	LOB	THAN
CONTINUE(O)	LOCAL	THEN
CREATE	LOCK	TO
CROSS	LOGANCHOR	TOP
CUBE(O)	LOGGING	TRIGGER
CURSOR(O)	LOOP	TRUE
CYCLE(O)	MAXROWS	TRUNCATE
DATABASE(O)	MAXTRANS	TYPE
DECLARE(O)	MERGE	TYPESET
DECRYPT(O)	MINUS	UNION
DEFAULT	MODE	UNIQUE
DELAUDIT(O)	MODIFY	UNLOCK
DELETE	MOVE	UNPIVOT
DEQUEUE(O)	MOVEMENT	UNTIL
DESC	NEW	UPDATE
DETERMINISTIC(O)	NOAUDIT	USING
DIRECTORY(O)	NOCOPY	VALUES
DISABLE(O)	NOCYCLE	VARIABLE
DISASTER(O)	NOLOGGING	VC2COLL
DISCONNECT(O)	NOT	VIEW
DISJOIN(O)	NULL	VOLATILE
DISTINCT	NULLS	WAIT
DROP	OF	WHEN
EACH(O)	OFF	WHENEVER
ELSE	OFFLINE	WHERE
ELSEIF(O)	OLD	WHILE
ELSIF(O)	ON	WITH
ENABLE(O)	ONLINE	WITHIN
END(O)	OPEN	WORK
ENQUEUE(O)	OR	WRAPPED
ESCAPE(O)	ORDER	WRITE
EXCEPTION	OTHERS
EXEC(O)	OUT
EXECUTE(O)	OUTER
EXISTS	OVER
EXIT(O)	PACKAGE
EXTENT(O)	PARALLEL
EXTENTSIZE(O)	PARTITION
FALSE	PIVOT
FETCH(O)	PRECEDING

[Table 2-1] List of Reserved Words]

HINT#

Syntax#

hints ::=

hint

Prerequisites#

The hint can be specified in the following:

Simple SELECT, UPDATE, DELETE, and INSERT statements
The main query or subquery of a compound statement
The initial query of a compound statement (enclosed in a set operator)

Description#

Users can specify the hint after the SELECT, UPDATE, DELETE, and INSERT keywords.

A plus sign (+) after a comment delimiter (/*) tells Altibase that the comment is a hint. The plus sign must follow immediately after the comment delimiter.

Users can specify multiple hints in a single comment by separating them with blank spaces. If a hint has a syntax error, Altibase ignores the hint and executes the query.

For more detailed information about each hint, please refer to the Hint List.

For more detailed information about using hints for query tuning, please refer to the Performance Tuning Guide.

Examples#

Direct-Path INSERT Hint#

<Query> Insert all data from table T1 to table T2 as a direct-PATH INSERT operation.

INSERT /*+ APPEND */ INTO T2 SELECT * FROM T1;

Table Access Method Hints (full scan, index scan, index ascending order scan, index descending order scan, no index scan)#

The following query selects the employee numbers, names, and occupations of all the female employees.

SELECT eno, e_firstname, e_lastname, emp_job FROM employees WHERE sex = 'F';

For example, an index would be created on the Gender (SEX) column of the table EMPLOYEES with the number of employees, and the value of this column would be either 'M' or 'F'.

If there are as many male employees as female employees, a full scan would be faster than an index scan. However, if there are many more male employees than female employees, an index scan would be faster than a full scan. If a column has only two different values, the query optimizer assumes that half of the rows contain each value and uses the cost-based approach to perform a full scan.

Comparing the numbers of accesses in the queries below, the numbers of accesses are 20 and 4, respectively.

<Query> Select the employee numbers, names, and jobs of all female employees (use a full scan).

iSQL> SELECT /*+ FULL SCAN(employees) */ eno, e_firstname, e_lastname, emp_job
 FROM employees 
 WHERE sex = 'F';
ENO E_FIRSTNAME E_LASTNAME EMP_JOB 
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 65, COST: 0.18 )
 SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, COST: 0.14 )
------------------------------------------------

<Query> Select the employee numbers, names, and jobs of all female employees (use an index scan).

iSQL> CREATE INDEX gender_index ON employees(sex);
Create success.
iSQL> SELECT /*+ INDEX(employees, gender_INDEX) use gender_index because there are few female employees */ eno, e_firstname, e_lastname, emp_job
 FROM employees
 WHERE sex = 'F';
ENO E_FIRSTNAME E_LASTNAME EMP_JOB 
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 65 )
 SCAN ( TABLE: EMPLOYEES, INDEX: GENDER_INDEX, ACCESS: 4, SELF_ID: 2 )
------------------------------------------------

<Query> Select the order numbers, good numbers, and order quantities for all orders between January and March (use an index scan). The order table for each month is called ORDERS_##.

create view orders as
select ono, order_date, eno, cno, gno, qty from orders_01
union all
select ono, order_date, eno, cno, gno, qty from orders_02
union all
select ono, order_date, eno, cno, gno, qty from orders_03;
create index order1_gno on orders_01(gno);
create index order2_gno on orders_02(gno);
create index order3_gno on orders_03(gno);

iSQL> select /*+ index( orders, 
           orders1_gno, orders2_gno,orders3_gno ) */
           ONO, GNO, QTY
      from orders;
ONO                  GNO         QTY         
-------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 24 )
 VIEW ( ORDERS, ACCESS: 14, SELF_ID: 6 )
  PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
   VIEW ( ACCESS: 14, SELF_ID: 5 )
    BAG-UNION
     PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
      SCAN ( TABLE: ORDERS_01, INDEX: ORDERS1_GNO, ACCESS: , SELF_ID: 0 )
     PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
      SCAN ( TABLE: ORDERS_02, INDEX: ORDERS2_GNO, ACCESS: 4, SELF_ID: 1 )
     PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
      SCAN ( TABLE: ORDERS_03, INDEX: ORDERS3_GNO, ACCESS: 7, SELF_ID: 4 )
------------------------------------------------

Join Order Hints (ordered, optimized)#

<Query> Select the employee number, name, and customer name of the employee who is in charge of the order (join the tables EMPLOYEES and CUSTOMERS, and then use the ORDERED hint to join that with the table ORDERS).

iSQL> SELECT /*+ ORDERED */ DISTINCT o.eno, e.e_lastname, c.c_lastname
FROM employees e, customers c, orders o
WHERE e.eno = o.eno AND o.cno = c.cno;
ENO E_LASTNAME C_LASTNAME 
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 )
 DISTINCT ( ITEM_SIZE: 40, ITEM_COUNT: 21, BUCKET_COUNT: 1024, ACCESS: 21, SELF_ID: 4, REF_ID: 3 )
 JOIN
 JOIN
 SCAN ( TABLE: EMPLOYEES E, FULL SCAN, ACCESS: 20, SELF_ID: 1 )
 SCAN ( TABLE: CUSTOMERS C, FULL SCAN, ACCESS: 400, SELF_ID: 2 )
 SCAN ( TABLE: ORDERS O, FULL SCAN, ACCESS: 12000, SELF_ID: 3 )
------------------------------------------------

<Query> Select the employee number, name, and customer name of the employee who is in charge of the order (let the optimizer determine the table join order, regardless of the table join order in the FORM clause).

iSQL> SELECT DISTINCT o.eno, e.e_lastname, c.c_lastname
FROM employees e, customers c, orders o 
WHERE e.eno = o.eno AND o.cno = c.cno;
ENO E_LASTNAME C_LASTNAME 
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 )
 DISTINCT ( ITEM_SIZE: 40, ITEM_COUNT: 21, BUCKET_COUNT: 1024, ACCESS: 21, SELF_ID: 4, REF_ID: 1 )
 JOIN
 JOIN
 SCAN ( TABLE: CUSTOMERS C, FULL SCAN, ACCESS: 20, SELF_ID: 2 )
 SCAN ( TABLE: ORDERS O, INDEX: ODR_IDX2, ACCESS: 30, SELF_ID: 3 )
 SCAN ( TABLE: EMPLOYEES E, INDEX: __SYS_IDX_ID_366, ACCESS: 30, SELF_ID: 1 )
------------------------------------------------

Optimizer Mode Hints (rule, cost)#

iSQL> SELECT /*+ RULE */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ COST */ * FROM t1, t2 WHERE t1.i1 = t2.i1;

Normal Form Hints (cnf, dnf)#

iSQL> SELECT /*+ CNF */ * FROM t1 WHERE i1 = 1 OR i1 = 2;
iSQL> SELECT /*+ DNF */ * FROM t1 WHERE i1 = 1 OR i1 = 2;

Join Method Hints (nested loop, hash, sort, sort merge)#

iSQL> SELECT /*+ USE_NL (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ USE_HASH (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ USE_SORT (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ USE_MERGE (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;

Hash Bucket Size Hints (hash bucket count, group bucket count, set bucket count)#

iSQL> SELECT /*+ HASH BUCKET COUNT (20) */ DISTINCT * FROM t1;
iSQL> SELECT * FROM t1 GROUP BY i1, i2;
iSQL> SELECT /*+ GROUP BUCKET COUNT (20) */ * FROM t1 GROUP BY i1, i2;
iSQL> SELECT * FROM t1 INTERSECT SELECT * FROM t2;
iSQL> SELECT /*+ SET BUCKET COUNT (20) */  * FROM t1 INTERSECT SELECT * FROM t2;

Push Predicate Hints#

<Query> Select the customer list and good numbers for orders of more than 10,000 goods between January and March (use the PUSH PREDICATE hint to join the tables CUSTOMERS and ORDERS).

iSQL> create view orders_t as
    2 select ono, order_date, eno, cno, gno, qty from orders orders_01
    3 union all
    4 select ono, order_date, eno, cno, gno, qty from orders orders_02
    5 union all
    6 select ono, order_date, eno, cno, gno, qty from orders orders_03;
Create success.
iSQL> alter session set explain plan = only;
Alter success.
iSQL> alter session set trclog_detail_predicate =1;
Alter success.
iSQL> select /*+ PUSH_PRED(orders_t) */ c_lastname, gno
    2 from customers, orders_t
    3 where customers.cno = orders_t.cno
    4 and orders_t.qty >= 10000;
C_LASTNAME            GNO
-------------------------------------
.
.
.
-----------------------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 34, COST: 1.68 )
 JOIN ( METHOD: NL, COST: 1.67 )
  SCAN ( TABLE: SYS.CUSTOMERS, FULL SCAN, ACCESS: ??, COST: 0.23 )
  VIEW ( SYS.ORDERS_T, ACCESS: ??, COST: 0.07 )
   PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48, COST: 0.04 )
    VIEW ( ACCESS: ??, COST: 0.04 )
     BAG-UNION
      PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48, COST: 0.01 )
       SCAN ( TABLE: SYS.ORDERS ORDERS_01, INDEX: SYS.ODR_IDX2, RANGE SCAN, ACCESS: ??, COST: 0.01 )
        [ VARIABLE KEY ]
        OR
         AND
          CUSTOMERS.CNO = ORDERS_01.CNO
        [ FILTER ]
        ORDERS_01.QTY >= 10000
      PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48, COST: 0.01 )
       SCAN ( TABLE: SYS.ORDERS ORDERS_02, INDEX: SYS.ODR_IDX2, RANGE SCAN, ACCESS: ??, COST: 0.01 )
        [ VARIABLE KEY ]
        OR
         AND
          CUSTOMERS.CNO = ORDERS_02.CNO
        [ FILTER ]
        ORDERS_02.QTY >= 10000
      PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48, COST: 0.01 )
       SCAN ( TABLE: SYS.ORDERS ORDERS_03, INDEX: SYS.ODR_IDX2, RANGE SCAN, ACCESS: ??, COST: 0.01 )
        [ VARIABLE KEY ]
        OR
         AND
          CUSTOMERS.CNO = ORDERS_03.CNO
        [ FILTER ]
        ORDERS_03.QTY >= 10000
-----------------------------------------------------------

Hint List#

This section briefly discusses how to use hints and their meanings. For more detailed information about how each hint is processed by the query optimizer, please refer to Chapter 3. Query Optimizer in the Performance Tuning Guide.

Category	Hint
Optimizer Approach and Goal	COST RULE FIRST_ROWS
Normalization Form	CNF DNF NO_EXPAND USE_CONCAT
Access Method	FULL SCAN INDEX INDEX ASC INDEX_ASC INDEX DESC INDEX_DESC NO INDEX NO_INDEX
Parallel Processing	PARALLEL NO_PARALLEL
Join Order	LEADING ORDERED
Join Method	USE_NL USE_FULL_NL USE_FULL_STORE_NL USE_INDEX_NL USE_ANTI USE_HASH USE_ONE_PASS_HASH USE_TWO_PASS_HASH USE_INVERSE_HASH USE_SORT USE_ONE_PASS_SORT USE_TWO_PASS_SORT USE_MERGE
Join Method	NL_SJ HASH_SJ SORT_SJ MERGE_SJ NL_AJ HASH_AJ SORT_AJ MERGE_AJ INVERSE_JOIN NO_INVERSE_JOIN NO_USE_HASH NO_USE_MERGE NO_USE_NL NO_USE_SORT
Query Conversion	NO_MERGE NO_TRANSITIVE_PRED NO_UNNEST UNNEST
Intermediate Result Table	TEMP_TBS_DISK TEMP_TBS_MEMORY
Hash Bucket Size	GROUP BUCKET COUNT HASH BUCKET COUNT SET BUCKET COUNT
Group Process Method	GROUP_HASH GROUP_SORT
Duplicate Elimination Method	DISTINCT_HASH DISTINCT_SORT
View Optimization Method	NO_PUSH_SELECT_VIEW PUSH_SELECT_VIEW PUSH_PRED
Simple Query	EXEC_FAST NO_EXEC_FAST
Simple Filter	SERIAL_FILTER NO_SERIAL_FILTER
Others	APPEND DELAY HIGH_PRECISION KEEP_PLAN NO DELAY NO_PLAN_CACHE RESULT_CACHE TOP_RESULT_CACHE PLAN_CACHE_KEEP

APPEND#

This hint specifies a direct-path INSERT operation and can only be used in INSERT statements. Direct-path INSERT creates a new page and inserts data, instead of searching for free space in pages. The V$DIRECT_PATH_INSERT performance view displays statistics for direct-Path INSERT operations.

append

CNF#

This hint specifies that predicates in the WHERE clause are to be normalized in the conjunctive normal form.

cnf

COST#

If this hint is specified, the optimizer creates an execution plan with the lowest cost.

cost

DELAY#

These hints activate a function which delays execution of hierarchy, sorting, windowing, grouping, set, and distinction based upon execution plan graphs regardless of properties in queries so that the execution can be carried out in fetch.

DISTINCT_HASH#

This hint speicifies HASH for DISTINT.

distinct_hash

DISTINCT_SORT#

This hint specifies SORT for DISTINCT.

distinct_sort

DNF#

This hint specifies that predicates in the WHERE clause are to be normalized in the disjunctive normal form.

dnf

EXEC_FAST#

If this hint is specified when EXECUTOR_FAST_SIMPLE_QUERY property is deactivated, simple statements, such as SELECT, INSERT, UPDATE, or DELETE statement are executed with SIMPLE QUERY. If SIMPLE QUERY is applied, it will be output on the execution plan.

FIRST_ROWS#

If this hint is specified, the optimizer creates an execution plan that most efficiently returns the first n rows of a table.

first_rows

FULL SCAN#

This hint specifies that the full table scan will be performed for the specified table.

full scan

GROUP BUCKET COUNT#

This hint specifies the number of hash buckets for the GROUP-AGGREGATION and AGGREGATION execution nodes.

group bucket count

GROUP_HASH#

This hint specifies HASH for GROUP BY.

group_hash

GROUP_SORT#

This hint specifies SORT for GROUP BY.

group_sort

HASH_AJ#

If this hint is specified, a nested subquery uses a hash join to perform an anti-join. You need to specify this hint within the subquery. If the subquery cannot be unnested with an anti-join, this hint becomes invalid.

hash_aj

HASH BUCKET COUNT#

This hint specifies the number of hash buckets for the HASH and DISTINCT execution nodes.

hash bucket count

HASH_SJ#

If this hint is specified, a nested subquery uses a hash join to perform a semi-join. You need to specify this hint within the subquery. If the subquery cannot be unnested with a semi-join, this hint becomes invalid.

hash_sj

HIGH_PRECISION#

This is a hint used for preventing errors in the four fundamental arithmetic operations and mod operation.

When this hint is used, the float type is used as the data type. When operating with a float type, computational performance may be lower than that of a real or double data type. But, the precision of up to 38 digits and mod operation is guaranteed.

INDEX#

This hint specifies an index scan.

index

INDEX ASC#

This hint specifies an index scan in ascending order.

index_asc

INDEX_ASC#

This hint performs the same action equivalent to the INDEX ASC hint.

INDEX DESC#

This hint specifies an index scan in descending order.

index desc

INDEX_DESC#

This hint performs the same action equivalent to the INDEX DESC hint.

INVERSE_JOIN#

If this hint is specified, a nested subquery uses an inverse join¹ to perform either an anti-join or a semi-join. The user needs to specify this hint within the subquery.

This hint can be used with other hints that force semi-joins or anti-joins. For example, this hint forces an inverse hash join if it is used with the HASH_SJ hint.

inverse_join

KEEP_PLAN#

If this hint is specified, the optimizer uses an exisiting execution plan (instead of recreating it) whenever the statistics for a table that is referenced by the plan changes. This hint can be used to prepare/execute and direct/execute a query.

keep_plan

LEADING#

This hint firstly joins the tables that are used in a hint.

MERGE_AJ#

If this hint is specified, a nested subquery uses a merge join to perform an anti-join. You need to specify this hint within the subquery. If the subquery cannot be unnested with an anti-join, this hint becomes invalid.

merge_aj

MERGE_SJ#

If this hint is specified, a nested subquery uses a merge join to perform a semi-join. Users need to specify this hint within the subquery. If the subquery cannot be unnested with a semi-join, this hint becomes invalid.

merge_sj

NL_AJ#

If this hint is specified, a nested subquery uses a nested loop join to perform an anti-join. Users need to specify this hint within the subquery. If the subquery cannot be unnested with an anti-join, this hint becomes invalid.

nl_aj

NL_SJ#

If this hint is specified, a nested subquery uses a nested loop join to perform a semi-join. Users need to specify this hint within the subquery. If the subquery cannot be unnested with a semi-join, this hint becomes invalid.

nl_sj

NO DELAY#

This hint deactivates aforementioned delaying function . Delaying execution of hierarchy, sorting, windowing, grouping, set, and distinction based upon execution plan graphs is not activated

NO_EXEC_FAST#

Even though SELECT, INSERT, UPDATE, DELETE statements are simple statements, they are not executed with SIMPLE QUERY if this hint is specified when EXECUTOR_FAST_SIMPLE_QUERY property is deactivated.

NO_EXPAND#

This hint performs the same action equivalent to the CNF hint.

NO INDEX#

This hint specifies not to perform an index scan.

no index

NO_INDEX#

This hint performs the same action equivalent to the NO INDEX hint.

NO_INVERSE_JOIN#

If this hint is specified, a nested subquery uses a one-pass hash join or a two-pass hash join to perform either an anti-join or a semi-join. Users need to specify this hint within the subquery.

This hint can be used with other hints that force semi-joins or anti-joins. For example, this hint forces a one-pass hash join or a two-pass hash join if it is used with the HASH_SJ hint.

no_inverse_join

NO_MERGE#

This hint instructs not to merge the main query and inline view query into one query.

no_merge

NO_PARALLEL#

This hint performs the same action equivalent to the NOPARALLEL hint.

NO_PLAN_CACHE#

This hint specifies that the plan cache is not to store plans.

no_plan_cache

NO_PUSH_SELECT_VIEW#

This hint specifies that a WHERE predicate outside a view is not to be pushed inside the view.

no_push_select_view

NO_SERIAL_FILTER#

If a hint is specified while the SERIAL_EXECUTE_MODE property is enabled, it will not operate in Serial Execute Mode.

no_serial_filter

NO_TRANSITIVE_PRED#

This hint specifies that predicate transitivity is not to be allowed. For more detailed information about predicate transitivity, please refer to the Performance Tuning Guide in Chapter 3: Query Optimizer.

no_transitive_pred

NO_UNNEST#

This hint specifies not to unnest a subquery.

no_unnest

NO_USE_HASH#

This hint selects a join method from among the hints excluding the HASH hint.

NO_USE_MERGE#

This hint selects a join method from among the hints excluding the MERGE hint.

NO_USE_NL#

This hint selects a join method from among the hints excluding the NL hint.

NO_USE_SORT#

This hint selects a join method from among the hints excluding the SORT hint.

PARALLEL#

This hint specifies to execute queries in parallel when scanning partitioned tables.

NOPARALLEL: Does not execute in parallel
PARALLEL integer: Executes as many threads in parallel as specified for integer

parallel

PLAN_CACHE_KEEP#

This is used to instruct the plan to exclude it from the victim selection process and keep it in the plan cache. This hint is applied during the hardprepare process. So when users switch the plan to unkeep, the soft prepare will not go back to the keep state.

plan_cache_keep

ORDERED#

This hint specifies to follow the join order in the FROM clause.

order

PUSH_PRED#

This hint specifies that a WHERE predicate outside a view is to be pushed inside the view.

push_pred

PUSH_SELECT_VIEW#

This hint specifies that a WHERE predicate outside a view is to be pushed inside the view.

push_select_view

RESULT_CACHE#

If this hint is specified, the intermediate results of the firstly query can be stored so that the results can be re-usable when the same query is executed.

RULE#

If this hint is specified, the optimizer creates a rule-based execution plan.

rule

SET BUCKET COUNT#

It is a hint to specify the number of hash buckets for SET-INTERSECT and SET-DIFFERENCE execution nodes.

set_bucket_count

SERIAL_FILTER#

If the hint is specified while the SERIAL_EXECUTE_MODE property is disabled, it operates in Serial Execute mode.

serial_filter

SORT_AJ#

If this hint is specified, a nested subquery uses a sort join to perform an anti-join. You need to specify this hint within the subquery. If the subquery cannot be unnested with an anti-join, this hint becomes invalid.

sort_aj

SORT_SJ#

If this hint is specified, a nested subquery uses a sort join to perform a semi-join. You need to specify this hint within the subquery. If the subquery cannot be unnested with a semi-join, this hint becomes invalid.

sort_sj

TEMP_TBS_DISK#

This hint specifies that all intermediate query results are to be stored on disk temporary space.

temp_tbs_disk

TEMP_TBS_MEMORY#

This hint specifies that all intermediate query results are to be stored in memory temporary space.

temp_tbs_memory

TOP_RESULT_CACHE#

This hints specifies the top result cache that the final results is cached.

UNNEST#

This hint specifies that a subquery is to be unnested.

unnest

USE_ANTI#

This hint specifies that a left outer join and an anti outer join are to be performed on the table in the FULL OUTER JOIN query and to concatenate the results. This hint is only available if both of the joined columns have indexes. For more detailed information, please refer to the ANTI-OUTER-JOIN node.

use_anti

USE_CONCAT#

This hint performs the same action equivalent to the DNF hint.

USE_FULL_NL#

This hint specifies that a full nested loop join is to be used.

use_full_nl

USE_FULL_STORE_NL#

This hint specifies that a full store nested loop join is to be used.

use_full_store_nl

USE_HASH#

This hint specifies that a hash join is to be used. If there is no join predicate, a nested loop join is used.

use_hash

USE_INDEX_NL#

This hint specifies that an index nested loop join is to be used.

use_index_nl

USE_INVERSE_HASH#

This hint specifies that an inverse hash join is to be used.

use_inverse_hash

USE_MERGE#

This hint specifies that a sort merge join is to be used. If there is no sort predicate, a nested loop join is used.

use_merge

USE_NL#

This hint specifies that a nested loop join is to be used.

use_nl

USE_ONE_PASS_HASH#

This hint specifies that a one-pass hash join is to be used.

use_one_pass_hash

USE_ONE_PASS_SORT#

This hint specifies that a one-pass sort join is to be used.

use_two_pass_sort

USE_SORT#

This hint specifies that a sort join is to be used. If there is no sort predicate, a nested loop join is used.

use_sort

USE_TWO_PASS_HASH#

This hint specifies that a two-pass hash join is to be used.

use_two_pass_hash

USE_TWO_PASS_SORT#

This hint specifies that a two-pass sort join is to be used.

use_two_pass_sort

An inverse join can be either an inverse index nested loop join, an inverse hash join, or an inverse sort join. For more detailed information about inverse joins, please refer to the Performance Tuning Guide. ↩