Scribd
Upload
198 views3 pages

Creating Index for Multi-Value Fields

The document describes the steps to create an index on a promoted column for a multi-value field in an Oracle database. It involves: 1) creating a function to extract the column value, 2) altering the table to add the column using the function, 3) creating an index on the new column, and 4) rebuilding the schema to include the new column in views. The explain plan is checked to confirm the index is used in queries on the column.

Uploaded by

kmgouda
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
198 views3 pages

Creating Index for Multi-Value Fields

The document describes the steps to create an index on a promoted column for a multi-value field in an Oracle database. It involves: 1) creating a function to extract the column value, 2) altering the table to add the column using the function, 3) creating an index on the new column, and 4) rebuilding the schema to include the new column in views. The explain plan is checked to confirm the index is used in queries on the column.

Uploaded by

kmgouda
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Index creation using promoted column for multi-value field in oracle

database.

Step-1:

Create a promoted column for the field which is in position 3 as below

Sample DDL extract for multivalue fields is as follows,

,extractValue(a.XMLRECORD,'/row/c3[position()=1]') "NAME_1"

,extract(a.XMLRECORD,'/row/c3') "NAME_1_3"

SQL> create or replace function CUS_FUN(XMLRECORD SYS.XMLTYPE)

  2  return varchar2 deterministic

  3  as rtnval varchar2(100);

  4  begin

  5  select XMLcast( XMLQuery('/row/c3/text()' passing XMLRECORD returning content) as


varchar2(100)) into rtnval from dual;

  6  return rtnval;

  7  end;

  8  /

Function created.

Step-2:

Alter the table for the column "NAME_1" using below command

SQL> alter table FBNK_CUSTOMER add NAME_1 varchar2(100) generated always as


(cast(CUS_FUN(XMLRECORD) as varchar2(100)));

Table altered.

Step-3:

Create index for the altered field as below in order to make use of index when column is
passed with a normal value.

SQL> CREATE INDEX IX_NAME_CUSTOMER ON FBNK_CUSTOMER(NAME_1);

Note: When there is a possibility to have null value for the column then the index should be
created with constant "1"
SQL> CREATE INDEX IX_NAME_CUSTOMER ON FBNK_CUSTOMER(NAME_1,'1');

Index created.

Step-4:

Rebuild SS to update the view.

Execute the below command in sqlplus in order to get the data definition language for the
problematic field. "TAFJR14" denotes the currently logged in database user name.

SQL> SET LINESIZE 32000;

SQL> SET PAGESIZE 40000;

SQL> SET LONG 50000;

SQL> select dbms_metadata.get_ddl('VIEW','V_FBNK_CUSTOMER','TAFJR14') from


dual;

Post creating the index if we perform a SS rebuild the new column will be taken into
consideration in the view definition.

,extract(a.XMLRECORD,'/row/c2') "SHORT_NAME_2"

,NAME_1 "NAME_1"

,extract(a.XMLRECORD,'/row/c3') "NAME_1_3"

,extractValue(a.XMLRECORD,'/row/c4[position()=1]') "NAME_2"

,extract(a.XMLRECORD,'/row/c4') "NAME_2_4"

,extractValue(a.XMLRECORD,'/row/c5[position()=1]') "STREET"

,extract(a.XMLRECORD,'/row/c5') "STREET_5"

,extractValue(a.XMLRECORD,'/row/c6[position()=1]') "ADDRESS"

,extract(a.XMLRECORD,'/row/c6') "ADDRESS_6"

,extractValue(a.XMLRECORD,'/row/c7[position()=1]') "TOWN_COUNTRY"

Step-5:
Execute the explain plan and check whether the SELECT command is making use of index or
not

SQL> EXPLAIN PLAN SET statement_id = 'ex_plan100' FOR SELECT RECID FROM
"V_FBNK_CUSTOMER" WHERE "NAME_1" ='OJASVI';

Explained. 

Step-6: 

SQL> SELECT PLAN_TABLE_OUTPUT FROM


TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan100','BASIC'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------
-------------

----------------------------------------------------------------------------------------------------------------
-------------

--------------------------------------------------

Plan hash value: 3740750524 

---------------------------------------------------

| Id  | Operation              | Name             |

---------------------------------------------------

|   0 | SELECT STATEMENT       |                  |

|   1 |  VIEW                  | index$_join$_002 |

|   2 |   HASH JOIN            |                  |

|   3 |    INDEX RANGE SCAN    | IX_NAME_CUSTOMER |

|   4 |    INDEX FAST FULL SCAN| SYS_C00140274    |

--------------------------------------------------- 

11 rows selected.

You might also like