Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, ludovic.

Asked: January 07, 2014 - 9:58 am UTC

Last updated: January 08, 2014 - 6:20 pm UTC

Version: 11.2.0.3.0

Viewed 1000+ times

You Asked

Hello Tom,

I have a strange behavior regarding default values on columns. I have different executions plan for a same "select" statement following I add a column with a default value in a existing table or I create the table with the default value directly on the column. I made an example :

SQL> create table T (i number);

Table created.

SQL> create table T1 (i number, a number default 0 not null);

Table created.

SQL>
SQL> insert into T values (1);

1 row created.

SQL> insert into T values (2);

1 row created.

SQL>
SQL> insert into T1 values (1,0);

1 row created.

SQL> insert into T1 values (2,0);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> alter table T add a number default 0 not null;

Table altered.

SQL>
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;

Session altered.

SQL>
SQL> select i,a
2 from T
3 where a = 1;

no rows selected

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 10697mdrsa0dj, child number 0
-------------------------------------
select i,a from T where a = 1

Plan hash value: 1601196873

------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 0 |00:00:00.01 | 7 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NVL("A",0)=1)

Note
-----
- dynamic sampling used for this statement (level=2)

22 rows selected.

SQL>
SQL> select i,a
2 from T1
3 where a = 1;

no rows selected

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID dcyn3q8dvzn3a, child number 0
-------------------------------------
select i,a from T1 where a = 1

Plan hash value: 3617692013

------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=1)

Note
-----
- dynamic sampling used for this statement (level=2)

22 rows selected.

In the first case only I have filter(NVL("A",0)=1) in the execution plan. If I create an index on the column A, this one will never be used by CBO. Why I have 2 different executions plan ?

I use the Oracle version 11.2.0.3.0.

Thanks in advance.

and Tom said...

I only see one execution plan here - full scan regardless of which table is used.

Please expand on what exactly the question is here.

And you should just index A if you want to use an index on A. And it will use it. For example:


ops$tkyte%ORA11GR2> create table t
  2  as
  3  select object_id, object_name, object_type
  4    from all_objects;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t add A number default 0 not null;

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 0, 'xxx', 'yyyy', 1 );

1 row created.

ops$tkyte%ORA11GR2> create index t_idx1 on t( a );

Index created.

ops$tkyte%ORA11GR2> create index t_idx2 on t( nvl(a,0) );

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', estimate_percent => 100, method_opt => 'for all indexed columns' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> select /*+ gather_plan_statistics */ * from t where a = 1;

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE                  A
---------- ------------------------------ ------------------- ----------
         0 xxx                            yyyy                         1

ops$tkyte%ORA11GR2> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cuc6cqxwxrtvj, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where a = 1

Plan hash value: 4055207394

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN          | T_IDX1 |      1 |      1 |      1 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"=1)


19 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select /*+ gather_plan_statistics */ * from t where nvl(a,0) = 1;

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE                  A
---------- ------------------------------ ------------------- ----------
         0 xxx                            yyyy                         1

ops$tkyte%ORA11GR2> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dwu8516gpfm28, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where nvl(a,0) = 1

Plan hash value: 3371054274

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN          | T_IDX2 |      1 |      1 |      1 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."SYS_NC00005$"=1)


19 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace off



Notice that the index on t(a) is used when you use "a=1" and the index on t(nvl(a,0)) is only used if you explicitly put that into the predicate.

yes, we do a tricky rewrite to support the fast add column in 11.2 - but it won't exclude the index from being used.





Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

data dictionary information about fast added column

Sokrates, January 07, 2014 - 7:44 pm UTC

is there any dictionary view where we can see that a column was fast added and thus a tricky rewrite might happen ?
Neither COLS nor USER_TAB_COLS seem to have this.
Tom Kyte
January 08, 2014 - 5:46 pm UTC

Not that I'm aware of. as far as anyone is concerned (application wise), it doesn't really matter if it was fast added or not.

In 11gR2 you can assume that any column added with a DEFAULT that is not null is "fast added"

In 12cR1, you can assume that any column added with a DEFAULT is "fast added":

ops$tkyte%ORA12CR1> create table t ( x int );

Table created.

ops$tkyte%ORA12CR1> 
ops$tkyte%ORA12CR1> insert into t values ( 0 );

1 row created.

ops$tkyte%ORA12CR1> commit;

Commit complete.

ops$tkyte%ORA12CR1> 
ops$tkyte%ORA12CR1> alter table t add a number default 0 not null;

Table altered.

ops$tkyte%ORA12CR1> alter table t add b number default 1;

Table altered.

ops$tkyte%ORA12CR1> 
ops$tkyte%ORA12CR1> select /*+ gather_plan_statistics */ * from t where a = 1;

no rows selected

ops$tkyte%ORA12CR1> select * from table( dbms_xplan.display_cursor( null,null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cuc6cqxwxrtvj, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where a = 1

Plan hash value: 1601196873

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      0 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |      1 |      0 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NVL("A",0)=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


22 rows selected.

ops$tkyte%ORA12CR1> 
ops$tkyte%ORA12CR1> select /*+ gather_plan_statistics */ * from t where b = 1;

         X          A          B
---------- ---------- ----------
         0          0          1

ops$tkyte%ORA12CR1> select * from table( dbms_xplan.display_cursor( null,null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3n6zhg0grhqyx, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where b = 1

Plan hash value: 1601196873

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |      1 |      1 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("B",1
              ),'0',NVL("B",1),'1',"B")=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


23 rows selected.


but again, it isn't like you have to code to this - you just index A or B and we'll use it when appropriate...




ops$tkyte%ORA12CR1> create index t_idx1 on t(a);

Index created.

ops$tkyte%ORA12CR1> create index t_idx2 on t(b);

Index created.

ops$tkyte%ORA12CR1> 
ops$tkyte%ORA12CR1> select /*+ gather_plan_statistics */ * from t where a = 1;

no rows selected

ops$tkyte%ORA12CR1> select * from table( dbms_xplan.display_cursor( null,null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cuc6cqxwxrtvj, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where a = 1

Plan hash value: 206624438

--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |      1 |        |      0 |00:00:00.01 |       1 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T      |      1 |      1 |      0 |00:00:00.01 |       1 |
|*  2 |   INDEX RANGE SCAN                  | T_IDX1 |      1 |      1 |      0 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


23 rows selected.

ops$tkyte%ORA12CR1> 
ops$tkyte%ORA12CR1> select /*+ gather_plan_statistics */ * from t where b = 1;

         X          A          B
---------- ---------- ----------
         0          0          1

ops$tkyte%ORA12CR1> select * from table( dbms_xplan.display_cursor( null,null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3n6zhg0grhqyx, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where b = 1

Plan hash value: 369893979

--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T      |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | T_IDX2 |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


23 rows selected.

ludovic Cellier, January 08, 2014 - 10:16 am UTC 

Hello Tom,

Thanks for your answer. I made the same example that you on my Oracle database and I don't have the same result in the execution plan for the query "select /*+ gather_plan_statistics */ * from t where a = 1;". 

In your execution plan I see :

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"=1)

In my result I have :

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NVL("A",0)=1)

Please find below the fully test I executed :

SQL> create table t
  2  as
  3  select object_id, object_name, object_type
  4  from all_objects;

Table created.

SQL> alter table t add A number default 0 not null;

Table altered.

SQL> insert into t values ( 0, 'xxx', 'yyyy', 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> create index t_idx1 on t( a );

Index created.

SQL> exec dbms_stats.gather_table_stats( user, 'T', estimate_percent => 100, method_opt => 'for all indexed columns' );

PL/SQL procedure successfully completed.

SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ * from t where a = 1;

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE                  A
---------- ------------------------------ ------------------- ----------
         0 xxx                            yyyy                         1

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  cuc6cqxwxrtvj, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ * from t where a = 1

Plan hash value: 4055207394

--------------------------------------------------------------------------------
----------------

| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Ti
me   | Buffers |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----------------

|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:0
0.01 |       3 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |      1 |      1 |00:00:0
0.01 |       3 |

|*  2 |   INDEX RANGE SCAN          | T_IDX1 |      1 |      1 |      1 |00:00:0

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0.01 |       2 |

--------------------------------------------------------------------------------
----------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NVL("A",0)=1)


19 rows selected.

However the index is used despit of "access(NVL("A",0)=1)"

Tom Kyte
January 08, 2014 - 6:20 pm UTC

as I said - you don't need to be concerned about the "hidden functions", they are completely internal, the do not obviate the ability to use an index.

When using the index, the hidden functions are not used - they do not need to be used. the index has to have been created AFTER the column was added, therefore, the entries will be in the index. When accessing the table however, we need the special function since we don't know if the value is in the table or not.


If anything - this is an issue with the display of the plan, when using "access" via the index, the function isn't necessary. When using "filter" on the table - it is.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library