Oracle Natural Joins and Count (1)

Question

Does anyone know why in Oracle 11g, when you make graph (1) with more than one natural join, it makes a Cartesian join and throws the score?

For instance,

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

It backs off like 3 million lines when

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

goes back like 36,000 lines, which is the right amount.

Did I miss something?

Here are the tables that I use 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 that this is not a problem in Oracle 9i, but when we switched to 11g, it became a problem.

Answer 1

My advice: DO NOT use NATURAL JOIN. Clearly define the connection conditions to avoid confusion and “hidden errors”. Here is the official NATURAL JOIN Oracle documentation and more discussions about this subject.

Answer 2

If this happens exactly as you say, then it should be an optimizer error, you should report it to Oracle.

Answer 3

you should try the counter (*)

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

Answer 4

Just noticed that you used 2 natural compounds ... From the documentation you can only use a natural compound on 2 tables Natural_Join