Count(*) 为0的问题研究 --- 后来发现问题终于可以重现了,参考我后面的关于merge的文章
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 |