Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to create function based index on REGEXP_LIKE funtion

sanju_sql_plsqlDec 17 2010 — edited Dec 17 2010
Dear Gurus,

I have below table CDR
Name Null Type
------------------------------ -------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STARTTIME NOT NULL DATE
SUBSCRIBERNUMBER NOT NULL NUMBER
CALLINGNUMBER NOT NULL VARCHAR2(20)
CALLEDNUMBER NOT NULL VARCHAR2(20)

I am regularly firing below query

SELECT count(*)
FROM CDR data
WHERE STARTTIME BETWEEN '01-Jul-2009 00:00:00' and '31-May-2012 23:59:59'
AND REGEXP_LIKE(data.SUBSCRIBERNUMBER, '^98721[0-9]*[5]+[0-9]*$');

since there is REGEXP_LIKE is being used, Can I use function based index to improve performance.

Thanking in advance
Sanjeev
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 14 2011
Added on Dec 17 2010
3 comments
2,516 views