Non-unique index used by primary key

 You must be wondering why there is an option to use a non-unique index by primary key or is it beneficial. 

First of all if we create index along with PK then oracle always creates unique index on PK columns. But if we create index first and then use it as part of primary key(make sure all primary key columns are part of index but visa versa in not mandatory) , uniqueness is not mandatory. 

In fact for deferred primary key constraint, columns can hold non-unique values until we commit. 

One common (?) performance method is define non unique index based on columns (A,B) and define primary key constraint on column A.

Now select statements of type:
select B from table where A = ? will only access index.

This is useful e.g. when B is status column.

Also if you use direct path insert on such table where non-unique index used for uniqueness, it will not allow direct path inserts. 

There will be a note in execution plan

- Direct Load disabled because unique constraints are enforced by non-unique index   

So there are some applications of non-unique index on PK

Note: If both PK and non-unique index is defined on same set of columns and in same order, mostly it's design issue then intended.

Oracle Database upgrade notes from 18c to 19c

 Differences in 18c and 19c

1. New database initialization parameters, MIN_AUTH_SERVERS and MAX_AUTH_SERVERS, have been added to configure Database Resident Connection Pooling (DRCP).

MIN_AUTH_SERVERS and MAX_AUTH_SERVERS allow the number of processes used to handle session authentication for DRCP to be configured for optimal usage.

2. The LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword.

The LISTAGG aggregate function orders the rows for each group in a query according to the ORDER BY expression and then concatenates the values into a single string. You can remove duplicate values from the specified expression before concatenation into a single string using the new DISTINCT keyword. This removes the need to create complex query processing to find the distinct values before using the aggregate LISTAGG function. Use the DISTINCT option to remove duplicate values within the LISTAGG function.

3.Immutable Tables

Immutable tables are insert-only tables in which existing data cannot be modified. Deleting rows is either prohibited or restricted based on the insertion time of the rows.

Immutable tables protect data against unauthorized modification by insiders. This includes database administrators or compromised users who have access to insider credentials. Immutable tables also prevent accidental data modification that may be caused by human error.

The DBMS_IMMUTABLE_TABLE package is used for maintenance of immutable tables.

The DELETE_EXPIRED_ROWS procedure removes any rows that are beyond the retention period. They can't be removed using a normal DELETE statement.

4. Performance - SQL Quarantine: SQL statements that are terminated by Oracle   Database Resource Manager due to their excessive consumption of CPU and I/O resources can be automatically quarantined. The execution plans associated with the terminated SQL statements are quarantined to prevent them from being executed again.This feature protects an Oracle database from performance degradation by preventing execution of SQL statements that excessively consume CPU and I/O resources.

5. Hybrid Partitioned Tables

Partitioned external tables were introduced in 12.2. Such tables use the external table driver (ORACLE_LOADER or ORACLE_DATAPUMP) to get the data from a file or even a cloud source (see also DBMS_CLOUD). And each partition can have a different file or even a different oracle directory as the source.

Now we can have tables that have external partitions and normal table partitions. They are called hybrid partitioned tables and are a special case of the external partitioned tables.

6.MAX_IDLE_BLOCKER_TIME

Additionally to MAX_IDLE_TIME there is a new parameter that can restrict the duration of a database session: MAX_IDLE_BLOCKER_TIME. Both are initialization parameters, but also resource plan directives.

Setting such a parameter to 0 means the session is unrestricted. Other values (for the initialization parameters) are in minutes.

MAX_IDLE_BLOCKER_TIME will limit sessions that consume resources. This is the much better option, because connection pools from application servers usually are idle when the are not in use. Those sessions should not be touched if we set MAX_IDLE_TIME to 0 and MAX_IDLE_BLOCKER_TIME to 30 for example.

7.DBMS_XPLAN : Compare Execution Plans in Oracle Database 19c and 21c (COMPARE_PLANS, COMPARE_CURSORS and COMPARE_EXPLAIN)

Script to test IO performance in oracle DB

 set timing on;

set serveroutput on size unlimited

set feedback on

column tm new_value file_time noprint

column tm2 new_value MARKET_ENV noprint

select to_char(sysdate, 'YYYYMMDD') tm from dual ;

select '&ENV' tm2 FROM DUAL;

spool C:\D\DatabaseUpgrade\&MARKET_ENV._IO_Testing_large_&file_time..log

PROMPT &MARKET_ENV

select sysdate AS RUN_DATE from dual;

create  type number_ntt as table of number;

/

create function row_generator ( rows_in in pls_integer ) return number_ntt pipelined is

begin

   for i in 1 .. rows_in loop

      pipe row (i);

   end loop;

   return;

end;

/

--drop table big_table;

create table big_table as select rownum id from dual where 1=0

/

alter table big_table nologging;

-- flush ALL

--alter system flush shared_pool;

--alter system flush buffer_cache;

--exec runstats_pkg.rs_start;

-- first execution

--insert /*+ append */ into big_table select * from table(row_generator(10000));

--commit;

--exec runstats_pkg.rs_middle;

-- second execution for drop the recursive calls and hard parse

insert /*+ append */ into big_table select * from table(row_generator(100000000));

commit;

--exec runstats_pkg.rs_stop;

--SELECT * FROM big_table

CREATE INDEX BIG_TABLE_IDX ON BIG_TABLE

(id)

TABLESPACE SCPODATA;

SET TIMING ON ;

SELECT /*+ INDEX_FFS(BIG_TABLE BIG_TABLE_IDX)*/ COUNT(*) FROM BIG_TABLE;

SET TIMING OFF;

drop table big_table purge;

drop function row_generator;

drop type number_ntt;

spool off