Oracle function based index with empty function name

indexoracle

I'm trying to understand this syntax in the creation of an index in a database I'm using:

CREATE  INDEX IDX ON SQ.MYTABLE (('ID_COLUMN1'), ('ID_COLUMN2'), ('ID_COLUMN3'));

After searching for a while, I could not find any example using this syntax, and how it is different from a normal multi column index:

CREATE  INDEX IDX_2 ON MYSQ.MYTABLE ('ID_COLUMN1', 'ID_COLUMN2', 'ID_COLUMN3');

I figured out it is a function based index, analyzing the following example I was trying to assemble: 

CREATE TABLE t (a number, b number, c number);

insert into t values (1, 2, 3);
insert into t values (4, 5, 6);
insert into t values (7, 8, 9);
insert into t values (10,11, 12);
insert into t values (13, 14, 15);
insert into t values (16, 17, 18);

CREATE INDEX IDX2 ON t (a, b, c);
CREATE INDEX IDX ON t (('d'), ('b'), ('c'));

select * from USER_INDEXES;


INDEX_NAME  INDEX_TYPE  TABLE_OWNER     TABLE_NAME  TABLE_TYPE  UNIQUENESS  COMPRESSION     PREFIX_LENGTH   TABLESPACE_NAME     INI_TRANS   MAX_TRANS   INITIAL_EXTENT  NEXT_EXTENT     MIN_EXTENTS     MAX_EXTENTS     PCT_INCREASE    PCT_THRESHOLD   INCLUDE_COLUMN  FREELISTS   FREELIST_GROUPS     PCT_FREE    LOGGING     BLEVEL  LEAF_BLOCKS     DISTINCT_KEYS   AVG_LEAF_BLOCKS_PER_KEY     AVG_DATA_BLOCKS_PER_KEY     CLUSTERING_FACTOR   STATUS  NUM_ROWS    SAMPLE_SIZE     LAST_ANALYZED   DEGREE  INSTANCES   PARTITIONED     TEMPORARY   GENERATED   SECONDARY   BUFFER_POOL     FLASH_CACHE     CELL_FLASH_CACHE    USER_STATS  DURATION    PCT_DIRECT_ACCESS   ITYP_OWNER  ITYP_NAME   PARAMETERS  GLOBAL_STATS    DOMIDX_STATUS   DOMIDX_OPSTATUS     FUNCIDX_STATUS  JOIN_INDEX  IOT_REDUNDANT_PKEY_ELIM     DROPPED     VISIBILITY  DOMIDX_MANAGEMENT   SEGMENT_CREATED
IDX2    NORMAL  USER_4_AE9EA5   T   TABLE   NONUNIQUE   DISABLED    (null)  FIDDLEDATA  2   255     65536   1048576     1   2147483645  (null)  (null)  (null)  (null)  (null)  10  YES     0   1   6   1   1   1   VALID   6   6   2019-08-19T22:06:23Z    1   1   NO  N   N   N   DEFAULT     DEFAULT     DEFAULT     NO  (null)  (null)  (null)  (null)  (null)  NO  (null)  (null)  (null)  NO  NO  NO  VISIBLE     (null)  YES
IDX     FUNCTION-BASED NORMAL   USER_4_AE9EA5   T   TABLE   NONUNIQUE   DISABLED    (null)  FIDDLEDATA  2   255     65536   1048576     1   2147483645  (null)  (null)  (nul

http://www.sqlfiddle.com/#!4/ae9ea5/5

What is the purpose of this index?

Best Answer

That is not an unnamed/empty function. That is a column expression made from a constant, between parentheses.

And it is listed as function-based index, because:

CREATE INDEX

When you specify column_expression, you create a function-based index.

The below is a function-based index too by definition, while it is nothing more than the a constant for all rows.

CREATE INDEX IDX3 ON t('a');
Related Question