APPROX_PERCENTILE in Oracle 12cR2

In my one of my previous blog post (http://mfzahirdba.blogspot.com/2012/09/difference-between-percentilecont-and.html) , we discussed about the PERCENTILE_CONT , PERCENTILE_DISC and median . 

Beginning in 12c Release , we can get the approximate value of PERCENTILE_CONT and these related functions . 

The adavcantage is that this is MUCH faster than regular PERCENTILE_CONT functions . This is very much useful , when we don't need the exact value for the results . Examples could be analyzing the website visits , surveys , etc. 

As it can be seen from the example below , the execution time differs from the PERCENTILE_CONT / APPROX_PERCENTILE. The difference in the execution time could be significant , if the underlying data object is huge . 



SQL> CREATE
2    TABLE t AS
3  SELECT
4    'TEST' item ,
5    'E'   AS region ,
6    level AS wk ,
7    ROUND(dbms_random.value(1,500)) forecastqty
8  FROM
9    dual
10    CONNECT BY level <= 3000 ;

 Table created.

 Execution #1:

 SQL> SELECT
   2      item,
   3      region,
   4      wk,
   5      PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY forecastqty) AS fqty
   6  FROM
   7      t
   8  GROUP BY
   9      item,
  10      region,
 11      wk


 .....
 .....
 ......


 ITEM R         WK       FQTY
 ---- - ---------- ----------
 TEST E      29986        228
 TEST E      29987        329
 TEST E      29988          8
 TEST E      29989        465
 TEST E      29990        411
 TEST E      29991        484
 TEST E      29992        232
 TEST E      29993        416
 TEST E      29994        443
 TEST E      29995         95
 TEST E      29996         48

 ITEM R         WK       FQTY
 ---- - ---------- ----------
 TEST E      29997        385
 TEST E      29998        367
 TEST E      29999        433

 30000 rows selected.

Elapsed: 00:00:11.70


 Execution #2:


SQL> SELECT
  2      item,
  3      region,
  4      wk,
  5      APPROX_PERCENTILE(0.5) WITHIN GROUP(
  6      ORDER BY
  7          forecastqty
  8      )   as approx_fqty
  9  FROM
 10      t
 11  GROUP BY
 12      item,
 13      region,
 14      wk
 15/

 ......
 ....
 ......



ITEM R         WK       FQTY
---- - ---------- ----------
TEST E      29986        228
TEST E      29987        329
TEST E      29988          8
TEST E      29989        465
TEST E      29990        411
TEST E      29991        484
TEST E      29992        232
TEST E      29993        416
TEST E      29994        443
TEST E      29995         95
TEST E      29996         48

ITEM R         WK       FQTY
---- - ---------- ----------
TEST E      29997        385
TEST E      29998        367
TEST E      29999        433

30000 rows selected.

Elapsed: 00:00:09.23



In Oracle 12c Release 2, there are few other new functions for approximation. 

Also , on a similar note .... 


In Oracle 12c Release 1, APPROX_COUNT_DISTINCT was introduced to cater to these situations . 

Please see this link for the deails  

http://mfzahirdba.blogspot.com/2015/01/approxcountdistinct-new-oracle-12c.html

In Oracle 12c Release 2 , we have another parameter (APPROX_FOR_COUNT_DISTINCT)  that can be set at the session level / system level to override the "approxmiation" behavior . Once we set this , the legacy  COUNT functions in our legacy code will go for approximation .  There is no need to revisit all our code to change it to 'APPROX_COUNT_DISTINCT' Again , this can be changed , only if the business use case allows it . 


BTW , this is my 100th blog post. Comments welcome !

CSV output from Oracle

Prior to 12c R2 , producing CSV ouptut from a table required bit of work . 
With 12c R2 , the process is quick and easy. The good old SQLPlus has got the long awaited option . 

With the SET option  , we should be spooled to a file quicker. 

Here is an example. 

SQL> select * from emp;

ENAME                        ID
-------------------- ----------
Zahir                        10
Hameed                       20
Farook                       30
Basheer                      30


SQL> set markup csv on

SQL> select * from emp;

"ENAME","ID"
"Zahir",10
"Hameed",20
"Farook",30
"Basheer",30


By default , the delimiter for the character values will be enclosed in quotes. 
We can turn it off by the quote off. 

SQL> set markup csv on quote off
SQL> select * from emp;

ENAME,ID
Zahir,10
Hameed,20
Farook,30
Basheer,30



Sometimes , it is beneficial to a different delimiter. 
In the following example , we use '|' as the delimiter . 

SQL> set markup csv on delim '|' quote on
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30


In this way , the CSV ouput from oracle tables/views is a loooooot easier. 

Comments welcome. 


Update : 
To make the example complete . We need to use spool on/off to produce a file in our directory.  In the example below , I have spool the results to a file emp.csv . We can few other options to not to display ( supress ) the header , query . To make it simpler , I have just simply spooled the file . 

Thanks very much  to my good friend , Soma for pointing out this .  


SQL> spool  emp.csv
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30


SQL> spool off
SQL> host dir
 Volume in drive D has no label.
 Volume Serial Number is AXCA-3871

 Directory of D:\test

01/17/2018  03:26 PM              .
01/17/2018  03:26 PM              ..
01/17/2018  03:27 PM               123 emp.csv
               1 File(s)            123 bytes
               2 Dir(s)  710,110,654,464 bytes free

SQL> ho type emp.csv
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30

Auto restart of PDB in multi tenant ( CDB) database

Most of the time , when we start/restart  the container database ( CDB) , we would like one ot more pluggable database ( PDBs) to start with the CDB.

In 12c release 1 , the option was to use the trigger (AFTER STARTUP ON DATABASE )  to open the pdbs .  In 12c release 2 , the other option was to save the current state  , so that upon restart of the CDB , the PDB initializes to the saved state ( in our example , READ_WRITE). 


Here is an example. 

As it can be seen below , the PDB is not in 'OPEN' state once the CDB is restarted. 


SQL> col name format a20

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB_SIS              READ WRITE

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 1.0335E+10 bytes
Fixed Size                 12468584 bytes
Variable Size            2751467160 bytes
Database Buffers         7549747200 bytes
Redo Buffers               21082112 bytes
Database mounted.
Database opened.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY

PDB_SIS              MOUNTED


Now , let us open the PDB , confirm its status and save the state. 
The view 'dba_pdb_saved_states' shows the saved state of all PDBs.

SQL> alter pluggable database PDB_SIS open ;

Pluggable database altered.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB_SIS              READ WRITE

SQL> alter pluggable database PDB_SIS save state ;

Pluggable database altered.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY

PDB_SIS              READ WRITE

SQL> col con_name format a20
SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME             STATE
-------------------- --------------

PDB_SIS               OPEN

Now , let us bounce the CDB and look at the status of the PDB. Now , the PDBs  re-initalized to the saved state. 

SQL>  shutdown abort;

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 1.0335E+10 bytes

Fixed Size                 12468584 bytes

Variable Size            2751467160 bytes

Database Buffers         7549747200 bytes

Redo Buffers               21082112 bytes

Database mounted.

Database opened.

SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME             STATE

-------------------- --------------

PDB_SIS               OPEN

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE

-------------------- ----------

PDB$SEED             READ ONLY

PDB_SIS               READ WRITE

Thanks !