Donghua's Blog - DBAGlobe

Sunday, April 9, 2023

Grants for new role DB_DEVELOPER_ROLE in Oracle 23c

Granted ROLE Privileges

SQL> set tab off
SQL> column grantee for a17

SQL> select * from dba_role_privs where grantee='DB_DEVELOPER_ROLE';

GRANTEE           GRANTED_ROLE      ADM DEL DEF COM INH
----------------- ----------------- --- --- --- --- ---
DB_DEVELOPER_ROLE SODA_APP          NO  NO  YES YES YES
DB_DEVELOPER_ROLE CTXAPP            NO  NO  YES YES YES

Granted SYSTEM Privileges

SQL> select * from dba_sys_privs where grantee='DB_DEVELOPER_ROLE';

GRANTEE           PRIVILEGE                      ADM COM INH
----------------- ------------------------------ --- --- ---
DB_DEVELOPER_ROLE CREATE DOMAIN                  NO  YES YES
DB_DEVELOPER_ROLE CREATE MLE                     NO  YES YES
DB_DEVELOPER_ROLE CREATE ANALYTIC VIEW           NO  YES YES
DB_DEVELOPER_ROLE CREATE HIERARCHY               NO  YES YES
DB_DEVELOPER_ROLE CREATE ATTRIBUTE DIMENSION     NO  YES YES
DB_DEVELOPER_ROLE EXECUTE DYNAMIC MLE            NO  YES YES
DB_DEVELOPER_ROLE CREATE CUBE BUILD PROCESS      NO  YES YES
DB_DEVELOPER_ROLE CREATE CUBE                    NO  YES YES
DB_DEVELOPER_ROLE CREATE CUBE DIMENSION          NO  YES YES
DB_DEVELOPER_ROLE CREATE MINING MODEL            NO  YES YES
DB_DEVELOPER_ROLE CREATE JOB                     NO  YES YES
DB_DEVELOPER_ROLE DEBUG CONNECT SESSION          NO  YES YES
DB_DEVELOPER_ROLE ON COMMIT REFRESH              NO  YES YES
DB_DEVELOPER_ROLE CREATE DIMENSION               NO  YES YES
DB_DEVELOPER_ROLE CREATE TYPE                    NO  YES YES
DB_DEVELOPER_ROLE CREATE MATERIALIZED VIEW       NO  YES YES
DB_DEVELOPER_ROLE CREATE TRIGGER                 NO  YES YES
DB_DEVELOPER_ROLE CREATE PROCEDURE               NO  YES YES
DB_DEVELOPER_ROLE FORCE TRANSACTION              NO  YES YES
DB_DEVELOPER_ROLE CREATE SEQUENCE                NO  YES YES
DB_DEVELOPER_ROLE CREATE VIEW                    NO  YES YES
DB_DEVELOPER_ROLE CREATE SYNONYM                 NO  YES YES
DB_DEVELOPER_ROLE CREATE TABLE                   NO  YES YES
DB_DEVELOPER_ROLE CREATE SESSION                 NO  YES YES

24 rows selected.

Granted Object Privileges

SQL> select * from dba_tab_privs where grantee='DB_DEVELOPER_ROLE';

GRANTEE           OWNER      TABLE_NAME                GRANTOR    PRIVILEGE  GRA HIE COM TYPE         INH
----------------- ---------- ------------------------- ---------- ---------- --- --- --- ------------ ---
DB_DEVELOPER_ROLE SYS        JAVASCRIPT                SYS        EXECUTE    NO  NO  NO  MLE LANGUAGE NO
DB_DEVELOPER_ROLE SYS        V_$STATNAME               SYS        READ       NO  NO  YES VIEW         YES
DB_DEVELOPER_ROLE SYS        V_$PARAMETER              SYS        READ       NO  NO  YES VIEW         YES
DB_DEVELOPER_ROLE SYS        DBA_PENDING_TRANSACTIONS  SYS        SELECT     NO  NO  YES VIEW         YES

Script to extract ROLE DDL and dbms_metadata.get_granted_ddl bug for Oracle 23c

Script to extract ROLE DDL

set long 8000 
set longchunksize 2000

with role_name as 
  (select 'DBA' as name from dual)
select dbms_metadata.get_ddl('ROLE', r.role) AS ddl
from   dba_roles r, role_name
where  r.role = role_name.name
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp, role_name
where  rp.grantee = role_name.name
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp, role_name
where  sp.grantee = role_name.name
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp, role_name
where  tp.grantee = role_name.name
and    rownum = 1
/

Bug in Oracle 23c (23.2)

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','CONNECT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','CONNECT')
--------------------------------------------------------------------------------

  BEGIN NULL; END
  BEGIN NULL; END

There is no such funny bug in Oracle 21c (21.9)

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.9.0.0.0


SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','CONNECT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','CONNECT')
--------------------------------------------------------------------------------

  GRANT CREATE SESSION TO "CONNECT"
  GRANT SET CONTAINER TO "CONNECT"

DROP and CREATE in Oracle 23c with IF EXISTS and IF NOT EXISTS

DROP and CREATE in Oracle 21c

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.9.0.0.0

SQL> drop table t;
drop table t
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table t (id int);

Table created.

SQL> create table t (id int);
create table t (id int)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

DROP and CREATE in Oracle 23c with `IF EXISTS` and `IF NOT EXISTS`

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> drop table if exists t;

Table dropped.

SQL> create table t (id int);

Table created.

SQL> create table if not exists t (id int);

Table created.

`CREATE OR REPLACE` can't coexist with `NOT EXISTS`

SQL> create synonym if not exists s for t;

Synonym created.

SQL> create or replace synonym s for t;

Synonym created.

SQL> create or replace if not exists s for t;
create or replace if not exists s for t
                  *
ERROR at line 1:
ORA-00922: missing or invalid option
SQL>

Saturday, April 8, 2023

Set Search Path at database level for PostgreSQL

There are quite a lot examples on how to set search_path at user level, here is one at the database level.

Check the setting before change

dev=> select * from pg_db_role_setting where setdatabase=(select oid from pg_database where datname='dev');
 setdatabase | setrole | setconfig
-------------+---------+-----------
(0 rows)

dev=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

Change the default search path at database level

The setting can be overwritten by user level setting and session level setting.

dev=> alter database dev set search_path="$user",appuser,public;
ALTER DATABASE

dev=> select * from pg_db_role_setting where setdatabase=(select oid from pg_database where datname='dev');
 setdatabase | setrole |                 setconfig
-------------+---------+--------------------------------------------
       16400 |       0 | {"search_path=\"$user\", appuser, public"}
(1 row)

-- the setting doesn't take effect without reconnection
dev=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

Reconnect

dev=> \c dev

psql (14.3, server 14.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
You are now connected to database "dev" as user "postgres".
dev=> show search_path;
       search_path
--------------------------
 "$user", appuser, public
(1 row)

Tuesday, April 4, 2023

Install Oracle 23c Free Developer Edition on Oracle Linux 8

Download Oracle Database 23c Free Developer Release here. OTN login is no longer required.

wget https://yum.oracle.com/repo/OracleLinux/OL8/developer/x86_64/getPackage/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
wget https://download.oracle.com/otn-pub/otn_software/db-free/oracle-database-free-23c-1.0-1.el8.x86_64.rpm

Perform the installation on Oracle Linux 8 (steps on RHEL8 and CentOS 8 shall be similar)

sudo yum install ./oracle-database-free-23c-1.0-1.el8.x86_64.rpm ./oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm

Configure Oracle database.

[oracle@ol stage]$ sudo /etc/init.d/oracle-free-23c configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database FREE.
Enter SYS user password:
************
Enter SYSTEM user password:
*******
Enter PDBADMIN User Password:
*******
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
33% complete
36% complete
39% complete
43% complete
Completing Database Creation
47% complete
49% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/FREE.
Database Information:
Global Database Name:FREE
System Identifier(SID):FREE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log" for further details.

Connect to Oracle Database using one of the connect strings:
     Pluggable database: ol.lab/FREEPDB1
     Multitenant container database: ol.lab

By default, Oracle installation to /opt/oracle/product/23c/dbhomeFree and data file created in /opt/oracle/oradata/. To change the data file location, modify this configure file /etc/sysconfig/oracle-free-23c.conf prior to run /etc/init.d/oracle-free-23c configure.

#This is a configuration file to setup the Oracle Database.
#It is used when running '/etc/init.d/oracle-free-23c configure'.

# LISTENER PORT used Database listener, Leave empty for automatic port assignment
LISTENER_PORT=

# Character set of the database
CHARSET=AL32UTF8

# Database file directory
# If not specified, database files are stored under Oracle base/oradata
DBFILE_DEST=

# DB Domain name
DB_DOMAIN=

# SKIP Validations, memory, space
SKIP_VALIDATIONS=false

Connect to Oracle database

[oracle@ol stage]$ . oraenv
ORACLE_SID = [orcl21c] ? FREE
The Oracle base has been changed from /u01/app/oracle to /opt/oracle

[oracle@ol stage]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue Apr 4 16:41:46 2023
Version 23.2.0.0.0

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0


SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

Sunday, March 19, 2023

Compare [NOT] IN/EXISTS query optimisation between Oracle and PostgreSQL

Overview

We will compare "IN", "EXISTS", "JOIN", "NOT IN", "NOT EXISTS" and "OUTER JOIN" between Oracle 21c and PostgresQL 15.

The execution summary is as below:

QueryID	Query	Oracle	PostgreSQL
Query 1: "IN"	select sum(v) from c where pid in (select pid from p where v>0.95);	00:00:00.01	76.142 ms
Query 2: "EXISTS"	select sum(v) from c where exists (select pid from p where c.pid = p.pid and v>0.95);	Same plan as query 1	Same plan as query 1
Query 3: "JOIN"	select sum(c.v) from c join p on c.pid=p.pid and p.v>0.95;	Same plan as query 1	Same plan as query 1
Query 4: "NOT IN"	select sum(v) from c where pid not in (select pid from p where v<=0.95);	00:00:00.01	112.662 ms
Query 5: "NOT EXISTS"	select sum(v) from c where not exists (select pid from p where c.pid = p.pid and v<=0.95);	Same plan as query 4	103.857 ms
Query 6: "OUTER JOIN"	select sum(c.v) from c left outer join (select * from p where p.v<=0.95 ) p2 on c.pid=p2.pid where p2.pid is null;	00:00:00.02	Same plan as query 5

Setup before testing

Oracle

-- setup demo table

drop table c;
drop table p;

create table p(pid int primary key, v number(3,2), extra char(100));
create table c(cid int primary key, pid int, v number(3,2), extra char(100), 
                constraint c_p_fk foreign key (pid) references p(pid));

insert into p select level, round(dbms_random.value,2), 'x' 
                from dual connect by level <= 1000;
insert into c select level, floor(dbms_random.value(1,1000)),round(dbms_random.value,2), 'x' 
                from dual connect by level <= 100000;
commit;

create index idx_p_v on p(v);
create index idx_c_pid on c(pid);

exec dbms_stats.gather_table_stats('','P');
exec dbms_stats.gather_table_stats('','C');

-- formating related
set timing on;
set autotrace on explain
set linesize 120
set tab off

PostgreSQL

-- setup demo table

drop table c;
drop table p;

create table p(pid int primary key, v numeric(3,2), extra char(100));
create table c(cid int primary key, pid int, v numeric(3,2), extra char(100), 
                constraint c_p_fk foreign key (pid) references p(pid));

insert into p select level, round(random()::numeric,2), 'x' 
                from generate_series(1,1000) level;
insert into c select level, floor(random()::numeric*1000)+1,round(random()::numeric,2), 'x' 
                from generate_series(1,1000000) level;

create index idx_p_v on p(v);
create index idx_c_pid on c(pid);

vacuum analyze p;
vacuum analyze c;

-- formating related
\timing

Database Version

SQL> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.9.0.0.0

mytest=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit
(1 row)

Query 1: "IN"

select sum(v) from c
  where pid in (select pid from p where v>0.95);

Oracle

SQL> select sum(v) from c
  2     where pid in (select pid from p where v>0.95);

    SUM(V)
----------
    2484.1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 423065292

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |     1 |    14 |   484   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE          |                  |     1 |    14 |            |          |
|*  2 |   HASH JOIN              |                  |  5005 | 70070 |   484   (1)| 00:00:01 |
|*  3 |    VIEW                  | index$_join$_002 |    50 |   350 |     5   (0)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | IDX_P_V          |    50 |   350 |     2   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SYS_C008978      |    50 |   350 |     4   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | C                |   100K|   683K|   479   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("PID"="PID")
   3 - filter("V">0.95)
   4 - access(ROWID=ROWID)
   5 - access("V">0.95)

Note
-----
   - this is an adaptive plan

PostgreSQL

mytest=# select sum(v) from c
mytest-#   where pid in (select pid from p where v>0.95);
   sum
----------
 24233.38
(1 row)

Time: 76.142 ms


mytest=# explain
mytest-# select sum(v) from c
mytest-#    where pid in (select pid from p where v>0.95);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=24522.20..24522.21 rows=1 width=32)
   ->  Gather  (cost=24521.98..24522.19 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=23521.98..23521.99 rows=1 width=32)
               ->  Hash Join  (cost=23.88..23470.94 rows=20417 width=4)
                     Hash Cond: (c.pid = p.pid)
                     ->  Parallel Seq Scan on c  (cost=0.00..22348.67 rows=416667 width=8)
                     ->  Hash  (cost=23.27..23.27 rows=49 width=4)
                           ->  Bitmap Heap Scan on p  (cost=4.65..23.27 rows=49 width=4)
                                 Recheck Cond: (v > 0.95)
                                 ->  Bitmap Index Scan on idx_p_v  (cost=0.00..4.64 rows=49 width=0)
                                       Index Cond: (v > 0.95)
(12 rows)

Time: 0.715 ms

Query 2: "EXISTS"

select sum(v) from c 
  where exists (select pid from p where c.pid = p.pid and v>0.95);

Oracle

SQL> select sum(v) from c
  2  where exists (select pid from p where c.pid = p.pid and v>0.95);

    SUM(V)
----------
    2484.1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 423065292

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |     1 |    14 |   484   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE          |                  |     1 |    14 |            |          |
|*  2 |   HASH JOIN              |                  |  5005 | 70070 |   484   (1)| 00:00:01 |
|*  3 |    VIEW                  | index$_join$_002 |    50 |   350 |     5   (0)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | IDX_P_V          |    50 |   350 |     2   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SYS_C008978      |    50 |   350 |     4   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | C                |   100K|   683K|   479   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("C"."PID"="P"."PID")
   3 - filter("V">0.95)
   4 - access(ROWID=ROWID)
   5 - access("V">0.95)

Note
-----
   - this is an adaptive plan

PostgreSQL

mytest=# select sum(v) from c
mytest-#   where exists (select pid from p where c.pid = p.pid and v>0.95);
   sum
----------
 24233.38
(1 row)

Time: 76.109 ms

mytest=# explain
mytest-# select sum(v) from c
mytest-#   where exists (select pid from p where c.pid = p.pid and v>0.95);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=24522.20..24522.21 rows=1 width=32)
   ->  Gather  (cost=24521.98..24522.19 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=23521.98..23521.99 rows=1 width=32)
               ->  Hash Join  (cost=23.88..23470.94 rows=20417 width=4)
                     Hash Cond: (c.pid = p.pid)
                     ->  Parallel Seq Scan on c  (cost=0.00..22348.67 rows=416667 width=8)
                     ->  Hash  (cost=23.27..23.27 rows=49 width=4)
                           ->  Bitmap Heap Scan on p  (cost=4.65..23.27 rows=49 width=4)
                                 Recheck Cond: (v > 0.95)
                                 ->  Bitmap Index Scan on idx_p_v  (cost=0.00..4.64 rows=49 width=0)
                                       Index Cond: (v > 0.95)
(12 rows)

Time: 0.723 ms

Query 3: "JOIN"

select sum(c.v) from c join p on c.pid=p.pid and p.v>0.95;

Oracle

SQL> select sum(c.v) from c join p on c.pid=p.pid and p.v>0.95;

  SUM(C.V)
----------
    2484.1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 423065292

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |     1 |    14 |   484   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE          |                  |     1 |    14 |            |          |
|*  2 |   HASH JOIN              |                  |  5005 | 70070 |   484   (1)| 00:00:01 |
|*  3 |    VIEW                  | index$_join$_002 |    50 |   350 |     5   (0)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | IDX_P_V          |    50 |   350 |     2   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SYS_C008978      |    50 |   350 |     4   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | C                |   100K|   683K|   479   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("C"."PID"="P"."PID")
   3 - filter("P"."V">0.95)
   4 - access(ROWID=ROWID)
   5 - access("P"."V">0.95)

Note
-----
   - this is an adaptive plan

PostgreSQL

mytest=# select sum(c.v) from c join p on c.pid=p.pid and p.v>0.95;
   sum
----------
 24233.38
(1 row)

Time: 75.596 ms

mytest=# explain
mytest-# select sum(c.v) from c join p on c.pid=p.pid and p.v>0.95;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=24522.20..24522.21 rows=1 width=32)
   ->  Gather  (cost=24521.98..24522.19 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=23521.98..23521.99 rows=1 width=32)
               ->  Hash Join  (cost=23.88..23470.94 rows=20417 width=4)
                     Hash Cond: (c.pid = p.pid)
                     ->  Parallel Seq Scan on c  (cost=0.00..22348.67 rows=416667 width=8)
                     ->  Hash  (cost=23.27..23.27 rows=49 width=4)
                           ->  Bitmap Heap Scan on p  (cost=4.65..23.27 rows=49 width=4)
                                 Recheck Cond: (v > 0.95)
                                 ->  Bitmap Index Scan on idx_p_v  (cost=0.00..4.64 rows=49 width=0)
                                       Index Cond: (v > 0.95)
(12 rows)

Time: 0.707 ms

Query 4: "NOT IN"

select sum(v) from c
  where pid not in (select pid from p where v<=0.95);

Oracle

SQL> select sum(v) from c
  2    where pid not in (select pid from p where v<=0.95);

    SUM(V)
----------
    2484.1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 257737780

----------------------------------------------------------------------------------------------
| Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                  |     1 |    14 |   485   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                  |     1 |    14 |            |          |
|*  2 |   HASH JOIN RIGHT ANTI SNA|                  |  4905 | 68670 |   485   (1)| 00:00:01 |
|*  3 |    VIEW                   | index$_join$_002 |   950 |  6650 |     6   (0)| 00:00:01 |
|*  4 |     HASH JOIN             |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN     | IDX_P_V          |   950 |  6650 |     3   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN | SYS_C008978      |   950 |  6650 |     4   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL      | C                |   100K|   683K|   479   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("PID"="PID")
   3 - filter("V"<=0.95)
   4 - access(ROWID=ROWID)
   5 - access("V"<=0.95)

PostgreSQL

mytest=# select sum(v) from c
mytest-#   where pid not in (select pid from p where v<=0.95);
   sum
----------
 24233.38
(1 row)

mytest=# explain
mytest-# select sum(v) from c
mytest-#   where pid not in (select pid from p where v<=0.95);
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=24944.27..24944.28 rows=1 width=32)
   ->  Gather  (cost=24944.05..24944.26 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=23944.05..23944.06 rows=1 width=32)
               ->  Parallel Seq Scan on c  (cost=32.88..23423.21 rows=208333 width=4)
                     Filter: (NOT (hashed SubPlan 1))
                     SubPlan 1
                       ->  Seq Scan on p  (cost=0.00..30.50 rows=951 width=4)
                             Filter: (v <= 0.95)
(9 rows)

Time: 0.493 ms

Query 5: "NOT EXISTS"

select sum(v) from c 
  where not exists (select pid from p where c.pid = p.pid and v<=0.95);

Oracle

SQL> select sum(v) from c
  2    where not exists (select pid from p where c.pid = p.pid and v<=0.95);

    SUM(V)
----------
    2484.1

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3094349839

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                  |     1 |    14 |   485   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE          |                  |     1 |    14 |            |          |
|*  2 |   HASH JOIN RIGHT ANTI   |                  |  4905 | 68670 |   485   (1)| 00:00:01 |
|*  3 |    VIEW                  | index$_join$_002 |   950 |  6650 |     6   (0)| 00:00:01 |
|*  4 |     HASH JOIN            |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN    | IDX_P_V          |   950 |  6650 |     3   (0)| 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| SYS_C008978      |   950 |  6650 |     4   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL     | C                |   100K|   683K|   479   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("C"."PID"="P"."PID")
   3 - filter("V"<=0.95)
   4 - access(ROWID=ROWID)
   5 - access("V"<=0.95)

PostgreSQL

mytest=# select sum(v) from c
mytest-#   where not exists (select pid from p where c.pid = p.pid and v<=0.95);
   sum
----------
 24233.38
(1 row)

Time: 103.857 ms

mytest=# explain
mytest-# select sum(v) from c
mytest-#   where not exists (select pid from p where c.pid = p.pid and v<=0.95);
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=25186.02..25186.03 rows=1 width=32)
   ->  Gather  (cost=25185.80..25186.01 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=24185.80..24185.81 rows=1 width=32)
               ->  Hash Anti Join  (cost=42.39..24134.76 rows=20417 width=4)
                     Hash Cond: (c.pid = p.pid)
                     ->  Parallel Seq Scan on c  (cost=0.00..22348.67 rows=416667 width=8)
                     ->  Hash  (cost=30.50..30.50 rows=951 width=4)
                           ->  Seq Scan on p  (cost=0.00..30.50 rows=951 width=4)
                                 Filter: (v <= 0.95)
(10 rows)

Time: 0.751 ms

Query 6: "OUTER JOIN""

select sum(c.v) from c left outer join (select * from p where p.v<=0.95 ) p2 
    on c.pid=p2.pid where p2.pid is null;

Oracle

SQL> select sum(c.v) from c left outer join (select * from p where p.v<=0.95 ) p2
  2      on c.pid=p2.pid where p2.pid is null;

  SUM(C.V)
----------
    2484.1

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3112675040

----------------------------------------------------------------------------------------------
| Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                  |     1 |    14 |   485   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                  |     1 |    14 |            |          |
|*  2 |   FILTER                  |                  |       |       |            |          |
|*  3 |    HASH JOIN RIGHT OUTER  |                  |  4905 | 68670 |   485   (1)| 00:00:01 |
|*  4 |     VIEW                  | index$_join$_003 |   950 |  6650 |     6   (0)| 00:00:01 |
|*  5 |      HASH JOIN            |                  |       |       |            |          |
|*  6 |       INDEX RANGE SCAN    | IDX_P_V          |   950 |  6650 |     3   (0)| 00:00:01 |
|   7 |       INDEX FAST FULL SCAN| SYS_C008978      |   950 |  6650 |     4   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL     | C                |   100K|   683K|   479   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - filter("P"."PID" IS NULL)
   3 - access("C"."PID"="P"."PID"(+))
   4 - filter("P"."V"(+)<=0.95)
   5 - access(ROWID=ROWID)
   6 - access("P"."V"<=0.95)

PostgreSQL

mytest=# select sum(c.v) from c left outer join (select * from p where p.v<=0.95 ) p2
mytest-#     on c.pid=p2.pid where p2.pid is null;
   sum
----------
 24233.38
(1 row)

Time: 104.988 ms

mytest=# explain
mytest-# select sum(c.v) from c left outer join (select * from p where p.v<=0.95 ) p2
mytest-#     on c.pid=p2.pid where p2.pid is null;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=25186.02..25186.03 rows=1 width=32)
   ->  Gather  (cost=25185.80..25186.01 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=24185.80..24185.81 rows=1 width=32)
               ->  Hash Anti Join  (cost=42.39..24134.76 rows=20417 width=4)
                     Hash Cond: (c.pid = p.pid)
                     ->  Parallel Seq Scan on c  (cost=0.00..22348.67 rows=416667 width=8)
                     ->  Hash  (cost=30.50..30.50 rows=951 width=4)
                           ->  Seq Scan on p  (cost=0.00..30.50 rows=951 width=4)
                                 Filter: (v <= 0.95)
(10 rows)

Time: 0.844 ms

Donghua's Blog - DBAGlobe

Sunday, April 9, 2023

Grants for new role DB_DEVELOPER_ROLE in Oracle 23c

Granted ROLE Privileges

Granted SYSTEM Privileges

Granted Object Privileges

Script to extract ROLE DDL and dbms_metadata.get_granted_ddl bug for Oracle 23c

Script to extract ROLE DDL

Bug in Oracle 23c (23.2)

There is no such funny bug in Oracle 21c (21.9)

DROP and CREATE in Oracle 23c with IF EXISTS and IF NOT EXISTS

DROP and CREATE in Oracle 21c

DROP and CREATE in Oracle 23c with `IF EXISTS` and `IF NOT EXISTS`

`CREATE OR REPLACE` can't coexist with `NOT EXISTS`

Saturday, April 8, 2023

Set Search Path at database level for PostgreSQL

Check the setting before change

Change the default search path at database level

Reconnect

Tuesday, April 4, 2023

Install Oracle 23c Free Developer Edition on Oracle Linux 8

Sunday, March 19, 2023

Compare [NOT] IN/EXISTS query optimisation between Oracle and PostgreSQL

Overview

Setup before testing

Database Version

Query 1: "IN"

Query 2: "EXISTS"

Query 3: "JOIN"

Query 4: "NOT IN"

Query 5: "NOT EXISTS"

Query 6: "OUTER JOIN""

Disclaimer

Labels

Blog Archive

Search This Website

Tweets by Me

Donghua's Blog - DBAGlobe

Sunday, April 9, 2023

Grants for new role DB_DEVELOPER_ROLE in Oracle 23c

Granted ROLE Privileges

Granted SYSTEM Privileges

Granted Object Privileges

Script to extract ROLE DDL and dbms_metadata.get_granted_ddl bug for Oracle 23c

Script to extract ROLE DDL

Bug in Oracle 23c (23.2)

There is no such funny bug in Oracle 21c (21.9)

DROP and CREATE in Oracle 23c with IF EXISTS and IF NOT EXISTS

DROP and CREATE in Oracle 21c

DROP and CREATE in Oracle 23c with IF EXISTS and IF NOT EXISTS

CREATE OR REPLACE can't coexist with NOT EXISTS

Saturday, April 8, 2023

Set Search Path at database level for PostgreSQL

Check the setting before change

Change the default search path at database level

Reconnect

Tuesday, April 4, 2023

Install Oracle 23c Free Developer Edition on Oracle Linux 8

Sunday, March 19, 2023

Compare [NOT] IN/EXISTS query optimisation between Oracle and PostgreSQL

Overview

Setup before testing

Database Version

Query 1: "IN"

Query 2: "EXISTS"

Query 3: "JOIN"

Query 4: "NOT IN"

Query 5: "NOT EXISTS"

Query 6: "OUTER JOIN""

Disclaimer

Labels

Blog Archive

Search This Website

Tweets by Me

DROP and CREATE in Oracle 23c with `IF EXISTS` and `IF NOT EXISTS`

`CREATE OR REPLACE` can't coexist with `NOT EXISTS`