Long Query - how to improve?
Using OnLine 4.1 on an HP 9000/807, I'm executing the following queries:
select
ticket_nr, contract_nr, ticket_desc
from
ticket
where
where_condition
into temp
t1 without log;
table_a has > 100k records, as a result of this query (which runs in about 8
seconds, t1 contains 8 records. Next,
select
t1.*,
contract.*
from
t1, outer ( contract, vendor, vendor_contract )
where
t1.contract_nr = contract.contract_nr and
contract.contract_nr = vendor_contract.contract_nr and
vendor.vendor_nr = vendor_contract.vendor_nr and
vendor_contract.priority = 'P'
This should return the contract information for a ticket, if it exists.
There may be many vendors on a particular contract - we only want the prime...
vendor contains 5k records, vendor_contract contains 8k records.
The following indexes exist:
i1 on contract(contract_nr)
i2 on vendor(vendor_nr)
i3 on vendor_contract(vendor_nr, contract_nr, priority)
The problem is that query2 takes almost 5 minutes, even with no nulls in
t1.contract_nr (outer joins unnecessary).
set explain shows indexes being followed for everything, exceptt1.contract_nr (SEQUENTIAL SCAN) but with 8 records, so what?
If I try:
select
t1.*,
contract.*
from
t1, outer contract
where
t1.contract_nr = contract.contract_nr
The query is almost instantaneous. As I add tables (vendor, vendor_contract)
to the outer join, the query slows down.
If a contract exists, it must have a vendor_contract and vendor entry.
What is happening here, and how can I speed things up?
--
--
In-Real-Life: John Bossert, Thalatta Corporation, (+1 206.455.9838)
Domain: bossert@Thalatta.COM Path: ...!sun!sunup!thebes!bossert