原帖由 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 编辑]