Database Reference
In-Depth Information
It turns out that TO_DATE , when used with YYYY , is not deterministic! That is why the index cannot be created:
it would only work correctly in the month you created it in (or insert/updated a row in). So, it is due to the user
environment, which includes the current date itself.
To use TO_DATE in a function-based index, you must use a date format that is unambiguous and
deterministic—regardless of what day it is currently.
Function-Based Indexes Wrap-up
Function-based indexes are easy to use and implement, and they provide immediate value. They can be used to speed
up existing applications without changing any of their logic or queries. Many orders of magnitude improvement may
be observed. You can use them to precompute complex values without using a trigger. Additionally, the optimizer
can estimate selectivity more accurately if the expressions are materialized in a function-based index. You can use
function-based indexes to selectively index only rows of interest as demonstrated earlier with the PROCESSED_FLAG
example. You can, in effect, index a WHERE clause using that technique. Lastly, you can use function-based indexes to
implement a certain kind of integrity constraint: selective uniqueness (e.g., “The fields X, Y, and Z must be unique
when some condition is true”).
Function-based indexes will affect the performance of inserts and updates. Whether or not that warning is
relevant to you is something you must decide. If you insert and very infrequently query the data, this might not be
an appropriate feature for you. On the other hand, keep in mind that you typically insert a row once and you query
it thousands of times. The performance hit on the insert (which your individual end user will probably never notice)
may be offset many thousands of times by speeding up the queries. In general, the pros heavily outweigh any of the
cons in this case.
Application Domain Indexes
Application domain indexes are what Oracle calls extensible indexing . They allow you to create your own index
structures that work just like indexes supplied by Oracle. When someone issues a CREATE INDEX statement using your
index type, Oracle will run your code to generate the index. If someone analyzes the index to compute statistics on it,
Oracle will execute your code to generate statistics in whatever format you care to store them in. When Oracle parses
a query and develops a query plan that may make use of your index, Oracle will ask you how costly this function is to
perform as it is evaluating the different plans. In short, application domain indexes give you the ability to implement a
new index type that does not exist in the database as of yet. For example, if you develop software that analyzes images
stored in the database, and you produce information about the images, such as the colors found in them, you could
create your own image index. As images are added to the database, your code is invoked to extract the colors from the
images and store them somewhere (wherever you want to store them). At query time, when the user asks for all blue
images, Oracle will ask you to provide the answer from your index when appropriate.
The best example of an application domain index is Oracle's own text index . This index is used to provide
keyword searching on large text items. You may create a simple text index like this:
EODA@ORA12CR1> create index myindex on mytable(docs)
2 indextype is ctxsys.context
3 /
Index created.
And then use the text operators the creators of that index type introduced into the SQL language.
select * from mytable where contains( docs, 'some words' ) > 0;
 
Search WWH ::




Custom Search