How to use a custom index on primary key constraints

I admit it, I had a completely wrong belief when thinking about primary key constraints and their related index. When creating a PK on a table, Oracle automatically associates a unique index to it

create table mytab(id         number(10),
                   run_date   date,
                   run_region number(2));

create index mytab_idx on mytab(id, run_date);
alter table mytab add primary key (id);

SQL> select index_name, index_type, table_name, uniqueness
  2  from user_indexes
  3  where table_name = 'MYTAB';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
SYS_C0033765                   NORMAL                      MYTAB                          UNIQUE


SQL> select constraint_name, constraint_type, status, deferrable, index_name
  2  from user_constraints
  3  where table_name = 'MYTAB';

CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS   DEFERRABLE     INDEX_NAME
------------------------------ --------------- -------- -------------- ------------------------------
SYS_C0033765                   P               ENABLED  NOT DEFERRABLE SYS_C0033765

create table mytab(id number(10),

run_date date,

run_region number(2));

create index mytab_idx on mytab(id, run_date);

alter table mytab add primary key (id);

SQL> select index_name, index_type, table_name, uniqueness

2 from user_indexes

3 where table_name = 'MYTAB';

INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS

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

SYS_C0033765 NORMAL MYTAB UNIQUE

SQL> select constraint_name, constraint_type, status, deferrable, index_name

2 from user_constraints

3 where table_name = 'MYTAB';

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS DEFERRABLE INDEX_NAME

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

SYS_C0033765 P ENABLED NOT DEFERRABLE SYS_C0033765

The index name is internally generated and associated to the constraint. Fact is that, as I discovered some time ago, it’s not the index that enforces the uniqueness but the constraint itself. The constraint uses the underlying index to go faster but the index itself does not enforce anything. But you can’t really blame me for thinking differently in the beginning, not after seeing that Oracle creates a unique index for the pk!

How to use RETURNING INTO with a multi-row insert

The RETURNING INTO clause is a very convenient way to retrieve values from rows that are being inserted/deleted/updated. When used with UPDATE and DELETE statements, it’s possible to retrieve values of multiple rows by using the RETURNING BULK COLLECT INTO. This is something that does not work with the INSERT statements: in that case you will be able to only return values from a single row.
Let’s see how this works and create a simple test table that will be the target of our DML operations

create table mytab(id       number(3),
                   run_date date,
                   cust_id  varchar2(30));

insert into mytab
   (select rownum, date '2016-01-01' + round(dbms_random.value(0, 30)), 'CLI'||round(dbms_random.value(100, 999))||upper(dbms_random.string('A', 3))
      from dual
    connect by level <= 100);
commit;

create table mytab(id number(3),

run_date date,

cust_id varchar2(30));

insert into mytab

(select rownum, date '2016-01-01' + round(dbms_random.value(0, 30)), 'CLI'||round(dbms_random.value(100, 999))||upper(dbms_random.string('A', 3))

from dual

connect by level <= 100);

commit;

First of all let’s use a simple case just to have a look at how this works.

Oracle Virtual Private Database is not so private!

Virtual Private Database (Oracle VPD) was introduced by Oracle in release 8i. It’s a security feature that provides access restriction at row/column level for privacy and regulatory compliance. It allows sophisticated logic to be applied through the use of custom functions in which rules can be written for fine-grained data access. If you want to know more about how it works check the official documentation.
The point of this post is that the column-level security of Oracle VPD can be easily tricked! It all started when I decided to check the new Redaction feature released with Oracle 12c. This is somehow similar to VPD but it works at a different level. I came across a very interesting article from David Litchfield that in his article demonstrates how this feature is broken and not safe at all. Well, starting from there I made my own similar investigations on VPD and got some really surprising results.

How locks work with move partition ONLINE on Oracle 12c

With the release 12c Oracle introduced the “online partition movement” making the operation a non-blocking DDL command. This means that it’s possible to run any DML on a partition while the same is being “moved” using the new ONLINE option.
Let’s see how that works on our usual test table populated with 1 million rows in the first partition

-- Create table
drop table mytab;
create table mytab(id       number(10),
                   run_date date,
                   status   varchar2(100))
partition by range(run_date)
(partition P1 values less than (date '2016-01-02'),
 partition P2 values less than (date '2016-01-03')
);

-- Populate data
insert into MYTAB select rownum, date '2016-01-01', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 1000000;
insert into MYTAB select rownum, date '2016-01-02', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 100;
commit;

-- Create one local and one global index
create index MYTAB_IDX_L on MYTAB(id) local;
create index MYTAB_IDX_G on MYTAB(status);

-- Create table

drop table mytab;

create table mytab(id number(10),

run_date date,

status varchar2(100))

partition by range(run_date)

(partition P1 values less than (date '2016-01-02'),

partition P2 values less than (date '2016-01-03')

);

-- Populate data

insert into MYTAB select rownum, date '2016-01-01', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 1000000;

insert into MYTAB select rownum, date '2016-01-02', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 100;

commit;

-- Create one local and one global index

create index MYTAB_IDX_L on MYTAB(id) local;

create index MYTAB_IDX_G on MYTAB(status);

We can now take a look at how move partition works normally when not running it in ONLINE mode.
Let’s execute

alter table MYTAB move partition P1 compress for OLTP;

1	alter table MYTAB move partition P1 compress for OLTP;

SQL Plus and the dark power of the character # (hash or pound)

Not everybody knows that in SQL Plus the character # (called hash or pound) has a special use that I discovered it myself not much time ago. Well, it’s not really a secret, you can read about it on the official SQL Plus manual, but it’s rarely used. The sign #, in fact, is the default character used as SQLPREFIX during a SQL Plus session and can be changed executing the command

SET SQLPRE[FIX] prefix_char

But what does it do? It tells SQL Plus to execute a SQL Plus command while in the middle of entering a SQL statement or a Pl/Sql block of code.
For example you are writing a select statement

SQL> select empno, ename,

1	SQL> select empno, ename,

and, while writing down the list of columns to extract, you don’t remember the name of one of them. Normally you would quit writing the query, execute a desc of the table and start writing it again from scratch. But the # sign give us another possibility. All you have to do is enter a new line, digit the magic sign # and write the desc you need

Implementing partial indexes on Oracle 11g

Similarly as discussed in this post explaining how to enable partial unique indexes on Oracle 12c, it is possible to use partial indexes on Oracle 11g (tested on 11.2.0.2.0) as well! As you know this feature has been introduced with the Oracle release 12c but using the same method explained in the previous post we can apply it to 11g.

Partial indexing for unique indexes on Oracle 12c

With release 12c Oracle has introduced partial indexing, the possibility to define partial local indexes on partitioned table. This is an interesting feature that allows to save some space getting rid of index segments for old partitions. Let’s quickly see how this works.
First of all, when creating a partitioned table, the INDEXING property can be specified to define if we want or not to create the indexes for that partition (default value is ON unless a different default is set at table level)

drop table MYTAB;
create table MYTAB(id number(10),
 run_date date,
 run_incr number(5))
partition by range(run_date)
(partition P1 values less than (date '2016-01-02') INDEXING OFF,
 partition P2 values less than (date '2016-01-03'),
 partition P3 values less than (date '2016-01-04')
);

drop table MYTAB;

create table MYTAB(id number(10),

run_date date,

run_incr number(5))

partition by range(run_date)

(partition P1 values less than (date '2016-01-02') INDEXING OFF,

partition P2 values less than (date '2016-01-03'),

partition P3 values less than (date '2016-01-04')

);

This property can also be changed in a second time issuing a simple alter command

alter table MYTAB move partition P1 indexing on;

1	alter table MYTAB move partition P1 indexing on;

The local indexes need to be created with the option INDEXING PARTIAL otherwise they will not be affected by the INDEXING ON/OFF property set at partition/subpartition level.
There is a restriction though: local unique indexes cannot be created as partial so it’s not possible to drop partitions of a unique index. Well, this is at least what Oracle says but let’s go on.

Oracle regexp: a smart and reliable way to convert character separated values into rows

I’m pretty sure that everybody at least once in his professional coding life had the need to convert a string of character separated values into tokens or rows.
Let’s be honest, we all ended writing something like

for k in 1..length(mystring) loop
  if substr(mystring), k, 1) = ',' then
    ...bla...
    ...bla...
  else
    strBuffer := strBuffer || substr(mystring, k, 1);
    ...bla...
    ...bla...
  end if;
end loop;

for k in 1..length(mystring) loop

if substr(mystring), k, 1) = ',' then

...bla...

else

strBuffer := strBuffer || substr(mystring, k, 1);

...bla...

end if;

end loop;

Problem is that among all those “bla” it’s quite easy to forget something (is there always a separator at the very end of the string? Any space between values and separators? Did I clean the buffer?) and in such code bugs might find a comfortable home.
Why should we write such risky code to do something that can be achieved differently in a completely reliable, fast and elegant way? And all of this just using one powerful feature that Oracle kindly introduced with version 10g (drum roll): regular expressions!

How the GLOBAL_STATS statistic affects the Oracle CBO

Some time ago I came across a query that was using dynamic sampling on a partitioned table even though statistics were present at global level. After some time spent investigating this issue I discovered an interesting but unexpected behaviour of the Oracle CBO when dealing with the GLOBAL_STATS statistic (observed on Oracle RDBMS ver. 11.2.0.2.0, 11.2.0.3.0, 12.1.0.2.0).
Let me go through a simple demo so I can explain what I’ve found.

Few (but meaningful) words about this blog

Welcome to Oracle Findings!
My name is Roberto Rigliaco and as you can easily imagine I’m passionate for database technologies. I’ve been working on Oracle RDBMS for many years and I decided to share my discoveries hoping you will find them as interesting as I do. I just want to clarify that these are my personal findings, things I have learned working on Oracle databases. You might read posts talking about similar topics on other blogs, I’m only reporting and sharing my direct experience.
All your comments are more than welcome.
Enjoy!