(I) functional index traps
When using the function index, you must re-create the function index after the function code is changed. Otherwise, Oracle will return an error but will not prompt you.
The test is as follows:
HR @ orcl> drop table t purge; Table dropped. HR @ orcl> Create Table T (X number, y varchar2 (30); table created. HR @ orcl> insert into T select rownum, rownum | 'A' from dual connect by rownum <1000; 999 rows created. HR @ orcl> edwrote file afiedt. buf 1 create or replace function f_david (p_value varchar2) return varchar2 2 deterministic is 3 begin 4 return p_value; 5 * end; 6 7/function created. HR @ orcl> Create I Ndex idx_f_david _t on T (f_david (y); index created. HR @ orcl> exec dbms_stats.gather_table_stats (ownname => 'hr', tabname => 'T', estimate_percent => 100, cascade => true, no_invalidate => false ); PL/SQL procedure successfully completed. HR @ orcl> select * from t where f_david (y) = '8a '; x y ---------- ------------------------------ 8 8ahr @ orcl> edwrote file afiedt. buf 1 create or replace function f_david (p_value varch AR2) return varchar2 2 deterministic is 3 begin 4 return p_value | 'B'; 5 * end; HR @ orcl>/function created. /* at this time, the function f_david is not the one we know, but the query is still like this !!! */Hr @ orcl> select * from t where f_david (y) = '8a '; x y ---------- ---------------------------- 8 8A/* The index reconstruction query has no records. This is the correct result. */hr @ orcl> drop index idx_f_david _t; index dropped. HR @ orcl> Create index idx_f_david _t on T (f_david (y); index created. HR @ orcl> exec dbms_stats.gather_table_stats (ownname => 'hr', tabname => 'T', estimate_percent => 100, cascade => true, no_invalidate => false ); PL/SQL procedure successfully completed. HR @ orcl> select * from t where f_david (y) = '8a '; No rows selected
(Ii) Avoid index contamination
Here are two comments,
① Do not add a function before the field
For example:
To_char (start_time, 'yyyy. Mm. dd') between '2017. 123' and '2017. 123'
And
Start_time between to_date ('192. 123', 'yyyy. Mm. dd') and to_date ('192. 123', 'yyyy. Mm. dd ')
It should be the second type at any time !!!
② Do not embed fields into expressions
For example:
Start_time + 7 <sysdate;
And
Start_time <sysdate-7
By David Lin
2013-06-06
Good luck