tencent cloud

TDSQL Boundless

Release Notes
Product Introduction
Overview
Scenarios
Product Architecture
Instance Types
Compatibility Notes
Kernel Features
Kernel Overview
Kernel Version Release Notes
Functionality Features
Performance Features
Billing
Billing Overview
Purchase Method
Pricing Details
Renewal
Overdue Payments
Refund
Getting Started
Creating an Instance
Connect to Instances
User Guide
Data Migration
Data Subscription
Instance Management
Configuration Change
Parameter Configuration
Account Management
Security Group
Backup and Restoration
Database Auditing
Tag Management
Use Cases
Technical Evolution and Usage Practices of Online DDL
Lock Mechanism Analysis and Troubleshooting Practices
Data Intelligent Scheduling and Related Practices for Performance Optimization
TDSQL Boundless Selection Guide and Practical Tutorial
Developer Guide
Developer Guide (MySQL Compatibility Mode)
Developer Guide (HBase Compatibility Mode)
Performance Tuning
Performance Tuning Overview
SQL Tuning
DDL Tuning
Performance White Paper
Performance Overview
TPC-C Test
Sysbench Test
API Documentation
History
Introduction
API Category
Making API Requests
Instance APIs
Security Group APIs
Task APIs
Backup APIs
Rollback APIs
Parameter APIs
Database APIs
Data Types
Error Codes
General Reference
System Architecture
SQL Reference
Database Parameter Description
TPC-H benchmark data model reference
Error Code Information
Security and Compliance
FAQs
Agreements
Service Level Agreement
Terms of Service
Privacy Policy
Data Processing And Security Agreement
Contact Us
Glossary
DokumentasiTDSQL BoundlessGeneral ReferenceSQL ReferenceBasic ElementsOptimizer HintsJoin Order Hint for the Column-Store Vectorized Engine

Join Order Hint for the Column-Store Vectorized Engine

PDF
Mode fokus
Ukuran font
Terakhir diperbarui: 2026-02-10 11:06:28

Description

For Join Order Hint, specify the generation of plan fragments of the columnar vectorization engine. When the vectorization engine is selected as the executor, add MySQL-style Join Order Hint to the query's SQL statement to pass it to the vectorization engine. Currently, three Join Order-related hints are supported: join_order, join_prefix, and join_suffix.
Type
Scope of Application
Location Constraint
Use Cases
join_order
fragment of the full table join order
intermediate position in the plan tree
Specify the path for multi-table joins
join_prefix
Enforce left-deep tree order for prefix tables
leftmost end of the plan tree
Prioritize the processing of dimension tables
join_suffix
Enforce right-deep tree order for suffix tables
rightmost end of the plan tree
Delaying large table joins

join_order

The join_order Hint type attempts to fix the join order of the specified tables into a fragment of a left-deep tree order. Tables not specified are joined according to the default optimizer logic and this fragment.
Example 1
SELECT /*+join_order(t2,t1)*/ * FROM t1 JOIN t2 ON t1.a=t2.a;
Analysis of the Execution Plan:
| physical_plan
 -> Projection: a a (rows=*)
   -> Inner Hash Join (a = a) (rows=*)
     -> Tdstore Scan Text: test.t1 Projections: a (rows=*)
     -> Tdstore Scan Text: test.t2 Projections: a (rows=*)
The join order of all tables is specified. In the case of Hash Join, t2 is the build side and t1 is the probe side, which is consistent with the MySQL execution plan.
Example 2
SELECT /*+join_order(t2,t1)*/ * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t2.a=t3.a;
Analysis of the Execution Plan:
 -> Inner Hash Join (a = a) (rows=*)
   -> Tdstore Scan Text: test.t1 Projections: a (rows=*)
   -> Tdstore Scan Text: test.t2 Projections: a (rows=*)
The above plan is treated as a plan fragment. The position of the t3 table within the plan is undetermined and will be decided by the optimizer.

join_prefix

In addition to the function of join_order, the specified plan fragment will appear at the leftmost end of the entire plan tree (bottom of the displayed plan).
Syntax Examples
SELECT /*+join_prefix(t2,t1)*/ * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t2.a=t3.a;
Analysis of the Execution Plan:
| physical_plan
 -> Projection: a a a (rows=*)
   -> Inner Hash Join (a = a) (rows=*)
     -> Tdstore Scan Text: test.t3 Projections: a (rows=*)
     -> Inner Hash Join (a = a) (rows=*)
       -> Tdstore Scan Text: test.t1 Projections: a (rows=*)
       -> Tdstore Scan Text: test.t2 Projections: a (rows=*)
The join order of t1 and t2 is specified and fixed at the leftmost position of the plan.

join_suffix

In addition to the function of join_order, the specified plan fragment will appear at the rightmost end of the entire plan tree (top of the displayed plan).
Syntax Examples
SELECT /*+join_suffix(t2,t1)*/ * FROM t1 JOIN t2 ON t1.a=t2.a JOIN t3 ON t2.a=t3.a;
Analysis of the Execution Plan:
| physical_plan
 -> Projection: a a a (rows=*)
   -> Inner Hash Join (a = a) (rows=*)
     -> Tdstore Scan Text: test.t1 Projections: a (rows=*)
     -> Inner Hash Join (a = a) (rows=*)
       -> Tdstore Scan Text: test.t2 Projections: a (rows=*)
       -> Tdstore Scan Text: test.t3 Projections: a (rows=*)
The join order of t1 and t2 is specified and fixed to the rightmost position of the plan.

Notes

Table names in hints are case-insensitive and only table names can be specified, not query block names.
In a single Hint, one or more tables can be specified, where join_order takes effect when more than two tables are designated. Multiple hints can coexist. For example, all the following hints are effective:
SELECT /*+join_prefix(t2,t1) join_order(t3,t5,t4)*/ * FROM  t1 JOIN t2 ON t1.a=t2.a
    JOIN t3 ON  t2.a=t3.a
    JOIN t4 ON  t3.a=t4.a
    JOIN t5 ON t4.a=t5.a;
A single join_prefix or join_suffix Hint is allowed per SQL query. If multiple Hints of the same type exist, only the first one takes effect while the rest are ignored.
When Hints conflict with each other, the Hint specified later will be ignored. For example, the following second Hint will not take effect:
SELECT /*+join_order(t2,t1) join_order(t1,t2)*/ * FROM t1 JOIN t2 ON t1.a=t2.a;
If two tables lack join conditions and are adjacent in the Join Order Hint, the Hint will be ignored. For example, the following Hint is invalid:
SELECT /*+join_order(t2,t1)*/ * FROM t1,t2;
Topik sebelumnya: INDEX_FOR_GROUPBYTopik selanjutnya: CURRENT_GLOBAL_TIMESTAMP

Bantuan dan Dukungan

Apakah halaman ini membantu?

masukan