optimizing simple query

I've got this very simple query that takes alot of time to execute:

SELECT sd.shipmentnumber
FROM shipmentdetail sd, shipment s
WHERE sd.shipmentnumber = s.shipmentnumber
AND sd.idreceived != s.iddest


Is there a way to optimize something like this?

Both tables are very large and I'm using Oracle 9i.

I take it you are looking for all shipments that have not reached it's destination.
There are a few things that could be done, not sure if it will speed it up much.

1. Tried archiving?
Add a trigger to move the data into another table once sd.idreceived = s.iddest
This way you lower the number of records that needs to be searched.

2. Manipulate the indexes?
Use the secondary index field to speed up index search. This results in faster searches.

3. Use numbers
If the sd.idreceived and s.iddest are text (my guess is they are not, but needs to be mentioned), then add a destination table and use the destination id as foreign key. Matching numbers are faster then varchars

4. Store the procedure.
Might not increase speed much.

5. Make it a view.
Might not increase speed much either.

6. Alter the sql.
Although this example uses a sub query it migth have less matches and thus be faster.
(I'm in doubt on this one, you'll have to try it and see.)


I think option 1 is the best solution.
But one of the others should work. You could even combine one ore more suggestions.

Hope this helps.

This would be a nice solution in a perfect world... All data is used all the time. And shipments received in wrong destinations are also used with right destinations for other reports. But I'll se if there's a way to use this idea some other way.



secondary index field?



Your guess is right. Already numbers


It does speed up, but not enough


Doesnt speed up at all. But I heard about Materialized views which seems to speed this up alot. Any downsides to doing it this way?

Doesnt speed up either...


Thanks for the help

Indexes:
I couldn't find it on oracle's page without having to look for it, so I ripped this url:
http://alpha6.its.monash.edu.au/ora...8schem.htm#3239

Hope that helps.