Oracle Natural Joins和Count(1)

问题描述

有人知道为什么在Oracle 11g中使用多个自然连接执行Count(1)时会进行笛卡尔连接并导致计数掉吗?

Does anyone know why in Oracle 11g when you do a Count(1) with more than one natural join it does a cartesian join and throws the count way off?

例如

SELECT Count(1) FROM record NATURAL join address NATURAL join person WHERE status=1
AND code = 1 AND state = 'TN'

这会在300万行时向后拉动

This pulls back like 3 million rows when

SELECT * FROM record NATURAL join address NATURAL join person WHERE status=1
AND code = 1 AND state = 'TN'

像36000行一样向后拉，这是正确的数量.

pulls back like 36000 rows, which is the correct amount.

我只是想念些东西吗?

这是我用来获取此结果的表.

Here are the tables I'm using to get this result.

CREATE TABLE addresses (
address_id           NUMBER(10,0)  NOT NULL,
address_1            VARCHAR2(60)  NULL,
address_2            VARCHAR2(60)  NULL,
city                 VARCHAR2(35)  NULL,
state                CHAR(2)       NULL,
zip                  VARCHAR2(5)   NULL,
zip_4                VARCHAR2(4)   NULL,
county               VARCHAR2(35)  NULL,
phone                VARCHAR2(11)  NULL,
fax                  VARCHAR2(11)  NULL,
origin_network       NUMBER(3,0)   NOT NULL,
owner_network        NUMBER(3,0)   NOT NULL,
corrected_address_id NUMBER(10,0)  NULL,
"HASH"                 VARCHAR2(200) NULL
);

CREATE TABLE rates (
rate_id      NUMBER(10,0) NOT NULL,
eob          VARCHAR2(30) NOT NULL,
network_code NUMBER(3,0)  NOT NULL,
product_code VARCHAR2(2)  NOT NULL,
rate_type    NUMBER(1,0)  NOT NULL
);

CREATE TABLE records (
pk_unique_id      NUMBER(10,0) NOT NULL,
rate_id           NUMBER(10,0) NOT NULL,
address_id        NUMBER(10,0) NOT NULL,
effective_date    DATE         NOT NULL,
term_date         DATE         NULL,
last_update       DATE         NULL,
status            CHAR(1)      NOT NULL,
network_unique_id VARCHAR2(20) NULL,
rate_id_2         NUMBER(10,0) NULL,
contracted_by     VARCHAR2(50) NULL,
contract_version  VARCHAR2(5)  NULL,
bill_address_id   NUMBER(10,0) NULL
);

我应该提到，这在Oracle 9i中不是问题，但是当我们切换到11g时，这成为了问题.

I should mention this wasn't a problem in Oracle 9i, but when we switched to 11g it became a problem.

推荐答案

我的建议是不要使用NATURAL JOIN.明确定义您的加入条件，以避免混淆和隐藏的错误".这是 Oracle NATURAL JOIN官方文档和有关此问题的更多讨论学科.

My advice would be to NOT use NATURAL JOIN. Explicitly define your join conditions to avoid confusion and "hidden bugs". Here is the official NATURAL JOIN Oracle documentation and more discussion about this subject.

这篇关于Oracle Natural Joins和Count(1)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！