Missing parenthesis error when creating Function Based Index

Question

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')));

An index doesn't "bring back values". Do you want an index only on the values in December? — Gordon Linoff
– Gordon Linoff, Commented Oct 28, 2014 at 16:24
I think you should explain what you are trying to accomplish. What query are you trying to optimize, for instance? — Gordon Linoff
– Gordon Linoff, Commented Oct 28, 2014 at 16:27
Your comment lead me to look at this further. What I was doing is trying to get the index to return a value. I changed things a bit and it now works. — ShoSom
– ShoSom, Commented Oct 28, 2014 at 16:47
@ShoSom - an index still doesn't return a value; you're trying to index a value generated from a column value. I kind of understand what you mean but the terminology matters when you're trying to explain a problem *8-) — Alex Poole
– Alex Poole, Commented Oct 28, 2014 at 16:57

Alex Poole · Accepted Answer · 2014-10-28 16:49:51Z

2

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.

answered Oct 28, 2014 at 16:49

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ShoSom Over a year ago

Wow. This actually brought back exactly what I wanted. Thank you.

Alex Poole Over a year ago

@ShoSom - you need the TRIM because by default all the month names are padded out with spaces to the length of the longest name, which is September (with an English data language!). Without the trim your indexed value is 'December ' with a trailing space. You can also use the FM modifier, i.e. fmMonth, to not have the value padded in the first place, and then you don't need to trim. But the query has to do exactly the same as the index. Another reason numbers are simpler...

ShoSom Over a year ago

That makes a lot of sense, thank you for the detailed feedback - it is much appreciated.

Dmitriy · Accepted Answer · 2014-10-28 16:49:01Z

2

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.

answered Oct 28, 2014 at 16:49

Dmitriy

5,58512 gold badges27 silver badges39 bronze badges

Collectives™ on Stack Overflow

Missing parenthesis error when creating Function Based Index

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related