MVSFORUMS.com

[Martin]

Hi All,

I need to know how the cost of a query gets calculated.

I ran an explain on a query and got the cost as X. the number of rows on the table was say 10000. Now I changed the query and again ran an explain on this query . The cost was X-2 say...( with the same num of rows).

Now I deleted 1000 rows from the table and ran an explain on the same 2 queries. The cost ratio was Y/Y-4

Now my question is shud the ratio

X/X-2 = Y/Y-4

I am really confused here. doesn't the cost depend on number of rows ?

any pointer will be of great help!

I did read the :

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/6.4.8?DT=20010710165542#HDRL344STB

But ther is no mention of how the cost gets calculated.

-Mt

[CICS Guy]

Smoke and mirrors....There is an "entity" that tries to predict the best way to access the data required for a select. It uses various system tables and other arcane information. It doesn't always pick the best, sometimes it has to be "duped" to chose a construct that is actually better.
The cost reflected by an explain can only be taken as a measure of what that "entity" thinks is the best way.
In short, sometimes re-arranging the conditions of a WHERE clause can change things, sometimes modifying a system table entry to increase the number of rows can influence the "entity" to pick or avoid a particular access path, sometimes reducing the number of rows can sway its decision....
Like I started with smoke and mirrors.... Or, that's why good DBAs get paid so much.....

[expat]

You could speak to your capacity and performance group. They are the ones that usually look at how much resource is consumed by what, and may be able to apply a cost to it.

Many years ago I was into all of that, it's conplicated, and you would need something like MXG to do most of the work for you.
_________________
If it's true that we are here to help others,
then what exactly are the others here for ?