oracle数据库删除多条数据,请教一个删除多条记录的SQL语句

原帖由 zw81929 于 2009-5-5 18:45 发表

你这把人家一下把小于这个rowid的都给删除了，

delete from  a

where CODE  in   (select CODE  from a group by CODE  having count(*) > 1)

and rowid not in (select min(rowid) from a group by CODE having count(*)>1)

这个就行了

实践是检验真理的唯一标准。

月光你这个SQL是基本是对的(除了把A换成表名)，而你说它错的那个SQL是正确的，而且是最优的。

做几个很简单实验：

结论1：这个SQL是正确的，而且是最优的：

delete from duptest a

where a.rowid < (select max(rowid) from  duptest b where a.OBJECT_ID=b.OBJECT_ID);

------------------------

SQL> create table duptest as select object_id,object_name from dba_objects where rownum<100;

Table created.

SQL> select count(*) from duptest;

COUNT(*)

----------

99

SQL> insert into duptest select * from duptest;

99 rows created.

SQL> commit;

Commit complete.

SQL>  select count(*) from duptest;

COUNT(*)

----------

198

SQL> set autotrace on

SQL> delete from duptest a

2  where a.rowid < (select max(rowid) from  duptest b where a.OBJECT_ID=b.OBJECT_ID);

99 rows deleted.

Execution Plan

----------------------------------------------------------

Plan hash value: 748762119

----------------------------------------

| Id  | Operation            | Name    |

----------------------------------------

|   0 | DELETE STATEMENT     |         |

|   1 |  DELETE              | DUPTEST |

|*  2 |   FILTER             |         |

|   3 |    TABLE ACCESS FULL | DUPTEST |

|   4 |    SORT AGGREGATE    |         |

|*  5 |     TABLE ACCESS FULL| DUPTEST |

----------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("A".ROWID< (SELECT MAX(ROWID) FROM "DUPTEST" "B" WHERE

"B"."OBJECT_ID"=:B1))

5 - filter("B"."OBJECT_ID"=:B1)

Note

-----

- rule based optimizer used (consider using cbo)

Statistics

----------------------------------------------------------

1  recursive calls

105  db block gets

443  consistent gets

0  physical reads

25488  redo size

1117  bytes sent via SQL*Net to client

1259  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

99  rows processed

SQL> commit;

Commit complete.

----------------------

SQL> select count(*) from duptest;

COUNT(*)

----------

99

SQL> select count(distinct object_id) from duptest;

COUNT(DISTINCTOBJECT_ID)

------------------------

99

结论2：这个SQL是对的(需要把A改成表名)。

delete from  duptest a

where object_id  in   (select object_id  from duptest  group by object_id  having count(*) > 1)

and rowid not in (select min(rowid) from duptest  group by object_id having count(*)>1);----------------------------------

SQL> insert into duptest select * from duptest;

99 rows created.

SQL> commit;

Commit complete.

SQL> delete from  duptest a

2  where object_id  in   (select object_id  from duptest group by object_id  having count(*) > 1)

3  and rowid not in (select min(rowid) from duptest group by object_id having count(*)>1);

99 rows deleted.

Execution Plan

----------------------------------------------------------

Plan hash value: 4141230337

---------------------------------------------

| Id  | Operation                | Name     |

---------------------------------------------

|   0 | DELETE STATEMENT         |          |

|   1 |  DELETE                  | DUPTEST  |

|*  2 |   FILTER                 |          |

|   3 |    MERGE JOIN            |          |

|   4 |     SORT JOIN            |          |

|   5 |      TABLE ACCESS FULL   | DUPTEST  |

|*  6 |     SORT JOIN            |          |

|   7 |      VIEW                | VW_NSO_1 |

|*  8 |       FILTER             |          |

|   9 |        SORT GROUP BY     |          |

|  10 |         TABLE ACCESS FULL| DUPTEST  |

|* 11 |    FILTER                |          |

|  12 |     SORT GROUP BY        |          |

|  13 |      TABLE ACCESS FULL   | DUPTEST  |

---------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter( NOT EXISTS (SELECT 0 FROM "DUPTEST" "DUPTEST" GROUP BY

"OBJECT_ID" HAVING COUNT(*)>1 AND LNNVL(MIN(ROWID)<>:B1)))

6 - access("OBJECT_ID"="$nso_col_1")

filter("OBJECT_ID"="$nso_col_1")

8 - filter(COUNT(*)>1)

11 - filter(COUNT(*)>1 AND LNNVL(MIN(ROWID)<>:B1))

Note

-----

- rule based optimizer used (consider using cbo)

Statistics

----------------------------------------------------------

1  recursive calls

103  db block gets

800  consistent gets

0  physical reads

25260  redo size

1118  bytes sent via SQL*Net to client

1362  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

202  sorts (memory)

0  sorts (disk)

99  rows processed

SQL> commit;

Commit complete.

SQL> set autotrace off

SQL> select count(*) from duptest;

COUNT(*)

----------

99

------------------------------------------

结论3：而真正正确的次优SQL是：

delete from  duptest a

where object_id  in   (select object_id  from duptest b group by object_id  having count(*) > 1)

and rowid not in (select min(rowid) from duptest c group by object_id having count(*)>1);

SQL> create table duptest as select object_id,object_name from dba_objects where rownum<100;

Table created.

SQL> insert into duptest select * from duptest;

99 rows created.

SQL> commit;

Commit complete.

SQL> set autotrace on

SQL> delete from  duptest a

2  where object_id  in   (select object_id  from duptest b group by object_id  having count(*) > 1)

and rowid not in (select min(rowid) from duptest c group by object_id having count(*)>1);  3

99 rows deleted.

Execution Plan

----------------------------------------------------------

Plan hash value: 4141230337

---------------------------------------------

| Id  | Operation                | Name     |

---------------------------------------------

|   0 | DELETE STATEMENT         |          |

|   1 |  DELETE                  | DUPTEST  |

|*  2 |   FILTER                 |          |

|   3 |    MERGE JOIN            |          |

|   4 |     SORT JOIN            |          |

|   5 |      TABLE ACCESS FULL   | DUPTEST  |

|*  6 |     SORT JOIN            |          |

|   7 |      VIEW                | VW_NSO_1 |

|*  8 |       FILTER             |          |

|   9 |        SORT GROUP BY     |          |

|  10 |         TABLE ACCESS FULL| DUPTEST  |

|* 11 |    FILTER                |          |

|  12 |     SORT GROUP BY        |          |

|  13 |      TABLE ACCESS FULL   | DUPTEST  |

---------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter( NOT EXISTS (SELECT 0 FROM "DUPTEST" "C" GROUP BY

"OBJECT_ID" HAVING COUNT(*)>1 AND LNNVL(MIN(ROWID)<>:B1)))

6 - access("OBJECT_ID"="$nso_col_1")

filter("OBJECT_ID"="$nso_col_1")

8 - filter(COUNT(*)>1)

11 - filter(COUNT(*)>1 AND LNNVL(MIN(ROWID)<>:B1))

Note

-----

- rule based optimizer used (consider using cbo)

Statistics

----------------------------------------------------------

1  recursive calls

105  db block gets

803  consistent gets

0  physical reads

25320  redo size

1116  bytes sent via SQL*Net to client

1366  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

202  sorts (memory)

0  sorts (disk)

99  rows processed

SQL> commit;

Commit complete.

SQL> select count(*) from duptest;

COUNT(*)

----------

99

[本帖最后由 viadeazhu 于 2009-5-22 18:09 编辑]