Learning is not a spectator sport

Some times I’m blown away by how the database will try very hard to save you from yourself :-). Consider the following example: I’ve got a table called T. It’s got two columns, X and Y, and it’s partitioned into four partitions, the values being based on X, 1, 2, 3 and 4 for the four partitions respectively.

I’ll insert 16 million rows into that table such that we get 4 million rows into each partition and the values of Y range from 1 all the way up to 16 million. So we know in advance that the highest value in that table for Y is 16 million.

SQL> create table t ( x int, y char(100))
  2  partition by list ( x )
  3  ( partition p1 values (1),
  4    partition p2 values (2),
  5    partition p3 values (3),
  6    partition p4 values (4)
  7  );
Table created.
SQL> insert /*+ APPEND */ into t
  2  select c1, lpad(rownum,10)
  3  from
  4  ( select rownum c1 from dual connect by level <= 4 ),
  5  ( select 1 from dual connect by level <= 2000 ),
  6  ( select 1 from dual connect by level <= 2000 );
16000000 rows created.
SQL> commit;
Commit complete.
SQL>

But what I’m going to do is query that table and try to find out the maximum value of Y and while that is running , I’m going to exchange one of the partitions with a table T_EX.

SQL> desc T_EX
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                                      NUMBER(38)
 Y                                      CHAR(100)
SQL> select count(*) from T_EX;
  COUNT(*)
----------
         0
1 row selected.

T_EX has no rows in it so in effect, we’re going to instantaneously wipe out 4 million rows from that table while the query is running in the other session.

So this is the command we’re going to be running:

SQL> alter table t exchange partition p4 with table t_ex without validation;

Because we’re removing partition P4, we know that we are taking effectively erasing the values of Y from 12 million up to 16 million. So let’s see what happens.

I’ll set my query running in session 1 and the moment it starts I’ll my exchange partition ready in the other:

--
-- Session 1
--
SQL> select max(y) from t;
[working]
--
-- Session 2 
--
SQL> alter table t exchange partition p4 with table t_ex without validation;
Table altered.
--
-- Back in session 1
--
[kept running without error...]
  MAX(Y)
  --------------------
              16000000
  1 row selected.

The partition has been exchanged and … yes, my query still ran fine! It didn’t crash!

That’s pretty impressive because if I rerun my query, you can see it now picks up the right answer post exchange.

SQL> select max(y) from t;
  MAX(Y)
  --------------------
              12000000

So even though we altered the dictionary definition of our table whilst our query was running, the database was smart enough to still run our query in the normal read consistent fashion.

That’s pretty cool.

But this is probably not something you should rely on … the reason for that is coming in the next post 🙂