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

Did you take a look at the explain plans of both queries. Though I would expect them to be the same ....
Posting the Explain plans would help (set autotrace traceonly explain).

By response time I hope you mean "the total time in which you get ALL the results".
If you have optimizer_goal = first_rows then you might start seeing the rows fast
(i.e first row appears in 3-4 secs .. but it might take minutes for the all the rows to be returned)
.. but the query will take its time to finish. By doing a count, the CBO might be showing "optimizer_goal=all_rows" kind of results....

The explain plan might provide us the answers.

Anurag

"Andreas Ritzer" <aritzer_at_yahoo.com> wrote in message news:fc5a28de.0109071446.6fbf6240_at_posting.google.com...
> 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 Fri Sep 07 2001 - 18:09:13 CDT