tag:blogger.com,1999:blog-71031889680392580522024-03-12T19:13:50.432-07:00The DM Oracle BlogVlad Tepes at workhttp://www.blogger.com/profile/03658019093286080629noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-7103188968039258052.post-5011215313816353222009-12-11T06:59:00.000-08:002009-12-11T15:08:10.882-08:00Partitioning tablespace defaults<span style="font-size:78%;">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.<br />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).<br /><br /><br />CREATE TABLE t1 (<br />my_favorite_song VARCHAR2(100),<br />my_date DATE NOT NULL)<br />PARTITION BY RANGE (my_date) (<br />PARTITION month_20090101 VALUES LESS THAN (TO_DATE('01-01-2009','DD-MM-YYYY'))<br />TABLESPACE part_test,<br />PARTITION month_20090201 VALUES LESS THAN (TO_DATE('01-02-2009','DD-MM-YYYY'))<br />TABLESPACE part_test,<br />PARTITION month_20090301 VALUES LESS THAN (TO_DATE('01-03-2009','DD-MM-YYYY'))<br />TABLESPACE part_test,<br />PARTITION month_max VALUES LESS THAN (MAXVALUE) TABLESPACE part_test);<br /><br />If we inspect the tablespace details of the partitions like so:<br /><br />SELECT table_name, partition_name, tablespace_name<br />FROM user_tab_partitions<br />WHERE table_name = 'T1';<br /><br />we get:<br /><br />TABLE_NAME PARTITION_NAME TABLESPACE_NAME<br /><br />T1.....................MONTH_20090101...PART_TEST<br />T1.....................MONTH_20090201...PART_TEST<br />T1.....................MONTH_20090301...PART_TEST<br />T1.....................MONTH_MAX............PART_TEST </span><br /><br /><p><span style="font-size:78%;">If we split the max partition:</span></p><p><span style="font-size:78%;"><br />ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-04-2009','DD-MM-YYYY'))<br />INTO (PARTITION month_20090401, PARTITION month_max);</span></p><p><span style="font-size:78%;">we get by running the same query as above:</span></p><p><span style="font-size:78%;"></p></span>TABLE_NAME PARTITION_NAME TABLESPACE_NAME<br />T1.....................MONTH_20090101...PART_TEST<br />T1.....................MONTH_20090201...PART_TEST<br />T1.....................MONTH_20090301...PART_TEST<br />T1.....................MONTH_20090401...PART_TEST<br />T1.....................MONTH_MAX............PART_TEST<br /><br />Now lets say that I want my new partition to be created in tablespace users as opposed to part_test.<br /><br /><br />ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-05-2009','DD-MM-YYYY'))<br />INTO (PARTITION month_20090501 TABLESPACE users, PARTITION month_max);<br /><br />This does the trick:<br /><br />TABLE_NAME PARTITION_NAME TABLESPACE_NAME<br />T1.....................MONTH_20090101...PART_TEST<br />T1.....................MONTH_20090201...PART_TEST<br />T1.....................MONTH_20090301...PART_TEST<br />T1.....................MONTH_20090401...PART_TEST<br />T1.....................MONTH_20090501...USERS<br />T1.....................MONTH_MAX............PART_TEST<br /><br />And if I want both new partitions to be created in the new tablespace:<br /><br /><br />ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-06-2009','DD-MM-YYYY'))<br />INTO (PARTITION month_20090601 TABLESPACE users, PARTITION month_max TABLESPACE users);<br /><br />and we check:<br /><br />TABLE_NAME PARTITION_NAME TABLESPACE_NAME<br />T1.....................MONTH_20090101...PART_TEST<br />T1.....................MONTH_20090201...PART_TEST<br />T1.....................MONTH_20090301...PART_TEST<br />T1.....................MONTH_20090401...PART_TEST<br />T1..................... MONTH_20090501...USERS<br />T1.....................MONTH_20090601...USERS<br />T1.....................MONTH_MAX...........USERS<br /><br />and on the next split, even if I don't specify the tablespaces:<br /><br /><br />ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-07-2009','DD-MM-YYYY'))<br />INTO (PARTITION month_20090701, PARTITION month_max);<br /><br />we get:<br /><br />TABLE_NAME PARTITION_NAME TABLESPACE_NAME<br />T1.....................MONTH_20090101...PART_TEST<br />T1.....................MONTH_20090201...PART_TEST<br />T1.....................MONTH_20090301...PART_TEST<br />T1.....................MONTH_20090401...PART_TEST<br />T1.....................MONTH_20090501...USERS<br />T1.....................MONTH_20090601...USERS<br />T1.....................MONTH_20090701...USERS<br />T1.....................MONTH_MAX...........USERS<br /><br />But now lets say that I want to go back to the previous tablespace and I set the table default tablespace:<br /><br /><br />ALTER TABLE t1 MODIFY DEFAULT ATTRIBUTES TABLESPACE part_test;<br /><br />and then I split again:<br /><br /><br />ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-08-2009','DD-MM-YYYY'))<br />INTO (PARTITION month_20090801, PARTITION month_max);<br /><br />if I check the results:<br /><br />TABLE_NAME PARTITION_NAME TABLESPACE_NAME<br />T1.....................MONTH_20090101...PART_TEST<br />T1.....................MONTH_20090201...PART_TEST<br />T1.....................MONTH_20090301...PART_TEST<br />T1.....................MONTH_20090401...PART_TEST<br />T1.....................MONTH_20090501...USERS<br />T1.....................MONTH_20090601...USERS<br />T1.....................MONTH_20090701...USERS<br />T1.....................MONTH_20090801...USERS<br />T1.....................MONTH_MAX...........USERS<br /><br />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:<br /><br /><br />CREATE INDEX i1 ON t1 (my_favorite_song) LOGGING LOCAL;<br /><br />and inspect the results:<br /><br /><br />SELECT index_name, partition_name, tablespace_name<br />FROM user_ind_partitions<br />WHERE index_name= 'I1'<br />ORDER BY partition_position;<br /><br />we get for each index partition the same tablespace as for the table partitions. That stays the case if I split the table again:<br /><br /><br />ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-09-2009','DD-MM-YYYY'))<br />INTO (PARTITION month_20090901, PARTITION month_max);<br /><br />and we check the table and the index:<br /><br />TABLE_NAME PARTITION_NAME TABLESPACE_NAME<br />T1.....................MONTH_20090101...PART_TEST<br />T1.....................MONTH_20090201...PART_TEST<br />T1.....................MONTH_20090301...PART_TEST<br />T1.....................MONTH_20090401...PART_TEST<br />T1.....................MONTH_20090501...USERS<br />T1.....................MONTH_20090601...USERS<br />T1.....................MONTH_20090701...USERS<br />T1.....................MONTH_20090801...USERS<br />T1.....................MONTH_20090901...USERS<br />T1.....................MONTH_MAX...........USERS<br /><br />INDEX_NAME PARTITION_NAME TABLESPACE_NAME<br />I1.....................MONTH_20090101...PART_TEST<br />I1.....................MONTH_20090201...PART_TEST<br />I1.....................MONTH_20090301...PART_TEST<br />I1.....................MONTH_20090401...PART_TEST<br />I1.....................MONTH_20090501...USERS<br />I1.....................MONTH_20090601...USERS<br />I1.....................MONTH_20090701...USERS<br />I1.....................MONTH_20090801...USERS<br />I1.....................MONTH_20090901...USERS<br />I1.....................MONTH_MAX...........USERS<br /><br />But if I change the index default tablespace:<br /><br /><br />ALTER INDEX i1 MODIFY DEFAULT ATTRIBUTES TABLESPACE part_test;<br /><br />and then we split the table:<br /><br />ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-10-2009','DD-MM-YYYY'))<br />INTO (PARTITION month_20091001, PARTITION month_max);<br /><br />we get for the table:<br /><br />TABLE_NAME PARTITION_NAME TABLESPACE_NAME<br />T1.....................MONTH_20090101...PART_TEST<br />T1.....................MONTH_20090201...PART_TEST<br />T1.....................MONTH_20090301...PART_TEST<br />T1.....................MONTH_20090401...PART_TEST<br />T1.....................MONTH_20090501...USERS<br />T1.....................MONTH_20090601...USERS<br />T1.....................MONTH_20090701...USERS<br />T1.....................MONTH_20090801...USERS<br />T1.....................MONTH_20090901...USERS<br />T1.....................MONTH_20091001...USERS<br />T1.....................MONTH_MAX...........USERS<br /><br />but for the index:<br /><br />INDEX_NAME PARTITION_NAME TABLESPACE_NAME<br />I1.....................MONTH_20090101...PART_TEST<br />I1.....................MONTH_20090201...PART_TEST<br />I1.....................MONTH_20090301...PART_TEST<br />I1.....................MONTH_20090401...PART_TEST<br />I1.....................MONTH_20090501...USERS<br />I1.....................MONTH_20090601...USERS<br />I1.....................MONTH_20090701...USERS<br />I1.....................MONTH_20090801...USERS<br />I1.....................MONTH_20090901...USERS<br />I1.....................MONTH_20091001...PART_TEST<br />I1.....................MONTH_MAX...........PART_TEST<br /><br />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:<br /><br /><br />ALTER TABLE t1 MOVE PARTITION month_max TABLESPACE part_test;<br /><br />and then split:<br /><br />ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-11-2010','DD-MM-YYYY'))<br />INTO (PARTITION month_20101101, PARTITION month_max);<br /><br />to get:<br /><br />TABLE_NAME PARTITION_NAME TABLESPACE_NAME<br />T1.....................MONTH_20090101...PART_TEST<br />T1.....................MONTH_20090201...PART_TEST<br />T1.....................MONTH_20090301...PART_TEST<br />T1.....................MONTH_20090401...PART_TEST<br />T1.....................MONTH_20090501...USERS<br />T1.....................MONTH_20090601...USERS<br />T1.....................MONTH_20090701...USERS<br />T1.....................MONTH_20090801...USERS<br />T1.....................MONTH_20090901...USERS<br />T1.....................MONTH_20091001...USERS<br />T1.....................MONTH_20101101...PART_TEST<br />T1.....................MONTH_MAX..........PART_TEST<br /><br />But if we move back the max partition:<br /><br /><br />ALTER TABLE t1 MOVE PARTITION month_max TABLESPACE users;<br /><br />and then split again:<br /><br /><br />ALTER TABLE t1 SPLIT PARTITION month_max AT (TO_DATE('01-12-2010','DD-MM-YYYY'))<br />INTO (PARTITION month_20101201, PARTITION month_max);<br /><br />TABLE_NAME PARTITION_NAME TABLESPACE_NAME<br />...<br />T1.....................MONTH_20090701...USERS<br />T1.....................MONTH_20090801...USERS<br />T1.....................MONTH_20090901...USERS<br />T1.....................MONTH_20091001...USERS<br />T1.....................MONTH_20101101...PART_TEST<br />T1.....................MONTH_20101201...USERS<br />T1.....................MONTH_MAX..........USERS<br /><br />we're back to where we started from. But if we drop a partition:<br /><br /><br />ALTER TABLE t1 DROP PARTITION month_max;<br /><br />and then we add it back without specifying a tablespace<br /><br /><br />ALTER TABLE t1 ADD PARTITION MONTH_MAX VALUES LESS THAN (MAXVALUE);<br /><br />and we then check the tablespaces we can see that:<br /><br />TABLE_NAME PARTITION_NAME TABLESPACE_NAME<br />T1.....................MONTH_20090501...USERS<br />T1.....................MONTH_20090601...USERS<br />T1.....................MONTH_20090701...USERS<br />T1.....................MONTH_20090801...USERS<br />T1.....................MONTH_20090901...USERS<br />T1.....................MONTH_20091001...USERS<br />T1.....................MONTH_20101101...PART_TEST<br />T1.....................MONTH_20101201...USERS<br />T1.....................MONTH_MAX...........PART_TEST<br /><br />So the default that we set up earlier is taken into account for partition add but not for partition split. To summarize:<br />By default:<br />A partition split will create the two new segments in the tablespace of the segment being split;<br />A partition add will create the new segment in the default tablespace of the table;<br /><br />After a split an index partition will be created in the tablespace of the table segment if default index tablespace is not defined.<br /><br />Hoping this makes partitioning a favourite of yours too.Vlad Tepes at workhttp://www.blogger.com/profile/03658019093286080629noreply@blogger.com0tag:blogger.com,1999:blog-7103188968039258052.post-5886300289987029862009-10-12T15:59:00.001-07:002009-12-11T15:17:45.066-08:00/*+ 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:<br /><br /><br /><br /><br /><br /><br />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)<br /><br /><br /><br /><br />In the following example we’ll first take a look at the apparent benefits of the hint and at the danger it represents:<br /><br /><br /><br /><br /><span class="Apple-style-span" style="color:#009900;">--Create a table</span><br />DROP TABLE t2 PURGE;<br /><span class="Apple-style-span" style="color:#3333ff;">Table dropped.</span><br /><br /><br /><br /><br />CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));<br /><span class="Apple-style-span" style="color:#3333ff;">Table created.</span><br /><br /><br /><br /><br />INSERT INTO t2 VALUES (1,'A');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />INSERT INTO t2 VALUES (2,'B');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />INSERT INTO t2 VALUES (3,'C');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />COMMIT;<br /><span class="Apple-style-span" style="color:#3333ff;">Commit complete.</span><br /><br /><br /><br /><br /><span class="Apple-style-span" style="color:#009900;">--Attempt to update it using its own data, strange but similar to the situation that I faced on the current project..</span><br />UPDATE<br />(SELECT ta.c2 ta_c2, tb.c2 tb_c2<br />FROM t2 ta, t2 tb<br />WHERE ta.c1=tb.c1) t<br />SET ta_c2=tb_c2;<br /><span class="Apple-style-span" style="color:#ff0000;">UPDATE </span><br /><span class="Apple-style-span" style="color:#ff0000;">(SELECT ta.c2 ta_c2, tb.c2 tb_c2</span><br /><span class="Apple-style-span" style="color:#ff0000;">FROM t2 ta, t2 tb </span><br /><span class="Apple-style-span" style="color:#ff0000;">WHERE ta.c1=tb.c1) t</span><br /><span class="Apple-style-span" style="color:#ff0000;">SET ta_c2=tb_c2</span><br /><span class="Apple-style-span" style="color:#ff0000;">Error at line 9</span><br /><span class="Apple-style-span" style="color:#ff0000;">ORA-01779: cannot modify a column which maps to a non key-preserved table</span><br /><br /><br /><br /><br /><span class="Apple-style-span" style="color:#009900;">--If we use the wonder hint the update goes through</span><br />UPDATE <span class="Apple-style-span" style="color:#009900;">/*+ BYPASS_UJVC */</span><br />(SELECT ta.c2 ta_c2, tb.c2 tb_c2<br />FROM t1 ta, t2 tb<br />WHERE ta.c1=tb.c1) t<br />SET ta_c2=tb_c2;<br /><span class="Apple-style-span" style="color:#3333ff;">1 row updated.</span><br /><br /><br />COMMIT;<br /><span class="Apple-style-span" style="color:#3333ff;">Commit complete.</span><br /><br /><br /><br /><br />SELECT * FROM t2;<br />C1 C2<br />---------- --<br />1 A<br />2 B<br />3 C<br /><span class="Apple-style-span" style="color:#3333ff;"><br /></span><br /><span class="Apple-style-span" style="color:#3333ff;">3 rows selected.</span><br /><br /><br /><span class="Apple-style-span" style="color:#009900;">--Same can be accomplished using a merge statement that uses the rowid to identify which row to be updated</span><br />MERGE INTO t2<br />USING (SELECT ta.rowid rid, ta.c2 ta_c2, tb.c2 tb_c2<br />FROM t2 ta, t2 tb<br />WHERE ta.c1=tb.c1) x<br />ON (t2.rowid=x.rid)<br />WHEN MATCHED THEN UPDATE SET c2=tb_c2<br />WHEN NOT MATCHED THEN INSERT (c2) VALUES (NULL);<br /><span class="Apple-style-span" style="color:#3333ff;">Merge successfully completed.</span><br /><br /><br />COMMIT;<br /><span class="Apple-style-span" style="color:#3333ff;">Commit complete.</span><br /><br /><br /><br /><br />SELECT * FROM t2;<br />C1 C2<br />---------- --<br />1 A<br />2 B<br />3 C<br /><br /><br /><span class="Apple-style-span" style="color:#3333ff;">3 rows selected.</span><br /><br /><br />DROP TABLE t2 PURGE;<br /><span class="Apple-style-span" style="color:#3333ff;">Table dropped.</span><br /><br /><br />CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));<br /><span class="Apple-style-span" style="color:#3333ff;">Table created.</span><br /><br /><br />INSERT INTO t2 VALUES (1,'A');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />INSERT INTO t2 VALUES (2,'B');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />INSERT INTO t2 VALUES (3,'C');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />COMMIT;<br /><span class="Apple-style-span" style="color:#3333ff;">Commit complete.</span><br /><br /><br /><span class="Apple-style-span" style="color:#009900;">--The correct solution would be to create the primary key though</span><br />ALTER TABLE t2 ADD CONSTRAINT pk_t2 PRIMARY KEY (c1);<br /><span class="Apple-style-span" style="color:#3333ff;">Table altered.</span><br /><br /><br /><span class="Apple-style-span" style="color:#009900;">—And the original update would execute without any issues</span><br />UPDATE<br />(SELECT ta.c2 ta_c2, tb.c2 tb_c2<br />FROM t2 ta, t2 tb<br />WHERE ta.c1=tb.c1) t<br />SET ta_c2=tb_c2;<br /><span class="Apple-style-span" style="color:#3333ff;">3 rows updated.</span><br /><br /><br />COMMIT;<br /><span class="Apple-style-span" style="color:#3333ff;">Commit complete.</span><br /><br /><br /><br /><br /><br /><br />SELECT * FROM t2;<br />C1 C2<br />---------- --<br />1 A<br />2 B<br />3 C<br /><br /><br /><span class="Apple-style-span" style="color:#3333ff;">3 rows selected.</span><br /><br /><br /><br /><br /><br /><br /><span class="Apple-style-span" style="color:#009900;">--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:</span><br /><br /><br />DROP TABLE t2 PURGE;<br /><span class="Apple-style-span" style="color:#3333ff;">Table dropped.</span><br /><br /><br />DROP TABLE t1 PURGE;<br /><span class="Apple-style-span" style="color:#3333ff;">Table dropped.</span><br /><br /><br />CREATE TABLE t1 (c1 INT, c2 VARCHAR2(1));<br /><span class="Apple-style-span" style="color:#3333ff;">Table created.</span><br /><br /><br />INSERT INTO t1 VALUES (1,NULL);<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));<br /><span class="Apple-style-span" style="color:#3333ff;">Table created.</span><br /><br /><br />INSERT INTO t2 VALUES (1,'A');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />INSERT INTO t2 VALUES (1,'B');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />INSERT INTO t2 VALUES (1,'C');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />COMMIT;<br /><span class="Apple-style-span" style="color:#3333ff;">Commit complete.</span><br /><br /><br /><span class="Apple-style-span" style="color:#009900;">--Normal update fails as expected</span><br />UPDATE<br />(SELECT t1.c2 t1_c2, t2.c2 t2_c2<br />FROM t1, t2<br />WHERE t1.c1=t2.c1) t<br />SET t1_c2=t2_c2;<br /><span class="Apple-style-span" style="color:#ff0000;">UPDATE </span><br /><span class="Apple-style-span" style="color:#ff0000;">(SELECT t1.c2 t1_c2, t2.c2 t2_c2</span><br /><span class="Apple-style-span" style="color:#ff0000;">FROM t1, t2</span><br /><span class="Apple-style-span" style="color:#ff0000;">WHERE t1.c1=t2.c1) t</span><br /><span class="Apple-style-span" style="color:#ff0000;">SET t1_c2=t2_c2</span><br /><span class="Apple-style-span" style="color:#ff0000;">Error at line 14</span><br /><span class="Apple-style-span" style="color:#ff0000;">ORA-01779: cannot modify a column which maps to a non key-preserved table</span><br /><br /><br /><br /><br /><span class="Apple-style-span" style="color:#009900;">--Hinted update succeeds but can it be trusted?</span><br />UPDATE /*+ BYPASS_UJVC */<br />(SELECT t1.c2 t1_c2, t2.c2 t2_c2<br />FROM t1, t2<br />WHERE t1.c1=t2.c1) t<br />SET t1_c2=t2_c2;<br /><span class="Apple-style-span" style="color:#3333ff;">3 rows updated.</span><br /><br /><br />COMMIT;<br /><span class="Apple-style-span" style="color:#3333ff;">Commit complete.</span><br /><br /><br /><br /><br />SELECT * FROM t1;<br />C1 C2<br />---------- --<br />1 C<br /><span class="Apple-style-span" style="color:#3333ff;">1 row selected.</span><br /><br /><br /><br /><br /><span class="Apple-style-span" style="color:#009900;">--Lets start all over with the same data but in different order</span><br />DROP TABLE t2 PURGE;<br /><span class="Apple-style-span" style="color:#3333ff;">Table dropped.</span><br /><br /><br />CREATE TABLE t2(c1 INT, c2 VARCHAR2(1));<br /><span class="Apple-style-span" style="color:#3333ff;">Table created.</span><br /><br /><br />INSERT INTO t2 VALUES (1,'C');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />INSERT INTO t2 VALUES (1,'A');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />INSERT INTO t2 VALUES (1,'B');<br /><span class="Apple-style-span" style="color:#3333ff;">1 row created.</span><br /><br /><br />COMMIT;<br /><span class="Apple-style-span" style="color:#3333ff;">Commit complete.</span><br /><br /><br /><span class="Apple-style-span" style="color:#009900;">--We run the update again</span><br />UPDATE /*+ BYPASS_UJVC */<br />(SELECT t1.c2 t1_c2, t2.c2 t2_c2<br />FROM t1, t2<br />WHERE t1.c1=t2.c1) t<br />SET t1_c2=t2_c2;<br /><span class="Apple-style-span" style="color:#3333ff;">3 rows updated.</span><br /><br /><br />COMMIT;<br /><span class="Apple-style-span" style="color:#3333ff;">Commit complete.</span><br /><br /><br />SELECT * FROM t1;<br /><br /><br />C1 C2<br />---------- --<br />1 B<br /><span class="Apple-style-span" style="color:#3333ff;">1 row selected.</span><br /><br /><br />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.<br /><br /><br /><span class="Apple-style-span" style="COLOR: rgb(51,51,51); LINE-HEIGHT: 20px"><span class="Apple-style-span" style="font-size:small;">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.</span></span>Vlad Tepes at workhttp://www.blogger.com/profile/03658019093286080629noreply@blogger.com3tag:blogger.com,1999:blog-7103188968039258052.post-82005311989249843122009-09-27T17:16:00.000-07:002009-09-27T17:58:30.462-07:00Oracle SQL Ansi SyntaxOracle introduced in 9i the SQL ANSI syntax with which you can rewrite queries like:<div><br /></div><div><div>SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2</div><div>FROM t1, t2</div><div>WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;</div></div><div><br /></div><div><div>SELECT *</div><div>FROM t1 NATURAL JOIN t2 </div><div>ON t1.c1=t2.c1;</div><div><br /></div><div>The new syntax promises improved readability, portability to other RDMSs and the possibility to implement full outer joins:</div><div><br /></div><div><div>SELECT *</div><div>FROM t1 ,t2</div><div>WHERE t1.c1(+)=t2.c1(+);</div><div><br /></div><div>--ORA-01468: a predicate may reference only one outer-joined table</div><div><br /></div><div>SELECT * </div><div>FROM t1 FULL OUTER JOIN t2 ON (t1.c1=t2.c2);</div><div><br /></div><div>--possible </div><div><br /></div><div>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:</div><div><br /></div><div><div><div>CREATE TABLE t1 (c1 VARCHAR2(1), c2 VARCHAR2(1));</div><div>CREATE TABLE t2 (c1 VARCHAR2(1), c2 VARCHAR2(1));</div><div>INSERT INTO t1 (c1,c2) VALUES ('1','a');</div><div>INSERT INTO t1 (c1,c2) VALUES ('2','b');</div><div>INSERT INTO t1 (c1,c2) VALUES ('3','c');</div><div>INSERT INTO t1 (c1,c2) VALUES (NULL,'d');</div><div>INSERT INTO t1 (c1,c2) VALUES ('5',NULL);</div><div><br /></div><div>INSERT INTO t2 (c1,c2) VALUES ('1','v');</div><div>INSERT INTO t2 (c1,c2) VALUES ('2','w');</div><div>INSERT INTO t2 (c1,c2) VALUES ('3','x');</div><div>INSERT INTO t2 (c1,c2) VALUES (NULL,'y');</div><div>INSERT INTO t2 (c1,c2) VALUES ('5',NULL);</div><div>COMMIT;</div><div><br /></div><div>SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2</div><div>FROM t1 JOIN t2</div><div>ON t1.c1=t2.c1</div><div>ORDER BY 1,2,3,4;</div><div><br /></div><div>SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2 </div><div>FROM t1 LEFT OUTER JOIN t2</div><div>ON t1.c1=t2.c1</div><div>ORDER BY 1,2,3,4;</div><div><br /></div><div><br /></div><div>--all rows from t1 are selected as t1 is on the left </div><div><br /></div><div><br /></div><div>SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2 </div><div>FROM t1 LEFT OUTER JOIN t2</div><div>ON t1.c1=t2.c1</div><div>AND t1.c1 IS NOT NULL</div><div>ORDER BY 1,2,3,4;</div><div><br /></div><div>--the and clause has no effect because all rows from t1 will be selected</div><div>--the left join part of the query overwrites the effect of the </div><div>-- t1.c1 is not null predicate </div><div><br /></div><div><br /></div><div>SELECT t1.c1 t1_c1, t1.c2 t1_c2, t2.c1 t2_c1, t2.c2 t2_c2 </div><div>FROM t1 LEFT OUTER JOIN t2</div><div>ON t1.c1=t2.c1</div><div>AND t1.c1 = 1</div><div>ORDER BY 1,2,3,4;</div><div><br /></div><div>--all row from t1 are selected, however only the rows from t2 that also </div><div>--satisfy the where clause on t1 are selected</div><div><br /></div><div>SELECT * </div><div>FROM t1 LEFT OUTER JOIN t2</div><div>ON t1.c1=t2.c1</div><div>WHERE t1.c1 IS NOT NULL;</div><div><br /></div><div>--as soon as the t1.c1 is not null predicate is moved to the where clause</div><div>--only the records that answer that request are selected</div><div><br /></div><div>SELECT * </div><div>FROM t1 FULL OUTER JOIN t2</div><div>ON t1.c1=t2.c1</div><div>AND t1.c1 IS NOT NULL;</div><div><br /></div><div>--all row from both tables are selected</div><div><br /></div><div>SELECT * </div><div>FROM t1 FULL OUTER JOIN t2</div><div>ON t1.c1=t2.c1</div><div>WHERE t1.c1 IS NOT NULL;</div><div><br /></div><div>--when moved to the where condition two rows are eliminated.</div><div><br /></div><div>SELECT * </div><div>FROM t1 RIGHT OUTER JOIN t2</div><div>ON t1.c1=t2.c1;</div><div><br /></div><div>--moving to a right outer join</div><div><br /></div><div>SELECT * </div><div>FROM t1 RIGHT OUTER JOIN t2</div><div>ON t1.c1=t2.c1</div><div>AND t1.c1 IS NOT NULL;</div><div><br /></div><div>--but when we switch to a right outer join the condition on the left table</div><div>--has no effect on the final result</div><div><br /></div><div><br /></div><div>SELECT * </div><div>FROM t1 RIGHT OUTER JOIN t2</div><div>ON t1.c1=t2.c1</div><div>WHERE t1.c1 IS NOT NULL;</div><div><br /></div><div>--so even if the condition is on the table on the left it is filtering the final </div><div>--results</div><div><br /></div><div>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.</div></div></div></div></div>Vlad Tepes at workhttp://www.blogger.com/profile/03658019093286080629noreply@blogger.com0