CODEWITHSUNDEEP
sqloracle-databasefunctionindexing
ShoSom
ShoSom

Reputation: 29

Missing parenthesis error when creating Function Based Index

I am attempting to create a Function Based Index on a predicate that has a high cost (Oracle).

I want to create an index on the TIME_ID column in the A4ORDERS table that brings back values for month of December: 

SELECT * FROM A4ORDERS WHERE TRIM(TO_CHAR(time_id, 'Month')) in ( 'December' );

Creating the FBI:

CREATE INDEX TIME_FIDX ON A4ORDERS(TRIM(TO_CHAR(time_id, 'Month'))  in ( 'December' ));

I get a "Missing Right parenthesis" error and I can't figure out why? Any guidance you can provide would be appreciated.

Solution from Alex Poole's response below that worked: 

CREATE INDEX TIME_FIDX ON A4ORDERS (TRIM(TO_CHAR(time_id, 'Month')));

Upvotes: 0

Views: 117

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

Your create index statement should not have the in ( 'December' ) part, that only belongs in the query. If you create the index as:

CREATE INDEX TIME_FIDX ON A4ORDERS (TRIM(TO_CHAR(time_id, 'Month')));

... then that index can be used by your query:

EXPLAIN PLAN FOR
SELECT * FROM A4ORDERS WHERE TRIM(TO_CHAR(time_id, 'Month')) in ( 'December' );

SELECT plan_table_output FROM TABLE (dbms_xplan.display());

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    29 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| A4ORDERS  |     1 |    29 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TIME_FIDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):                                      
---------------------------------------------------                                      

   2 - access(TRIM(TO_CHAR(INTERNAL_FUNCTION("TIME_ID"),'Month'))='December')

So you can see from the plan that TIME_FIDX is being used. Whether it will give you a significant performance gain remains to be seen of course, and the optimiser could decide it isn't selective enough anyway.

'Month' is NLS-sensitive though; it would be safer to either use the month number, or specify the NLS_DATE_LANGUAGE in the TO_CHAR call, but it has to be done consistently - which will be a little easier with numbers. You could also make it an indexed virtual column.

Upvotes: 2

Dmitriy
Dmitriy

Reputation: 5565

You can use:

CREATE INDEX TIME_FIDX ON A4ORDERS(TRIM(TO_CHAR(time_id, 'Month')));

But also you can make it bit more simple:

CREATE INDEX TIME_FIDX ON A4ORDERS(TO_CHAR(time_id, 'mm'));

and write SQL:

SELECT * FROM A4ORDERS WHERE TO_CHAR(time_id, 'mm') in ( '12');

But if you provide more information about your problem (workaround, SQL query, plans etc.) you can receive more help.

Upvotes: 2

Related Questions