For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
I can reproduce the effect in 11.2.0.1 - but not in 12.1.0.1.
drop table tbl_1; create table tbl_1 as select rownum id , lpad('*', 50, '*') padding from dual connect by level <= 1000000; ALTER TABLE tbl_1 ADD (col_4 NUMBER DEFAULT 100); --> slow in 11.2.0.1; fast in 12.1.0.1 ALTER TABLE tbl_1 ADD (col_4 NUMBER DEFAULT 100 NOT NULL); --> fast in 11.2.0.1 and 12.1.0.1
drop table tbl_1;
create table tbl_1
as
select rownum id
, lpad('*', 50, '*') padding
from dual
connect by level <= 1000000;
ALTER TABLE tbl_1 ADD (col_4 NUMBER DEFAULT 100);
--> slow in 11.2.0.1; fast in 12.1.0.1
ALTER TABLE tbl_1 ADD (col_4 NUMBER DEFAULT 100 NOT NULL);
--> fast in 11.2.0.1 and 12.1.0.1
A SQL trace for both operations (in 11g) shows that the slow alter (without the explicit not null constraint) does an explicit (and time consuming) update for all row in the table:
update "TBL_1" set "COL_4"=100 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.02 0 1 0 0 Execute 1 11.63 33.56 8336 1016125 2053531 1000000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 11.63 33.58 8336 1016126 2053531 1000000
update "TBL_1" set "COL_4"=100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 1 0 0
Execute 1 11.63 33.56 8336 1016125 2053531 1000000
Fetch 0 0.00 0.00 0 0 0 0
total 2 11.63 33.58 8336 1016126 2053531 1000000
The other trace file does not contain the explicite update. I assume that in the optimized case Oracle memorizes somewhere that col_4 is initially set to 100 and only updates the row in the block after following DML operations. But that's just a guess.
In 11g for adding new columns with DEFAULT values and NOT NULL constraint , the default value is stored in the data dictionary instead of updating the table column as a result especially for large tables the execution time is reduced.
In addition to what Garg has mentioned about 11g new DDL optimisation, here it is a demonstration of that based on Martin's example
SQL> ALTER TABLE tbl_1 ADD (col_4 NUMBER DEFAULT 100 NOT NULL);
Table altered.
Elapsed: 00:00:00.01 ----> very fast
SQL> select /*+ full(tbl_1) */ count(1) from tbl_1 where col_4 = 100;
COUNT(1)
----------
1000000
SQL_ID 4btmwv4k0wkwa, child number 0
-------------------------------------
select /*+ full(tbl_1) */ count(1) from tbl_1 where col_4 = 100
Plan hash value: 3474700848
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 2161 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| TBL_1 | 807K| 10M| 2161 (3)| 00:00:06 |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("COL_4",100)=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Spot the the apparition of the NVL function in the predicate. This is DDL optimisation in action. But when I issue an alter table without the NOT NULL option here what I have
SQL> ALTER TABLE tbl_1 ADD (col_4 NUMBER DEFAULT 100);
Elapsed: 00:00:21.09
2 - filter("COL_4"=100)
There is no NVL function in the predicate part anymore and the alter table took more than 21 seconds instead of less than a second in the first case of DDL optimisation
Best regards
Mohamed Houri
Yep....that has changed with 12c where the adding of column wich are nullable has been optimized the same way as the not null column adding in 11g...
hth
You are right Garg
In MY case same behavior...
select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
15:29:04 SQL> ALTER TABLE tbl_1 ADD (col_4 NUMBER DEFAULT 100); Table altered. 15:31:13 SQL> 15:38:36 SQL> ALTER TABLE tbl_1 ADD (col_5 NUMBER DEFAULT 100 NOT NULL); Table altered. 15:38:45 SQL>
15:29:04 SQL> ALTER TABLE tbl_1 ADD (col_4 NUMBER DEFAULT 100);
15:31:13 SQL>
15:38:36 SQL> ALTER TABLE tbl_1 ADD (col_5 NUMBER DEFAULT 100 NOT NULL);
15:38:45 SQL>
Mohamed,
in 12c the NVL filter has vanished in the dbms_xplan output - but since in this release the addition is also optimized for a column without NOT NULL constraint the NVL would not be sufficient to handle both cases. So I made a CBO trace and there the NVL is still in the plan for the NOT NULL version. For the Query without a constraint I get something weird:
-- 12.1.0.1 -- dbms_xplan.display for both queries PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------- Plan hash value: 3474700848 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | TABLE ACCESS FULL| TBL_1 | 10 | 130 | 5 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("COL_4"=100) -- event 10053 with NOT NULL constraint for col_4 ============ Plan Table ============ --------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 5 | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | TABLE ACCESS FULL | TBL_1 | 10 | 130 | 5 | 00:00:01 | --------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 2 - filter(NVL("COL_4",100)=100) -- event 10053 without NOT NULL constraint for col_4 ============ Plan Table ============ --------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 5 | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | TABLE ACCESS FULL | TBL_1 | 10 | 130 | 5 | 00:00:01 | --------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("COL_4",100),'0',NVL("COL_4",100),'1',"COL_4")=100)
-- 12.1.0.1
-- dbms_xplan.display for both queries
PLAN_TABLE_OUTPUT
| 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TBL_1 | 10 | 130 | 5 (0)| 00:00:01 |
-- event 10053 with NOT NULL constraint for col_4
============
Plan Table
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | | | 5 | |
| 2 | TABLE ACCESS FULL | TBL_1 | 10 | 130 | 5 | 00:00:01 |
Predicate Information:
----------------------
-- event 10053 without NOT NULL constraint for col_4
2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("COL_4",100),'0',NVL("COL_4",100),'1',"COL_4")=100)
The SYS_OP_VECBIT function has been mentioned by Jared Still http://jkstill.blogspot.de/2008/10/undocumented-oracle-functions.html ("Return the value of the bit at position N in a raw value") - but of course it is undocumented. In the given situation it seems to be sufficient to make the distinction between "real" NULL values and the imposters added in the context of the ADD COLUMN operation.
Regards
Martin
and looking closer at the Filter condition it is obvious that there is a virtual column SYS_NC00003$ added - and I guess that this column is updated when col_4 is explicitly set to NULL.
and a little test to check the assumption:
select vsize(SYS_NC00003$), count(*) from TBL_1 group by vsize(SYS_NC00003$); VSIZE(SYS_NC00003$) COUNT(*) ------------------- ---------- 1000 update TBL_1 set col_4 = NULL where id < 10; --> 9 rows updated select vsize(SYS_NC00003$), count(*) from TBL_1 group by vsize(SYS_NC00003$); VSIZE(SYS_NC00003$) COUNT(*) ------------------- ---------- 1 9 991
select vsize(SYS_NC00003$), count(*) from TBL_1 group by vsize(SYS_NC00003$);
VSIZE(SYS_NC00003$) COUNT(*)
------------------- ----------
1000
update TBL_1 set col_4 = NULL where id < 10;
--> 9 rows updated
1 9
991
Looks plausible.
Indeed every update of col_4 results in an update of SYS_NC00003$ - so the column seems to be just a "has-been-updated-after-column-definition" flag.
Martin,
create an index on col_4 and when it used then the NVL is gone
SQL> select * from v$version;
SQL> create index ind_col_4 on tbl_1 (col_4);
Index created.
SQL> select /*+ index(tbl_1) */ count(1) from tbl_1 where col_4 = 100;
SQL_ID d0jxun6q4tr28, child number 0
select /*+ index(tbl_1) */ count(1) from tbl_1 where col_4 = 100
Plan hash value: 2370058606
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 533 (100)| |
|* 2 | INDEX RANGE SCAN| IND_COL_4 | 1129K| 14M| 533 (8)| 00:00:02 |
2 - access("COL_4"=100)
This is because the index contains not null col_4
Update : I want to say that there is no threat of not using an index because of this internal NVL modification. This (according to my tests) NVL transformation occurs only for FULL table scan
in this case I would expect the "magic" to take place in the context of the index build - since the server has to know that the indexed value is not NULL (probably resulting in one block index without leaf entries) but 100. I tried to make a CBO trace of the index creation - but though the result is not meaningless I don't see an explicit information on this special operation I would expect to take place after the full table scan.
-- example with only 1000 rows ============ Plan Table ============ -------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------+-----------------------------------+ | 0 | CREATE INDEX STATEMENT | | | | 3 | | | 1 | INDEX BUILD NON UNIQUE | TBL_1_IDX| | | | | | 2 | SORT CREATE INDEX | | 981 | 12K | | | | 3 | TABLE ACCESS FULL | TBL_1 | 981 | 12K | 2 | 00:00:01 | -------------------------------------------+-----------------------------------+
-- example with only 1000 rows
-------------------------------------------+-----------------------------------+
| 0 | CREATE INDEX STATEMENT | | | | 3 | |
| 1 | INDEX BUILD NON UNIQUE | TBL_1_IDX| | | | |
| 2 | SORT CREATE INDEX | | 981 | 12K | | |
| 3 | TABLE ACCESS FULL | TBL_1 | 981 | 12K | 2 | 00:00:01 |
When the mapping information (NULL => 100) is already included in the index there is no additional need for a NVL operation in the plan for the select, I think.
Mohamed Houri wrote: Update : I want to say that there is no threat of not using an index because of this internal NVL modification. This (according to my tests) NVL transformation occurs only for FULL table scan
Mohamed Houri wrote:
have you tried what happens in case of a multi-column index that covers NULL values for col_4?
Randolf
Martin Preiss wrote: Mohamed, in this case I would expect the "magic" to take place in the context of the index build - since the server has to know that the indexed value is not NULL (probably resulting in one block index without leaf entries) but 100. I tried to make a CBO trace of the index creation - but though the result is not meaningless I don't see an explicit information on this special operation I would expect to take place after the full table scan.
Martin Preiss wrote:
shouldn't this be part of the projection information taken from the full table scan? I wouldn't expect this as separate, special operation, but simply as projection expression. May be it would become visible by doing an EXPLAIN PLAN on the CREATE INDEX statement with the PROJECTION format modifier (or ADVANCED).
Randolf,
Here is the case of multi-columns index
SQL> create table tbl_1
2 as
3 select rownum id
4 , lpad('*', 50, '*') padding
5 from dual
6 connect by level <= 1000000;
Table created.
SQL> alter table tbl_1 modify id not null;
SQL> explain plan for create index ind1 on tbl_1(col_4);
Explained.
SQL> select * from table(dbms_xplan.display(format => 'advanced'));
Plan hash value: 3205640992
| 0 | CREATE INDEX STATEMENT | | 692K| 8787K| 2696 (2)| 00:00:08 |
| 1 | INDEX BUILD NON UNIQUE| IND1 | | | | |
| 2 | SORT CREATE INDEX | | 692K| 8787K| | |
| 3 | TABLE ACCESS FULL | TBL_1 | 692K| 8787K| 2149 (2)| 00:00:06 |
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - CRI$1
3 - CRI$1 / TBL_1@CRI$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - SYSDEF[272]
2 - (#keys=2) "COL_4"[NUMBER,22], "TBL_1".ROWID[ROWID,10]
3 - "TBL_1".ROWID[ROWID,10], "COL_4"[NUMBER,22]
- estimated index size: 18M bytes
SQL> explain plan for create index ind1 on tbl_1(id, col_4);
| 0 | CREATE INDEX STATEMENT | | 692K| 17M| 3006 (2)| 00:00:08 |
| 2 | SORT CREATE INDEX | | 692K| 17M| | |
| 3 | TABLE ACCESS FULL | TBL_1 | 692K| 17M| 2149 (2)| 00:00:06 |
2 - (#keys=3) "ID"[NUMBER,22], "COL_4"[NUMBER,22],
"TBL_1".ROWID[ROWID,10]
3 - "TBL_1".ROWID[ROWID,10], "ID"[NUMBER,22], "COL_4"[NUMBER,22]
- estimated index size: 28M bytes
28 rows selected.
SQL> create index ind1 on tbl_1(id, col_4);
SQL> select count(1) from tbl_1 where id = 1 and col_4 = 100;
1
SQL_ID 00h4fsjcfw5s4, child number 0
select count(1) from tbl_1 where id = 1 and col_4 = 100
Plan hash value: 2032253151
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | INDEX RANGE SCAN| IND1 | 1 | 26 | 1 (0)| 00:00:01 |
2 - access("ID"=1 AND "COL_4"=100)
SQL> select count(1) from tbl_1 where col_4 = 100;
SQL> start c:\x
SQL_ID 5h5vx6b1mk6g9, child number 0
select count(1) from tbl_1 where col_4 = 100
Plan hash value: 2283549759
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 701 (100)| |
|* 2 | INDEX FAST FULL SCAN| IND1 | 936K| 11M| 701 (6)| 00:00:02 |
When an index that contains col_4 is used (even in a mutli-column index that covers the ''null'' col_4 values) there is no NVL transformation.
But when a full table scan kicks in then this NVL transformation occurs
SQL> select /*+ full(tbl_1) */ count(1) from tbl_1 where id = 1 and col_4 = 100;
SQL_ID 9njm4qha9m1d6, child number 0
select /*+ full(tbl_1) */ count(1) from tbl_1 where id = 1 and col_4 =
100
| 0 | SELECT STATEMENT | | | | 2168 (100)| |
|* 2 | TABLE ACCESS FULL| TBL_1 | 1 | 26 | 2168 (3)| 00:00:06 |
2 - filter(("ID"=1 AND NVL("COL_4",100)=100))
good idea - but in the predicate section I don't see a difference to the result I get when I create an index on a similar table with a initially loaded col_4:
-- 11.2.0.1 -- ... -- ALTER TABLE tbl_1 ADD (col_4 NUMBER DEFAULT 100 NOT NULL); explain plan for create index tbl_1_idx on tbl_1(col_4); select * from table(dbms_xplan.display(NULL, NULL, 'advanced')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------ Plan hash value: 2562295134 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | CREATE INDEX STATEMENT | | 981 | 12753 | 3 (0)| 00:00:01 | | 1 | INDEX BUILD NON UNIQUE| TBL_1_IDX | | | | | | 2 | SORT CREATE INDEX | | 981 | 12753 | | | | 3 | TABLE ACCESS FULL | TBL_1 | 981 | 12753 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - CRI$1 3 - CRI$1 / TBL_1@CRI$1 Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - SYSDEF[296] 2 - (#keys=2) "COL_4"[NUMBER,22], "TBL_1".ROWID[ROWID,10] 3 - "TBL_1".ROWID[ROWID,10], "COL_4"[NUMBER,22] Note ----- - estimated index size: 65536 bytes -- and the result for a simpler table tbl_2 create table tbl_2 as select rownum id , lpad('*', 50, '*') padding , 100 col_4 from dual connect by level <= 1000; explain plan for create index tbl_2_idx on tbl_2(col_4); select * from table(dbms_xplan.display(NULL, NULL, 'advanced')); Plan hash value: 1614733972 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | CREATE INDEX STATEMENT | | 981 | 12753 | 3 (0)| 00:00:01 | | 1 | INDEX BUILD NON UNIQUE| TBL_2_IDX | | | | | | 2 | SORT CREATE INDEX | | 981 | 12753 | | | | 3 | TABLE ACCESS FULL | TBL_2 | 981 | 12753 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - CRI$1 3 - CRI$1 / TBL_2@CRI$1 Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - SYSDEF[296] 2 - (#keys=2) "COL_4"[NUMBER,22], "TBL_2".ROWID[ROWID,10] 3 - "TBL_2".ROWID[ROWID,10], "COL_4"[NUMBER,22] Note ----- - estimated index size: 65536 bytes
-- 11.2.0.1
-- ...
-- ALTER TABLE tbl_1 ADD (col_4 NUMBER DEFAULT 100 NOT NULL);
explain plan for
create index tbl_1_idx on tbl_1(col_4);
select *
from table(dbms_xplan.display(NULL, NULL, 'advanced'));
------------------------------------------------------------------------------------
Plan hash value: 2562295134
| 0 | CREATE INDEX STATEMENT | | 981 | 12753 | 3 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| TBL_1_IDX | | | | |
| 2 | SORT CREATE INDEX | | 981 | 12753 | | |
| 3 | TABLE ACCESS FULL | TBL_1 | 981 | 12753 | 2 (0)| 00:00:01 |
1 - SYSDEF[296]
- estimated index size: 65536 bytes
-- and the result for a simpler table tbl_2
create table tbl_2
, 100 col_4
connect by level <= 1000;
create index tbl_2_idx on tbl_2(col_4);
Plan hash value: 1614733972
| 1 | INDEX BUILD NON UNIQUE| TBL_2_IDX | | | | |
| 3 | TABLE ACCESS FULL | TBL_2 | 981 | 12753 | 2 (0)| 00:00:01 |
3 - CRI$1 / TBL_2@CRI$1
2 - (#keys=2) "COL_4"[NUMBER,22], "TBL_2".ROWID[ROWID,10]
3 - "TBL_2".ROWID[ROWID,10], "COL_4"[NUMBER,22]
thanks for peforming the tests. So, as Martin already suggested, certainly the index creation needs to take care of the expression already. By the way, in 11.2.0.3 the NVL expression is already eliminated from the predicate information... A bit worrying that the actual predicates applied differ from what the predicates shown suggest.
yes, unfortunately the "Projection" information isn't really reflecting what expressions get used, it's the same with queries, I often have the problem that I would like to know at what operation exactly what expression is evaluated (for example, if it is a costly PL/SQL function, or a CPU intensive expression), but in most cases this is not reflected properly in the projection information.
by the way, if you change the test slightly to an index + table lookup with a filter on COL_4 on the table lookup the NVL expression is back, too (so not only for full table scans) - basically whenever the untouched table blocks need to be accessed some special logic needs to be there.
Yes.
This is what I was trying to express in my above message (table access being it full or via index rowid) but unfortunately have confused people by saying full table scan instead of table access.
SQL> drop index ind1;
Index dropped.
SQL> create index ind1 on tbl_1 (id);
SQL> select * from tbl_1 where id in(1,12428) and (col_4 is null or col_4 <= 42);
no rows selected
SQL_ID cn8ruz8w5m2wd, child number 0
select * from tbl_1 where id in(1,12428) and (col_4 is null or col_4 <=
42)
Plan hash value: 3814447929
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 14 (100)| |
| 1 | INLIST ITERATOR | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| TBL_1 | 23 | 2944 | 14 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND1 | 3746 | | 5 (0)| 00:00:01 |
2 - filter(NVL("COL_4",100)<=42)
3 - access(("ID"=1 OR "ID"=12428))