hi,
I want to know how does(What parameters does it take into consideration) the optimizer calculate Estimated cost when we run a set explain on SQL statement.


bye.

Hi,

Here some info:

The cost is calculated as follows:

Cost = number of I/O's + (number of rows proccessed * fudge factor)
Fudge factor = 0.03 (fixed value)

The most important parameter is OPTCOMPIND. This will tell if informix will examine all possibilities (2) or if only index scans are examinded if possible (0).

Hope this helps a bit,

Rob Prop

Hi,

Is it the "number of IO's" is the disk accesses required to process the rows satisfying the condition in where clause. Also the "number of rows processed" is the rows satisfying in the where caluse. And what is the fudge factor and how is this value derived.


bye.

Some extra info,

Fudge factor (0.03) is defined/chosen by the IDS developers. This value is fixed and tells IDS it can process 33.333 rows in the same time it can do 1 I/O. This is of course just an assumption. Some other DBMS's like DB2 calculate the fudge factor. AT startup/installation it tests the CU speed en the user can define the disk speed (seek time and transfer speed) for every disk. In IDS this is not the case. Here the fudge factor is just a given and pre-determined fixed value.

Th I/O and rows processed is not what really passes or searched but what the statistics tells IDS. (Thats why good statistics are so important).

Rows processed is not the number of rows selected but the number of rows evaluated. In a sequential scan for example it is every record. (So the nrows column of the systables which is updated with update statistics)

Numbers of I/O is the total number of I/O IDS will do (following the statistics again). For a sequential scan it are all datapages (I/O size can differ). For an index svcan all index pages that are read.

If you need some extr/other info please let me now. You can also read the book 'the next great wave' from Michael Stonebraker. In this book DBMS optimizers are discussed (Relational and Object-Relational). IDS is often used as an example in this book.

Greetz,

Rob Prop

Thanks Rob for your abundant of information.

Bye.