I was testing an Oracle function-based index the other day and discovered that the parenthesis that are part of the index definition are required, even though the CREATE INDEX command works fine without them.
Here’s an example:
SQL> -- create a test table and load it with some rows SQL> create table EMP 2 ( id number, 3 name varchar2(20) 4 ); Table created. SQL> declare 2 begin 3 for i in 1..500000 loop 4 insert into emp values (i,'Johnson'); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> insert into emp values (500001,'Joe'); 1 row created. SQL> commit; Commit complete. SQL> -- notice no parenthesis around upper(name) SQL> create index emp_func_idx on emp upper(name); Index created. SQL> -- notice that the resulting index is NOT a function-based index SQL> select index_type from user_indexes where index_name = 'EMP_FUNC_IDX'; INDEX_TYPE --------------------------- NORMAL 1 row selected. SQL> drop index emp_func_idx; Index dropped. SQL> -- note the extra enclosing parenthesis (in red) are SQL> -- part of the index definition SQL> create index emp_func_idx on emp (upper(name)); Index created. SQL> -- now the index is a function-based index SQL> select index_type from user_indexes where index_name = 'EMP_FUNC_IDX'; INDEX_TYPE --------------------------- FUNCTION-BASED NORMAL 1 row selected. SQL> -- confirm the functionality of the index by using a function in query SQL> explain plan for 2 select * from emp where upper(name) = 'JOE'; Explained. SQL> @xplan Plan hash value: 2877811124 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3684 | 133K| 340 (0)| 00:00:05 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3684 | 133K| 340 (0)| 00:00:05 | |* 2 | INDEX RANGE SCAN | EMP_FUNC_IDX | 1474 | | 665 (0)| 00:00:08 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(UPPER("NAME")='JOE') SQL> explain plan for 2 select * from emp where name = 'Joe'; Explained. SQL> @xplan Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 350 | 346 (2)| 00:00:05 | |* 1 | TABLE ACCESS FULL| EMP | 14 | 350 | 346 (2)| 00:00:05 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"='Joe')
Advertisement
