How will that impact the time required to drop the table? (This is
less of a consideration if you are using locally managed tablespaces, which you should
be using if you??™re running Oracle Database 10g or later.)
Function-based indexes are useful, but you should implement them sparingly. The more
indexes you create on a table, the longer all insert, update, and delete operations will take. Of
course, this applies to creating any additional indexes on a table, regardless of type.
Text indexes use Oracle??™s text options (Oracle Text) to create and manage lists of words and
their occurrences??”similar to the way a book??™s index works. Text indexes are most often used to
support applications that perform searches on portions of words with wildcards.
Partitioned tables can have indexes that span all partitions (global indexes) or indexes that are
partitioned along with the table partitions (local indexes). From a query-tuning perspective, local
indexes may be preferable because they contain fewer entries than global indexes.
Generating Explain Plans
How can you determine which access path the database will use to perform a query? This
information can be viewed via the explain plan command. This command will evaluate the
execution path for a query and will place its output into a table (named PLAN_TABLE) in the
database. A sample explain plan command is shown in the following listing:
explain plan
for
select *
from BOOKSHELF
where Title like 'M%';
The first line of this command tells the database that it is to explain its execution plan for the query
without actually executing the query.
Pages:
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421