Database Reference
In-Depth Information
It will even respond to commands such as the following:
EODA@ORA12CR1> begin
2 dbms_stats.gather_index_stats( user, 'MYINDEX' );
3 end;
4 /
PL/SQL procedure successfully completed.
It will participate with the optimizer at runtime to determine the relative cost of using a text index over some
other index or a full scan. The interesting thing about all of this is that you or I could have developed this index.
The implementation of the text index was done without
inside kernel knowledge
. It was done using the dedicated,
documented, and exposed API. The Oracle database kernel is not aware of how the text index is stored (the APIs store
it in many physical database tables per index created). Oracle is not aware of the processing that takes place when a
new row is inserted. Oracle text is really an application built on top of the database, but in a wholly integrated fashion.
To you and me, it looks just like any other Oracle database kernel function, but it is not.
I personally have not found the need to go and build a new exotic type of index structure. I see this particular
feature as being of use mostly to third-party solution providers that have innovative indexing techniques.
I think the most interesting thing about application domain indexes is that they allow others to supply new
indexing technology I can use in my applications. Most people will never make use of this particular API to build
a new index type, but most of us will use the end results. Virtually every application I work on seems to have some
text
associated with it,
XML
to be dealt with, or
images
to be stored and categorized. The Oracle Multimedia set of
functionality, implemented using the Application Domain Indexing feature, provides these capabilities. As time passes,
the set of available index types grows. We'll take a more in-depth look at the text index in a subsequent chapter.
Invisible Indexes
In Oracle Database 11
g
and higher, you have the option of making an index invisible to the optimizer. The index is
only invisible in the sense that the optimizer won't use the index when creating an execution plan. You can either
create an index as invisible or alter an existing index to be invisible. Here we create a table, load it with test data,
generate statistics, and then create an invisible index:
EODA@ORA12CR1> create table t(x int);
Table created.
EODA@ORA12CR1> insert into t select round(dbms_random.value(1,10000)) from dual
2 connect by level <=10000;
EODA@ORA12CR1> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
EODA@ORA12CR1> create index ti on t(x) invisible;
Index created.
Now we turn on autotrace and run a query where one would expect the optimizer to use the index when
generating an execution plan:
EODA@ORA12CR1> set autotrace traceonly explain
EODA@ORA12CR1> select * from t where x=5;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2 | 8 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
