Home » Articles » 8i » Right here

Serve as-Based totally Indexes

Historically, appearing a occupation on an listed column within the the place clause of a question assured an index would
no longer be worn. Oracle 8i presented Serve as-Based totally Indexes to counter this infection. Instead than indexing a column, you
index the occupation on that column, storing the fabricated from the occupation, no longer the latest column information. When a question is handed
to the server that would have the benefit of that index, the question is rewritten to permit the index to be worn. Please see code
samples give an instance of the utility of Serve as-Based totally Indexes.

• Build Test Table
• Build Regular Index
• Build Function-Based Index
• Concatenated Columns

Manufacture Check Desk

First we create a take a look at desk and populate it with plenty information in order that utility of an index could be tremendous.

CREATE TABLE user_data (
 identity          NUMBER(10)    NOT NULL,
 first_name  VARCHAR2(40)  NOT NULL,
 last_name   VARCHAR2(40)  NOT NULL,
 gender      VARCHAR2(1),
 dob         DATE
);

BEGIN
  FOR cur_rec IN 1 .. 2000 LOOP
    IF MOD(cur_rec, 2) = 0 THEN
      INSERT INTO user_data 
      VALUES (cur_rec, 'John' || cur_rec, 'Doe', 'M', SYSDATE);
    ELSE
      INSERT INTO user_data 
      VALUES (cur_rec, 'Jayne' || cur_rec, 'Doe', 'F', SYSDATE);
    END IF;
    COMMIT;
  END LOOP;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'user_data', cascade => TRUE);

At this level the desk isn’t listed so we’d be expecting a complete desk scan for any question.

SET AUTOTRACE ON
SELECT *
FROM   user_data
WHERE  UPPER(first_name) = 'JOHN2';

Execution Plan
----------------------------------------------------------
Plan hash price: 2489064024

-------------------------------------------------------------------------------
| Identity  | Operation         | Title      | Rows  | Bytes | Value (%CPU)| Week     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    20 |   540 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| USER_DATA |    20 |   540 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Manufacture Common Index

If we now form a common index at the FIRST_NAME column we see that the index isn’t worn.

CREATE INDEX first_name_idx ON user_data (first_name);
EXEC DBMS_STATS.gather_table_stats(USER, 'user_data', cascade => TRUE);

SET AUTOTRACE ON
SELECT *
FROM   user_data
WHERE  UPPER(first_name) = 'JOHN2';

Execution Plan
----------------------------------------------------------
Plan hash price: 2489064024

-------------------------------------------------------------------------------
| Identity  | Operation         | Title      | Rows  | Bytes | Value (%CPU)| Week     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    20 |   540 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| USER_DATA |    20 |   540 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Manufacture Serve as-Based totally Index

If we now exchange the common index with a function-based index at the FIRST_NAME column we see that the index is worn.

DROP INDEX first_name_idx;
CREATE INDEX first_name_idx ON user_data (UPPER(first_name));
EXEC DBMS_STATS.gather_table_stats(USER, 'user_data', cascade => TRUE);

-- Nearest releases poised those via default.
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

SET AUTOTRACE ON
SELECT *
FROM   user_data
WHERE  UPPER(first_name) = 'JOHN2';
   
Execution Plan
----------------------------------------------------------
Plan hash price: 1309354431

----------------------------------------------------------------------------------------------
| Identity  | Operation                   | Title           | Rows  | Bytes | Value (%CPU)| Week     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| USER_DATA      |     1 |    36 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FIRST_NAME_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Concatenated Columns

This mode works for concatenated indexes additionally.

DROP INDEX first_name_idx;
CREATE INDEX first_name_idx ON user_data (gender, UPPER(first_name), dob);
EXEC DBMS_STATS.gather_table_stats(USER, 'user_data', cascade => TRUE);

SET AUTOTRACE ON
SELECT *
FROM   user_data
WHERE  gender="M"
AND    UPPER(first_name) = 'JOHN2';

Execution Plan
----------------------------------------------------------
Plan hash price: 1309354431

----------------------------------------------------------------------------------------------
| Identity  | Operation                   | Title           | Rows  | Bytes | Value (%CPU)| Week     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    36 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| USER_DATA      |     1 |    36 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FIRST_NAME_IDX |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Bear in mind, function-based indexes require extra aim to preserve than common indexes, so having concatenated indexes on this way might build up the occurrence of index repairs in comparison to a function-based index on a unmarried column.

For more info see:

• Function-Based Indexes

Hope this is helping. Regards Tim…

Back to the Top.