Don't let forced type conversions steal performance

This topic: effect of mandatory type conversion performance

Some table structure default values and SQL statements are found to be inconsistent with field types during SQL Trace.

Although the business logic is not wrong, it causes index invalidation or increases the complexity of optimization. The incorrect effect of the table structure default value is more significant because he affects many of the associated SQL statements,

Problem One: Table structure default value type error

Embarrassment: Is there a large amount of business data, is the conversion table default value or the conversion type of function index optimization?

Example: Same char (1) type, some default value is 0, some ' 0 '

CREATE TABLE Status_note

(

CONTAINER_ID number NOT NULL,

......

Is_print CHAR (1) Default 0,

Check_result CHAR (1) Default ' 0 ',

Deleted_flag CHAR (1) Default 0,

)

Analysis: This table has an indexed column on the Deleted_flag, but the Oracle optimizer prompts: the predicate to_number ("CS".) Deleted_flag ") =0

Used at line ID 6 of the execution plan contains a implicit data type conversion on indexed column "Deleted_flag".

Because there is a forced type conversion on the index column that causes the index to fail, the cost of the execution plan for an SQL statement on the table is 2023.

And if the field type is correct, then the cost value will drop to at least 613, so easy to improve several times performance, why not.

Problem two: field type error in SQL statement

Example: Deleted_flag is a char type, but SQL statement exact and integer comparisons

Select T.id,t.msg_type,t.content,t.modi_date,t.deleted_flag

From Send_control t

Where (t.state= ' 0 ')--or t.state= ' 2 ')

and Func_available_date (t.create_date,t.try_times) <=sysdate and t.deleted_flag=0

Analysis: This table does not have an index created, and the Oracle Optimizer reminds consider running the Access Advisor to improve the physical schema or creating the Recommende D index.

Send_control ("state", To_number ("Deleted_flag"))

Prompt to create ("state", To_number ("Deleted_flag") index, obviously, because of the deleted_flag=0 in the SQL statement that caused the coercion type conversion.

If the function index is created as described in the optimizer, then the other correct SQL statements are forced type conversions, in which case the wrong SQL statement needs to be reversed.

Optimize Front statement cost:

SELECT STATEMENT, GOAL = all_rows cost=855 cardinality=1 bytes=134

TABLE ACCESS Full Object Owner=suzhou object Name=sz_send_control cost=855 cardinality=1 B ytes=134

Optimized statement cost:

SELECT STATEMENT, GOAL = all_rows cost=2 cardinality=1 bytes=134

TABLE ACCESS by INDEX ROWID Object Owner=suzhou object Name=send_control cost=2 cardinality=1 Byte s=134

INDEX RANGE SCAN Object Owner=suzhou object Name=test1 cost=1 Cardinality=1

The statement is optimized before the cost of 855, and if the statement field type is correct, the index of the zodiac, then it will be reduced to 2, increase hundreds of times times the performance, how fast.