Count(*) 为0的问题研究 --- 后来发现问题终于可以重现了，参考我后面的关于merge的文章

 

Count(*) 为0的问题研究 --- 后来发现问题终于可以重现了，参考我后面的关于merge的文章

url:http://hi.baidu.com/klkl1110/blog/item/72834d62a54767670c33fa92.html

2008-11-04 11:04

kl@adw1p5> CONN kl/meng_01@test Connected. kl@test1> select count(*) from kl.te_org_a_node_gl_plc; COUNT(*) ----------          0 kl@test1> select last_analyzed ,num_rows from dba_tables where table_name='TE_ORG_A_NODE_GL_PLC'; glST_ANALYZED        NUM_ROWS ------------------ ---------- 15-JUL-08             4463081 05-OCT-08             1201070 kl@test1> select last_analyzed ,num_rows, owner from dba_tables where table_name='TE_ORG_A_NODE_GL_PLC'; glST_ANALYZED        NUM_ROWS OWNER ------------------ ---------- ------------------------------------------------------------------------------------------ 15-JUL-08             4463081 kl 05-OCT-08             1201070 kl kl@test1> select count(*) from kl.te_org_a_node_gl_plc; COUNT(*) ----------    5322739 kl@test1> explain plan for select count(*) from kl.te_org_a_node_gl_plc; Expglined. kl@test1> select plan_table_output from table(dbms_xplan.display); PglN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------- Pgln hash value: 2688649534 --------------------------------------------------------------------------------------------- | Id | Operation             | Name                        | Rows | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |                             |     1 |   205   (7)| 00:00:02 | |   1 | SORT AGGREGATE       |                             |     1 |            |          | |   2 |   INDEX FAST FULL SCAN| te_ORG_a_NODE_gl_PLC_PK | 1201K|   205   (7)| 00:00:02 | --------------------------------------------------------------------------------------------- 9 rows selected. kl@test1> select status from dba_indexes where index_name='TE_ORG_A_NODE_GL_PLC_PK'; STATUS ------------------------ VALID VALID kl@test1> select blevel, num_rows from dba_indexes where index_name='TE_ORG_A_NODE_GL_PLC_PK';     BLEVEL   NUM_ROWS ---------- ----------          2    1188187          2    4594526 kl@test1> explain plan for select count(*) from kl.te_org_a_node_gl_plc; Expglined. kl@test1> select plan_table_output from table(dbms_xplan.display); PglN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------- Pgln hash value: 1197074294 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation              | Name                     | Rows | Cost (%CPU)| Time     |    TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT       |                          |     1 |   528   (6)| 00:00:05 |        |   |               | |   1 | SORT AGGREGATE        |                          |     1 |            |          |        |   |               | |   2 |   PX COORDINATOR       |                          |       |            |          |        |   |               | |   3 |    PX SEND QC (RANDOM) | :TQ10000                 |     1 |            |          | Q1,00 | P->S | QC (RAND) | |   4 |     SORT AGGREGATE     |                          |     1 |            |          | Q1,00 | PCWP |            | |   5 |      PX BLOCK ITERATOR |                          | 4463K|   528   (6)| 00:00:05 | Q1,00 | PCWC |            | |   6 |       TABLE ACCESS FULL| te_ORG_a_NODE_gl_PLC | 4463K|   528   (6)| 00:00:05 | Q1,00 | PCWP |            | ------------------------------------------------------------------------------------------------------------------------ 13 rows selected. kl@test1> alter index te_ORG_a_NODE_gl_PLC_PK rebuild; alter index te_ORG_a_NODE_gl_PLC_PK rebuild * ERROR at line 1: ORA-01418: specified index does not exist kl@test1> alter index kl.te_ORG_a_NODE_gl_PLC_PK rebuild; Index altered. kl@test1> select count(*) from kl.te_org_a_node_gl_plc; COUNT(*) ----------          0 kl@test1> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'kl',TABNAME => 'TE_ORG_A_NODE_GL_PLC',ESTIMATE_PERCENT => 0.1,CASCAD PL/SQL procedure successfully completed. kl@test1> select count(*) from kl.te_org_a_node_gl_plc; COUNT(*) ----------          0 kl@test1> create table kl.te_org_a_node_gl_plc_back as select * from kl.te_org_a_node_gl_plc; Table created. kl@test1> select count(*) from kl.te_org_a_node_gl_plc_back; COUNT(*) ----------    6406167 kl@test1> select /*+ full (t1) */ count(*) from kl.te_org_a_node_gl_plc t1; COUNT(*) ----------    6406167 kl@test1> truncate table kl.te_org_a_node_gl_plc; Table truncated. kl@test1> insert into kl.te_org_a_node_gl_plc select * from kl.te_org_a_node_gl_plc_back; 6406167 rows created. kl@test1> commit; Commit complete. kl@test1> select count(*) from kl.te_org_a_node_gl_plc; COUNT(*) ----------    6406167