Sign InTry Free

Execution Plan Binding

The Optimizer Hints document introduces how to select a specific execution plan using Hint. However, sometimes you need to interfere with execution selection without modifying SQL statements. Execution Plan Binding provides a set of functionalities to do this.

Syntax

Create binding

CREATE [GLOBAL | SESSION] BINDING FOR SelectStmt USING SelectStmt

This statement binds SQL execution plans at the GLOBAL or SESSION level. The default scope is SESSION. The bound SQL statement is parameterized and stored in the system table. When a SQL query is processed, the corresponding optimizer hint is available as long as the parameterized SQL statement and a bound one in the system table are consistent.

Parameterization is a process that converts a constant in SQL to a variable parameter, with standardized processing on the spaces and line breaks in the SQL statement, for example,

select * from t where a > 1 -- parameterized: select * from t where a >

Remove binding

DROP [GLOBAL | SESSION] BINDING FOR SelectStmt

This statement removes a specified execution plan binding at the GLOBAL or SESSION level. The default scope is SESSION.

View binding

SHOW [GLOBAL | SESSION] BINDINGS [ShowLikeOrWhere]

This statement outputs the execution plan bindings at the GLOBAL or SESSION level. The default scope is SESSION. Currently SHOW BINDINGS outputs eight columns, as shown below:

Column NameNote
original_sqlOriginal SQL statement after parameterization
bind_sqlBound SQL statement with hints
default_dbDefault database
statusStatus including Using, Deleted, and Invalid
create_timeCreating time
update_timeUpdating time
charsetCharacter set
collationOrdering rule
Download PDF
Playground
New
One-stop & interactive experience of TiDB's capabilities WITHOUT registration.
Products
TiDB
TiDB Dedicated
TiDB Serverless
Pricing
Get Demo
Get Started
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.