Chapter 3 Building Physical Diagrams

Creating a function-based index

In some DBMS, you can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index precomputes the value of the function or expression and stores it in the index. The function or the expression will replace the index column in the index definition.

Function-based indexes provide an efficient mechanism for evaluating statements that contain functions in their WHERE clauses.

Reverse engineering function-based index

An index column with an expression has a LONG data type that cannot be concatenated in a string statement during reverse engineering. The only way to bypass this limitation and concatenate this value is to use variables in the query executed to retrieve the adequate information.

In the Oracle 8i and Oracle 8i2 DBMS, the query SqlListQuery defined in the Index category contains the following variable used to recover the index expression in a column with the LONG data type.

'%SqlExpression.Xpr'||i.table_name||i.index_name||c.column_position||'%'

For more information on the use of variables in reverse engineering queries, see section Extension mechanism for ODBC reverse engineering queries, in chapter DBMS Reference Guide, in the Advanced User Documentation .

Example

Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches.

You want to define an index that will put all names in lowercase on the table EMPLOYEE in order to ease search. You can define the following index (syntax for Oracle 8i):

CREATE INDEX low_name_idx ON EMPLOYEE (LOWER(EMPLNAM))

Then the DBMS can use it when processing queries such as:

SELECT * FROM EMPLOYEE WHERE LOWER(EMPLNAM)="brown"

Steps To create a function-based index:

  1. Double-click a table in the diagram to display the table property sheet.
  2. Click the Indexes tab to display the Index page.
  3. Click the Add a Row tool.

    An arrow appears at the beginning of the line.
  4. Type an index name and an index code.
  5. Click Apply.
  6. Click the Properties tool.

    or

    Double-click the arrow at the beginning of the line.

    The index property sheet opens to the General page.
  7. Click the Columns tab to display the Columns page.
  8. Click the Add a Row tool.

    An arrow appears at the beginning of the line.
  9. Click in the Expression column and select the Ellipsis button.

    The SQL Editor dialog box appears.
  10. Type an expression in the editor.
  11. Click OK.

    The index expression appears in the Name, Code, and Expression columns in the Expression column in the list of index columns.
  12. Click OK in each of the dialog boxes.

 

Copyright (C) 2005. Sybase Inc. All rights reserved.