Regarding cartesian product

[himridul]

Hi all,

I've a query like this :
select
a.indid
from
master_table a,
ind_phone b,
ind_phone c,
ind_phone d
where
a.indid=b.indid and b.tel='B' and
a.indid=c.indid and c.tel='C' and
a.indid=d.indid and d.tel='D'

The master table has 100 millions of data.
Now if ind_phone table has 600 millions of data with
200 millions of data when tel='B'
200 millions of data when tel='C'
200 millions of data when tel='D'

Then what will be the cartesian product of this query?

I mean is it 100*200*200*200
OR
100*600*600*600

Will Oracle go first to where clause then go for cartesian product ?

or Will it go first to cartesian product then it will go for where clause ?

Thanks ..

 

[SantaMufasa]

Himridul,

If you are talking about a true Cartesian product (that is no indexes in place to prevent the Cartesian product), then the calculation is not 100*200*200*200, nor 100*600*600*600, but 100,000,000 * 600,000,000 * 600,000,000 * 600,000,000 which equals 21,600,000,000,000,000,000,000,000,000,000,000.

With proper indexing, Cartesian product should not even be an issue.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:29 (09Feb04) UTC (aka "GMT" and "Zulu&quot, 12:29 (09Feb04) Mountain Time)

 

[aryeh1010]

I think there is a misconception here. A cartesian product refers to a result set. It doesn't matter if there are indexes or not. That is a performance/sql tuning issue.

Since there are equi-joins from the master table to each of the other tables in the query, there will not be a cartestian product.

This would be the cartesian product for the query in question:

select
a.indid
from
master_table a,
ind_phone b,
ind_phone c,
ind_phone d

No where clause in site. Then it would give the 100,000,000 * 600,000,000 * 600,000,000 * 600,000,000 which equals 21,600,000,000,000,000,000,000,000,000,000,000 result that I have just copied from Mufasa's previous response.

The number of records returned is dependent on the number of records that match the conditions in the where clause. However in this case, no cartesian product. That should be a relief.

Aryeh Keefe

 

[sem]

Aryeh, Cartesian Product itself doesn't refer performance issue Look here:

http://en.wikipedia.org/wiki/Cartesian_product

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Aryeh,

Correct. As you mentioned a Cartesian product occurs when the result set contains every row combination from all rows in all tables. An example of that is when there is no WHERE clause.

In my reply, above, although my calculation was correct for the Cartesian product, I should not have said anything about indexes while discussing Cartesian product, because even if you have proper indexes, if you have no WHERE clause, you still get a Cartesian product.

Now, without any indexes, given Himridul's WHERE clause, instead of a Cartesian product, he would encounter "full-table scans". What I should have said was, "With proper indexing, you can avoid full-table scans".

Thanks, Aryeh, for causing the discussion to remain between the lines.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:49 (11Feb04) UTC (aka "GMT" and "Zulu&quot, 23:49 (10Feb04) Mountain Time)