ヒント句一覧

(8.0.26)

 

インデックスヒント
https://dev.mysql.com/doc/refman/8.0/ja/index-hints.html

USE INDEX
IGNORE INDEX
FORCE INDEX


オプティマイザヒント
https://dev.mysql.com/doc/refman/8.0/ja/optimizer-hints.html

BKA
NO_BKA
BNL
NO_BNL
DERIVED_CONDITION_PUSHDOWN
NO_DERIVED_CONDITION_PUSHDOWN
GROUP_INDEX
NO_GROUP_INDEX
HASH_JOIN
NO_HASH_JOIN
INDEX
NO_INDEX
INDEX_MERGE
NO_INDEX_MERGE
JOIN_FIXED_ORDER
JOIN_INDEX
NO_JOIN_INDEX
JOIN_ORDER
JOIN_PREFIX
JOIN_SUFFIX
MAX_EXECUTION_TIME
MERGE
NO_MERGE
MRR
NO_MRR
NO_ICP
NO_RANGE_OPTIMIZATION
ORDER_INDEX
NO_ORDER_INDEX
QB_NAME
RESOURCE_GROUP
SEMIJOIN
NO_SEMIJOIN
SKIP_SCAN
NO_SKIP_SCAN
SET_VAR
SUBQUERY

 

(19c)
https://ameblo.jp/keshi0414/entry-12312718722.html
https://docs.oracle.com/cd/F19136_01/sqlrf/Comments.html#GUID-D316D545-89E2-4D54-977F-FC97815CD62E


select name
from v$sql_hint
order by name;

ですべてのヒント句を取得可能

373個ある

 

ALL_ROWS
APPEND
APPEND_VALUES
CACHE
CHANGE_DUPKEY_ERROR_INDEX
CLUSTER
CLUSTERING
CONTAINERS
CURSOR_SHARING_EXACT
DISABLE_PARALLEL_DML
DRIVING_SITE
DYNAMIC_SAMPLING
ENABLE_PARALLEL_DML
FACT
FIRST_ROWS
FRESH_MV
FULL
GATHER_OPTIMIZER_STATISTICS
GROUPING
HASH
IGNORE_ROW_ON_DUPKEY_INDEX
INDEX
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
INDEX_JOIN
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC
INMEMORY
INMEMORY_PRUNING
LEADING
MERGE
MODEL_MIN_ANALYSIS
MONITOR
NATIVE_FULL_OUTER_JOIN
NOAPPEND
NOCACHE
NO_CLUSTERING
NO_EXPAND
NO_FACT
NO_GATHER_OPTIMIZER_STATISTICS
NO_INDEX
NO_INDEX_FFS
NO_INDEX_SS
NO_INMEMORY
NO_INMEMORY_PRUNING
NO_MERGE
NO_MONITOR
NO_NATIVE_FULL_OUTER_JOIN
NO_PARALLEL
NOPARALLEL
NO_PARALLEL_INDEX
NOPARALLEL_INDEX
NO_PQ_CONCURRENT_UNION
NO_PQ_SKEW
NO_PUSH_PRED
NO_PUSH_SUBQ
NO_PX_JOIN_FILTER
NO_QUERY_TRANSFORMATION
NO_RESULT_CACHE
NO_REWRITE
NOREWRITE
NO_STAR_TRANSFORMATION
NO_STATEMENT_QUEUING
NO_UNNEST
NO_USE_BAND
NO_USE_CUBE
NO_USE_HASH
NO_USE_MERGE
NO_USE_NL
NO_XML_QUERY_REWRITE
NO_XMLINDEX_REWRITE
NO_ZONEMAP
OPTIMIZER_FEATURES_ENABLE
OPT_PARAM
ORDERED
PARALLEL
PARALLEL_INDEX
PQ_CONCURRENT_UNION
PQ_DISTRIBUTE
PQ_FILTER
PQ_SKEW
PUSH_PRED
PUSH_SUBQ
PX_JOIN_FILTER
QB_NAME
RESULT_CACHE
RETRY_ON_ROW_CHANGE
REWRITE
STAR_TRANSFORMATION
STATEMENT_QUEUING
UNNEST
USE_BAND
USE_CONCAT
USE_CUBE
USE_HASH
USE_MERGE
USE_NL
USE_NL_WITH_INDEX

 

(14)
https://github.com/ossc-db/pg_hint_plan

SeqScan
TidScan
IndexScan
IndexOnlyScan
BitmapScan
IndexScanRegexp
IndexOnlyScanRegexp
BitmapScanRegexp
NoSeqScan
NoTidScan
NoIndexScan
NoIndexOnlyScan
NoBitmapScan
NestLoop
HashJoin
MergeJoin
NoNestLoop
NoHashJoin
NoMergeJoin
Leading
Rows
Parallel
Set

(2019)
https://docs.microsoft.com/ja-jp/sql/t-sql/queries/hints-transact-sql?view=sql-server-ver15


-- ① 結合ヒント
LOOP JOIN
HASH JOIN
MERGE JOIN
REMOTE JOIN


-- ② クエリヒント
HASH GROUP
ORDER GROUP
CONCAT UNION
HASH UNION
MERGE UNION
LOOP JOIN
MERGE JOIN
HASH JOIN
EXPAND VIEWS
FAST 
FORCE ORDER
FORCE EXTERNALPUSHDOWN
DISABLE EXTERNALPUSHDOWN
FORCE SCALEOUTEXECUTION
DISABLE SCALEOUTEXECUTION
IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
KEEP PLAN
KEEPFIXED PLAN
MAX_GRANT_PERCENT
MIN_GRANT_PERCENT
MAXDOP
MAXRECURSION
NO_PERFORMANCE_SPOOL
OPTIMIZE FOR
OPTIMIZE FOR UNKNOWN
PARAMETERIZATION SIMPLE
PARAMETERIZATION FORCED
QUERYTRACEON
RECOMPILE
ROBUST PLAN
USE HINT
USE PLAN
TABLE HINT


-- ③ テーブルヒント
NOEXPAND
INDEX
FORCESEEK
FORCESCAN
HOLDLOCK
NOLOCK
NOWAIT
PAGLOCK
READCOMMITTED
READCOMMITTEDLOCK
READPAST
READUNCOMMITTED
REPEATABLEREAD
ROWLOCK
SERIALIZABLE
SNAPSHOT
SPATIAL_WINDOW_MAX_CELLS
TABLOCK
TABLOCKX
UPDLOCK
XLOCK