PROSAGA码农传奇-Oracle-分析具有重复记录的oracle表

<div class =“post-text”itemprop =“text”>
  <P>
    欢迎来到Oracle世界。
  </p>
  <P>
    您使用的声明
     <code>
      analyze table tablename compute statistics
    </code>
     不会帮到你。此命令用于计算查询优化器使用的Oracle DB内部统计信息，因此Oracle可以找到查询数据的最佳方法（有关更多信息，请参阅
    <a href="https://docs.oracle.com/cd/E98457_01/opera_5_6_core_help/cost_based_optimizer.htm" rel="nofollow noreferrer">
      基于成本的优化工具
    </A>
    ）。此命令也已弃用，应使用dbms_stats
    <a href="https://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm#ADMIN11524" rel="nofollow noreferrer">
      分析表，索引和群集
    </A>
  </p>
  <P>
    现在让我们回答你的问题。它可以通过select中的子句来解决。见下面的例子：
  </p>
   <pre>
    <code>
      create table foo_bar (
      id_foo integer generated by default on null as identity
    , name_bar varchar2(100)
    , constraint Foo_Bar_PK primary key (id_foo)
);

insert into foo_bar (name_bar) values ('John');
insert into foo_bar (name_bar) values ('John');
insert into foo_bar (name_bar) values ('Mike');
insert into foo_bar (name_bar) values ('Susan');
insert into foo_bar (name_bar) values ('Jerry');
insert into foo_bar (name_bar) values ('Jerry');
insert into foo_bar (name_bar) values ('Jerry');

-- query for all data
select * from foo_bar;

+--------+----------+
| ID_Foo | Name_bar |
+--------+----------+
|      1 | John     |
|      2 | John     |
|      3 | Mike     |
|      4 | Susan    |
|      5 | Jerry    |
|      6 | Jerry    |
|      7 | Jerry    |
+--------+----------+

-- this query will return all names which have more than 1 occurrence in the table
select name_bar
  from foo_bar
 group by name_bar
 having count(1)>1;

+----------+
| name_bar |
+----------+
| John     |
| Jerry    |
+----------+

--to get number of duplicate records you can use this
select sum(count(name_bar))
  from foo_bar
 group by name_bar
having count(1)>1;

drop table foo_bar;

</code>
  </pre>
</DIV>