IDS Forum

=?ISO646-US?Q?Re:_Re:_Estimated_cost_=3F?=

Posted By: vze2qjg5@verizon.net
Date: Wednesday, 29 June 2005, at 9:43 a.m.

In Response To: Estimated cost (PARAMESHWAR PATHRI)

Sigh. My machine has gone away, so I am stuck with webmail. This probably won't get through to the list.

You can see that it is doing the same work for each query - and using an index to boot. The higher cost looks like it id due to the estimated number of rows returned for each query.

However, these estimated costs are so low that they really are not worth worrying about. I would be more concerned if the statements in question actually took longer than you expected.

Regards,
Jack Parker


>From: Chakravarthy Pathri <pcdudyala@yahoo.com>
>Date: Mon Jun 27 08:09:21 CDT 2005
>To: Jack Parker <vze2qjg5@verizon.net>, ids@iiug.org
>Subject: Re: Estimated cost [5258]

>hi,Here is the explain out ?delete from test where x in (19,10,1)Estimated Cost: 27
>Estimated # of Rows Returned: 636? 1) informix.test: INDEX PATH??? (1) Index Keys: x?? (Serial, fragments: ALL)
>??????? Lower Index Filter: informix.test.x = 19??? (2) Index Keys: x?? (Serial, fragments: ALL)
>??????? Lower Index Filter: informix.test.x = 10??? (3) Index Keys: x?? (Serial, fragments: ALL)
>??????? Lower Index Filter: informix.test.x = 1???delete from test where x =19Estimated Cost: 5
>Estimated # of Rows Returned: 90? 1) informix.test: INDEX PATH??? (1) Index Keys: x?? (Serial, fragments: ALL)
>??????? Lower Index Filter: informix.test.x = 19
>?delete from test where x =10Estimated Cost: 5
>Estimated # of Rows Returned: 96? 1) informix.test: INDEX PATH??? (1) Index Keys: x?? (Serial, fragments: ALL)
>??????? Lower Index Filter: informix.test.x = 10
>delete from test where x =1Estimated Cost: 44
>Estimated # of Rows Returned: 1085? 1) informix.test: INDEX PATH??? (1) Index Keys: x?? (Serial, fragments: ALL)
>??????? Lower Index Filter: informix.test.x = 1
>?Bye.?????Here
>
>Jack Parker <vze2qjg5@verizon.net> wrote:First of all - the estimated cost is not an exact number - you should use it
>as an order of magnitude check, not a real value.
>
>Second of all, you need to tell us what the execution method of each is -
>the first and second look like a potential scan of a small table, while the
>second two look like they might be index reads.
>
>Third of all, in the first query, the engine is not going to read the table
>three times, it's going to read it once and check each row for your filter.<BR>In the 2-4th queries, you are doing three sets of reads - however they are<BR>performed. So if anything, in an ideal world, the estimated costs for each<BR>of the 4 queries should be the same.<BR><BR>You may not have an index, but perhaps you've done update statistics on this
>column and the database knows there are no rows with a value of 2 or 3.
>That would explain the low cost there.
>
>j.
>----- Original Message -----
>From: "PARAMESHWAR...." <PCDUDYALA@YAHOO.COM>
>To: <IDS@IIUG.ORG>
>Sent: Monday, June 27, 2005 4:09 AM
>Subject: Estimated cost [5258]
>
>
>> Hi,
>> I want to know why the sum of estimated cost in the second form of
>exection is not same as in first SQL.
>>
>> 1. select * from test where x in (1,2,3)
>> estmated cost is 676
>>
>> 2nd method
>>
>> select * from test where x=1
>> est cost is 1085
>> select * from test where x=2
>> est cost is 90
>> select * from test where x=3
>> est cost is 91
>>
>> Ideally i feel the estimated cost of the serial execution of 3 selects
>should match the est cost of first quuery.
>>
>> If i am wrong pls let me know the mystry behind it.
>>
>>
>>
>
>
> Yahoo! Sports
>Rekindle the Rivalries. Sign up for Fantasy Football

Messages In This Thread

• Estimated cost
  PARAMESHWAR PATHRI -- Monday, 27 June 2005, at 3:09 a.m.
  • RE: Estimated cost
    Michaelis, Daniel -- Monday, 27 June 2005, at 8:30 a.m.
  • Re: Estimated cost
    Chakravarthy Pathri -- Monday, 27 June 2005, at 9:15 a.m.
    • RE: Estimated cost
      malcolm weallans -- Monday, 27 June 2005, at 2:28 p.m.
  • =?ISO646-US?Q?Re:_Re:_Estimated_cost_=3F?=
    vze2qjg5@verizon.net -- Wednesday, 29 June 2005, at 9:43 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.