Mohamed Houri’s Oracle Notes

June 24, 2017

ORA-54032 : column to be renamed is used in a virtual column expression

Filed under: Oracle,virtual column — hourim @ 3:45 pm

This is a simple note re-explaining what this 12cR1 ORA-54032 error is as it kicked in again a couple of days ago at one of my customer sites and showing that it has definitely been solved in 12cR2.

Here’s a how to reproduce it at will:

12cR1

SQL> select banner from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> create table t1(x number, y number, z number);

Table created.

SQL> alter table t1 rename column x to x_bis;

Table altered.

As you can see we renamed column x to x_bis without any issue. However if we create a column group extension out of (x_bis, y) columns we will not be able to rename neither x_bis nor y column as shown below:

SQL> SELECT
          dbms_stats.create_extended_stats
           (ownname   => user
           ,tabname   => 't1'
          ,extension => '(x_bis,y)'
           ) ext
    FROM dual;

-------------------------------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0

SQL> alter table t1 rename column x_bis to x;
alter table t1 rename column x_bis to x
                             *
ERROR at line 1:
ORA-54032: column to be renamed is used in a virtual column expression

That’s exactly what happened to my client with the sole difference that the client has never explicitly created any virtual column hence its great stupefaction. I had to explain him that this is because Oracle has created, behind the scenes, an extended column group, which is nothing else than a virtual column. This creation is very probably a response to a SQL Plan Directive request.

And indeed, the column group extension I have manually created above is a virtual column created out of a combination of x_bis and y column as clearly shown by the default value of this virtual column:

SQL> select 
         column_name
        ,data_default
        ,data_length
     from
       user_tab_cols
     where
       table_name = 'T1'
     and hidden_column = 'YES';

COLUMN_NAME                    DATA_DEFAULT                      DATA_LENGTH
------------------------------ --------------------------------- -----------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0 SYS_OP_COMBINED_HASH("X_BIS","Y")   22

We are unable to rename the x_bis column to x because x_bis is used in the definition of the above virtual column. But that was before the arrival of 12cR2.

12cR2

Execute the same experiment in 12cR2 and you will realize that things have changed as the followings demonstrate:

SQL> select banner from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t1(x number, y number, z number);

Table T1 created.

SQL> alter table t1 rename column x to x_bis;

Table T1 altered.

  SELECT
      dbms_stats.create_extended_stats
       (ownname   => user
       ,tabname   => 't1'
       ,extension => '(x_bis,y)'
       ) ext
  FROM dual;

EXT
-------------------------------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0

SQL> alter table t1 rename column x_bis to x;

Table T1 altered.

As of 12cR2 we are not anymore annoyed by the error when altering a table column used in a virtual column expression. This is because Oracle will take the alter column from the table level and extend it to the virtual column where it can be found as shown below:

SQL> select 
         column_name
        ,data_default
        ,data_length
     from
       user_tab_cols
     where
       table_name = 'T1'
     and hidden_column = 'YES';

COLUMN_NAME                    DATA_DEFAULT                   DATA_LENGTH
------------------------------ ------------------------------ -----------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0 SYS_OP_COMBINED_HASH("X","Y")           22

Spot how, in the data_default value of the generated column group extension, the new modified name of the column has been changed as well.

SUMMARY

Again you know that an automatic extended column group extension, which might be created on behalf of you, generates a border corner where you are not anymore able to rename any of the columns that composed the extend virtual column group. Fortunately as of Oracle 12cR2 Oracle extends the column renaming directly to the extended virtual column and doesn’t raise the ORA-054023 anymore.

Advertisements

October 25, 2013

Index on a virtual column: would it help others?

Filed under: virtual column — hourim @ 2:30 pm

Is it possible to create an index on a column a and use it for column b? In certain situations yes.

Let’s first build the model and explore those situations (in 11.2.0.3)

  create table t1
        as   select
             rownum n1
            ,to_date(to_char(to_date('01/01/1985','dd/mm/yyyy'),'J') + trunc(dbms_random.value(1,11280)),'J') ord_date
            ,dbms_random.string('L',dbms_random.value(1,30))||rownum text_comment
        from dual
        connect by level <= 10000;

  alter table t1 add virt_date date generated always as (trunc(ord_date)) virtual;

  begin
        dbms_stats.gather_table_stats
           (ownname          => user,
            tabname          =>'t1',
            method_opt       => 'for all columns size 1'
          );
      end;
     /
 

I have created a simple table t1 to which I have attached a virtual column using trunc function (here below with n°4):

  describe t1
 Name                 Type
 -------------------- --------------------
 1      N1            NUMBER
 2      ORD_DATE      DATE
 3      TEXT_COMMENT  VARCHAR2(4000 CHAR)
 4      VIRT_DATE     DATE
 

I want to explore the following select:

 select * from t1 where ord_date =to_date('02/08/2011','dd/mm/yyyy');
 

I have a predicate on ord_date. I have a virtual column based on ord_date. What do you think if I create an index on the virtual column?  Would it help my above query?

 create index ind_virt_date on t1(virt_date);
 select * from t1 where ord_date =to_date('02/08/2011','dd/mm/yyyy');

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

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter("ORD_DATE"=TO_DATE(' 2011-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 2 - access("T1"."VIRT_DATE"=TRUNC(TO_DATE(' 2011-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
 

Yes it did. The CBO uses the index on the virtual column (ind_virt_date) which is based on a different column (ord_date) to cover the predicate where ord_date = to_date(’02/08/2011′,’dd/mm/yyyy’);

What I have shown up to now is: If you create an index on a virtual column, and you use a where clause on the column on which the virtual column is based on, the index is selected by the CBO.

But the question is: would this be extensible to all types of virtual columns?

And the answer is: I don’t think so

Ok, let’s test with another virtual column using again the trunc function

 alter table t1 add virt1_n1 number generated always as(trunc(n1)) virtual;
 create index ind_virt_n1 on t1 (virt1_n1);

 select * from t1 where n1 = 1;

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

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter("N1"=1)
 2 - access("T1"."VIRT_N1"=TRUNC(1))
 

The index on the virtual column has been again used to cover a predicate on its underlying column.

Let’s test another case with ceil function this time.


 drop index ind_virt_n1;
 drop index ind_virt_date;
 alter table t1 drop column virt1_n1;
 alter table t1 add virt1_n1 generated always as (ceil(n1)) virtual;

 create index ind_virt_n1 on t1(virt1_n1);

 select * from t1 where n1 = 1;

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

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

The index on the virtual column has, however, not been used by the CBO in this case

Another case with abs function this time.


 alter table t1 drop column virt1_n1;
 alter table t1 add virt1_n1 generated always as (abs(n1)) virtual;
 create index ind_virt_n1 on t1(virt1_n1);

 select * from t1 where n1 = 1;

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

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

Another case with nvl function this time:

alter table t1 drop column virt1_n1;
alter table t1 add virt1_n1 generated always as (nvl(n1,0)) virtual;

create index ind_virt_n1 on t1(virt1_n1);

select * from t1 where n1 = 1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    17 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    40 |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=1)

The index on these virtual columns has not been used by the CBO.

Summary: In all cases of functions I have used to generate the virtual column, the only function that has permitted to an index created on its generated virtual column to be selected by the CBO (when this virtual column is not in the predicate part) is the trunc function.

My first thought was that when the internal definition of the virtual column matches the expression I’ve used to create the virtual column then the index will be used. But I was wrong because both trunc and ceil (or abs or nvl) have the same matching between their internal definition and the expression I have used to create them respectively:

 select column_name col_name, data_default def
 from dba_tab_columns
 where table_name = 'T1'
 and column_name in ('VIRT_DATE','VIRT_N1', 'VIRT1_N1', 'VIRT2_N1','VIRT3_N1');

 COL_NAME       DEF
 ------------  -----------------
 VIRT_DATE     TRUNC("ORD_DATE")
 VIRT_N1       TRUNC("N1",2)
 VIRT2_N1      ABS("N1")
 VIRT3_N1      NVL("N1",0)
 VIRT1_N1      CEIL("N1")
 

Do you know why the trunc function works differently here?

Ah! by the way I have also tested the trunc function by setting(in 12c) the hidden parameter _truncate_optimization_enabled from its default value TRUE to FALSE and this has not changed anything

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)