Re: Query Optimising Problem !!

Alan Basically, the output of SET EXPLAIN ON tells you the access path that the optimiser has chosen. It describes what order the tables are processed in and by what method e.g. via index, sequential scan etc The optimiser assigns a score to each access method and chooses the 'cheapest' path for each table - sometimes it gets it wrong :-) Having seen what routes have been chosen you can tune what indexes are on the tables etc. One point if your tables are changing frequently you should issue an UPDATE STATISTICS at least once a week. If the basic details held by theoptimiser are incorrect it can seriously affect performance. A good idea is to modify your programs do a SET EXPLAIN ON when they start - if a command line parameter is set for example. This way you can test the application at any time without having different test/live versions. If you want mail me a short bit and I'll take you through it - you can buy me some Char Kway Teow next time I'm in Singapore :-) Paul Hatcher Graduate Asssociates Ltd Phone 0181 840 3110 Fax 0181 567 1894