Function-Based Index Solution-Indexes

The concept here is that you’re building an index and applying a function to it in a way that limits the length of the index key and also results in a usable index. Here, I use the same code (as in the prior section) to create a table with an extended column and populate it with test data:
$ sqlplus eoda/foo@PDB1
SQL> create table t(x varchar2(32767)); Table created.
SQL> insert into tselect to_char(level)|| rpad(‘abc’,10000,’xyz’)from dual connect by level < 1001unionselect to_char(level)from dual connect by level < 1001; 2000 rows created.

Now suppose you’re familiar with the data and know that the first ten characters of the extended columns are usually sufficient for identifying a row; therefore, you create an index on the substring of the first ten characters and generate statistics for the table:
SQL> create index te on t(substr(x,1,10)); Index created.
SQL> exec dbms_stats.gather_table_stats(user,’T’); PL/SQL procedure successfully completed.

The optimizer can use an index like this when there are equality and range predicates in the WHERE clause. Some examples will illustrate this:
SQL> set autotrace traceonly explain
SQL> select count(*) from t where x = ‘800’;

Assuming the same table and seed data as used with the prior SUBSTR examples, here we add a function-based index using STANDARD_HASH:
SQL> create index te on t(standard_hash(x));

Now verify that an equality-based search uses the index:
SQL> set autotrace traceonly explain
SQL> select count(*) from t where x = ‘800’;

This allows for efficient equality-based searches, but does not work for range-based searches, as the data is stored in an index based on the randomized hash value.

Frequently Asked Questions and Myths About Indexes

As I said in the introduction to this book, I field lots of questions about Oracle. I am the Tom behind the “Ask Tom” column in Oracle Magazine and at http://asktom.oracle. com, where I answer people’s questions about the Oracle database and tools. In my experience, the topic of indexes attracts the most questions. In this section, I answer some of the most frequently asked questions. Some of the answers may seem like common sense, while other answers might surprise you. Suffice it to say, there are lots of myths and misunderstandings surrounding indexes.

Do Indexes Work on Views?

A related question is, “How can I index a view?” Well, the fact is that a view is nothing more than a stored query. Oracle will replace the text of the query that accesses the view with the view definition itself. Views are for the convenience of the end user or programmer—the optimizer works with the query against the base tables. Any and all indexes that could have been used if the query had been written against the base tables will be considered when you use the view. To index a view, you simply index the base tables.