Oracle Select *返回行，但是Select count(1)返回0 [英] Oracle Select * returns rows but Select count(1) return 0

问题描述

所以，这很奇怪，这是我以前从未见过的.我希望有人能给出一个神奇的答案，从而可以弄清楚这个问题...

So, this is bizarre and it's something I have never seen before. I'm hoping someone has the magic answer that can shed some light on this problem...

SELECT * FROM TABLE -- returns rows... a lot of rows

但是，

SELECT COUNT(1) FROM TABLE -- returns zero (0), as in the number zero (0) as the result

这是表格结构:

CREATE TABLE TRACKING (
  A_ID NUMBER,
  D_CODE NUMBER,
  HOD NUMBER,
  ADR_CNT NUMBER,
  TTL_CNT NUMBER,
  CREATED DATE,
  MODIFIED DATE
);
CREATE INDEX HOD_D_CODE_IDX ON TRACKING (HOD, D_CODE);
CREATE UNIQUE INDEX TRACKING_PK ON TRACKING (A_ID, D_CODE, HOD);
CREATE INDEX MOD_DATE_IDX ON TRACKING (MODIFIED);
ALTER TABLE TRACKING ADD CONSTRAINT TRACKING_PK PRIMARY KEY (A_ID, D_CODE, HOD);

Oracle表如何有行，但count(1)返回零?我在网上做了一些搜索，但什么也没找到.我发现的唯一其他帖子与MS SQL Server有关.这是在Oracle中发生的.

How can an Oracle table have rows but count(1) return zero? I've done some searching on the web but found nothing. The only other post I found was in relation to MS SQL Server. This is happening in Oracle.

有什么主意吗?有人吗?

Any idea? Anyone?

在此先感谢您提供的帮助.

Thank you in advance for any help you can provide.

我可能希望添加的另一件事是希望能帮助解答这个难题，Oracle Job使用此表来汇总和填充另一个表.但是，它已经完成了几天.另一个表已完全填充，并显示了预期的记录数.我检查了Oracle Job Log，它显示了所有成功，而不是一个错误.

Another thing I might add in hopes that it will help answer the puzzle, this table was used by an Oracle Job to aggregate and populate another table. However, it's been done for a few days now. The other table is fully populated and shows expected record counts. I checked the Oracle Job Log and it shows all success and not a single error.

推荐答案

错误的结果可能是由破坏，错误和无提示更改SQL语句的功能引起的.

Wrong results can be caused by corruption, bugs, and features that silently change SQL statements.

1. 损坏的索引.很少有索引损坏，并且索引中的数据与表中的数据不匹配.当查询计划更改并使用索引时，这会导致意外的结果，但是对于使用表访问的不同查询，一切看起来都很正常.有时，只需重建对象即可解决此问题.如果不是这样，则需要创建一个完全可重现的测试用例(包括数据).可以将其张贴在此处或提交给Oracle支持.跟踪下来可能要花几个小时.
2. 错误.很少有错误会导致查询在返回或更改数据时失败.同样，需要完全可复制的测试用例来 诊断出来，这可能需要一段时间.
3. 切换SQL的功能有几种透明更改SQL语句的方法.查看虚拟专用数据库(VPD)，DBMS_ADVANCED_REWRITE和SQL转换框架.

1. Corrupt index. Very rarely an index gets corrupt and the data from an index does not match the data from a table. This causes unexpected results when the query plan changes and an index is used, but everything looks normal for different queries that use table access. Sometimes simply re-building objects can fix this. If it doesn't, you'll need to create a fully reproducible test case (including data); either post it here or submit it to Oracle Support. It can take many hours to track this down.
2. Bug. Very rarely a bug can cause queries to fail when returning or changing data. Again, a fully reproducible test case is required to diagnose this, and it can take a while.
3. Feature that switches SQL There are a few ways to transparently alter SQL statements. Look into Virtual Private Database (VPD), DBMS_ADVANCED_REWRITE, and the SQL Translation Framework.

要排除#3，下面的代码向您展示了执行此操作的邪恶方法之一以及如何检测到它.首先，创建架构和一些数据:

To rule out #3, the code below shows you one of the evil ways to do this, and how to detect it. First, create the schema and some data:

CREATE TABLE TRACKING (
  A_ID NUMBER,
  D_CODE NUMBER,
  HOD NUMBER,
  ADR_CNT NUMBER,
  TTL_CNT NUMBER,
  CREATED DATE,
  MODIFIED DATE
);
CREATE INDEX HOD_D_CODE_IDX ON TRACKING (HOD, D_CODE);
CREATE UNIQUE INDEX TRACKING_PK ON TRACKING (A_ID, D_CODE, HOD);
CREATE INDEX MOD_DATE_IDX ON TRACKING (MODIFIED);
ALTER TABLE TRACKING ADD CONSTRAINT TRACKING_PK PRIMARY KEY (A_ID, D_CODE, HOD);

insert into tracking values (1,2,3,4,5,sysdate,sysdate);
commit;

起初，一切都会按预期进行:

At first, everything works as expected:

SQL> SELECT * FROM TRACKING;

      A_ID     D_CODE        HOD    ADR_CNT    TTL_CNT CREATED   MODIFIED
---------- ---------- ---------- ---------- ---------- --------- ---------
         1          2          3          4          5 17-JUN-16 17-JUN-16

SQL> SELECT COUNT(1) FROM TRACKING;

  COUNT(1)
----------
         1

然后有人这样做:

begin
  sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
    'april_fools',
    'SELECT COUNT(1) FROM TRACKING',
    'SELECT 0 FROM TRACKING WHERE ROWNUM = 1',
    false);
end;
/

现在结果是错误":

SQL> ALTER SESSION SET query_rewrite_integrity = trusted;

Session altered.

SQL> SELECT COUNT(1) FROM TRACKING;

  COUNT(1)
----------
         0

这可以通过查看解释计划来检测.在下面的示例中，谓词2 - filter(ROWNUM=1)提示存在问题，因为该谓词不在原始查询中.有时，解释计划的注释"部分会告诉您确切的原因，但有时仅提供线索.

This can be probably be detected by looking at the explain plan. In the example below, the Predicate 2 - filter(ROWNUM=1) is a clue that something is wrong, since that predicate is not in the original query. Sometimes the "Notes" section of the explain plan will tell you exactly why it was transformed, but sometimes it only gives clues.

SQL> explain plan for SELECT COUNT(1) FROM TRACKING;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1761840423

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     2 |     1   (0)| 00:00:01 |
|   1 |  VIEW             |                |     1 |     2 |     1   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY   |                |       |       |            |          |
|   3 |    INDEX FULL SCAN| HOD_D_CODE_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1)

15 rows selected.

(不相关的注释-始终使用COUNT(*)而不是COUNT(1).COUNT(1)是一个古老的神话，看起来像是对货物崇拜的编程.)

(On an unrelated note - always use COUNT(*) instead of COUNT(1). COUNT(1) is an old myth that looks like cargo cult programming.)

这篇关于Oracle Select *返回行，但是Select count(1)返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！