The SQL standard allows you to use an inline view in a DELETE statement, see the pseudo form below – If a condition such as key-preserved table is not met then it will result in error, “ORA-01752: cannot delete from view without exactly one key-preserved table.”
DELETE (SELECT some_column FROM table_one JOIN table_two USING (join_column));
I would like to show you the caveat of using a DELETE statement with an inline view. At the outset, one would think that the the removal of row is always perfomed on table_one, but it can be from table_one or table_two, it all depends on the physics of the table. Let us start building a simple test case to illustrate the issue.
- Create two test tables, t1 and t2 with primary key
SQL> SELECT banner FROM v$version 2 WHERE rownum = 1; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi SQL> SET HEAD OFF SQL> CREATE TABLE t1 (id NUMBER); Table created. SQL> CREATE TABLE t2 (id NUMBER); Table created. SQL> ALTER TABLE t1 ADD CONSTRAINT t1_id_pk PRIMARY KEY (id); Table altered. SQL> ALTER TABLE t2 ADD CONSTRAINT t2_id_pk PRIMARY KEY (id); Table altered.
- Explain plan of DELETE statement with two different FROM clause (i.e one from t1 to t2 and another from t2 to t1).
SQL> EXPLAIN PLAN FOR DELETE (SELECT id FROM t1 JOIN t2 USING (id)); Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'BASIC')); Plan hash value: 4198931308 ---------------------------------------- | Id | Operation | Name | ---------------------------------------- | 0 | DELETE STATEMENT | | | 1 | DELETE | T1 | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL| T2 | | 4 | INDEX UNIQUE SCAN| T1_ID_PK | ---------------------------------------- 11 rows selected. SQL> EXPLAIN PLAN FOR DELETE (SELECT id FROM t2 JOIN t1 USING (id)); Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'BASIC')); Plan hash value: 988330285 ---------------------------------------- | Id | Operation | Name | ---------------------------------------- | 0 | DELETE STATEMENT | | | 1 | DELETE | T2 | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL| T1 | | 4 | INDEX UNIQUE SCAN| T2_ID_PK | ---------------------------------------- 11 rows selected.
If you examine the execution plans above, you see that the delete is performed on the table, which first appears in the FROM clause. Also, If you force the optimizer to not use both the indexes, it will result in same behaviour.
SQL> EXPLAIN PLAN FOR DELETE 2 (SELECT /*+ NO_INDEX (t1 t1_id_pk) NO_INDEX (t2 t2_id_pk)*/ id 3 FROM t1 JOIN t2 USING (id)); Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'BASIC')); Plan hash value: 3682150560 ------------------------------------ | Id | Operation | Name | ------------------------------------ | 0 | DELETE STATEMENT | | | 1 | DELETE | T1 | | 2 | HASH JOIN | | | 3 | TABLE ACCESS FULL| T2 | | 4 | TABLE ACCESS FULL| T1 | ------------------------------------ 11 rows selected. SQL> EXPLAIN PLAN FOR DELETE 2 (SELECT /*+ NO_INDEX (t1 t1_id_pk) NO_INDEX (t2 t2_id_pk)*/ id 3 FROM t2 JOIN t1 USING (id)); Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'BASIC')); Plan hash value: 4238075461 ------------------------------------ | Id | Operation | Name | ------------------------------------ | 0 | DELETE STATEMENT | | | 1 | DELETE | T2 | | 2 | HASH JOIN | | | 3 | TABLE ACCESS FULL| T1 | | 4 | TABLE ACCESS FULL| T2 | ------------------------------------ 11 rows selected.
- Remove T1’s primary key constraint/index and retain T2’s and obtain explain plans
SQL> ALTER TABLE t1 DROP CONSTRAINT t1_id_pk; Table altered. SQL> EXPLAIN PLAN FOR DELETE (SELECT id FROM t1 JOIN t2 USING (id)); Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'BASIC')); Plan hash value: 2997925617 ---------------------------------------- | Id | Operation | Name | ---------------------------------------- | 0 | DELETE STATEMENT | | | 1 | DELETE | T1 | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL| T1 | | 4 | INDEX UNIQUE SCAN| T2_ID_PK | ---------------------------------------- 11 rows selected. SQL> EXPLAIN PLAN FOR DELETE (SELECT id FROM t2 JOIN t1 USING (id)); Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'BASIC')); Plan hash value: 2997925617 ---------------------------------------- | Id | Operation | Name | ---------------------------------------- | 0 | DELETE STATEMENT | | | 1 | DELETE | T1 | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL| T1 | | 4 | INDEX UNIQUE SCAN| T2_ID_PK | ---------------------------------------- 11 rows selected.
After droping the primary constraint/index on T1, the DELETE is performed on T1 no matter whether your FROM clause is T1 to T2, T2 to T1.
- Remove T2’s primary key constraint/index and add primary key constraint/index to T1 and obtain explain plans
SQL> ALTER TABLE t2 DROP CONSTRAINT t2_id_pk; Table altered. SQL> ALTER TABLE t1 ADD CONSTRAINT t1_id_pk PRIMARY KEY (id); Table altered. SQL> EXPLAIN PLAN FOR DELETE (SELECT id FROM t1 JOIN t2 USING (id)); Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'BASIC')); Plan hash value: 4135363241 ---------------------------------------- | Id | Operation | Name | ---------------------------------------- | 0 | DELETE STATEMENT | | | 1 | DELETE | T2 | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL| T2 | | 4 | INDEX UNIQUE SCAN| T1_ID_PK | ---------------------------------------- 11 rows selected. SQL> EXPLAIN PLAN FOR DELETE (SELECT id FROM t2 JOIN t1 USING (id)); Explained. SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'BASIC')); Plan hash value: 4135363241 ---------------------------------------- | Id | Operation | Name | ---------------------------------------- | 0 | DELETE STATEMENT | | | 1 | DELETE | T2 | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL| T2 | | 4 | INDEX UNIQUE SCAN| T1_ID_PK | ---------------------------------------- 11 rows selected.
After droping the primary constraint/index on T2 and recreating the primary key primary constraint/index on T1, the DELETE is performed on T2 no matter whether your FROM clause is T1 to T2, T2 to T1.
If you run the DELETE statement when there is no index on both the tables, it will result in error ““ORA-01752: cannot delete from view without exactly one key-preserved table.”
SQL> ALTER TABLE t1 DROP CONSTRAINT t1_id_pk;
Table altered.
SQL> EXPLAIN PLAN FOR DELETE (SELECT id FROM t2 JOIN t1 USING (id));
EXPLAIN PLAN FOR DELETE (SELECT id FROM t2 JOIN t1 USING (id))
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table
Don’t get excited with the new features or syntaxes, test things thoroughly and cover all possible scenarios before you put in production.