Partitioning tablespace defaults

Partitioning is a great tool for large database implementations and is quite easy to maintain if implemented properly in the first place. The example bellow will show the importance of setting the correct defaults for table and index tablespace defaults.
We'll start with a table that stores my favourite songs, partitioned by month, because every month I have a different set of favourite songs (I guess any reason is good enough for me to partition a table).


CREATE TABLE t1 (
my_favorite_song VARCHAR2(100),
my_date DATE NOT NULL)
PARTITION BY RANGE (my_date) (
PARTITION month_20090101 VALUES LESS THAN (TO_DATE('01-01-2009','DD-MM-YYYY'))
TABLESPACE part_test,
PARTITION month_20090201 VALUES LESS THAN (TO_DATE('01-02-2009','DD-MM-YYYY'))
TABLESPACE part_test,
PARTITION month_20090301 VALUES LESS THAN (TO_DATE('01-03-2009','DD-MM-YYYY'))
TABLESPACE part_test,
PARTITION month_max VALUES LESS THAN (MAXVALUE) TABLESPACE part_test);

If we inspect the tablespace details of the partitions like so:

SELECT table_name, partition_name, tablespace_name
FROM user_tab_partitions
WHERE table_name = 'T1';

we get:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_MAX............PART_TEST

If we split the max partition:

ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-04-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090401, PARTITION month_max);

we get by running the same query as above:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_MAX............PART_TEST

Now lets say that I want my new partition to be created in tablespace users as opposed to part_test.


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-05-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090501 TABLESPACE users, PARTITION month_max);

This does the trick:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_MAX............PART_TEST

And if I want both new partitions to be created in the new tablespace:


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-06-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090601 TABLESPACE users, PARTITION month_max TABLESPACE users);

and we check:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1..................... MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_MAX...........USERS

and on the next split, even if I don't specify the tablespaces:


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-07-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090701, PARTITION month_max);

we get:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_MAX...........USERS

But now lets say that I want to go back to the previous tablespace and I set the table default tablespace:


ALTER TABLE t1 MODIFY DEFAULT ATTRIBUTES TABLESPACE part_test;

and then I split again:


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-08-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090801, PARTITION month_max);

if I check the results:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_MAX...........USERS

Not what I wanted. I was hoping for the at least the 0801 partition to be created in part_test tablespace. Even more confusing is the behaviour of the default tablespace for a local index. If I create an index on the table like so:


CREATE INDEX i1 ON t1 (my_favorite_song) LOGGING LOCAL;

and inspect the results:


SELECT index_name, partition_name, tablespace_name
FROM user_ind_partitions
WHERE index_name= 'I1'
ORDER BY partition_position;

we get for each index partition the same tablespace as for the table partitions. That stays the case if I split the table again:


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-09-2009','DD-MM-YYYY'))
INTO (PARTITION month_20090901, PARTITION month_max);

and we check the table and the index:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_20090901...USERS
T1.....................MONTH_MAX...........USERS

INDEX_NAME PARTITION_NAME TABLESPACE_NAME
I1.....................MONTH_20090101...PART_TEST
I1.....................MONTH_20090201...PART_TEST
I1.....................MONTH_20090301...PART_TEST
I1.....................MONTH_20090401...PART_TEST
I1.....................MONTH_20090501...USERS
I1.....................MONTH_20090601...USERS
I1.....................MONTH_20090701...USERS
I1.....................MONTH_20090801...USERS
I1.....................MONTH_20090901...USERS
I1.....................MONTH_MAX...........USERS

But if I change the index default tablespace:


ALTER INDEX i1 MODIFY DEFAULT ATTRIBUTES TABLESPACE part_test;

and then we split the table:

ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-10-2009','DD-MM-YYYY'))
INTO (PARTITION month_20091001, PARTITION month_max);

we get for the table:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_20090901...USERS
T1.....................MONTH_20091001...USERS
T1.....................MONTH_MAX...........USERS

but for the index:

INDEX_NAME PARTITION_NAME TABLESPACE_NAME
I1.....................MONTH_20090101...PART_TEST
I1.....................MONTH_20090201...PART_TEST
I1.....................MONTH_20090301...PART_TEST
I1.....................MONTH_20090401...PART_TEST
I1.....................MONTH_20090501...USERS
I1.....................MONTH_20090601...USERS
I1.....................MONTH_20090701...USERS
I1.....................MONTH_20090801...USERS
I1.....................MONTH_20090901...USERS
I1.....................MONTH_20091001...PART_TEST
I1.....................MONTH_MAX...........PART_TEST

Even if you change the user default tablespace, the new table partitions will still be created in tablespace USERS. The only way to change the default is to move the max partition:


ALTER TABLE t1 MOVE PARTITION month_max TABLESPACE part_test;

and then split:

ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-11-2010','DD-MM-YYYY'))
INTO (PARTITION month_20101101, PARTITION month_max);

to get:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090101...PART_TEST
T1.....................MONTH_20090201...PART_TEST
T1.....................MONTH_20090301...PART_TEST
T1.....................MONTH_20090401...PART_TEST
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_20090901...USERS
T1.....................MONTH_20091001...USERS
T1.....................MONTH_20101101...PART_TEST
T1.....................MONTH_MAX..........PART_TEST

But if we move back the max partition:


ALTER TABLE t1 MOVE PARTITION month_max TABLESPACE users;

and then split again:


ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-12-2010','DD-MM-YYYY'))
INTO (PARTITION month_20101201, PARTITION month_max);

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
...
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_20090901...USERS
T1.....................MONTH_20091001...USERS
T1.....................MONTH_20101101...PART_TEST
T1.....................MONTH_20101201...USERS
T1.....................MONTH_MAX..........USERS

we're back to where we started from. But if we drop a partition:


ALTER TABLE t1 DROP PARTITION month_max;

and then we add it back without specifying a tablespace


ALTER TABLE t1 ADD PARTITION MONTH_MAX VALUES LESS THAN (MAXVALUE);

and we then check the tablespaces we can see that:

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
T1.....................MONTH_20090501...USERS
T1.....................MONTH_20090601...USERS
T1.....................MONTH_20090701...USERS
T1.....................MONTH_20090801...USERS
T1.....................MONTH_20090901...USERS
T1.....................MONTH_20091001...USERS
T1.....................MONTH_20101101...PART_TEST
T1.....................MONTH_20101201...USERS
T1.....................MONTH_MAX...........PART_TEST

So the default that we set up earlier is taken into account for partition add but not for partition split. To summarize:
By default:
A partition split will create the two new segments in the tablespace of the segment being split;
A partition add will create the new segment in the default tablespace of the table;

After a split an index partition will be created in the tablespace of the table segment if default index tablespace is not defined.

Hoping this makes partitioning a favourite of yours too.

/*+ BYPASS_UJVC */

The UPDATE statement is more complex than the simple select, insert, delete statements and incomplete or misleading business requirements can make it even more complex. I recently tried to use the hint /*+ BYPASS_UJVC */ to bypass some of the limitations that Oracle imposes on the updates that use a join, such as the fact that the join be key preserved:






Key preserved means that rows from the base tables appear at most once in the output of the view (join) on that table. (Tom Kyte)




In the following example we’ll first take a look at the apparent benefits of the hint and at the danger it represents:




--Create a table
DROP TABLE t2 PURGE;
Table dropped.




CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));
Table created.




INSERT INTO t2 VALUES (1,'A');
1 row created.


INSERT INTO t2 VALUES (2,'B');
1 row created.


INSERT INTO t2 VALUES (3,'C');
1 row created.


COMMIT;
Commit complete.




--Attempt to update it using its own data, strange but similar to the situation that I faced on the current project..
UPDATE
(SELECT ta.c2 ta_c2, tb.c2 tb_c2
FROM t2 ta, t2 tb
WHERE ta.c1=tb.c1) t
SET ta_c2=tb_c2;
UPDATE
(SELECT ta.c2 ta_c2, tb.c2 tb_c2
FROM t2 ta, t2 tb
WHERE ta.c1=tb.c1) t
SET ta_c2=tb_c2
Error at line 9
ORA-01779: cannot modify a column which maps to a non key-preserved table




--If we use the wonder hint the update goes through
UPDATE /*+ BYPASS_UJVC */
(SELECT ta.c2 ta_c2, tb.c2 tb_c2
FROM t1 ta, t2 tb
WHERE ta.c1=tb.c1) t
SET ta_c2=tb_c2;
1 row updated.


COMMIT;
Commit complete.




SELECT * FROM t2;
C1 C2
---------- --
1 A
2 B
3 C


3 rows selected.


--Same can be accomplished using a merge statement that uses the rowid to identify which row to be updated
MERGE INTO t2
USING (SELECT ta.rowid rid, ta.c2 ta_c2, tb.c2 tb_c2
FROM t2 ta, t2 tb
WHERE ta.c1=tb.c1) x
ON (t2.rowid=x.rid)
WHEN MATCHED THEN UPDATE SET c2=tb_c2
WHEN NOT MATCHED THEN INSERT (c2) VALUES (NULL);
Merge successfully completed.


COMMIT;
Commit complete.




SELECT * FROM t2;
C1 C2
---------- --
1 A
2 B
3 C


3 rows selected.


DROP TABLE t2 PURGE;
Table dropped.


CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));
Table created.


INSERT INTO t2 VALUES (1,'A');
1 row created.


INSERT INTO t2 VALUES (2,'B');
1 row created.


INSERT INTO t2 VALUES (3,'C');
1 row created.


COMMIT;
Commit complete.


--The correct solution would be to create the primary key though
ALTER TABLE t2 ADD CONSTRAINT pk_t2 PRIMARY KEY (c1);
Table altered.


—And the original update would execute without any issues
UPDATE
(SELECT ta.c2 ta_c2, tb.c2 tb_c2
FROM t2 ta, t2 tb
WHERE ta.c1=tb.c1) t
SET ta_c2=tb_c2;
3 rows updated.


COMMIT;
Commit complete.






SELECT * FROM t2;
C1 C2
---------- --
1 A
2 B
3 C


3 rows selected.






--So before attempting SQL gymnastics check the referential integrity and if there are any primary, foreign or unique keys missing create them. Lets start all over to demonstrate the dangers of the hint. Create 2 tables:


DROP TABLE t2 PURGE;
Table dropped.


DROP TABLE t1 PURGE;
Table dropped.


CREATE TABLE t1 (c1 INT, c2 VARCHAR2(1));
Table created.


INSERT INTO t1 VALUES (1,NULL);
1 row created.


CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));
Table created.


INSERT INTO t2 VALUES (1,'A');
1 row created.


INSERT INTO t2 VALUES (1,'B');
1 row created.


INSERT INTO t2 VALUES (1,'C');
1 row created.


COMMIT;
Commit complete.


--Normal update fails as expected
UPDATE
(SELECT t1.c2 t1_c2, t2.c2 t2_c2
FROM t1, t2
WHERE t1.c1=t2.c1) t
SET t1_c2=t2_c2;
UPDATE
(SELECT t1.c2 t1_c2, t2.c2 t2_c2
FROM t1, t2
WHERE t1.c1=t2.c1) t
SET t1_c2=t2_c2
Error at line 14
ORA-01779: cannot modify a column which maps to a non key-preserved table




--Hinted update succeeds but can it be trusted?
UPDATE /*+ BYPASS_UJVC */
(SELECT t1.c2 t1_c2, t2.c2 t2_c2
FROM t1, t2
WHERE t1.c1=t2.c1) t
SET t1_c2=t2_c2;
3 rows updated.


COMMIT;
Commit complete.




SELECT * FROM t1;
C1 C2
---------- --
1 C
1 row selected.




--Lets start all over with the same data but in different order
DROP TABLE t2 PURGE;
Table dropped.


CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));
Table created.


INSERT INTO t2 VALUES (1,'C');
1 row created.


INSERT INTO t2 VALUES (1,'A');
1 row created.


INSERT INTO t2 VALUES (1,'B');
1 row created.


COMMIT;
Commit complete.


--We run the update again
UPDATE /*+ BYPASS_UJVC */
(SELECT t1.c2 t1_c2, t2.c2 t2_c2
FROM t1, t2
WHERE t1.c1=t2.c1) t
SET t1_c2=t2_c2;
3 rows updated.


COMMIT;
Commit complete.


SELECT * FROM t1;


C1 C2
---------- --
1 B
1 row selected.


Note how the final results changed due only to a change in the order of the inserts in table t2. This shows that using the hint /*+ BYPASS_UJVC */ allows us to produce non-deterministic results, given the same inputs we might arrive at different results on different executions. This hint should not be used on production systems.


Run the test case and see for yourself. If you have any questions please don't hesitate to ask them on the blog or by email.

Oracle SQL Ansi Syntax

Oracle introduced in 9i the SQL ANSI syntax with which you can rewrite queries like:

SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2

FROM t1, t2

WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;

SELECT *

FROM t1 NATURAL JOIN t2

ON t1.c1=t2.c1;

The new syntax promises improved readability, portability to other RDMSs and the possibility to implement full outer joins:

SELECT *

FROM t1 ,t2

WHERE t1.c1(+)=t2.c1(+);

--ORA-01468: a predicate may reference only one outer-joined table

SELECT *

FROM t1 FULL OUTER JOIN t2 ON (t1.c1=t2.c2);

--possible

I recently came across some particularities of this syntax related to where conditions are placed, either in the from clause or in the where clause. Here is a test case with my explanation:

CREATE TABLE t1 (c1 VARCHAR2(1), c2 VARCHAR2(1));

CREATE TABLE t2 (c1 VARCHAR2(1), c2 VARCHAR2(1));

INSERT INTO t1 (c1,c2) VALUES ('1','a');

INSERT INTO t1 (c1,c2) VALUES ('2','b');

INSERT INTO t1 (c1,c2) VALUES ('3','c');

INSERT INTO t1 (c1,c2) VALUES (NULL,'d');

INSERT INTO t1 (c1,c2) VALUES ('5',NULL);

INSERT INTO t2 (c1,c2) VALUES ('1','v');

INSERT INTO t2 (c1,c2) VALUES ('2','w');

INSERT INTO t2 (c1,c2) VALUES ('3','x');

INSERT INTO t2 (c1,c2) VALUES (NULL,'y');

INSERT INTO t2 (c1,c2) VALUES ('5',NULL);

COMMIT;

SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2

FROM t1 JOIN t2

ON t1.c1=t2.c1

ORDER BY 1,2,3,4;

SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2

FROM t1 LEFT OUTER JOIN t2

ON t1.c1=t2.c1

ORDER BY 1,2,3,4;

--all rows from t1 are selected as t1 is on the left

SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2

FROM t1 LEFT OUTER JOIN t2

ON t1.c1=t2.c1

AND t1.c1 IS NOT NULL

ORDER BY 1,2,3,4;

--the and clause has no effect because all rows from t1 will be selected

--the left join part of the query overwrites the effect of the

-- t1.c1 is not null predicate

SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2

FROM t1 LEFT OUTER JOIN t2

ON t1.c1=t2.c1

AND t1.c1 = 1

ORDER BY 1,2,3,4;

--all row from t1 are selected, however only the rows from t2 that also

--satisfy the where clause on t1 are selected

SELECT *

FROM t1 LEFT OUTER JOIN t2

ON t1.c1=t2.c1

WHERE t1.c1 IS NOT NULL;

--as soon as the t1.c1 is not null predicate is moved to the where clause

--only the records that answer that request are selected

SELECT *

FROM t1 FULL OUTER JOIN t2

ON t1.c1=t2.c1

AND t1.c1 IS NOT NULL;

--all row from both tables are selected

SELECT *

FROM t1 FULL OUTER JOIN t2

ON t1.c1=t2.c1

WHERE t1.c1 IS NOT NULL;

--when moved to the where condition two rows are eliminated.

SELECT *

FROM t1 RIGHT OUTER JOIN t2

ON t1.c1=t2.c1;

--moving to a right outer join

SELECT *

FROM t1 RIGHT OUTER JOIN t2

ON t1.c1=t2.c1

AND t1.c1 IS NOT NULL;

--but when we switch to a right outer join the condition on the left table

--has no effect on the final result

SELECT *

FROM t1 RIGHT OUTER JOIN t2

ON t1.c1=t2.c1

WHERE t1.c1 IS NOT NULL;

--so even if the condition is on the table on the left it is filtering the final

--results

Run the test case and see for yourself. If you have any questions please don't hesitate to ask them on the blog or by email.