My Oracle administration notes/findings. You are welcome to browse and comment.

Wednesday, October 7, 2009

Multi-column partitioning

Simple example of table with more than 1 partition column.

create table xp (a int, b int, c int, data varchar2(20))
partition by range (a,b)(
  partition part1 values less than (1,1),
  partition part2 values less than (2,2),
  partition part3 values less than (3,3),
  partition part4 values less than (MAXVALUE,MAXVALUE)
);

Check:
select * from user_part_key_columns;

NAME                           OBJEC COLUMN_NAME     COLUMN_POSITION
------------------------------ ----- --------------- ---------------
XP                             TABLE A                             1
XP                             TABLE B                             2


select partition_name, high_value, partition_position
from user_tab_partitions
where table_name='XP';


PARTITION_NAME                 HIGH_VALUE           PARTITION_POSITION
------------------------------ -------------------- ------------------
PART1                          1, 1                                  1
PART2                          2, 2                                  2
PART3                          3, 3                                  3
PART4                          MAXVALUE, MAXVALUE                    4



Now insert some values:
create or replace procedure popxp
(imax in int, jmax in int)
is
begin
for i in 0..imax loop
  for j in 0..jmax loop
    insert into xp values (i, j, null, null);
  end loop;
end loop;
end;
/

Run it:

exec popxp(3,3);


And look at the values by partition:
select * from xp partition (part1) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         0          0
         0          1
         0          2
         0          3
         1          0


select * from xp partition (part2) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         1          1
         1          2
         1          3
         2          0
         2          1

select * from xp partition (part3) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         2          2
         2          3
         3          0
         3          1
         3          2

select * from xp partition (part4) order by a,b;

         A          B          C DATA
---------- ---------- ---------- --------------------
         3          3

So in determining the partition for a row, the value of the first column over-rides that of the 2nd column.   For e.g. (0,3) lies in PART1 because a=0 satisfies the condition a<1 for PART1.  Although b=3 violates the condition b<1, this fact is irrelevant.  

The 2nd column is only considered when the value of the 1st column lies on a partition boundary.  For e.g., (2,0), (2,1), (2,2) and (2,3) all lie on partition boundaries for column "a" of PART2.  For (2,0) and (2,1) the "b" values are the deciding factor in placing these rows in PART2.

For (2,2) "b" is on the boundary for PART2, so "a" again determines the partition for the row.  For (2,3) "b" is now a boundary value for PART3, and since a=2 satisfies the condition for PART3, the row goes there (regardless of "b").



In summary,



Row

PART1
->
PART2
->
PART3
->
PART4









(2,0)

a is too big, so go to next partition.

a is boundary.  So consider b.
b=0 satisfies condition for PART2, so put the row there.

-

-









(2,1)

a is too big, so go to next partition.

a is boundary.  So consider b.
b=1 satisfies condition for PART2, so put the row here.

-

-









(2,2)

a is too big, so go to next partition.

Both a and b lie on boundaries.  So a dominates again.  Since a=2 is too big for PART2, go to the next partition.

a=2 satisfies condition for PART3 so put the row here.

-









(2,3)

a is too big, so go to next partition.

b is too big for this partition.  So a dominates again.  Since a=2 is too big for PART2, go to the next partition.

a=2 satisfies condition for PART3 so put the row here.

-

Thursday, October 1, 2009

Multi-table insert first

Example

user1@orcl92> select * from tt;

         X          Y Z
---------- ---------- --------------------
         1        100 aa
         2        200 bb
         3        300 cc
         4        400 dd


user1@orcl92> select * from tt1, tt2;

no rows selected

user1@orcl92> insert first
when x=3 then into tt1
when x>0 then into tt2
select * from tt;

4 rows created.

user1@orcl92> select * from tt1;

         X          Y Z
---------- ---------- --------------------
         3        300 cc

user1@orcl92> select * from tt2;

         X          Y Z
---------- ---------- --------------------
         1        100 aa
         2        200 bb
         4        400 dd   <-- No X=3 row.


The row with x=3 is missing from tt1 because it is excluded during the evaluation of the second "where" clause due to this row satisfying the first clause "x=3".

Followers