This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,147 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Make it possible to distinguish in-line constraint from out-of-line one when both are semantically t

Kot Dmitriy
Kot Dmitriy Member Posts: 10 Blue Ribbon
edited Jan 11, 2016 6:20PM in Database Ideas - Ideas

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".

Kot Dmitriy2960288top.gunJagadekaraGeert Gruwezborneseld7f12d83-7600-4f4c-a322-f460d9f82f42a9beb81b-aeb4-4bc1-b2a2-8f46def5166559646e6a-6009-4f1b-8a42-305d49759d54user6024646grigorievasManish Chaturvediuser6341307Lothar FlatzMartin Preiss569571e0-6ac9-4eab-b64f-77b5fb62fbe6Zlatko SiroticPravin Takpire1f99d358-5463-4aa1-b783-2d69139083908a9f69cb-44f5-4874-a8f0-417cbd4095c6kulikouskiPeterG
26 votes

Active · Last Updated

Comments

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown
    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,Y

    See the difference? The column in TEST is marked NOT NULLABLE but the column in TEST1 is NOT marked.

  • Kot Dmitriy
    Kot Dmitriy Member Posts: 10 Blue Ribbon
    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

    top.gun
  • Kot Dmitriy
    Kot Dmitriy Member Posts: 10 Blue Ribbon

    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,Y

    See 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!

    kulikouski
  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    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));

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    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

    William Robertson
  • Kot Dmitriy
    Kot Dmitriy Member Posts: 10 Blue Ribbon
    edited May 29, 2015 4:59PM

    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

    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

  • William Robertson
    William Robertson London, UKMember Posts: 9,579 Bronze Crown

    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.

    Sven W.
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    interesting stuff.

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown
    edited May 23, 2016 10:19AM

    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> NULL constraints 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"