Create an Oracle's functional index
In many cases, we want to use the index to increase efficiency, but sometimes you have to use a function to calculate, then if you use the index directly, you will find that the index is invalid. In general, we need to write a function such as the aliquot of the equal number, such as: Sal> 100 * 2.5 instead of SAL / 2.5> 100. However, in some cases, we cannot do this, you must use a function to calculate, then we need to apply Oracle's function index.
Environmental settings
Set the startup parameter / session / system level:
- Tell the optimizer trust the deterministic code of the programmer tag
Query_rewrite_integrity must be set to trusted
- Allow optimizer to use a function-based index to write inquiry
Query_rewrite_enabled must be set to true
Compatible Must Set To 8.1.0.0.0 or a Greater Value
The operation is as follows:
Log in with administrator, SYS / XXXX As Sysdba
SQL> Show parameter query_rewrite_integrity;
If not trusted,
SQL> ALTER system set query_rewrite_integrity = trusted;
Also set other parameters
ALTER system set query_rewrite_enabled = true;
2. Create a function index
CREATE INDEX IND_CHAR_XX ON TABLE (TO_CHAR (XX));
3. Re-Analysis Table Analyze
Analyze Table YYY Compute Statistics;
__________________
excerpt:
...................... ...
