Difference between update statistics low & high
When we update the same table statistics low and high separately, we get two
different sqexplain.out as shown below on running same SQL statements.
============================================================
QUERY (table is updated statistics as high)
-----------
select * from table where field1 >= '0000-0000' and field2 <= 2 and field3 !=4 order by field1, field4;
Estimated Cost : 128081
Estimated # of Rows Returned: 67396
1) table: INDEX PATH
Filters: (table.field1 >= '0000-0000' and (table.field2 <=2 and
table.field3 !=4 ))
(1) Index Keys : field1, field4
============================================================
QUERY (table is updated statistics as low)
-----------
select * from table where field1 >= '0000-0000' and field2 <= 2 and field3 !=4 order by field1, field4;
Estimated Cost : 128081
Estimated # of Rows Returned: 43147
1) table: INDEX PATH
Filters: (table.field2 <=2 and table.field3 !=4 )
(1) Index Keys : field1, field4
Lower Index Filter : table.field1 >= '0000-0000'
============================================================
They all use index path for searching, the estimated cost of them is the same;
however, the optimizer use two different filters(index path filters & low
index filter) that the filter (table.field1 >= '0000-0000') uses in index path
or lower index).
What result they returned though the same cost ?
In our situation, the first and second performs the same at the beginning.
However, the performance of first one is gradually decreasing while it is
running.
Regards,
Daniel