In-depth understanding of Oracle indexes (18): functional index traps and how to avoid index contamination

(I) function index traps when using function indexes, you must re-build the function index after the function code is changed. Otherwise, Oracle will return an error result but will not be prompted for the following test: [plain] viewplaincopyprint? SPANstyleBACKGROUND-COLOR: rgb (102,102,102) hr @ ORCLdroptabletpurge; Tabledropped. hr @ ORCLcrea

(I) function index traps when using function indexes, be sure to re-create the function index after the function code is changed. Otherwise, Oracle will return an error result but will not prompt the following test: [plain] view plaincopyprint? SPANstyle = BACKGROUND-COLOR: rgb (102,102,102) hr @ ORCLdroptabletpurge; Tabledropped. hr @ ORCLcrea

(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:

[Plain]View plaincopyprint?

1. Hr @ ORCL> drop table t purge;
3. Table dropped.
5. Hr @ ORCL> create table t (x number, y varchar2 (30 ));
7. Table created.
9. Hr @ ORCL> insert into t select rownum, rownum | 'A' from dual connect by rownum <1000;
11. 999 rows created.
13. Hr @ ORCL> ed
14. Wrote file afiedt. buf
16. 1 create or replace function f_david (p_value varchar2) return varchar2
17. 2 deterministic is
18. 3 begin
19. 4 return p_value;
20. 5 * end;
21. 6
22. 7/
24. Function created.
26. Hr @ ORCL> create index idx_f_david _t on t (f_david (y ));
28. Index created.
30. Hr @ ORCL> exec dbms_stats.gather_table_stats (ownname => 'hr', tabname => 'T', estimate_percent => 100, cascade => TRUE, no_invalidate => false );
32. PL/SQL procedure successfully completed.
34. Hr @ ORCL> select * from t where f_david (y) = '8a ';
36. X Y
37. ----------------------------------------
38. 8 8a
40. Hr @ ORCL> ed// What is the ed splplus command?
41. Wrote file afiedt. buf
43. 1 create or replace function f_david (p_value varchar2) return varchar2
44. 2 deterministic is
45. 3 begin
46. 4 return p_value | 'B ';
47. 5 * end;
48. Hr @ ORCL>/
50. Function created.
52. /* At this time, the function f_david is not the one we know, but the query is still like this !!! */
53. Hr @ ORCL> select * from t where f_david (y) = '8a ';
55. X Y
56. ----------------------------------------
57. 8 8a
60. /* The index reconstruction query has no records. This is the correct result */
61. Hr @ ORCL> drop index idx_f_david _t;
63. Index dropped.
65. Hr @ ORCL> create index idx_f_david _t on t (f_david (y ));
67. Index created.
69. Hr @ ORCL> exec dbms_stats.gather_table_stats (ownname => 'hr', tabname => 'T', estimate_percent => 100, cascade => TRUE, no_invalidate => false );
71. PL/SQL procedure successfully completed.
73. Hr @ ORCL> select * from t where f_david (y) = '8a ';
75. No rows selected

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 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 ); // What does this sentence mean?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 varchar2) 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