Tag: dba

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…

Data denormalization … another take

I read an interesting article the other day about data modelling and data denormalization. I’ll paraphrase the topic and requirement here (apologies to the original author for any omissions or excessive brevity). We have a fictional application with chat rooms, people subscribing to those…

Partitioning an existing index

I had a question on AskTom recently, where due to concurrency issues, the poster of the question wanted to take their existing primary key index and hash partition it to reduce the “hotness” of the leading leaf block(s).  Their database was 11g, but I…

Securefile in 12c – part 2

In a previous post, I noted that the parameter db_securefile changes from PERMITTED in 11g, to PREFERRED in 12c.  Jonathan Lewis raised the interesting question of what happens when you upgrade to 12c from 11g, where a partitioned table may already have some basicfile…

Syntax formatter might change your data

I saw this on an AskTom question today answered by my colleague Chris.  Check out this simple example SQL> create table T ( 2 x int default 1, 3 y int default 1 4 ,z int); Table created. It looks like I’ve assigned a…

Can a query on the standby update the primary ?

You would think that (with the exception of the V$ tables which are predominantly memory structures reflecting the state of various parts of the database instance) a query on a read-only standby database would have absolutely no interaction with the primary.  After all, the…