Oracle APPROX_COUNT_DISTINCT Function

Some notes on the Oracle APPROX_COUNT_DISTINCT Function

The APPROX_COUNT_DISTINCT Function – A Test Case

Testing

http://externaltable.blogspot.co.uk/2014/08/scaling-up-cardinality-estimates-in.html

Scaling up Cardinality Estimates in 12.1.0.2

http://afatkulin.blogspot.co.uk/2013/11/hyperloglog-in-oracle.html

HyperLogLog in Oracle

Sketch of the Day: HyperLogLog — Cornerstone of a Big Data Infrastructure

HyperLogLog — Cornerstone of a Big Data Infrastructure

 

Advertisements

ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database

http://www.liberidu.com/blog/2013/12/24/ora-14696-max_string_size-migration-is-incomplete-for-pluggable-database/

ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database


Oracle varchar2 and “nonpadded comparison semantics”.

The Oracle varchar2 data type has “nonpadded comparison semantics” as per

http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1822

This means values with trailing whitespace can be stored e.g. Under version 12.1.0.2.0:

SQL> create table a ( b varchar2 (30));

Table created.

SQL> insert into a values (‘abc ‘);

1 row created.

SQL> select ‘x’||b||’x’ from a;

‘X’||B||’X’
——————————–
xabc x

but not directly compared against a value with trailing whitespace

SQL> select * from a where b=’123 ‘;

no rows selected

We can compare against a value with trailing whitespace by first casting to the CHAR datatype which has “blank-padded comparison semantics”:

select * from a where cast (b as char(30)) = ‘abc ‘;

B
——————————
abc

Using a trigger to fix the issue

SQL> create user c##dave identified by x;

User created.

SQL> GRANT CONNECT,RESOURCE,DBA TO c##dave;

Grant succeeded.

SQL> GRANT CREATE SESSION TO c##dave;

Grant succeeded.

SQL> alter session set current_schema = c##dave;

Session altered.

SQL> create table a ( b varchar2 (30));

Table created.

SQL> CREATE OR REPLACE TRIGGER a_insert_update
BEFORE INSERT OR UPDATE ON a
FOR EACH ROW
DECLARE
my_varchar2 VARCHAR2(30);
BEGIN
— Force to char to remove trailing whitespace
my_varchar2 := :NEW.b;
:NEW.b := RTRIM(my_varchar2);
END;
/  2    3    4    5    6    7    8    9   10   11

Trigger created.

SQL> insert into a values (‘abc ‘);

1 row created.

SQL> select ‘x’||b||’x’ from a;

‘X’||B||’X’
——————————–
xabcx

SQL> drop table a;

Table dropped.

SQL> alter session set current_schema = sys;

Session altered.

SQL> drop user c##dave;

User dropped.


Database product installation FAQ

Database product installation FAQ http://www.smooth1.co.uk/installs.html

Currently SQL Server,DB2 10.5,Oracle 12.1.0.2,SAP ASE 16.0,Informix 12.10.FC4