This topic describes how to avoid implicit type conversion in OceanBase AI Database.
Avoid implicit type conversion
In OceanBase AI Database, when an operator is used with operands of different types, type conversion occurs to make the operands compatible. This is called implicit conversion, as the database automatically converts numbers to strings and strings to numbers as needed.
Among the SQL statements that cause offline slow queries, this type is particularly inappropriate. The business logic itself is correct; the issue arises from failing to pay attention to the data types of the fields when writing the SQL. There are mainly the following two types:
When a table is created, a column is defined as a numeric type, but the parameter value passed in a query is a character. In this case, it does not affect the execution plan, and the index will be used as expected.
Example: The following statements are used to create a table and an index.
obclient> CREATE TABLE `test1`(`id` INT,`name` VARCHAR(10)); obclient> CREATE INDEX idx_test_id ON test1(`id`) GLOBAL;The value of the input parameter is a numeric type.
In the NAME column of the execution plan, you can see that the correct index is being 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 setThe input parameter values are of the correct character type.
In the NAME column of the execution plan, you can see that the correct index is being 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
When a table is created with fields of the character type, and numeric values are passed as parameters in queries. This prevents the use of indexes and forces a full-table scan.
Example: The following statements are used to create a table and an index.
obclient> CREATE TABLE `test1` (`id` VARCHAR(10), `name` VARCHAR(10)); obclient> CREATE INDEX idx_test_id ON test1(`id`) GLOBAL;The value of the input parameter is a numeric type.
By checking the current execution plan, we can see that the
namecolumn has no index (idx_test_id) on tabletest1, and the created index is not being used in this query. The current 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 setThe input parameter values are of the correct character type.
By checking the current execution plan, we can see that the name column includes not only table test1 but also index idx_test_id(id). The current query makes good use of the created index. At this point, 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 between these two examples is that when comparing numbers and characters, the database converts the characters to numbers before making the comparison.