Why does Oracle change the index construction function instead of error output? ORA-01722: invalid n-CodePudding

Creating a mySomeTable table with 2 fields

create table mySomeTable (
    IDRQ VARCHAR2(32 CHAR),
    PROCID VARCHAR2(64 CHAR)
);

Creating an index on the table by the PROCID field

create index idx_PROCID on mySomeTable(trunc(PROCID));

Inserting records:

insert into mySomeTable values ('a', '1'); -- OK
insert into mySomeTable values ('b', 'c'); -- FAIL

As you can see, an error has been made in the index construction script and the script will try to build an index on the field using the trunc() function.

trunct() is a function for working with dates or numbers, and the field has the string type

This index building script successfully works out and creates an index without displaying any warnings and errors.

An index is created on the table using the TRUNC(TO_NUMBER(PROCID)) function

When trying to insert or change an entry in the table, if PROCID cannot be converted to a number, I get the error ORA-01722: invalid number, which is actually logical.

However, the understanding that I am working in a table with rows and adding string values to the table, and the error is about converting to a number, was misleading and not understanding what is happening...

Question: Why does Oracle change the index construction function, instead of giving an error? And how can this be avoided in the future?

Oracle version 19.14

Naturally, there was only one solution - to create the right index with the right script

    create index idx_PROCID on mySomeTable(PROCID);

however, this does not explain, to me, this Oracle behavior.

CodePudding user response：

Oracle doesn't know if the index declaration is wrong or the column data type is wrong. Arguably (though some may well disagree!) Oracle shouldn't try to second-guess your intentions or enforce restrictions beyond those documented in the manual - that's what user-defined constraints are for. And, arguably, this index acts as a form of pseudo-constraint. That's a decision for the developer, not Oracle.

It's legal, if usually ill-advised, to store a number in a string column. If you actually intentionally chose to store numbers as strings - against best practice and possibly just to irritate future maintainers of your code - then the index behaviour is reasonable.

A counter-question is to ask where it should draw the line - if you expect it to error on your index expression, what about something like

create index idx_PROCID on mySomeTable(
  case when regexp_like(PROCID, '^\d.?\d*$') then trunc(PROCID) end
);

create index idx_PROCID on mySomeTable(
  trunc(to_number(PROCID default null on conversion error))
);

You might actually have chosen to store both numeric and non-numeric data in the same string column (again, I'm not advocating that) and an index like that might then useful - and you wouldn't want Oracle to prevent you from creating it.

Something that obviously doesn't make sense and shouldn't be allowed to you is much harder for software to evaluate.

Interestingly the documentation says:

Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:

...

If implicit data type conversion occurs in an index expression, then Oracle Database might not use the index because it is defined for the pre-conversion data type. This can have a negative impact on performance.

which is presumably why it actually chooses here to apply explicit conversion when it creates the index expression (which you can see in user_ind_expressions - fiddle)

But you'd get the same error if the index expression wasn't modified - there would still be an implicit conversion of 'c' to a number, and that would still throw ORA-01722. As would some strings that look like numbers if your NLS settings are incompatible.