Another SQL question
Date: 1996/07/22
Message-ID: <4t1j3o$1h2_at_ring01.cs.utsa.edu>#1/1
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.Received on Mon Jul 22 1996 - 00:00:00 CEST