Re: Another SQL question

Amine Y. Tarhini (atarhini_at_ringer.cs.utsa.edu) 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.

How badly would it screw up your data if you inserted into your inventory table dummy records containing every combination of make and color?

That way you could
select yr, car, color, count(*)-1
from inventory
group by yr, car, color

This is a corruption of the data and would need to be documented carefully but it should give you an improvement in performance Received on Sat Jul 27 1996 - 00:00:00 CEST