Document Type | Technical Information
Category | Tuning
Applicable Product Versions | 6FS06, 6FS07, 6FS07PS
Document Number | TTUTI007
Overview
In Tibero, depending on the WHERE clause condition, an UNIQUE SCAN using an index is performed, which greatly affects query performance.
However, if the bind variable type does not match the index column type, the expected UNIQUE SCAN may not be performed, and the operation can change to a full index scan (similar to an Index Full Scan).
This document experiments with differences in execution plans according to bind variable types and suggests practical response strategies.
Method
Experiment Environment and Scenario
Experiment Data Setup
DROP TABLE t; CREATE TABLE t ( c1 VARCHAR(20) UNIQUE );
INSERT INTO t VALUES ('001');
INSERT INTO t VALUES ('1.0');
INSERT INTO t VALUES ('1.00');
INSERT INTO t SELECT TO_CHAR(level) FROM dual CONNECT BY level < 1000000;
COMMIT;Experiment Scenario Comparison
| Scenario | Bind Type | Condition Example | Returned Rows | Expected Scan Type |
|---|---|---|---|---|
| A | NUMBER | :b := 1 | Up to 4 rows | Index Unique Scan |
| B | VARCHAR | :b := '1.0' | 1 | Index Unique Scan |
Execution Plan Comparison and Performance Analysis
1. Scenario A_Bind Type: NUMBER
VAR b NUMBER; EXEC :b := 1; SET AUTOT ON EXP STAT PLANS SELECT * FROM t WHERE c1 = :b;
Execution Plan
1 COLUMN PROJECTION (Cost:3, %%CPU:0, Rows:1)
2 INDEX (UNIQUE SCAN): _SYS_CON1105700182 (Cost:3, %%CPU:0, Rows:1)
Execution Stat
1 COLUMN PROJECTION (Time:.03 ms, Rows:4, Starts:1)
2 INDEX (UNIQUE SCAN): _SYS_CON1105700182 (Time:437.43 ms, Rows:4, Starts:1)
Predicate Information
2 - access: ("T"."C1" = :B) (0.000)
Additional Statistics
| Item | Value |
|---|---|
db block gets | 147 |
consistent gets | 2,395 |
physical reads | 0 |
redo size | 0 |
sorts (disk) | 0 |
sorts (memory) | 6 |
rows processed | 4 |
2. Scenario B_Bind Type: VARCHAR
VAR b varchar(10); EXEC :b := '1.0'; SET AUTOT ON EXP STAT PLANS SELECT * FROM t WHERE c1 = :b;
Execution Plan
1 COLUMN PROJECTION (Cost:3, %%CPU:0, Rows:1)
2 INDEX (UNIQUE SCAN): _SYS_CON1105700182 (Cost:3, %%CPU:0, Rows:1)Execution Stat
1 COLUMN PROJECTION (Time:.01 ms, Rows:1, Starts:1)
2 INDEX (UNIQUE SCAN): _SYS_CON1105700182 (Time:.05 ms, Rows:1, Starts:1)
Predicate Information
2 - access: ("T"."C1" = :B) (0.000)2 - access: ("T"."C1" = :B) (0.000)Additional Statistics
| Item | Value |
|---|---|
db block gets | 0 |
consistent gets | 15 |
physical reads | 0 |
redo size | 60 |
sorts (disk) | 0 |
sorts (memory) | 1 |
rows processed | 1 |
Conditions Causing the Problem
If none of the following conditions are met, implicit casting occurs due to the bind variable type, which disables the original behavior of the UNIQUE SCAN and changes it to an operation similar to a full index scan.
Condition Number | Condition Description |
|---|---|
1 | When the index key and the bind parameter types are identical |
2 | When both the index key and bind parameter are string types (char, varchar, long, clob, nchar, nvarchar, nclob) |
3 | When the type precedence between the index key and bind parameter favors the key type |
Practical Response Strategies
Strategy | Description |
|---|---|
Maintain Type Consistency | Ensure exact matching of the bind variable and index column types |
Avoid Explicit Casting | Usage of TO_CHAR, TO_NUMBER, etc., can cause index disablement |
Check Execution Plans | This case is difficult to verify by plan alone; confirm the normal state of consistent reads (cr reads) |
Standardize Development | Establish coding standards to ensure bind variable type consistency |
Summary
Index Efficiency Comparison
Comparison Item (Bind Type) | Impact |
|---|---|
When matching the column type | Accurate unique scan, superior performance |
When not matching the column type | Casting leads to multiple matches โ performance degradation |
Flowchart of Bind Type and Index Matching
[ Column Type: VARCHAR ]
โ
โโโ Bind Type: NUMBER (:b := 1)
โ โ Implicit casting
โ โ Multiple string matches ('001', '1', '1.0', '1.00')
โ โ Multiple rows returned โ Similar to Index Full Scan
โ
โโโ Bind Type: VARCHAR (:b := '1.0')
โ Type match
โ Exact value match ('1.0')
โ Index Unique Scan โ Fast processing