1. Registered User
Join Date
Apr 2004
Location
zxczxczxc
Posts
52

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.

2. Registered User
Join Date
Mar 2004
Location
Netherlands
Posts
183
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

3. Registered User
Join Date
Apr 2004
Location
zxczxczxc
Posts
52

## Optimizer Statistics

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.

4. Registered User
Join Date
Mar 2004
Location
Netherlands
Posts
183
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

5. Registered User
Join Date
Apr 2004
Location
zxczxczxc
Posts
52

## Optimizer statistics

Thanks Rob for your abundant of information.

Bye.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•