INSERTing a row with or without NULL explicitly ?

Here's something I've wanted to explore.

Say you have a table with a column that is not NOT NULL -- i.e. it does allow NULLs.

When you issue an INSERT statement, you can either
a. Explicitly specify a NULL value
b. Exclude the column from the list of values

Is there a difference ?

This test is in 12.2

$sqlplus hemant/hemant

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 10 22:19:26 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Wed Jun 10 2020 22:17:58 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table my_test_table
  2  (pk_col number not null primary key,
  3   not_null_data varchar2(15) not null,
  4   nullable_data varchar2(15)
  5  )
  6  /

Table created.

SQL> 

Then, from two separate sessions (with Instance Shutdowns in-between), with Tracing enabled and disabled using DBMS_SESSION.SET_SQL_TRACE, I run two different INSERT statements :

SQL> insert into my_test_table
  2  values (1,'First Row',NULL);

1 row created.

and

SQL> insert into my_test_table 
  2  (pk_col,not_null_data)
  3  values (2,'Second Row');

1 row created.

Now check the Trace Files generated.

The first INSERT with values for all the colums but not explicitly naming the columns shows :

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.02          2          3          1           0
Execute      4      0.01       0.06         12        830         45           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.01       0.09         14        833         46           3

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       68      0.01       0.01          0          0          0           0
Execute   1475      0.08       0.13          0        156          0           0
Fetch     1825      0.02       0.07          9       5630          0       12171
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3368      0.12       0.22          9       5786          0       12171

Misses in library cache during parse: 18
Misses in library cache during execute: 44

    5  user  SQL statements in session.
   60  internal SQL statements in session.
   65  SQL statements in session.

On the other hand, the second INSERT explicitly naming the two not null columns and excluding the nullable column shows :

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          1          1          0           0
Execute      4      0.00       0.04          7        757          7           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.05          8        758          7           3

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute    124      0.01       0.03          0          0          0           0
Fetch      134      0.00       0.08          9        422          0         921
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      261      0.01       0.12          9        422          0         921

Misses in library cache during parse: 2
Misses in library cache during execute: 6

    5  user  SQL statements in session.
   20  internal SQL statements in session.
   25  SQL statements in session.

This is quite a huge difference between the two types of INSERT statements in terms of the RECURSIVE call overheads.

The 3 Non-Recursive Parse Calls for 
(a) DBMS_SESSION.SET_SQL_TRACE(sql_trace=>TRUE)
(b) COMMIT -- after the INSERT
(c) DBMS_SESSION.SET_SQL_TRACE(sql_trace=>FALSE)