constraint unique for deferrable novalidate or validate

Testing enviroment 11.2.0.4.7 x86_64_Linux

  1. The table t1 has duplicate value and use the deferrable novalidate to create unique constraint;
  2. The table t2 records are unique and the unique constraint is validate.
scripts:

drop table t1 purge;
create table t1 as select 'aaaa' name,rownum id from dual connect by level  user,tabname => 'T1',estimate_percent => null,cascade=>true,method_opt=>'FOR ALL COLUMNS SIZE 1');
exec dbms_stats.gather_index_stats(ownname => user, indname=>'UK_T1', estimate_percent=> null);
select index_name,table_owner,table_name,uniqueness,status,num_rows from user_indexes where index_name = 'UK_T1';
insert into t1 values('cccc',500);
set autotrace on
select * from t1 where id = 200;
select * from t1 where id = 500;
set autotrace off

drop table t2 purge;
create table t2 as select 'aaaa' name,rownum id from dual connect by level  user,tabname => 'T2',estimate_percent => null,cascade=>true,method_opt=>'FOR ALL COLUMNS SIZE 1');
exec dbms_stats.gather_index_stats(ownname=>null, indname=>'UK_T2', estimate_percent=> null);
select index_name,table_owner,table_name,uniqueness,status,num_rows from user_indexes where index_name = 'UK_T2';
set autotrace on
select * from t2 where id = 500;
set autotrace off

Testing results:

SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select 'aaaa' name,rownum id from dual connect by level  insert into t1 values('bbbb',500); 
1 row created.

SQL> commit;

Commit complete.


--add unique constraint

SQL> alter table t1 add constraint uk_t1 unique(id) deferrable novalidate;

Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T1',estimate_percent => null,cascade=>true,method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(ownname=>null, indname=>'UK_T1', estimate_percent=> null);

PL/SQL procedure successfully completed.

SQL> select index_name,table_owner,table_name,uniqueness,status,num_rows from user_indexes where index_name = 'UK_T1';

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     UNIQUENES STATUS     NUM_ROWS
------------------------------ ------------------------------ ------------------------------ --------- -------- ----------
UK_T1                          CBO                            T1                             NONUNIQUE VALID         10001

When the constraint is deferrable novalidate, the index is nonunique.

Do a test value to test the constraint is available, and you see it’s working.

SQL> insert into t1 values('cccc',500);
insert into t1 values('cccc',500)
*
ERROR at line 1:
ORA-00001: unique constraint (CBO.UK_T1) violated


Do a query for a unique key id = 200 and get the execution plan:

SQL> select /*+ gather_plan_statistics*/ * from t1 where id = 200;

NAME         ID
---- ----------
aaaa        200

SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1js7bhvunaa2y, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/ * from t1 where id = 200

Plan hash value: 3862923884

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |       |     2 (100)|      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |     9 |     2   (0)|      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | UK_T1 |      1 |      1 |       |     1   (0)|      1 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------

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

   2 - access("ID"=200)

19 rows selected.

Do a query for the duplicate value id = 500 and get the plan

SQL> select /*+ gather_plan_statistics*/ * from t1 where id = 500;

NAME         ID
---- ----------
aaaa        500
bbbb        500

SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a1hy077k0cy45, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/ * from t1 where id = 500

Plan hash value: 3862923884

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |       |     2 (100)|      2 |00:00:00.01 |       5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |     9 |     2   (0)|      2 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN          | UK_T1 |      1 |      1 |       |     1   (0)|      2 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------

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

   2 - access("ID"=500)

19 rows selected.

From the queries above, when the unique constraint is deferrable novalite and whatever the value is, Oracle optimizer always chooses the INDEX RANGE SCAN.

Now test second case:
the unique constraint is validated.

SQL> drop table t2 purge;

Table dropped.

SQL> create table t2 as select 'aaaa' name,rownum id from dual connect by level  alter table t2 add constraint uk_t2 unique(id) validate;

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T2',estimate_percent => null,cascade=>true,method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(ownname=>null, indname=>'UK_T2', estimate_percent=> null);

PL/SQL procedure successfully completed.

SQL> select index_name,table_owner,table_name,uniqueness,status,num_rows from user_indexes where index_name = 'UK_T2';

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     UNIQUENES STATUS     NUM_ROWS
------------------------------ ------------------------------ ------------------------------ --------- -------- ----------
UK_T2                          CBO                            T2                             UNIQUE    VALID         10000

SQL> select /*+ gather_plan_statistics*/ * from t2 where id = 500;

NAME         ID
---- ----------
aaaa        500

SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2c2vk9hkfbg9p, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/ * from t2 where id = 500

Plan hash value: 1806459291

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |       |     2 (100)|      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |      1 |      1 |     9 |     2   (0)|      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | UK_T2 |      1 |      1 |       |     1   (0)|      1 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------------

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

   2 - access("ID"=500)

19 rows selected.

The execution plan is using INDEX UNIQUE SCAN.

note:x.sql from tanel poder website http://blog.tanelpoder.com.

此条目发表在Index分类目录。将固定链接加入收藏夹。

发表评论

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 / 更改 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s