0

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?

0

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');
2
  • Ok, so in this particular case, CREATE INDEX IDX ON SQ.MYTABLE (('ID_COLUMN1'), ('ID_COLUMN2'), ('ID_COLUMN3')); what is the porpose of using the column names as a constants? Aug 20 '19 at 3:26
  • 1
    @SvenKnowles Nothing. It is useless. I have seen this before a few times. Some people did not know the difference between ' and ". 'ID_COLUMN1' is a string literal, a constant value. "ID_COLUMN1" means the ID_COLUMN1 column. " adds case sensitivity to identifiers. If not specified, by default all identifiers are stored as uppercase strings. In this context, "ID_COLUMN1" means the same as ID_COLUMN1, the column itself, but 'ID_COLUMN1' is just a string constant. Aug 20 '19 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.