Hi Reader,

I use a pattern to select access indexes of a table defined in our application schema. Now having Oracle 10G i changed the SQL from standard LIKE to REGEXP_LIKE. If you are used to work with regular expression specially on UNIX operating systems you will be very excited about the regular expression engine built in Oracle SQL.

The classic approach :

SELECT Ui.Index_Name
 FROM User_Indexes Ui
 WHERE Ui.Table_Name =‘NVDW_FACTS_V02_IFX’
   AND(   Ui.Index_Name LIKE Ui.Table_Name || ‘\_IX__’ ESCAPE ‘\’
       OR Ui.Index_Name LIKE Ui.Table_Name || ‘\_FX__’ ESCAPE ‘\’
       OR Ui.Index_Name LIKE Ui.Table_Name || ‘\_BX__’ ESCAPE ‘\’)

;

The new approach :

SELECT Ui.Index_Name
  FROM User_Indexes Ui
 WHERE Ui.Table_Name = ‘NVDW_FACTS_V02_IFX’
   AND regexp_like(ui.index_name, ui.table_name || ‘_[IFB]X[[:digit:]]{2}’)

;

The output :

SQL> SELECT Ui.Index_Name
  2    FROM User_Indexes Ui
  3   WHERE Ui.Table_Name = ‘NVDW_FACTS_V02_IFX’
  4     AND regexp_like(ui.index_name, ui.table_name || ‘_[IFB]X[[:digit:]]{2}’);

INDEX_NAME
——————————
NVDW_FACTS_V02_IFX_BX01
NVDW_FACTS_V02_IFX_BX02

SQL>

The new appraoach is more exact because the last to characters are exactly defined as two digits ‘[[:digit:]]{2}’, you could do the same with Oracle SQL too but with a very large where clause!

HTH
Karl

Links :

• Oracle Regular Expressions