Quick Search:
 Oracle PL/SQL: INDEXES: Function-Based Index Jump to:  
Category: >> Oracle PL/SQL >> INDEXES: Function-Based Index  

<< lastnext >>

Snippet Name: INDEXES: Function-Based Index

Description: In Oracle, you can create function-based indexes as well as indexes on columns.

You must have the QUERY REWRITE system privilege in order to create a function-based index in your own schema on your own tables.

Also see:
» INDEXES: View table indexes
» INDEXES: Analyze Index
» INDEXES: Block Dump
» INDEXES: Rebuild Reverse
» INDEXES: ENABLE (function-based index)
» INDEXES: DISABLE (function-based index)
» INDEXES: Alter Index Parallel
» INDEXES: Alter Index Deallocate Unused
» INDEXES: Alter Index Allocate Extent
» INDEXES: Virtual / NoSegment
» INDEXES: Reverse Key Indexes
» INDEXES: Bitmap Join Indexes
» INDEXES: Bitmap Indexes
» INDEXES: Unique indexes
» INDEXES: Parallel Index
» INDEXES: Compute Statistics
» INDEXES: Alter index
» INDEXES: Single Column Non-unique
» INDEXES: Index Usage Notes
» Compressed Indexes
» Create INDEX

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 10th, 2009

-- syntax for creating a function-based index:
    CREATE [UNIQUE] INDEX index_name
      ON table_name (function1, function2, . function_n)
-- for example:
    CREATE INDEX supplier_metrics
       ON supplier (UPPER(supplier_city));
-- to be sure that the Oracle optimizer uses this index 
-- when executing your SQL statements, be sure that 
-- UPPER(supplier_city) does not evaluate to a NULL value. 
-- To ensure this, add UPPER(supplier_city) IS NOT NULL to 
-- your WHERE clause as follows:
    SELECT supplier_id, supplier_city, UPPER(supplier_city)
    FROM supplier
    WHERE UPPER(supplier_city) IS NOT NULL
    ORDER BY UPPER(supplier_city);

   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org