Re: Query Optimization Help needed

On Sat, 31 Oct 1998 18:29:33 -0500, "knez" <knez_at_ix.netcom.com> wrote:

>I wrote a query that retrieves account numbers and items that the account
>has. Because the items are different, I pull back 1 acct# per item with the
>same acct#.
>Example: Acct# Item
> 12001 apple
> 12001 pear
> 12001 grape
> 12005 apple
> 12009 pear
> 12009 grape
>
>I run another query against the results of the first query that looks like
>this: select acct#, count(*) from query_one group by acct#.
>
>This gives me exactly what I want (1 account number with the corresponding
>total count of items in the next column).
>Example: Acct# Count
> 12001 3
> 12005 1
> 12009 2
>
>Can I accomplish the same results with one query, maybe by using a subquery.
>I don't have access to PL/SQL, therefore it has to be standard query
>language.
>
>When I run the 2nd query it take quite a bit of time because the querying is
>done on my desktop. The front end program that I'm using doesn't index.. If
>I can get rid of the second query my run time would be cut in half.
>
>By the way my initial query is pulling from more than on table.
>
>Any help would be appreciated..
>

How is the data stored? 1 table, two tables, three? Give us the schema and I'm sure we can help you speed it up.

But a simple way to accomplish it with what you have already done is with an inline view.

eg.

select a.acct#, count(*)
  from ( <first query here> ) a
 group by a.acct#

Hope this helps.

chris.

>Thanks,
>Knez
>
Received on Mon Nov 02 1998 - 07:08:23 CST