Databases Reference
In-Depth Information
text indexes, which are commonly used to facilitate searching on large text items. In order to create a text
index, you include the
indextype
clause in a
create index
statement, as shown here:
SQL> create index test_idx2 on test_tab(desc)
2* indextype is ctxsys.context;
Index created.
SQL>
Once you create a text index, you can use text operators to query the text columns.
SQL>select * from test_tab where contains(desc,'word pattern') >0;
You can gather statistics on the text index just as you would with a regular index, by invoking the
DBMS_STATS.GATHER_INDEX_STATS
procedure. Although we discuss application domain indexes briefly
here, for the most part, these indexes are of more interest to third-party solution providers that need to
use innovative indexing solutions to access various types of non-traditional data, such as text and
images. When you're using certain types of data such as video clips, for example, they may not fit very
well into Oracle data types. In addition, these special types of data may need special kinds of operators,
for example, to grade the colors in images. You can define these special types of operators using
comparison operators that enable the grading of colors in an image.
Domain indexes are mostly relevant to applications you implement with Oracle's data cartridges.
For more information on implementing domain indexes, check out the
Oracle Database Cartridge
Developer's Guide
.
Summary
This chapter provided an introduction to various specialized indexes that you can create in an Oracle
Database to enhance performance and make index management simpler. You learned how to use
invisible indexes to test the efficiency of indexes. This chapter showed you how to take advantage of
function-based indexes to influence the optimizer's choice of indexes during the execution of queries
where the predicates involve a function on a column. This chapter also explained the benefits of key
compression and how and when to compress indexes. Composite indexes are very important in many
applications and you learned the advantages of using composite indexes. This chapter showed you both
how to create and manage indexes on virtual columns, as well as how to create a virtual or fake index
that doesn't utilize any storage. Reverse keys are critical in avoiding contention on hot blocks, especially
in an Oracle RAC environment. This chapter explained in detail when and how to create reverse key
indexes. It also listed the disadvantages involved in the use of reverse key indexes.
