Indexes - Expert Oracle Database Architecture - page 474

cpu time = .2
function was called: 9916
PL/SQL procedure successfully completed.

We can see this query took two-tenths of a CPU second to execute and had to do a full scan on the table. The
function MY_SOUNDEX was invoked almost 10,000 times (according to our counter). We'd like it to be called much less
frequently, however.

■ In older releases (pre-Oracle 10 g release 2), the function would be called many more times than observed
above. In fact, it would be invoked approximately 20,000 times—twice for each row! Oracle 10 g release 2 and above
use the DETERMINISTIC hint to reduce the number of times it feels inclined to invoke the function.

Note

Let's see how indexing the function can speed up things. The first thing we'll do is create the index as follows:

EODA@ORA12CR1> create index emp_soundex_idx on
2 emp( substr(my_soundex(ename),1,6) )
3 /
Index created.

The interesting thing to note in this CREATE INDEX command is the use of the SUBSTR function. This is because
we are indexing a function that returns a string. If we were indexing a function that returned a number or date, this
SUBSTR would not be necessary. The reason we must SUBSTR the user-written function that returns a string is that such
functions return VARCHAR2(4000) types. That may well be too big to be indexed—index entries must fit within about
three quarters the size of a block. If we tried, we would receive (in a tablespace with a 4KB block size) the following:

EODA@ORA12CR1> create index emp_soundex_idx on
2 emp( my_soundex(ename) ) tablespace ts4k;
emp( my_soundex(ename) ) tablespace ts4k
*
ERROR at line 2:
ORA-01450: maximum key length (3118) exceeded

It is not that the index actually contains any keys that large, but that it could as far as the database is concerned.
But the database understands SUBSTR . It sees the inputs to SUBSTR of 1 and 6, and knows the biggest return value
from this is six characters; hence, it permits the index to be created. This size issue can get you, especially with
concatenated indexes. Here is an example on an 8KB block size tablespace:

EODA@ORA12CR1> create index emp_soundex_idx on
2 emp( my_soundex(ename), my_soundex(job) );
emp( my_soundex(ename), my_soundex(job) )
*
ERROR at line 2:
ORA-01450: maximum key length (6398) exceeded

The database thinks the maximum key size is 8,000 bytes and fails the CREATE statement once again. So, to index a
user-written function that returns a string, we should constrain the return type in the CREATE INDEX statement. In this
example, knowing that MY_SOUNDEX returns at most six characters, we are substringing the first six characters.