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.

Try changing the order in from clause:

    from inventory, cars, yr_model
( try to keep number of records to min from the driving table..the table far   away from "FROM" clause.. yr_model)

Hope it helps.. Received on Wed Jul 24 1996 - 00:00:00 CEST