Tag: dba

Attribute clustering (part 2)

In the previous post, I demonstrated attribute clustering by creating a table of source data which contained data in randomised order, via SQL> create table source_data as 2 select d.* 3 from dba_objects d 4 where object_id is not null 5 order by dbms_random.random;…

Attribute clustering (part 1)

One of the nice facilities in 12c is the ability to instruct the database to co-locate data during loading in order to facilitate reduced resource consumption for subsequent queries. Here’s an example of it in use. First I’ll create table SOURCE_DATA to mimic some…

Translating SQL (a migration tool)

When you are moving from another database to Oracle, in a perfect world, you’d set aside plenty of time to refactor all of your code and re-engineer your applications to get the maximum benefit out of the new database technology.  But there are not…

Running external programs from the scheduler

Although I normally use the job or scheduler facility to run database-centric style processes, most commonly PL/SQL programs, there is nothing to stop you from using the scheduler to gain control over tasks that might normally need to be done outside of the database. …

Exceeding 1 million partitions

In my previous post we saw that the partition number in the execution plan might not align with the partition position in the data dictionary when it comes to interval partitions.  As we saw, the partition numbers are preordained based on the low boundary…

Pending statistics and partition queries

This issue came through from an AskTom question, that turned out to be a known bug. In my talks on optimizer statistics, I’ve often mentioned the benefit of using pending statistics as a risk mitigation facility, and since this bug involved pending statistics, I…

The non-existent partition

Things get a little interesting in the data dictionary when it comes to interval partitions.  Consider the following example: SQL> create table t 2 partition by range(x) interval(1) 3 ( partition p0 values less than (1) ) 4 as 5 select rownum x from…