Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
Make it possible to distinguish in-line constraint from out-of-line one when both are semantically t
At the moment there is no standard way to distinguish in-line constraint from out-of-line one when both are semantically the same. It's not possible to do using regular database API (user_constraints, for example). Although the constraints look just the same, there is an important difference between them which does matter for optimizer, for example. If Oracle makes difference between constraints of the types, then the possibility should be provided for those who use this functionality, i.e. for Oracle developers. The following code snippet shows the details of the idea:
1. Create table with in-line and out-of-line constraints which are semantically the same.
SQL> create table test2
2 (id number not null,
3 check ("ID" IS NOT NULL))
4 /
Table created.
2. Try to find out, using regular API, which of them is in-line and which is not.
SQL> select constraint_name, search_condition
2 from user_constraints
3 where table_name = 'TEST2'
4 and constraint_type = 'C'
5 /
CONSTRAINT_NAME SEARCH_CONDITION
------------------------------ --------------------------------------------------------------------------------
SYS_C00699619 "ID" IS NOT NULL
SYS_C00699620 "ID" IS NOT NULL
SQL> select constraint_name, table_name, column_name
2 from user_cons_columns
3 where table_name = 'TEST2'
4 /
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ -----------
SYS_C00699619 TEST2 ID
SYS_C00699620 TEST2 ID
3. Still no luck. To get the info one should look at sys.cdef$.type# data. It contains 7 for not null in-line constraints, and 1 - for not null checks (out-of-line constraints). But this is undocumented way to query database dictionary. Hence, developers cannot be sure that Oracle will keep the values in its next releases. Let alone the problem of getting select privilege on sys-tables.
The problem is encountered when automating a system deployment process: there is a need to find out which of existing constraints make a column "not null" (i.e. in-line one), and which one is just out-of-line check and therefore should be dropped as "duplicate".
Comments
-
Although the constraints look just the same, there is an important difference between them which does matter for optimizer
Can you prove that the CBO handles them differently?
-
I provided a solution to that in my reply to the thread you posted before.
. . .
COLS.NULLABLE is only set to ‘N’ when you specify a not null constraint . . .As I said there you can EASILY test that yourself:
CREATE TABLE TEST (ID NUMBER NOT NULL);
CREATE TABLE TEST1 (ID NUMBER);
ALTER TABLE TEST1 ADD CHECK ("ID" IS NOT NULL);
select table_name, column_name, data_type, nullable
from user_tab_columns
where table_name in ('TEST', 'TEST1') order by table_name, column_name;
TABLE_NAME,COLUMN_NAME,DATA_TYPE,NULLABLE
TEST,ID,NUMBER,N
TEST1,ID,NUMBER,YSee the difference? The column in TEST is marked NOT NULLABLE but the column in TEST1 is NOT marked.
-
Although the constraints look just the same, there is an important difference between them which does matter for optimizer
Can you prove that the CBO handles them differently?
Please find the proof below:
SQL> create table test3 (id number, name varchar2(30), check (id is not null));
Table created.
SQL> create index test3_i on test3(id);
Index created.
SQL> insert into test3 select object_id, object_name from all_objects;
7908295 rows created.
SQL> exec dbms_stats.gather_table_stats(user, 'TEST3')
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> select count(*) from test3;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1832809687
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4783 (1)| 00:01:07 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| TEST3 | 7908K| 4783 (1)| 00:01:07 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16279 consistent gets
152 physical reads
7492 redo size
349 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter table test3 modify(id number not null);
Table altered.
SQL> exec dbms_stats.gather_table_stats(user, 'TEST3')
PL/SQL procedure successfully completed.
SQL> select count(*) from test3;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1978832621
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3902 (1)| 00:00:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX STORAGE FAST FULL SCAN| TEST3_I | 7908K| 3902 (1)| 00:00:55 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14249 consistent gets
0 physical reads
0 redo size
349 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> spool off
-
I provided a solution to that in my reply to the thread you posted before.
. . .
COLS.NULLABLE is only set to ‘N’ when you specify a not null constraint . . .As I said there you can EASILY test that yourself:
CREATE TABLE TEST (ID NUMBER NOT NULL);
CREATE TABLE TEST1 (ID NUMBER);
ALTER TABLE TEST1 ADD CHECK ("ID" IS NOT NULL);
select table_name, column_name, data_type, nullable
from user_tab_columns
where table_name in ('TEST', 'TEST1') order by table_name, column_name;
TABLE_NAME,COLUMN_NAME,DATA_TYPE,NULLABLE
TEST,ID,NUMBER,N
TEST1,ID,NUMBER,YSee the difference? The column in TEST is marked NOT NULLABLE but the column in TEST1 is NOT marked.
It's fine that columns are marked! But I am talking about constraints rather than columns. See the difference? Just try to find out which of your two constraints makes id column marked "NOT NULLABLE". And it is not the one created with "add check". Just try to distinguish them after they have been created!
-
Please find the proof below:
SQL> create table test3 (id number, name varchar2(30), check (id is not null));
Table created.
SQL> create index test3_i on test3(id);
Index created.
SQL> insert into test3 select object_id, object_name from all_objects;
7908295 rows created.
SQL> exec dbms_stats.gather_table_stats(user, 'TEST3')
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> select count(*) from test3;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1832809687
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4783 (1)| 00:01:07 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| TEST3 | 7908K| 4783 (1)| 00:01:07 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16279 consistent gets
152 physical reads
7492 redo size
349 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter table test3 modify(id number not null);
Table altered.
SQL> exec dbms_stats.gather_table_stats(user, 'TEST3')
PL/SQL procedure successfully completed.
SQL> select count(*) from test3;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1978832621
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3902 (1)| 00:00:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX STORAGE FAST FULL SCAN| TEST3_I | 7908K| 3902 (1)| 00:00:55 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14249 consistent gets
0 physical reads
0 redo size
349 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> spool off
ok - so I see the moral-of-the-story is to use for performance reasons:
SQL> create table test3 (id number not null, name varchar2(30));
instead of:
SQL> create table test3 (id number, name varchar2(30), check (id is not null));
-
Please find the proof below:
SQL> create table test3 (id number, name varchar2(30), check (id is not null));
Table created.
SQL> create index test3_i on test3(id);
Index created.
SQL> insert into test3 select object_id, object_name from all_objects;
7908295 rows created.
SQL> exec dbms_stats.gather_table_stats(user, 'TEST3')
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> select count(*) from test3;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1832809687
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4783 (1)| 00:01:07 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| TEST3 | 7908K| 4783 (1)| 00:01:07 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16279 consistent gets
152 physical reads
7492 redo size
349 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter table test3 modify(id number not null);
Table altered.
SQL> exec dbms_stats.gather_table_stats(user, 'TEST3')
PL/SQL procedure successfully completed.
SQL> select count(*) from test3;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1978832621
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3902 (1)| 00:00:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX STORAGE FAST FULL SCAN| TEST3_I | 7908K| 3902 (1)| 00:00:55 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14249 consistent gets
0 physical reads
0 redo size
349 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> spool off
To me, this is proof that you added a constraint that modified the CBO's behavior. It is not a proof that in-line vs out-of-line constraints is modifying the CBO's behavior.
Cheers,
Brian -
To me, this is proof that you added a constraint that modified the CBO's behavior. It is not a proof that in-line vs out-of-line constraints is modifying the CBO's behavior.
Cheers,
BrianI agree, that most solid proofs are those based on experimental results and theoretical explanations. However, I assume that it is CBO behavior what does really matter rather then my ability to provide a scientific proof of something well-known, isn't it? :-)
O.K. Let's try once again:
At starting point we have a table with two constraints. They are semantically the same and the difference is just that one is in-line and another is out-of-line. The former checks that relative column is not empty and marks it "notnullable", and the latter checks just the same, but doesn't mark the column.
1. Make sure the in-line constraint is disabled and the out-of-line one is not:
SQL> select constraint_name, search_condition, status from user_constraints where table_name = 'TEST3';
CONSTRAINT_NAME SEARCH_CONDITION STATUS
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_C00699629 "ID" IS NOT NULL DISABLED
SYS_C00699630 "ID" IS NOT NULL ENABLED
2 rows selected.
As the constraint is disabled, id column is marked as "nullable" now:
SQL> select column_name, nullable from user_tab_columns where table_name = 'TEST3';
COLUMN_NAME NULLABLE
----------- -
ID Y
NAME Y
2 rows selected.
Query the table:
SQL> set autotrace traceonly
SQL> select count(*) from test3;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1832809687
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4783 (1)| 00:01:07 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| TEST3 | 7908K| 4783 (1)| 00:01:07 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
19 recursive calls
0 db block gets
16316 consistent gets
0 physical reads
0 redo size
349 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
2. Revert the constraints' state: enable in-line one, and disable another one.
SQL> alter table test3 disable constraint sys_c00699630;
Table altered.
SQL> alter table test3 enable constraint sys_c00699629;
Table altered.
SQL> set autotrace off
SQL> select constraint_name, search_condition, status from user_constraints where table_name = 'TEST3';
CONSTRAINT_NAME SEARCH_CONDITION STATUS
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_C00699629 "ID" IS NOT NULL ENABLED
SYS_C00699630 "ID" IS NOT NULL DISABLED
2 rows selected.
As in-line constraint is enabled now, id column is marked as "notnullable".
SQL> select column_name, nullable from user_tab_columns where table_name = 'TEST3';
COLUMN_NAME NULLABLE
----------- -
ID N
NAME Y
2 rows selected.
Query the table once again and make sure the CBO uses another execution plan, because now it "knows" that id column contains data for every table row. Hence, it is possible to use existing index. If the column was "nullable" (even if there was relative out-of-line check constraint - CBO just ignores it), then using index wouldn't do. The reason is that b-tree indexes don't contain nulls and as such, they not necessary have the data for every table row until at least one of indexed columns is "notnullable". So, it wouldn't be reliable to count rows using such an index. Our first query, when in-line constraint was disabled and CBO didn't "know" that indexed column was "notnullable", was exactly the case.
SQL> set autotrace traceonly
SQL> select count(*) from test3;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1978832621
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3902 (1)| 00:00:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX STORAGE FAST FULL SCAN| TEST3_I | 7908K| 3902 (1)| 00:00:55 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
19 recursive calls
0 db block gets
14286 consistent gets
0 physical reads
0 redo size
349 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
To make the proof even more firm, get the table's ddl to make sure that one of the constraints is in-line and another one is out-of-line:
SQL> set autotrace off
SQL> set long 20000
SQL> select dbms_metadata.get_ddl('TABLE', 'TEST3') from dual;
DBMS_METADATA.GET_DDL('TABLE','TEST3')
--------------------------------------------------------------------------------
CREATE TABLE "DOUBTING_THOMAS"."TEST3"
( "ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(30),
CHECK ("ID" IS NOT NULL) DISABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CURRENT_XX_LARGE"
1 row selected.
SQL> spool off
-
I agree, that most solid proofs are those based on experimental results and theoretical explanations. However, I assume that it is CBO behavior what does really matter rather then my ability to provide a scientific proof of something well-known, isn't it? :-)
O.K. Let's try once again:
At starting point we have a table with two constraints. They are semantically the same and the difference is just that one is in-line and another is out-of-line. The former checks that relative column is not empty and marks it "notnullable", and the latter checks just the same, but doesn't mark the column.
1. Make sure the in-line constraint is disabled and the out-of-line one is not:
SQL> select constraint_name, search_condition, status from user_constraints where table_name = 'TEST3';
CONSTRAINT_NAME SEARCH_CONDITION STATUS
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_C00699629 "ID" IS NOT NULL DISABLED
SYS_C00699630 "ID" IS NOT NULL ENABLED
2 rows selected.
As the constraint is disabled, id column is marked as "nullable" now:
SQL> select column_name, nullable from user_tab_columns where table_name = 'TEST3';
COLUMN_NAME NULLABLE
----------- -
ID Y
NAME Y
2 rows selected.
Query the table:
SQL> set autotrace traceonly
SQL> select count(*) from test3;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1832809687
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4783 (1)| 00:01:07 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| TEST3 | 7908K| 4783 (1)| 00:01:07 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
19 recursive calls
0 db block gets
16316 consistent gets
0 physical reads
0 redo size
349 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
2. Revert the constraints' state: enable in-line one, and disable another one.
SQL> alter table test3 disable constraint sys_c00699630;
Table altered.
SQL> alter table test3 enable constraint sys_c00699629;
Table altered.
SQL> set autotrace off
SQL> select constraint_name, search_condition, status from user_constraints where table_name = 'TEST3';
CONSTRAINT_NAME SEARCH_CONDITION STATUS
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS_C00699629 "ID" IS NOT NULL ENABLED
SYS_C00699630 "ID" IS NOT NULL DISABLED
2 rows selected.
As in-line constraint is enabled now, id column is marked as "notnullable".
SQL> select column_name, nullable from user_tab_columns where table_name = 'TEST3';
COLUMN_NAME NULLABLE
----------- -
ID N
NAME Y
2 rows selected.
Query the table once again and make sure the CBO uses another execution plan, because now it "knows" that id column contains data for every table row. Hence, it is possible to use existing index. If the column was "nullable" (even if there was relative out-of-line check constraint - CBO just ignores it), then using index wouldn't do. The reason is that b-tree indexes don't contain nulls and as such, they not necessary have the data for every table row until at least one of indexed columns is "notnullable". So, it wouldn't be reliable to count rows using such an index. Our first query, when in-line constraint was disabled and CBO didn't "know" that indexed column was "notnullable", was exactly the case.
SQL> set autotrace traceonly
SQL> select count(*) from test3;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1978832621
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3902 (1)| 00:00:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX STORAGE FAST FULL SCAN| TEST3_I | 7908K| 3902 (1)| 00:00:55 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
19 recursive calls
0 db block gets
14286 consistent gets
0 physical reads
0 redo size
349 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
To make the proof even more firm, get the table's ddl to make sure that one of the constraints is in-line and another one is out-of-line:
SQL> set autotrace off
SQL> set long 20000
SQL> select dbms_metadata.get_ddl('TABLE', 'TEST3') from dual;
DBMS_METADATA.GET_DDL('TABLE','TEST3')
--------------------------------------------------------------------------------
CREATE TABLE "DOUBTING_THOMAS"."TEST3"
( "ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(30),
CHECK ("ID" IS NOT NULL) DISABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CURRENT_XX_LARGE"
1 row selected.
SQL> spool off
Isn't this really about explicit NOT NULL constraints vs check constraints that happen to specify "IS NOT NULL" (or some equivalent such as "id >= 0"), rather than inline vs out of line?
I'm not sure these terms are well defined. To me, "inline" would mean part of the column declaration, so "alter table x modify y not null" is not exactly inline, but still sets the "NOT NULL" column property recognised by the optimiser.
-
interesting stuff.
-
What @Kot Dmitriy shows is just that the CBO handles check constraints differently than NOT NULL constraints. Even if both do the same logic. I don't see the same difference for any other logic, then for this NOT NULL issue.
From the documentation: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52180
You can define constraints syntactically in two ways: As part of the definition of an individual column or attribute. This is called inline specification. As part of the table definition. This is called out-of-line specification.
<strong>NOT</strong>NULLconstraints must be declared inline. All other constraints can be declared either inline or out of line.So there is no out-of-line not null constraint. This simply does not exist.
And for the other type of constraints I believe the CBO handles them equally whether they are declared as inline or of of line specification.
To phrase the original suggestion in a way that resembles the current documentation would be:
"Automatically convert certain types of check constraints into a not null constraint"