This topic describes how to avoid implicit type conversion in OceanBase Database.
Avoid implicit type conversion
OceanBase Database automatically converts data types when operators are used with operands of different types. For example, it converts numbers to strings and strings to numbers as needed.
In SQL statements that cause slow queries, implicit type conversion is often the root cause. The business logic itself is correct, but the SQL statement does not specify the correct data types for the fields. The following two types of SQL statements are common:
The field is a numeric type when the table is created, and a character value is passed as a parameter in the query. In this case, the execution plan is not affected, and the query will use the index.
Example: The following statements are used to create a table and create an index.
obclient> CREATE TABLE `test1`(`id` INT,`name` VARCHAR(10)); obclient> CREATE INDEX idx_test_id ON test1(`id`) GLOBAL;A numeric value is passed as a parameter.
In the execution plan, the NAME column indicates that the correct index is used.
obclient> EXPLAIN SELECT * FROM test1 WHERE id=100; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------- |0 |TABLE SCAN|test1(idx_test_id)|1 |92 | ================================================= Outputs & filters: ------------------------------------- 0 - output([test1.id], [test1.name]), filter(nil), access([test1.id], [test1.name]), partitions(p0) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in setA correct character value is passed as a parameter.
In the execution plan, the NAME column indicates that the correct index is used.
obclient> EXPLAIN SELECT * FROM test1 WHERE id='100'; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------- |0 |TABLE SCAN|test1(idx_test_id)|1 |92 | ================================================= Outputs & filters: ------------------------------------- 0 - output([test1.id], [test1.name]), filter(nil), access([test1.id], [test1.name]), partitions(p0) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
The field is a character type when the table is created, and a numeric value is passed as a parameter in the query. In this case, the query will not use the index and will scan the entire table.
Example: The following statements are used to create a table and create an index.
obclient> CREATE TABLE `test1` (`id` VARCHAR(10), `name` VARCHAR(10)); obclient> CREATE INDEX idx_test_id ON test1(`id`) GLOBAL;A numeric value is passed as a parameter.
The current execution plan indicates that the query does not use the created index. The NAME column only shows the table test1, and the index idx_test_id(id) is not used. The current query does not use the created index. The COST value is 408.
obclient> EXPLAIN SELECT * FROM test1 WHERE id=100; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ==================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------ |0 |TABLE SCAN|test1|5 |408 | ==================================== Outputs & filters: ------------------------------------- 0 - output([test1.id], [test1.name]), filter([cast(test1.id, DECIMAL(-1, -1)) = ?]), access([test1.id], [test1.name]), partitions(p0) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in setA correct character value is passed as a parameter.
The current execution plan indicates that the query uses the created index. The NAME column shows both the table test1 and the index idx_test_id(id). The current query uses the created index. The COST value is 92, which is significantly lower.
obclient> EXPLAIN SELECT * FROM test1 WHERE id='100'; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------- |0 |TABLE SCAN|test1(idx_test_id)|1 |92 | ================================================= Outputs & filters: ------------------------------------- 0 - output([test1.id], [test1.name]), filter(nil), access([test1.id], [test1.name]), partitions(p0) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
The reason for the difference in the two examples is that when a numeric value and a character value are compared, the database converts the character value to a numeric value before the comparison.