index baseado em função

[burgaParticipante]

Oi Souza,

Recorrendo novamente ao site do Tom, segue o que é necessário para criar índices baseados em funções:

How to use this

Above we now have most of the steps you need to use function based indexes. In addition to the above steps, there are some init.ora or session settings you must use and a privelege you must have. The following is a list of what needs to be done to use function based indexes:

* You must have the system privelege query rewrite to create function based indexes on tables in your own schema.
* You must have the system privelege global query rewrite to create function based indexes on tables in other schemas
* For the optimizer to use function based indexes, the following session or system variables must be set:

·

· QUERY_REWRITE_ENABLED=TRUE

· QUERY_REWRITE_INTEGRITY=TRUSTED

You may enable these at either the session level with ALTER SESSION or at the system level via ALTER SYSTEM or by setting them in the init.ora parameter file. The meaning of query_rewrite_enabled is to allow the optimizer to rewrite the query allowing it to use the function based index. The meaning of query_rewrite_integrity=trusted is to tell the optimizer to ‘trust’ that the code marked deterministic by the programmer is in fact deterministic. If the code is in fact not deterministic (that is, it returns different output given the same inputs), the resulting rows from the index may be incorrect.

* Use the Cost Based Optimizer. Function based indexes are only visible to the Cost Based Optimizer and will not be used by the Rule Based Optimizer ever.
* Use substr() to constrain return values from user written functions that return VARCHAR2 or RAW types. Optionally hide the substr in a view (recommended).

Once the above list has been satisfied, it is as easy as “CREATE INDEX” from there on in. The optimizer will find and use your indexes at runtime for you.

Fonte: http://asktom.oracle.com/pls/apex/ASKTOM.download_file?p_file=8597570332252006617

Espero que ajude…
Abraços!!