Query rewrite an one of Query Transformations which makes possible to use materialized view to get results instead of querying underlying tables.
Basic prerequisites for query rewrite described in corresponding chapter of a Database Data Warehousing Guide. Let’s start with a simple example to show how it works.
In this I’ll do a quick performance comparison of built-in Oracle Text index versus the one created using Extensible indexing interface (described in this post).
First of all, Oracle Text option must be installed to use built-in text indexes. This functionality is getting installed under CTXSYS schema.
Speaking about DIY text indexes, it’s mandatory to have privileges to create index types and operators or simply RESOURCE role.