Re: Count query taking too long vs identical query with columns returned

Andreas,

You are generating catesian product coz you missed join key between table B and C as well as D and E. Review your business logic to match your query.

Cheers,

Reid Lai

Andreas Ritzer wrote:

> We're currently running Oracle 8.i and have 5 tables I'm querying with
> approx. 1.5 millions records in each.
>
> When I perform the following type of query (with joins between the
> tables and filtering):
> SELECT A.col1, B.col2, C.col3, D.col4, E.col5
> FROM table1 A, table2 B, table3 C, table4 D, table5 E
> WHERE A.col = B.col
> AND A.col = C.col
> AND C.col = D.col
> AND C.col = E.col
> AND A.anotherColumn = 'SOMEARG'
>
> the response time is in the neighorbood of 3-4 seconds.
>
> When I try to perform the EXACT query trying to use the count
> function, e.g.:
> SELECT count(A.primaryKey)
> FROM table1 A, table2 B, table3 C, table4 D, table5 E
> WHERE A.col = B.col
> AND A.col = C.col
> AND C.col = D.col
> AND C.col = E.col
> AND A.anotherColumn = 'SOMEARG'
>
> the response time is well over 2 minutes. I have analyzed the tables
> and indexes to death and tried count(1), count(*), decode(count(*), 0,
> 0, 1), count(rowid), all to no avail.
>
> Can anyone help me understand why the same query would perform so
> poorly when I'm only trying to retrieve the count rather then the
> underlying data. In my naive head I thought with all things being
> equal the count would perform equally if not better.
>
> Thanks for all your help,
> Andreas
Received on Sat Sep 08 2001 - 13:27:36 CDT