[SQL hints Comparison]

Here is a detailed comparison of query hints between MySQL, MariaDB, and Oracle. These hints are tools used to influence the query optimizer’s decisions in each database system.

Query Hints Comparison

Hint Type	MySQL	MariaDB	Oracle	Description
Index Usage	USE INDEX	USE INDEX	INDEX	Suggests using a specific index.
	IGNORE INDEX	IGNORE INDEX	NO_INDEX	Instructs to ignore a specific index.
	FORCE INDEX	FORCE INDEX	INDEX	Forces the use of a specific index.
Join Order	STRAIGHT_JOIN	STRAIGHT_JOIN	ORDERED	Forces join order to be as written in the query.
Join Methods	N/A	USE_MERGE	USE_MERGE	Suggests using a merge join.
	N/A	USE_HASH	USE_HASH	Suggests using a hash join.
	N/A	USE_NL	USE_NL	Suggests using a nested loop join.
Parallel Execution	N/A	N/A	PARALLEL, NO_PARALLEL	Controls parallel execution.
Table Scans	N/A	N/A	FULL, NO_FULL	Forces or prevents a full table scan.
Query Caching	SQL_NO_CACHE	SQL_NO_CACHE	NO_RESULT_CACHE	Prevents results from being stored in the result cache.
Priority Execution	HIGH_PRIORITY	HIGH_PRIORITY	N/A	Gives the query high priority over others.
Buffering Results	SQL_BUFFER_RESULT	SQL_BUFFER_RESULT	N/A	Forces the server to buffer the results of the query.
Result Size Hint	SQL_BIG_RESULT	SQL_BIG_RESULT	N/A	Informs the optimizer that the result set will be large.
	SQL_SMALL_RESULT	SQL_SMALL_RESULT	N/A	Informs the optimizer that the result set will be small.
Row Calculation	SQL_CALC_FOUND_ROWS	SQL_CALC_FOUND_ROWS	N/A	Tells the server to calculate the total number of rows without LIMIT.
Join Cache	N/A	SQL_NO_JOIN_CACHE	NO_MERGE, NO_PUSH_SUBQ	Prevents the optimizer from caching join results.
Optimizing for Row Retrieval	HIGH_PRIORITY	HIGH_PRIORITY	FIRST_ROWS, ALL_ROWS	Optimizer mode hints for prioritizing either quick retrieval of first rows or all rows.
Leading Table in Joins	N/A	N/A	LEADING	Suggests the leading table in a join.
Driving Site	N/A	N/A	DRIVING_SITE	Specifies which table in a distributed query should drive the execution.
Transformation Rules	N/A	N/A	STAR_TRANSFORMATION, NO_STAR_TRANSFORMATION	Controls star transformation for star schemas.
Subquery Optimization	N/A	N/A	PUSH_SUBQ, NO_PUSH_SUBQ	Controls subquery pushdown optimization.
Row Prefetching	N/A	N/A	PREFETCH, NO_PREFETCH	Controls the prefetching of rows for queries.
Direct-Path Insert	N/A	N/A	APPEND	Controls direct-path insert.
Result Caching	N/A	N/A	RESULT_CACHE, NO_RESULT_CACHE	Controls result caching for the query.

Key Points:

1. MySQL and MariaDB:

   • MySQL and MariaDB share many of the same hints because MariaDB was originally forked from MySQL. However, MariaDB has added some hints that are not present in MySQL, particularly related to join methods like USE_MERGE, USE_HASH, and USE_NL.
   • Both databases offer basic hints like USE INDEX, IGNORE INDEX, and FORCE INDEX for index control, and STRAIGHT_JOIN for join order enforcement.
   • Parallel execution hints and more advanced query manipulation hints (e.g., for star schemas) are not available in MySQL/MariaDB.

2. Oracle:

   • Oracle offers a much more extensive and flexible set of hints compared to MySQL/MariaDB. These include parallel execution control (PARALLEL), specific join method hints (USE_NL, USE_MERGE, USE_HASH), and query transformation hints (STAR_TRANSFORMATION, PUSH_SUBQ).
   • Oracle hints can also control specific execution details like caching (RESULT_CACHE), prefetching (PREFETCH), and optimizer modes (ALL_ROWS, FIRST_ROWS).

Summary

• MySQL/MariaDB: Focus primarily on index usage and join order hints. They offer fewer advanced query optimization and execution hints compared to Oracle.
• Oracle: Provides a comprehensive set of hints that give fine-grained control over nearly all aspects of query execution, from parallelism to result caching, making it much more flexible for complex queries and large-scale applications.