SQL Analysis Report in Oracle 23c

SQL Analysis report was the new feature built into the Optimizer in Oracle 23c database that help us to identity some mistakes while building queries
 
It was a report enabled by default and appears as a new section at the end of SQL execution plan.
 
Here are some examples where SQL Analysis report kicks in to provide some meaningful insights
 
Having function calls on indexed columns:
 
demo@FREEPDB1> select *
  2  from t
  3  where upper(owner)= 'SYS';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    12M| 52830   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    |   100K|    12M| 52830   (1)| 00:00:03 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(UPPER("OWNER")='SYS')
 
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
 
   1 -  SEL$1 / "T"@"SEL$1"
           -  The following columns have predicates which preclude their
              use as keys in index range scan. Consider rewriting the
              predicates.
                "OWNER"
 
Incorrectly missing a join condition in a multi-table join.
 
demo@FREEPDB1> select *
  2  from emp e1, emp e2, emp e3
  3  where e1.empno = e2.empno
  4  and e3.sal > 10
  5  and e1.deptno = 10;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4236580214
 
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    65 |  4940 |    10   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    65 |  4940 |    10   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | EMP  |     5 |   190 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |    14 |   532 |     7   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL | EMP  |    14 |   532 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("E1"."DEPTNO"=10)
   4 - filter("E3"."SAL">10)
 
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
 
   1 -  SEL$B43A75B1
           -  The query block has 1 cartesian product which may be
              expensive. Consider adding join conditions or removing the
              disconnected tables or views.
 
Having a union instead of union-all
 
demo@FREEPDB1> select ename from emp
  2  union
  3  select dname from dept;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 556839882
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    18 |   124 |     8  (25)| 00:00:01 |
|   1 |  HASH UNIQUE        |      |    18 |   124 |     8  (25)| 00:00:01 |
|   2 |   UNION-ALL         |      |    18 |   124 |     8  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |    84 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPT |     4 |    40 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
 
   1 -  SET$1
           -  The query block contains UNION which may be expensive.
              Consider using UNION ALL if duplicates are allowed or
              uniqueness is guaranteed.
 
 

set jsonprint new feature in SQL*Plus client

 
New feature introduced in Oracle 21c sql*plus client is formatting the output of JSON type columns.
Starting with Oracle 21c, JSON is the newly introduced datatype for handling JSON data in SQL and PL/SQL programming.
 
The “pretty” option, displays a formatted JSON output with proper alignment and spacing.
The default is “normal” which clears the proper alignment and spacing.
 
demo@PDB1> show jsonprint
jsonprint NORMAL
demo@PDB1> select json_arrayagg( json_object(*) returning json ) from dept;
 
JSON_ARRAYAGG(JSON_OBJECT(*)RETURNINGJSON)
--------------------------------------------------------------------------------
[{"DEPTNO":10,"DNAME":"ACCOUNTING","LOC":"NEW YORK"},{"DEPTNO":20,"DNAME":"RESEA
RCH","LOC":"DALLAS"},{"DEPTNO":30,"DNAME":"SALES","LOC":"CHICAGO"},{"DEPTNO":40,
"DNAME":"OPERATIONS","LOC":"BOSTON"}]
 
demo@PDB1> set jsonprint pretty
demo@PDB1> show jsonprint
jsonprint PRETTY
demo@PDB1> select json_arrayagg( json_object(*) returning json ) from dept;
 
JSON_ARRAYAGG(JSON_OBJECT(*)RETURNINGJSON)
--------------------------------------------------------------------------------
[
  {
    "DEPTNO" : 10,
    "DNAME" : "ACCOUNTING",
    "LOC" : "NEW YORK"
  },
  {
    "DEPTNO" : 20,
    "DNAME" : "RESEARCH",
    "LOC" : "DALLAS"
  },
  {
    "DEPTNO" : 30,
    "DNAME" : "SALES",
    "LOC" : "CHICAGO"
  },
  {
    "DEPTNO" : 40,
    "DNAME" : "OPERATIONS",
    "LOC" : "BOSTON"
  }
]
 
 

Boolean Datatype

Oracle 23c introduced Boolean datatype in SQL, Boolean datatype has been available in PL/SQL for many years already, but now with this Oracle 23c release we can define a Boolean datatype using BOOLEAN or BOOL keywords in SQL language.
 
hr@FREEPDB1> create table emails( address varchar2(20), active boolean );
 
we can assign the values to a Boolean datatype in a number of ways – possible combinations below
 
hr@FREEPDB1> insert into emails values('email_id1',true);
hr@FREEPDB1> insert into emails values('email_id2',false);
hr@FREEPDB1> insert into emails values('email_id3','yes');
hr@FREEPDB1> insert into emails values('email_id4','no');
hr@FREEPDB1> insert into emails values('email_id5','on');
hr@FREEPDB1> insert into emails values('email_id6','off');
hr@FREEPDB1> insert into emails values('email_id7',1);
hr@FREEPDB1> insert into emails values('email_id8',0);
hr@FREEPDB1> insert into emails values('email_id9','y');
hr@FREEPDB1> insert into emails values('email_id10','n');
hr@FREEPDB1> insert into emails values('email_id11','t');
hr@FREEPDB1> insert into emails values('email_id12','f');
hr@FREEPDB1> commit;
hr@FREEPDB1> select  * from emails ;
 
ADDRESS           ACTIVE
-------------------- -----------
email_id1         TRUE
email_id2         FALSE
email_id3         TRUE
email_id4         FALSE
email_id5         TRUE
email_id6         FALSE
email_id7         TRUE
email_id8         FALSE
email_id9         TRUE
email_id10        FALSE
email_id11        TRUE
email_id12        FALSE
 
like all other datatypes, Boolean datatypes can also accept null values unless a NOT NULL constraint has been applied.
 
In prior releases of Oracle database, we will be using Varchar2(1) datatype with values as Y or N (or null) to denote an Boolean value in SQL
 
hr@FREEPDB1> create table emails( address varchar2(20), active varchar2(1) );
hr@FREEPDB1> insert into emails values('email_id1','Y');
hr@FREEPDB1> insert into emails values('email_id2',null);
hr@FREEPDB1> commit;
hr@FREEPDB1> select * from emails ;
 
ADDRESS           A
-------------------- -
email_id1         Y
email_id2
 
the question that comes up frequently is how we convert existing table like above to take the advantage of “Boolean” datatypes.
 
On a non-empty table, it will fail like this
 
hr@FREEPDB1> alter table emails modify active boolean;
alter table emails modify active boolean
                          *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
 
For smaller tables, add a new column with a new datatype we want, and then run an update command to copy the data into this new column, we have a new function called TO_BOOLEAN(..) which can convert char/varchar2/numeric datatypes to Boolean datatypes. For larger tables having zero downtime for migration, then dbms_redefinition is the way to go.
 
hr@FREEPDB1> alter table emails add active_new bool;
 
Table altered.
 
hr@FREEPDB1> update emails
  2  set active_new = to_boolean(active);
 
2 rows updated.
 
hr@FREEPDB1> select * from emails ;
 
ADDRESS           A ACTIVE_NEW
-------------------- - -----------
email_id1         Y TRUE
email_id2
 
hr@FREEPDB1>
 
then finally set the “original” column as unused and rename the new column as “original” column
 
hr@FREEPDB1> alter table emails set unused column active;
 
Table altered.
 
hr@FREEPDB1> alter table emails rename column active_new to active;
 
Table altered.
 
hr@FREEPDB1>
hr@FREEPDB1> select * from emails ;
 
ADDRESS           ACTIVE
-------------------- -----------
email_id1         TRUE
email_id2
 
hr@FREEPDB1> set linesize 71
hr@FREEPDB1> desc emails
Name             Null?    Type
---------------- -------- -------------
ADDRESS                   VARCHAR2(20)
ACTIVE                    BOOLEAN
 
Boolean expressions can be used in SQL anywhere expressions are allowed.
 
hr@FREEPDB1> select * from emails ;
 
ADDRESS                    ACTIVE
-------------------- -----------
email_id1                  TRUE
email_id2
 
hr@FREEPDB1> select * from emails where active ;
 
ADDRESS                    ACTIVE
-------------------- -----------
email_id1                  TRUE
 
hr@FREEPDB1> select * from emails where active or address is not null;
 
ADDRESS                    ACTIVE
-------------------- -----------
email_id1                  TRUE
email_id2
 
can be used in a CASE expressions
 
hr@FREEPDB1> select address,
  2    case when active then 'Active'
  3    else 'Inactive' end as status
  4  from emails ;
 
ADDRESS           STATUS
-------------------- --------
email_id1         Active
email_id2         Inactive
 
we can even index them
 
hr@FREEPDB1> create index email_idx on emails(active);
 
Index created.
 
Check constraints and virtual columns are possible from Boolean datatypes
 
hr@FREEPDB1> alter table emails    
  2  add constraint emails_chk
  3  check( active or address is not null );
 
Table altered.
 
hr@FREEPDB1>
hr@FREEPDB1> alter table emails
  2  add status_flag as (  case when active then 'Y' else 'N' end ) ;
 
Table altered.
 
hr@FREEPDB1> select * from emails;
 
ADDRESS                    ACTIVE S
-------------------- ----------- -
email_id1                  TRUE   Y
email_id2                         N
 
the overloads of other conversion function does not work by default in PL/SQL, for those to work we need to set this parameter plsql_implicit_conversion_bool either at session/system level
 
hr@FREEPDB1> show parameter plsql_implicit_conversion
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_implicit_conversion_bool       boolean     FALSE
hr@FREEPDB1>
hr@FREEPDB1> declare
  2     l_true boolean := true;
  3     l_false boolean := false;
  4     n number;
  5  begin
  6     n := to_number(l_true);
  7     n := to_number(l_false);
  8  end;
  9  /
        n := to_number(l_true);
             *
ERROR at line 6:
ORA-06550: line 6, column 7:
PLS-00306: wrong number or types of arguments in call to 'TO_NUMBER'
ORA-06550: line 6, column 2:
PL/SQL: Statement ignored
ORA-06550: line 7, column 7:
PLS-00306: wrong number or types of arguments in call to 'TO_NUMBER'
ORA-06550: line 7, column 2:
PL/SQL: Statement ignored
 
 
hr@FREEPDB1> alter session set plsql_implicit_conversion_bool = true;
 
Session altered.
 
hr@FREEPDB1> declare
  2     l_true boolean := true;
  3     l_false boolean := false;
  4     n number;
  5  begin
  6     n := to_number(l_true);
  7     n := to_number(l_false);
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
hr@FREEPDB1>