June 18, 2008 1 Comment
184124858368.00 to be precise.
I’ve got a problem with a set of sql statements in production. Fortunately the problem is reproducible in a non-prod environment.
These statements all reference one particular virtual column in a composite function-based index.
The sql statements all look something like this:
FROM my_schema.table1 tab1
AND NVL(col2,'UNASSIGNED') = SYS_CONTEXT('MY_CONTEXT','MY_COL2_VALUE')
And they are raising the following error:
ORA-00904: "MY_SCHEMA"."TAB1"."SYS_NC00062$": invalid identifier
This “SYS_NC00062$” is a hidden/virtual column on this table that is part of a function-based index. I can see it here at least:
where obj# in (select object_id from dba_objects where object_name = 'TABLE1' and owner = 'MY_SCHEMA') order by col#;
This article isn’t about this error specifically. Not yet at least but I might blog about it later once I know some more.
However, in the meantime, I’ve just started gathering details, running some extended trace files (10046, 10053), etc with a view to finding some extra information and then probably raising an issue on metalink.
I’ve not even started analysing this info yet but as I zipped through the 10053 file, I noted something odd.
Note that in this 10053 trace file, the original reported SYS_NC00062$ has now become SYS_NC00066$ because I dropped and recreated the FBI to see if that helped – it didn’t.
Looking at the cost_io numbers, you get various costs for various access methods – e.g a 16.00, a 3.00, a 15632.00, a 53.00 but then holy moly:
***** Virtual column Adjustment ******
Column name SYS_NC00066$
***** End virtual column Adjustment ******
I’ve literally done nothing yet but seen that number and thought I’d put in down quickly in an article.
But wow – that’s a big adjustment! What’s that about?
I picked the wrong day to leave my Jonathan Lewis CBO book at home…