Re: Optimizing left outer join query
Posted by:
Toa Sty
Date: September 26, 2006 04:06AM
That was a quick reply :)
To be honest Muhammad I'm not sure what's best now. To do your query for that vendor_id is always gonna take 400K index lookups, so your goals have to be to speed up the index lookups as much as possible.
Things to consider are:
* Use temp tables as their index lookups can be faster (as I did ealier)
* Do lookups on an integer columns rather than varchar(25). Integers are much smaller so you should see faster index lookups. (so maybe look at normalising so that you have a an integer id for each mfg_no that you have)
* Combine the two. (join on an integer using a temp table! (might not help any more - I dunno)
* If you dont want to do this, you might (possibly) see some benefit to changing your varchar(25) to char(25) to get fixed row widths, but given that your query is just looking at index files I'm not sure that this will help. In fact it might make it worse. You can always try it and see. (It will probably be a good idea to run optimize after the col type change)
There may well be some other stuff you can do too, but I don't think that query is ever going to be super fast. It just has too much work to do.
HTH,
Toasty
-----------------------------------------
email: 'toasty'*3 at gmail
Edited 1 time(s). Last edit at 09/26/2006 04:41AM by Toa Sty.
Subject
Views
Written By
Posted
14250
September 24, 2006 11:40PM
4767
September 25, 2006 03:00AM
4242
September 25, 2006 04:54AM
4487
September 25, 2006 08:09AM
3744
September 25, 2006 09:55PM
3585
September 26, 2006 03:35AM
3261
September 26, 2006 03:46AM
Re: Optimizing left outer join query
5079
September 26, 2006 04:06AM
3139
September 26, 2006 06:31PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.