Oracle Natural Joins and Count(1)

Question

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?

Such as

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

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'

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

Am I just missing something?

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
);

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

Answer 1

Could you provide а reproducible testcase?

Answer 2

If it happens exactly as you say then it must be an optimiser bug, you should report it to Oracle.

Answer 3

you should try a count(*)

There is a difference between the two.
count(1) signifies count rows where 1 is not null
count(*) signifies count the rows

Answer 4

Just noticed you used 2 natural joins... From the documentation you can only use a natural join on 2 tables Natural_Join

Answer 5

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.