Parameterization is the process of replacing constants in an SQL query with variables.
Obtain an execution plan based on fast parameterization
The same SQL statement may use different parameters during each execution. By parameterizing these parameters, you can obtain an SQL string that is independent of specific parameters. This string is then used as the key value for retrieving an execution plan from the plan cache, enabling SQL statements with different parameters to share the same execution plan.
In traditional databases, parameterization typically involves parameterizing a syntax tree and then using the parameterized syntax tree as the key value to retrieve a plan from the plan cache. In contrast, OceanBase AI Database directly parameterizes a text string using lexical analysis and uses this string as the key value for the plan cache, which is why it is called fast parameterization.
The advantages of the execution plan cache based on fast parameterization are as follows:
It eliminates the need for the syntax analysis step.
When searching the hash map, it replaces the hashing and comparison operations on the parameterized syntax tree with hashing and
MEMCMPoperations on the text string, improving execution efficiency.
Constant parameterization and constraints
In OceanBase AI Database, constants cannot be parameterized in certain scenarios. The constraints are as follows:
All constants after
ORDER BY(for example,ORDER BY 1,2;).All constants after
GROUP BY(for example,GROUP BY 1,2;).String constants used as format strings (for example,
%din the SQL statementSELECT DATE_FORMAT('2006-06-00', '%d');).Input parameters to functions that affect the function's result and ultimately the execution plan (for example,
NUMBER(2,1)in the SQL statementCAST(999.88 as NUMBER(2,1)), or1and2inSUBSTR('abcd', 1, 2)).Input parameters to functions that contain implicit information and ultimately affect the execution plan (for example, "2015-11-13 10:20:19.012" in the SQL statement
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');, which implicitly specifies the precision value for the function's processing to milliseconds while specifying the input timestamp).
General constant parameterization example
The following is an example of an existing SQL query:
obclient> SELECT * FROM t1 WHERE c1 = 5 AND c2 ='oceanbase';
After parameterization within OceanBase AI Database, the SQL query in the example above results in the following: the constants 5 and oceanbase are parameterized into variables @1 and @2, respectively. The system then compares these with previously executed SQL statements in the current database to match the corresponding execution plan.
obclient> SELECT * FROM T1 WHERE c1 = @1 AND c2 = @2;
Example where constant parameterization is not supported
However, not all constants can be parameterized during plan matching. For example, constants after ORDER BY indicate the column to sort by among those projected in the SELECT statement, so they cannot be parameterized.
Create the sample table t1 and insert appropriate data. Table t1 contains columns c1 and c2, where c1 is the primary key column.
obclient> CREATE TABLE t1(c1 INT PRIMARY KEY,c2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES (1,2);
Query OK, 1 row affected
obclient> INSERT INTO t1 VALUES (2,1);
Query OK, 1 row affected
obclient> INSERT INTO t1 VALUES (3,1);
Query OK, 1 row affected
Execute the following SQL query to sort the results by column
c1. Sincec1is a primary key column and is ordered, accessing by primary key eliminates the need for sorting.obclient> SELECT c1, c2 FROM t1 ORDER BY 1; +----+------+ | C1 | C2 | +----+------+ | 1 | 2 | | 2 | 1 | | 3 | 1 | +----+------+ 3 rows in set obclient> EXPLAIN SELECT c1, c2 FROM t1 ORDER BY 1; Query Plan: | =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t1 |1000 |1381| =================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1], [T1.C2]), filter(nil), access([T1.C1], [T1.C2]), partitions(p0)Execute the following SQL query to sort the results by column
c2, which requires an explicit sorting operation. The execution plan is shown in the following example:obclient> SELECT c1, c2 FROM t1 ORDER BY 2; +----+------+ | C1 | C2 | +----+------+ | 2 | 1 | | 3 | 1 | | 1 | 2 | +----+------+ 3 rows in set obclient> EXPLAIN SELECT c1, c2 FROM t1 ORDER BY 2; Query Plan: | ==================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------ |0 |SORT | |1000 |1886| |1 | TABLE SCAN|t1 |1000 |1381| ==================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1], [T1.C2]), filter(nil), sort_keys([T1.C2, ASC]) 1 - output([T1.C1], [T1.C2]), filter(nil), access([T1.C1], [T1.C2]), partitions(p0)
Therefore, if the constants after ORDER BY are parameterized, different ORDER BY values will have the same parameterized SQL, potentially leading to incorrect plan matching.
Resolve constant parameterization mismatch issues
To address potential mismatch issues in constant parameterization, OceanBase AI Database uses analytical syntax tree methods during hard parse to parameterize SQL requests and identify inconsistencies. For example, information corresponding to a statement that "the third element of the fast-parameterized parameter array must be the number 3" can be referred to as a "constraint."
For the Q1 query SELECT c1, c2, c3 FROM t1 WHERE c1 = 1 AND c2 LIKE 'senior%' ORDER BY 3;, after lexical analysis, the parameterized SQL statement is obtained as follows, with the parameterized array being {1, 'senior%', 3}.
obclient> SELECT c1, c2, c3 FROM t1 WHERE c1 = @1 AND c2 LIKE @2 ORDER BY @3;
When the constants after ORDER BY are different, they cannot share the same execution plan. Therefore, another parameterization result is obtained during parsing via the syntax tree. As shown in the following example, the parameterized array for Q1 query corresponds to {1, 'senior'}, and the constraint is "the third element of the fast-parameterized parameter array must be the number 3". OceanBase AI Database stores the newly generated parameterized text, constraints, and execution plan for Q1 query into the plan cache.
obclient> SELECT c1, c2, c3 FROM t1 WHERE c1 = @1 AND c2 LIKE @2 ORDER BY 3;
When the user issues the Q2 query command again SELECT c1, c2, c3 FROM t1 WHERE c1 = 1 AND c2 LIKE 'senior%' ORDER BY 2;, the result after fast parameterization is as follows, with the corresponding parameterized array being {1, 'senior%', 2}.
obclient> SELECT c1, c2, c3 FROM t1 WHERE c1 = @1 and c2 like @2 ORDER BY @3;
This is the same SQL result after fast parameterization for Q1 query, but since it does not satisfy the constraint "the third element of the fast-parameterized parameter array must be the number 3", it cannot match that plan. At this point, Q2 generates a new execution plan and constraints (i.e., "the third element of the fast-parameterized parameter array must be the number 2") through hard parse, and adds the new plan and constraints to the plan cache. This way, the correct execution plan will be hit for both Q1 and Q2 queries next time they are executed.
Strategy for distinguishing between parameterized and non-parameterized execution plans
OceanBase AI Database also supports controlling whether literals in SQL statements undergo parameter rewriting using hints.
For an SQL request, use the following strategy to determine whether this SQL should hit a parameterized plan or a non-parameterized plan:
Check
cursor_sharingFirst, check the
cursor_sharingsystem variable. SQL statements with differentcursor_sharingvalues will definitely hit different plans. See cursor_sharing.Check
query_sqlIf the
cursor_sharingvariable cannot help us determine whether it is a parameterized plan, it meanscursor_sharingis inEXACTmode, which is controlled by theCURSOR_SHARING_EXACThint. Since adding a hint affects the generation ofquery_sql, we can usequery_sqlto distinguish between parameterized and non-parameterized plans.query_sqlcan be viewed through theGV$OB_SQL_AUDITorGV$OB_PLAN_CACHE_PLAN_STATview.