Re: Another SQL question

atarhini_at_ringer.cs.utsa.edu (Amine Y. Tarhini) wrote:

>This is my 2nd question on this group (I'll try to keep it down :), I have
>in fact just discovered this newsgroup, and have been working with
>ORACLE REPORTS 2.5 for a couple of months now. I hope I'll contribute
>somehow positively to the group (i.e. not just asking questions).
>Here's an analogy of what I'm trying to do:
 

>table yr_model table cars table inventory
>-------------- ---------- ---------------
>year car color year car color
>---- --- ----- ---- --- -----
>94 Chevy Blue 94 Chevy Blue
>95 Chevy Green 95 Chevy Blue
> Dodge Red 94 Dodge White
> Dodge White
> Ford Yellow
 

>Whereas the first 2 tables are small, table inventory is a huge table
>with over 2 million records. If I do:
> select year, car, color, count(*) from inventory
> where year = 94
> group by year, car, color, I get:
 

> year car color count(*)
> ---- --- ----- --------
> 94 Chevy Blue 1
> 94 Dodge White 1
>and the search takes about 5 minutes.
 

>But I need to get the following for year 94:
> year car color count(*)
> ---- --- ----- --------
> 94 Chevy Blue 1
> 94 Chevy Green 0
> 94 Dodge Red 0
> 94 Dodge white 1
> 94 Ford Yellow 0
 

>so I do something like:
> select yr_model.year, cars.car, cars.color, count(inventory.year)
> from yr_model, cars, inventory
> where (inventory.year = yr_model.year or inventory.year is null)
> and inventory.car (+) = cars.car
> and inventory.color (+) = cars.color
> and yr_model.year = 94
> group by yr_model.year, cars.car, cars.color
 

>I get the result shown above, but the query takes at least one hour to
>execute. I'm asking here in case the answer (to improving performance) is
>trivial. In case it is involved (indexes and table structures, etc..) then
>I'll go the "ask-your-managers way" :)
>Any help is certainly appreciated.
>--
>Amine.
>atarhini_at_ringer.cs.utsa.edu
 

>The above views are mine only ... etc. etc.

Since you are using Reports 2.5, you can make a 2 query report to achieve the desired output.

The first query would be:

    select yr_model.year, cars.car, cars.color

      from yr_model, cars
      where yr_model.year = 94

  note: this is a cartisian product, but it is unavoidable if the cars
             table does not have a year.  A parameter could be used
             for the year to allow a different year to be input every
             time it is run.

The second query would be:

    select year, car, color, count(*)

      from inventory
      group by year, car, color

On the data model screen, create the queries, then using the link tool, link the year column in the first query to the year column in the second query. Do the same for the car and color columns. On the Layout, you want to display the year, car, and color from query 1 and the count from query 2. Add a format trigger to the count field on the layout. The format trigger should contain the following code:

    function xxxxxxxxxxxxxxx return number as     begin

        if :count_column is null then
          srw.set_field_num(0,0);
        end if;
        return(true);

This should get the desired results and be considerably faster then the outer joined query you used.

If you have any problem, drop me a line.

Bill Lusk
Lusk Consulting Services
Houston TX Received on Sun Jul 28 1996 - 00:00:00 CEST