所以,这很奇怪,这是我以前从未见过的.我希望有人能给出一个神奇的答案,从而可以弄清楚这个问题...
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.
要排除#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
发布评论