| Optimize query [message #21292] |
Wed, 24 July 2002 21:09  |
Lizzy
Messages: 3
Registered: July 2002
|
Junior Member |
|
|
I have written a query which gives the correct results, but if a run the query on our production database it does not perform at al. Can somebody help me???
I have two tables:
transacties, with attributes:
-nr_transaction
-nr_relation_counterparty
-nr_relation_broker
-nr_relation_issuer
-nr_relation_receive
relations, with attributes:
-nr_relation
-name
I have to report all transactions which does not have a corresponding nr_relation in the relations table.
I have tried the query below. I know the query can be optimized, but i don't know how... Please help me!
select trn.nr_transactie
from transactions trn
where
(trn.nr_relation_receive not in
(select rel.nr_relation from relations rel)
or
trn.nr_relatie_counterparty not in
(select rel.nr_relation from relations rel)
or
trn.nr_relation_issuer not in
(select rel.nr_relation from relations rel)
or
trn.nr_relation_broker not in
(select rel.nr_relation from relations rel))
I think i can get rid of the not in's, but it don't know how to do it.
|
|
|
|
| Re: Optimize query [message #21297 is a reply to message #21292] |
Thu, 25 July 2002 03:15  |
Geoffrey
Messages: 32
Registered: February 2002
|
Member |
|
|
this might go faster
select
trn.nr_transactie
from
transactions trn ,
nr_relation r1,
nr_relation r2,
nr_relation r3,
nr_relation r4
where
trn.nr_relation_receive = r1.nr_relation (+) and
trn.nr_relatie_counterparty = r2.nr_relation (+) and
trn.nr_relation_issuer = r3.nr_relation (+) and
trn.nr_relation_broker = r4.nr_relation (+) and
(
r1. nr_relation is null or
r2. nr_relation is null or
r3. nr_relation is null or
r4. nr_relation is null
)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
