Oracle function based index

Richard Foote's Blog

Slides explaining implications and performance of function indexes and computed columns.


Optimization steps:

- Execute show plan before optimization - record and review
- Check row count of tables in where clause to see impact of full table scans
- Measure query performance to see if improvement is necessary
- Index and or rewrite queries as necessary
- Execute show plan to validate index selection by the CBO
- Measure the query performance to validate gains
- Document and share your results with the team


For Function-based indexes:

- Create a function that computes a virtual column
- Create a function based index on the virtual column computed by the function
- Use the virtual column via the function in the query where clause

Syntax for function-based indexes:

create [unique | nonclustered] index index_name
 on [[ database.] owner.] table_name
 (column_expression [asc | desc] 
 [, column_expression [asc | desc]]...

set showplan on
set noexec on

select * FROM someTable WHERE (fieldA = 1 AND fieldB = 2) OR (fieldB = 2 AND fieldA = 1)

Row count 841,090
Before Query time 1.5 seconds

Code: 6248 SQL State: 01000 --- |ROOT:EMIT Operator (VA = 2)
Code: 6248 SQL State: 01000 --- |
Code: 6248 SQL State: 01000 --- | |RESTRICT Operator (VA = 1)(0)(0)(0)(13)(0)
Code: 6248 SQL State: 01000 --- | |
Code: 6248 SQL State: 01000 --- | | |SCAN Operator (VA = 0)
Code: 6215 SQL State: 01000 --- | | | FROM TABLE
Code: 6217 SQL State: 01000 --- | | | someTable
Code: 6223 SQL State: 01000 --- | | | Table Scan.
Code: 6276 SQL State: 01000 --- | | | Forward Scan.
Code: 6278 SQL State: 01000 --- | | | Positioning at start of table.
Code: 10240 SQL State: 01000 --- | | | Using I/O Size 16 Kbytes for data pages.
Code: 10239 SQL State: 01000 --- | | | With LRU Buffer Replacement Strategy for data pages.

CREATE FUNCTION someFunction(@fieldA numeric(18,0), @fieldB numeric(18,0)) returns char(1)
as
  DECLARE @rtn char(1)
  IF (@fieldA = 1 AND @fieldB = 2) OR (@fieldA = 2 AND @fieldB = 1)
    BEGIN
      SET @rtn = 'Y'
    END
  ELSE
    BEGIN
      SET @rtn = 'N'
    END
  RETURN @rtn
Go

create index someTableSomeIdx on someTable (dbo.someFunction(fieldA, fieldB))


Code: 6248 SQL State: 01000 --- |ROOT:EMIT Operator (VA = 1)
Code: 6248 SQL State: 01000 --- |
Code: 6248 SQL State: 01000 --- | |SCAN Operator (VA = 0)
Code: 6215 SQL State: 01000 --- | | FROM TABLE
Code: 6217 SQL State: 01000 --- | | someTable
Code: 6225 SQL State: 01000 --- | | Index : someTableSomeIdx
Code: 6276 SQL State: 01000 --- | | Forward Scan.
Code: 6281 SQL State: 01000 --- | | Positioning by key.
Code: 6286 SQL State: 01000 --- | | Index contains all needed columns. Base table will not be read.
Code: 6287 SQL State: 01000 --- | | Keys are:
Code: 6288 SQL State: 01000 --- | | sybfi15_1 ASC
Code: 6272 SQL State: 01000 --- | | Using I/O Size 16 Kbytes for index leaf pages.
Code: 6273 SQL State: 01000 --- | | With LRU Buffer Replacement Strategy for index leaf pages.

After Query time 0.016 seconds