Monday night test

Here's an interesting little puzzle from today - I've distilled it into a simple demo - the basic problem that appeared can be summed up in the screenshot below

The two tables should have equivalent indexes - but the one index has SYS generated column names implying that something odd is going on.

Any guesses as to what it was (no reading ahead.....)?

Was it some weird NLS setting?

Was it some specially changed function based index?

Was it an index on hidden columns?

???????

Well we knew they should have been exactly the same so something odd was going on - but what?

Does this give you a clue?

SQL> select table_name,column_name,DATA_DEFAULT  from dba_tab_cols where table_name='DEMO'


TABLE_NAME COLUMN_NAME      DATA_DEFAULT
---------- ---------------- --------------------------------------------------------------------------------
DEMO       SYS_NC00005$     'col2'
DEMO       SYS_NC00004$     'col1'
DEMO       COL3
DEMO       COL2
DEMO       COL1
DEMO       COL3
DEMO       COL2
DEMO       COL1

8 rows selected.

Still not got it?

Next clue

SQL>  select dbms_metadata.get_ddl('INDEX','DEMOIDX','DEMO1') from dual;

DBMS_METADATA.GET_DDL('INDEX','DEMOIDX','DEMO1')
--------------------------------------------------------------------------------

  CREATE INDEX "DEMO1"."DEMOIDX" ON "DEMO1"."DEMO" ("COL1", "COL2")

SQL>  select dbms_metadata.get_ddl('INDEX','DEMOIDX','DEMO2') from dual;

DBMS_METADATA.GET_DDL('INDEX','DEMOIDX','DEMO2')
--------------------------------------------------------------------------------

  CREATE INDEX "DEMO2"."DEMOIDX" ON "DEMO2"."DEMO" ('col1', 'col2')


Spot the difference yet?

Well it's a simple case of the wrong type of quote being used.....

In the index in the demo2 schema rather than the actual column value being indexed, we have created an index on the actual text values 'col1' and 'col2' - so a completely pointless index as it's not actually indexing values from the table - just some arbritrary text string...


Don;t know how this happened to be honest - not seen it before - but you get no warning there is a problem as this is a valid create index statement....