Bitten by a Virtual Column, _OPTIMIZER_IGNORE_HINTS Doesn’t Ignore Hints?

9 03 2013

March 9, 2013

I had a couple of spare minutes today, so I tried a couple of experiments with Oracle Database 11.2.0.2 just to see if I could produce some unexpected results.

First, I will create a simple database table with two indexes:

CREATE TABLE T1 (
  N1 NUMBER,
  V1 VARCHAR2(20),
  D1 DATE,
  PADDING VARCHAR2(300));

CREATE INDEX IND_T1_I1 ON T1(V1);
CREATE INDEX IND_T1_I2 ON T1(N1,1);

In the above, I indexed column V1.  The second index is a composite index with column N1 as the first column and the constant 1 as the second column (see this article to understand the reason for the constant 1).  Creating the second index causes Oracle Database to add a hidden virtual column to the table.

Let’s insert 100,000 rows into the table (column N1 includes a repeating sequence from 1 to 999 and then a NULL, column V1 is the Roman numeral for the repeating sequence from 1 to 999 and then 0, column D1 simply adds from 1 to 999 and then 0 days to the current date, and column PADDING is just a series of A characters to discourage the Oracle query optimizer from using full table scans rather than an index access path, if such an access path is legal):

INSERT INTO
  T1 
SELECT 
  DECODE(MOD(ROWNUM,1000),0,NULL,MOD(ROWNUM,1000)) N1, 
  TRIM(TO_CHAR(MOD(ROWNUM,1000),'RN')) V1, 
  TRUNC(SYSDATE) + MOD(ROWNUM,1000) D1, 
  RPAD('A',300,'A') PADDING 
FROM 
  DUAL 
CONNECT BY 
  LEVEL<=100000;

COMMIT;

Let’s take a look at the columns that are included in each of the indexes:

SELECT
  INDEX_NAME,
  COLUMN_NAME
FROM
  USER_IND_COLUMNS
WHERE
  TABLE_NAME = 'T1'
ORDER BY
  INDEX_NAME,
  COLUMN_POSITION;

INDEX_NAME   COLUMN_NAME
------------ ------------
IND_T1_I1    V1
IND_T1_I2    N1
IND_T1_I2    SYS_NC00005$

As can be seen above, Oracle added the SYS_NC00005$ hidden virtual column to the table so that the number 1 could be included as the second column of the composite index.  Somewhat interesting, we are able to query the value of the SYS_NC00005$ hidden virtual column in a query:

SET LINESIZE 140
SET PAGESIZE 1000

SELECT
  V1,
  N1,
  "SYS_NC00005$"
FROM
  T1
WHERE
  N1 IS NULL;

V1                           N1 SYS_NC00005$
-------------------- ---------- ------------
###############                            1
###############                            1
###############                            1
###############                            1
###############                            1
...
###############                            1
###############                            1
###############                            1

100 rows selected.

The above indicates that (according to Oracle) just as there is no Roman number higher than 3,999, there is no Roman number for 0.

While probably not wise, we are able to rename this hidden virtual column:

ALTER TABLE T1 RENAME COLUMN "SYS_NC00005$" TO "C";

Table altered.

Let’s check which columns are included in the index definitions again:

SELECT
  INDEX_NAME,
  COLUMN_NAME
FROM
  USER_IND_COLUMNS
WHERE
  TABLE_NAME = 'T1'
ORDER BY
  INDEX_NAME,
  COLUMN_POSITION;

INDEX_NAME   COLUMN_NAME
------------ -----------
IND_T1_I1    V1
IND_T1_I2    N1
IND_T1_I2    C

The output of the above might suggest to the unsuspecting person that someone intentionally added a column named C to the table, and included that column in a composite index with column N1.  Interesting…

Let’s collect statistics on all of the rows in the table, as well as the table’s indexes:

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>NULL,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100)

SELECT
  V1,
  N1,
  C
FROM
  T1
WHERE
  N1 IS NULL;

V1                           N1          C
-------------------- ---------- ----------
###############                          1
###############                          1
###############                          1
###############                          1
###############                          1
...
###############                          1
###############                          1
###############                          1

100 rows selected.

DBMS_XPLAN will be used to retrieve the actual execution plan for the SQL statement that was just executed:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  f35sfgu6s8huh, child number 0
-------------------------------------
SELECT   V1,   N1,   C FROM   T1 WHERE   N1 IS NULL

Plan hash value: 2648162318

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |   102 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |   100 |  1500 |   102   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_I2 |   100 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1" IS NULL)

The above indicates that the optimizer determined that using an index access path was likely the most efficient access path to search for cases where column N1 contains NULL values.

Oracle Database offers more than 200 hints to help control the query optimizer’s behavior (as well as other behavior).  In general, hints should be used sparingly.  For example, we are able to add a FULL hint to force the optimizer to use a full table scan when searching column N1 for NULL values:

SELECT /*+ FULL(T1) */
  V1,
  N1,
  C
FROM
  T1
WHERE
  N1 IS NULL;

V1                           N1          C
-------------------- ---------- ----------
###############                          1
###############                          1
###############                          1
###############                          1
###############                          1
...
###############                          1
###############                          1
###############                          1

100 rows selected.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  3w60zjynqtwrp, child number 0
-------------------------------------
SELECT /*+ FULL(T1) */   V1,   N1,   C FROM   T1 WHERE   N1 IS NULL

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   590 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |  1500 |   590   (2)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1" IS NULL)

As the above shows, Oracle’s optimizer selected to use a full table scan even though the calculated cost for that access path (590) is higher than the calculated cost of the index access path (102).  There is an obvious temptation when learning about hints to overuse those hints – as data volumes (and the data itself) in a table changes, those index hints may very well hurt performance, rather than help performance.

_OPTIMIZER_IGNORE_HINTS doesn’t ignore hints?

That was a recent set of search keywords used to access this blog.  Oracle Database offers a fairly large number of parameters (we will get back to hints in a moment), some of which are considered hidden parameters because those parameters begin with _ (underscore) characters, typically do not display when querying theV$PARAMETER view (unless the parameter value was modified), and typically should not be modified without the explicit request of Oracle Support.  If we had used the +OUTLINE format parameter in the DBMS_XPLAN call we would see a similarly named IGNORE_OPTIM_EMBEDDED_HINTS hint in the Outline Data section of the execution plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3w60zjynqtwrp',0,'TYPICAL +OUTLINE'));

SQL_ID  3w60zjynqtwrp, child number 0
-------------------------------------
SELECT /*+ FULL(T1) */   V1,   N1,   C FROM   T1 WHERE   N1 IS NULL

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   590 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |  1500 |   590   (2)| 00:00:01 |
--------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1" IS NULL)

Let’s modify the _OPTIMIZER_IGNORE_HINTS hidden parameter at the session level and try the test query again:

ALTER SESSION SET "_optimizer_ignore_hints"=TRUE;

Session altered.

SELECT /*+ FULL(T1) */
  V1,
  N1,
  C
FROM
  T1
WHERE
  N1 IS NULL;

V1                           N1          C
-------------------- ---------- ----------
###############                          1
###############                          1
###############                          1
###############                          1
###############                          1
...
###############                          1
###############                          1
###############                          1

100 rows selected.

The above is no surprise, so let’s take a look at the execution plan with the Outline Data section included:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL +OUTLINE'));

SQL_ID  3w60zjynqtwrp, child number 1
-------------------------------------
SELECT /*+ FULL(T1) */   V1,   N1,   C FROM   T1 WHERE   N1 IS NULL

Plan hash value: 2648162318

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |   102 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |   100 |  1500 |   102   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_I2 |   100 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."N1" "T1"."C1"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1" IS NULL)

Looking at the above execution plan, you will notice that the Oracle optimizer has ignored the FULL hint that was specified in the SQL statement, and that by examining the Outline Data section of the plan you will see that FULL(@”SEL$1″ “T1″@”SEL$1″) was replaced with INDEX_RS_ASC(@”SEL$1″ “T1″@”SEL$1″ (“T1″.”N1″ “T1″.”C1″)).

I am wondering about the above search keywords that were used to access my blog.  Can I force the Oracle query optimizer NOT to ignore embedded hints when the _OPTIMIZER_IGNORE_HINTS hidden parameter is set to TRUE?  Before we start, let’s set the hidden parameter back to the default value of FALSE.

ALTER SESSION SET "_optimizer_ignore_hints"=FALSE;

Occasionally, when the Outline Data section of the execution plan is reviewed (either with DBMS_XPLAN or by reviewing a 10053 trace) an OPT_PARAM hint may appear in the Outline Data section.  Oracle Database itself inserts these hints into SQL statement, for example (from a 10046 trace file):

PARSING IN CURSOR #448514944 len=3052 dep=1 uid=64 oct=3 lid=64 tim=853194336675 hv=3275773959 ad='3edba6b28' sqlid='2sdms4r1n0q07'
SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
                       /* EXEC_FROM_DBMS_XPLAN */ id, position, depth , operation, options, object_name, cardinality, bytes, temp_space, cost,

A 10053 trace file also includes a section titled PARAMETERS IN OPT_PARAM HINT that indicates the SQL statement level parameters that are modified through the OPT_PARAM hint.

Let’s test that OPT_PARAM hint to see if we are able to hint the optimizer to ignore all other hints in the SQL statement:

SELECT /*+ OPT_PARAM('_optimizer_ignore_hints','TRUE') FULL(T1) */
  V1,
  N1,
  C
FROM
  T1
WHERE
  N1 IS NULL;

V1                           N1          C
-------------------- ---------- ----------
###############                          1
###############                          1
###############                          1
###############                          1
###############                          1
...
###############                          1
###############                          1
###############                          1

100 rows selected.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  c4fxyhf0fzg4t, child number 0
-------------------------------------
SELECT /*+ OPT_PARAM('_optimizer_ignore_hints','TRUE') FULL(T1) */
V1,   N1,   C FROM   T1 WHERE   N1 IS NULL

Plan hash value: 2648162318

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |   102 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |   100 |  1500 |   102   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_I2 |   100 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1" IS NULL)

The hint that modified the parameter that controls whether or not hints are ignored worked.  Well, that just does not seem right – should it not be the case that that hint is also ignored?  Might this be a catch 22?

What if… we reverse the order of the hints?

SELECT /*+ FULL(T1) OPT_PARAM('_optimizer_ignore_hints','TRUE') */
  V1,
  N1,
  C
FROM
  T1
WHERE
  N1 IS NULL;

V1                           N1          C
-------------------- ---------- ----------
###############                          1
###############                          1
###############                          1
###############                          1
###############                          1
...
###############                          1
###############                          1
###############                          1

100 rows selected.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  07pu6c3jvdrd0, child number 0
-------------------------------------
SELECT /*+ FULL(T1) OPT_PARAM('_optimizer_ignore_hints','TRUE') */
V1,   N1,   C FROM   T1 WHERE   N1 IS NULL

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   590 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |  1500 |   590   (2)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1" IS NULL)

Well, the FULL hint definitely was not ignored by the optimizer that time, as it was for the previous arrangement of the hints.  Might we be on to something?

Let’s verify that the OPT_PARAM hint does work to control optimizer parameters at the SQL statement level:

SELECT /*+ OPT_PARAM('optimizer_index_cost_adj',1) */
  V1,
  N1,
  C
FROM
  T1
WHERE
  N1 IS NULL;

V1                           N1          C
-------------------- ---------- ----------
###############                          1
###############                          1
###############                          1
###############                          1
###############                          1
...
###############                          1
###############                          1
###############                          1

100 rows selected.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  ac45wtypubpfu, child number 0
-------------------------------------
SELECT /*+ OPT_PARAM('optimizer_index_cost_adj',1) */   V1,   N1,   C
FROM   T1 WHERE   N1 IS NULL

Plan hash value: 2648162318

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |   100 |  1500 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_I2 |   100 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1" IS NULL)

Reviewing the above, you might have noticed that the calculated cost of the index access decreased from a value of 102 to just 1 as a result of the OPT_PARAM hint, so we might be able to conclude that the hint does work.  Might this difference in the execution plan be explained as just the query optimizer NOT silently ignoring invalid hints?  Food for thought.

You might have noticed that the above queries have selected column C from the table.  Let’s take a look at the table’s definition:

DESC T1

 Name             Null?    Type
 ---------------- -------- -------------
 N1                        NUMBER
 V1                        VARCHAR2(20)
 D1                        DATE
 PADDING                   VARCHAR2(300)

Yep, no column C.  How are we able to select column C from the table if DESC T1 does not list that column?

Let’s drop the second index, the one that used the constant 1 as the second column in the index:

DROP INDEX IND_T1_I2;

Index dropped.

Now that the index is gone that created that hidden virtual column, let’s create our own column C and use that column in a new IND_T1_I2 composite index:

ALTER TABLE T1 ADD (C NUMBER DEFAULT 1 NOT NULL);

                    *
ERROR at line 1:
ORA-01430: column being added already exists in table

ORA-01430?  I never added a column C to the table, did you?  Take a look:

DESC T1

 Name             Null?    Type
 ---------------- -------- -------------
 N1                        NUMBER
 V1                        VARCHAR2(20)
 D1                        DATE
 PADDING                   VARCHAR2(300)

Be gone mysterious column C (do you think this might be the reason why it is a bad idea to rename the system generated hidden virtual columns?):

ALTER TABLE T1 DROP (C);

                     *
ERROR at line 1:
ORA-12996: cannot drop system-generated virtual column

But, but, I dropped the index that caused that hidden virtual column to be created.

ALTER TABLE T1 ADD (C1 NUMBER DEFAULT 1 NOT NULL);

Table altered.

CREATE INDEX IND_T1_I2 ON T1(N1,C1);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>NULL,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100)

I didn’t really want a column C any way, sniff.

Now, if we were to execute this SQL statement, will Oracle’s query optimizer use the just created IND_T1_I2 index to search for NULL values in column N1?  Would the result potentially be different if column C1 did not have a declared NOT NULL constraint?  Keep in mind that the Oracle documentation states: “The query must be guaranteed not to need any NULL values from the indexed expression, because NULL values are not stored in indexes.”

SELECT
  V1,
  N1,
  C1
FROM
  T1
WHERE
  N1 IS NULL;

Did I mention that I had a couple of spare minutes today?  That does not happen too often lately.

Above I stated that it was odd that Oracle Database did not automatically dop the automatically created hidden virtual column when the index for which the column was created was dropped.  So, what happens if the automatically created hidden virtual column is not renamed?  Here is a simple script based on the above experiment to determine what happens (change SYS_NC00005$ in the ALTER TABLE commands as necessary so that the column name is the same as the column name returned from the query of the USER_IND_COLUMNS view).

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  N1 NUMBER,
  V1 VARCHAR2(20),
  D1 DATE,
  PADDING VARCHAR2(300));

CREATE INDEX IND_T1_I1 ON T1(V1);

CREATE INDEX IND_T1_I2 ON T1(N1,1);

INSERT INTO
  T1
SELECT
  DECODE(MOD(ROWNUM,1000),0,NULL,MOD(ROWNUM,1000)) N1,
  TRIM(TO_CHAR(MOD(ROWNUM,1000),'RN')) V1,
  TRUNC(SYSDATE) + MOD(ROWNUM,1000) D1,
  RPAD('A',300,'A') PADDING
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT;

SELECT
  INDEX_NAME,
  COLUMN_NAME
FROM
  USER_IND_COLUMNS
WHERE
  TABLE_NAME = 'T1'
ORDER BY
  INDEX_NAME,
  COLUMN_POSITION;

ALTER TABLE T1 ADD ("SYS_NC00005$" NUMBER DEFAULT 1 NOT NULL);

DROP INDEX IND_T1_I2;

ALTER TABLE T1 ADD ("SYS_NC00005$" NUMBER DEFAULT 1 NOT NULL);

My results follow:

SQL> DROP TABLE T1 PURGE;
Table dropped.

SQL> CREATE TABLE T1 (
  2    N1 NUMBER,
  3    V1 VARCHAR2(20),
  4    D1 DATE,
  5    PADDING VARCHAR2(300));

Table created.

SQL> CREATE INDEX IND_T1_I1 ON T1(V1);

Index created.

SQL> CREATE INDEX IND_T1_I2 ON T1(N1,1);

Index created.

SQL> INSERT INTO
  2    T1
  3  SELECT
  4    DECODE(MOD(ROWNUM,1000),0,NULL,MOD(ROWNUM,1000)) N1,
  5    TRIM(TO_CHAR(MOD(ROWNUM,1000),'RN')) V1,
  6    TRUNC(SYSDATE) + MOD(ROWNUM,1000) D1,
  7    RPAD('A',300,'A') PADDING
  8  FROM
  9    DUAL
 10  CONNECT BY
 11    LEVEL<=100000;

100000 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2    INDEX_NAME,
  3    COLUMN_NAME
  4  FROM
  5    USER_IND_COLUMNS
  6  WHERE
  7    TABLE_NAME = 'T1'
  8  ORDER BY
  9    INDEX_NAME,
 10    COLUMN_POSITION;

INDEX_NAME   COLUMN_NAME
------------ --------------------
IND_T1_I1    V1
IND_T1_I2    N1
IND_T1_I2    SYS_NC00005$

SQL> ALTER TABLE T1 ADD ("SYS_NC00005$" NUMBER DEFAULT 1 NOT NULL);
ALTER TABLE T1 ADD ("SYS_NC00005$" NUMBER DEFAULT 1 NOT NULL)
                    *
ERROR at line 1:
ORA-01430: column being added already exists in table

SQL> DROP INDEX IND_T1_I2;

Index dropped.

SQL> ALTER TABLE T1 ADD ("SYS_NC00005$" NUMBER DEFAULT 1 NOT NULL);

Table altered.

With the original test case, both the first and second ALTER TABLE commands failed.


Actions

Information

4 responses

18 03 2013
Latest data Industry news round up, Log Buffer #311

[...] by a Virtual Column, _OPTIMIZER_IGNORE_HINTS Doesn’t Ignore Hints? Charles Hooper [...]

25 03 2013
Martin Preiss

Charles,

playing a little bit with your example I was surprised to see that it’s possible to drop the base column of a (renamed) virtual column defined by a (dropped) FBI:

drop table t1;

create table t1(
    a number
  , b number    
);

create index t1_idx on t1(a + 1);

select column_name
     , data_default
  from user_tab_cols
 where table_name = 'T1';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ ------------
A
B
SYS_NC00003$                   "A"+1

alter table t1 rename column SYS_NC00003$ to C;

alter table t1 drop column A;

select column_name
     , data_default
  from user_tab_cols
 where table_name = 'T1';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ ------------
B
SYS_NC00002$                   "A"+1

So the renamed virtual column is renamed again (to a – different – synthetic name) – but (more important) it’s data_default is aiming at nowhere. A select on SYS_NC00002$ now results in ORA-00904. For a explicitly defined virtual column it seems to be impossible to drop the associated base column:

drop table t2;
create table t2(
    a number
  , b number
);

alter table t2 add c generated always as (a + 1);

alter table t2 drop column a;
alter table t2 drop column a
                           *
ERROR at Line 1:
ORA-54031: column to be dropped is used in a virtual column expression

Looks not too consistent …

Regards

Martin

25 03 2013
Charles Hooper

Martin,

Nice extension to the test case example. It does appear that there is a bit of error checking code missing to prevent the renaming of a system generated virtual column. Maybe someone else will be able to offer some insight.

We might further exploit the glitch to create a “duplicate” index on the value of A+1 (this almost qualifies for http://jonathanlewis.wordpress.com/2013/03/04/duplicate-indexes/ – until you determine that the original index was in fact dropped)

SELECT
  B,
  SYS_NC00002$
FROM
  T1;
*
ERROR at line 1:
ORA-00904: "A": invalid identifier
 
ALTER TABLE T1 ADD (A VARCHAR2(10));
 
Table altered.
 
SELECT
  B,
  SYS_NC00002$
FROM
  T1;
 
no rows selected
 
INSERT INTO T1 VALUES (1,1);
 
1 row created.
 
INSERT INTO T1 VALUES ('A',1);
                       *
ERROR at line 1:
ORA-01722: invalid number
 
INSERT INTO T1 VALUES ('1',1);
 
CREATE INDEX T1_IDX2 ON T1(A+2);
 
Index created.
 
CREATE INDEX T1_IDX3 ON T1(A+1);
 
Index created.
 
CREATE INDEX T1_IDX4 ON T1(A+1);                           *
ERROR at line 1:
ORA-01408: such column list already indexed
 
SELECT
  COLUMN_NAME,
  DATA_DEFAULT
FROM
  USER_TAB_COLS
WHERE
  TABLE_NAME='T1';
 
COLUMN_NAME                    DATA_DEFAULT
------------------------------ ----------------
A
B
SYS_NC00002$                   "A"+1
SYS_NC00004$                   TO_NUMBER("A")+2
SYS_NC00005$                   TO_NUMBER("A")+1
  
SELECT
  INDEX_NAME,
  COLUMN_NAME
FROM
  USER_IND_COLUMNS
WHERE
  TABLE_NAME='T1';
 
INDEX_NAME COLUMN_NAME
---------- ------------
T1_IDX2    SYS_NC00004$
T1_IDX3    SYS_NC00005$
25 03 2013
Martin Preiss

Charles,

the area seems to become a little bit dangerous:

alter table t1 rename column SYS_NC00002$ to c
                                             *
FEHLER in Zeile 1:
ORA-00600: internal error code, arguments: [kkbrcupfind2], [], [], [], [], [], [], [], [], [], [], []

The (almost) duplicate index also looks strange, but I am not allowed to contribute to this topic again, because Jonathan disqualified me there “for tampering with intent to discredit data dictionary views” – and of course he had good reasons…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 143 other followers

%d bloggers like this: