slow sql query execution

Question

I have following rather simple query

select count(*) from tbl t1, tbl t2

For some reason it takes tens of seconds to execute this command. I wonder why it happens? It seems that it builds Cartesian product of that tables (table has about 270k rows), but amount of rows is obvious without building product.

Some clarifications would be helpful.

The cartesian product is known to be slow because it returns all of the data. — Jay Prall
– Jay Prall, Commented Apr 6, 2012 at 14:28
Despite the 5 down votes, I think this is a good question. Oracle does not always use every table in a query, it's not unreasonable to expect Oracle would apply that same optimization here. — Jon Heller
– Jon Heller, Commented Apr 7, 2012 at 15:29

Thorsten · Accepted Answer · 2012-04-06 15:15:50Z

3

I would assume that the optimizer is not built to optimize for cartesian products (especially not for large tables as you mention). In your case it probably build the complete result set and counts it.

Rather than fight it, just count the table once

select count(*) from tbl

and build the product yourself.

(I assume that you have a more useful case in mind than the rather senseless self-join you posted :-), please add some more info on what you want to achieve.)

answered Apr 6, 2012 at 15:15

Thorsten

13.2k18 gold badges66 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Heller Over a year ago

+1 I agree that the optimizer doesn't seem to work well with cross joins. My guess is that since 99% of all cross joins are application bugs, Oracle doesn't put a lot of effort into optimizing it. Which is unfortunate, because some times when I really need to use a cross join the optimizer completely fails.

Jon Heller · Accepted Answer · 2012-04-07 15:19:14Z

2

If just want to know the Cartesian product why not calculate it yourself?

SELECT Power(COUNT(*), 2) AS product 
FROM   tbl t1;

edited Apr 7, 2012 at 15:19

Jon Heller

37k6 gold badges84 silver badges150 bronze badges

answered Apr 7, 2012 at 12:11

Shiplu Mokaddim

57.5k20 gold badges147 silver badges193 bronze badges

1 Comment

Shiplu Mokaddim Over a year ago

@jon thanks. In fact I checked in a online tool which showed its correct. Seems it just checked the syntax. Not validated it.

Collectives™ on Stack Overflow

slow sql query execution

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related