Search code examples
oracle-databaseplsql

How to create multi-column index with column expression(NLSSORT)?


With the NLSSORT expression, we are attempting to create an index(multi-column) that should include three columns. The script that we tried below didn't work and resulted in a "too many arguments for function" exception.

CREATE INDEX NCIX_PERSON_GROUP_ID_START_END_DATE ON TBL_PERSON_GROUP(NLSSORT((PERSON_GROUP_ID,START_DATE,END_DATE),'NLS_SORT=BINARY'));

For a single-column index, we attempted to create a similar column expression, and it was successful. The script, which worked flawlessly, is provided below.

CREATE INDEX TBL_PERSON_NAME ON TBL_PERSON(NLSSORT(PERSON_NAME,'NLS_SORT=BINARY'));

Is there a way to use the NSLSORT expression to create a multi-column index? Thanks in advance.


Solution

  • Can't test this right now, but try to define the nlssort for each column separately.

    CREATE INDEX NCIX_PERSON_GROUP_ID_START_END_DATE
      ON TBL_PERSON_GROUP(
        NLSSORT(PERSON_GROUP_ID,'NLS_SORT=BINARY'),
        NLSSORT(START_DATE,'NLS_SORT=BINARY'),
        NLSSORT(END_DATE,'NLS_SORT=BINARY'));