Single Quotes in Oracle Database Index Column Specification

In my previous post, I mentioned that a downside of using double quotes to explicitly specify case in Oracle identifiers is the potential for being confused with the use of single quotes for string literals. Although I don't personally think this is sufficient reason to avoid use of double quotes for identifiers in Oracle, it is worth being aware of this potential confusion. When to use single quotes versus when to use double quotes has been a source of confusion for users new to databases that distinguish between the two for some time. In this post, I look at an example of how accidental misuse of single quote where no quote is more appropriate can lead to the creation of an unnecessary index.

The SQL in the simple script createPersonTable.sql generates a table called PEOPLE and an index will be implicitly created for this table's primary key ID column. However, the script also contains an explicit index creation statement that, at first sight, might appear to also create an index on this primary key column.

createPersonTable.sql

CREATE TABLE people
(
   id number PRIMARY KEY,
   last_name varchar2(100),
   first_name varchar2(100)
);

CREATE INDEX people_pk_index ON people('id');

We might expect the statement that appears to explicitly create the primary key column index to fail because that column is already indexed. As the output below shows, it does not fail.

When a query is run against the indexes, it becomes apparent why the explicit index creation did not fail. It did not fail because it was not creating another index on the same column. The single quotes around what appears to be the "id" column name actually make that 'id' a string literal rather than a column name and the index that is created is a function-based index rather than a column index. This is shown in the query contained in the next screen snapshot.

The index with name PEOPLE_PK_INDEX was the one explicitly created in the script and is a function-based index. The implicitly created primary key column index has a system-generated name. In this example, the function-based index is a useless index that provides no value.

It's interesting to see what happens when I attempt to explicitly create the index on the column by using double quotes with "id" and "ID". The first, "id", fails ("invalid identifier") because Oracle case folds the name 'id' in the table creation to uppercase 'ID' implicitly. The second, "ID", fails ("such column list already indexed") because, in this attempt, I finally am trying to create an index on the same column for which an index was already implicitly created.

In my original example, the passing of a literal string as the "column" to the index creation statement resulted in it being created as a useless function-based index. It could have been worse if my intended primary key column index hadn't already been implicitly created because then I might not have the index I thought I had. This, of course, could happen when creating an index for a column or list of columns that won't have indexes created for them implicitly. There is no error message to warn us that the single-quoted string is being treated as a string literal rather than as a column name.

Conclusion

The general rule of thumb to remember when working with quotation marks in Oracle database is that double quotes are for identifiers (such as column names and table names) and single quotes are for string literals. As this post has demonstrated, there are times when one may be misused in place of the other and lead to unexpected results without necessarily displaying an error message.