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!