Re: Explain (more is better?)

From: "John Berry" <johnberry@clara.net> > >We have a large number of tables > 700 with a large majority in the range >of > 1GB > >Some of the queries on the database can take a number of minutes especially >when >multiple joins are being performed. > >Now I have always assumed the lower the cost of query the better :-) In most cases yes. It's also just an arbitrary, meaningless number, in other words, if you had 2 different queries that somehow had exactly the same query cost, they might not take the same time to run. >However I am starting to doubt this when I run a low cost query on a >number >of frequent hit tables it can take upto 2 mins to return data >If I re-structure the query the cost goes up to 2500 but data is returned >in >seconds ? This has happened to me too. >Would I be right in assuming the cost is based on use of indexes and amount >of data retrieved and not a lot else so a high cost query is not always >bad. No. It's an arbitrary formula based on the expected amount of disk IO and CPU time needed to complete your query. Something like: CPU TIME (in milliseconds) + N x NUMBER OF I/Os NEEDED where N is some fudge factor. >and if so what about OPTCONF hmm 0,1 or 2 Depends on your system. If it's an OLTP system, 0 is probably better. If it's a DSS system, 2 is probably better. Also remember to keep your statistics updated, especially if you frequently add lots of data -- this improves the optimisers chances of getting the sums right. _____________________________________________________________________________________ Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com