函数索引的使用细节——常数表达式(国外英语资料).docVIP

函数索引的使用细节——常数表达式(国外英语资料) The use details of function indexes -- constant expressions Personal category: Oracle performance tuning In the previous Blog, the function index Functional Index has been introduced many times. Including the function index principle, use and application scenarios. For two use details, add this knowledge point. The SQL statement is a descriptive language, and we only need to describe the data, and the database system DBMS generates execution plans, paths, and operations. The same data manipulation needs, SQL statement writing may have some differences. DBMSs SQL engine usually does some rewriting before processing SQL statements. Such as expression calculation, predicate advance and view, and so on. One of the characteristics of expression calculation is that the rewriting operation does not cross the equal sign. See below through an example: The first is environmental preparation: SQL desc t; Name, Type, Nullable, Default, Comments -------------------------------------------------- OWNER VARCHAR2 (30) Y OBJECT_NAME VARCHAR2 (128) Y SUBOBJECT_NAME VARCHAR2 (30) Y OBJECT_ID NUMBER Y (length, reason, omission, omission, etc) SQL, create, index, idx_t_id, on, t (object_id); Index created SQL exec dbms_stats.gather_table_stats (user,T, cascade = true); PL/SQL procedure successfully completed Lets look at the condition object_id1000/5 first. SQL, explain, plan, for, select * from, t, whereobject_id1000/5; Explained SQL select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 514881935 -------------------------------------------------------------------------------- Id Operation Name | | | | Rows | | Bytes Cost (%CPU) | Ti -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 179 17363 7 (0) | 00 | 1 TABLE ACCESS BY INDEX ROWID| T | | | | 179 17363 7 (0) | 00 2 INDEX RANGE SCAN |* | | IDX_T_