Skip to main content

Search

Welcome to Tibero GTS!

Impact of Bind Variable Type on Index UNIQUE SCAN

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
ScenarioBind TypeCondition ExampleReturned RowsExpected Scan Type 
ANUMBER:b := 1Up to 4 rows
Index Unique Scan
BVARCHAR
: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

ItemValue
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
ItemValue
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
 
 
 

Related to