|Oracle 8i Data Cartridge Developer's Guide
Release 2 (8.1.6)
Part Number A76937-01
Design Considerations, 4 of 8
It is wrong to assume that creating domain index is always the best course. If, after careful consideration, you determine that you need to create domain index, you should keep the following factors in mind. For one, if the domain index is complex, the functional implementation will work better
Judicious use of the extensible optimizer can lead to good performance.
Naming of internal components can be an issue. Naming of internal data objects for a domain index implementation and are typically based on names you provide for table and indexes. The problem is that the derived names for the internal objects should not conflict with any other user defined object or system object. You may have to develop some policy that restricts names, or implement some metadata management scheme to avoid errors during
You can create only one index on IOTs in 8.0.x releases. However, if most of your data is in the index, it's more efficient than storing your data in both the table and then an additional index.
You can create secondary indexes on IOTs in Orace8i release 8.1.5 which offers a big advantage if you are accessing the data different ways.
Index structures can be stored in
LOBs but take care to tune the
LOB for best performance. If you are accessing a particular
LOB frequently, create your table with the
CACHE option and place the
LOB index in a separate tablespace. If you are updating a
LOGGING and read/write in multiples of
CHUNK size. If you are accessing a particular portion of a
LOB frequently, buffer your reads/writes using
LOB buffering or your own buffering scheme.
With the extensible indexing framework, the meaning and representation of a user-defined index is left to the cartridge developer. We do provide basic index implementations such as IOTs. In certain cases, binary or character
LOBs can also be used to store complex index structures. IOTs,
CLOBs all live within the database. In addition to them, you may also store a user-defined index as a structure external to the database, say in a
The external index structure gives you the most flexibility in terms of how your index is represented. It is useful if you have already invested in the development of in-memory indexing structures. For example, an operating system file may store index data, which is read into a memory mapped file at run time. Such cases may be handled as BFILEs in the external index routines.
External index structures may provide superior performance. However, this comes at some cost. Index structures external to the database do not participate in the transaction semantics of the database which, in the case of index structures inside the database, make data and concomitant index updates atomic. This means that if update to the data causes an update for the external index to be invoked via the extensible indexing interface, any failures may cause the data updates to be rolled back but not the index updates. The database can only roll back what is internal to it -- external index structures cannot be rolled back in synchronization with a database rollback.
External index structures can be very useful for read-only access. Their semantics become complex if updates to data are involved.
ODCIIndexFetch routine is called, the
ROWIDs of all the rows that satisfy the operator predicate are returned. The maximum number of rows that can be returned by the
ODCIIndexFetch routine is
nrows being an argument to the
ODCIIndexFetch routine). The value of
nrows is decided by Oracle based on some internal factors. If you have a better idea of the number of rows that ought to be returned to achieve optimal query performance, you can determine that this number of rows is returned in the
VARRAY instead of
nrows. Note that the number of values in the
ODCIRidList has to be less than or equal to
You, as cartridge designer, are in the best position to make a judgement regarding the number of rows to be returned. For example, if in the index the number of (say 1500) rowids are stored together and
nrows = 2000, then it may be optimal to return 1500 rows in lieu of 2000 rows. Otherwise the user would have to retrieve 3000 rowids, return 2000 if them and note which 1000 rowids were not returned.
If you not have any specific optimization in mind, you can use the value of
nrows to determine the number of rows to be returned. Currently the value of
nrows has been set to 2000.
Anyone implementing indexes which use callouts should use multirow fetch to fetch the largest number of rows back to the server. This offsets the cost of making the callout.