views:

1416

answers:

5

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.

A: 

Could you provide а reproducible testcase?

+1  A: 

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

Tony Andrews
+1  A: 

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

Since 1 is never null, there is no difference in potential results.
Tony Andrews
... and count(1) is rewritten to count(*) anyway. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245
David Aldridge
+1  A: 

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

That is incorrect (and the site you referenced is not the documentation, it is an independent Wiki):See http://68.142.116.68/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#sthref9834
Tony Andrews
+8  A: 

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.

EddieAwad
Thanks a bunch! This helped me diagnose my bug (http://gist.github.com/72614) that turned out to be due to NATURAL JOINs.
rampion