Update statistics Questions
Subject: Update Statistics Confusion
Date: 10/9/97 9:08 PM
We are running On-Line v7.23. I ran some test evaluating update
statistics using the following SQL and we got some very strange behavior.
Why?
select count(*)
from x_forms x,prdct_END e
where e.endcd = x.formcd
and e.prdctcd = 'D&OHC'
and UPPER(x.internaldesc) like '%INSURED ENT%'
;
Update Statistics Pattern 1:
UPDATE STATISTICS MEDIUM FOR TABLE x_forms DISTRIBUTIONS ONLY ;
UPDATE STATISTICS LOW FOR TABLE x_forms (formver);
UPDATE STATISTICS HIGH FOR TABLE x_forms (formdesc);
UPDATE STATISTICS HIGH FOR TABLE x_forms (formcd);
UPDATE STATISTICS MEDIUM FOR TABLE prdct_end DISTRIBUTIONS ONLY ;
UPDATE STATISTICS LOW FOR TABLE prdct_end (carrinit);
UPDATE STATISTICS HIGH FOR TABLE prdct_end (prdctcd);
UPDATE STATISTICS HIGH FOR TABLE prdct_end (endcd);
Produced the following:
QUERY:
------
select count(*)
from x_forms x,prdct_END e
where e.endcd = x.formcd
and e.prdctcd = 'D&OHC'
and UPPER(x.internaldesc) like '%INSURED ENT%'
Estimated Cost: 2333
Estimated # of Rows Returned: 1
1) informix.x: SEQUENTIAL SCAN
Filters: informix.upper(informix.x.internaldesc )LIKE '%INSURED
ENT%'
2) informix.e: INDEX PATH
Filters: informix.e.prdctcd = 'D&OHC'
(1) Index Keys: endcd
Lower Index Filter: informix.e.endcd = informix.x.formcd
Update Statistics Pattern 2:
UPDATE STATISTICS MEDIUM FOR TABLE x_forms DISTRIBUTIONS ONLY ;
UPDATE STATISTICS LOW FOR TABLE x_forms (formver);
UPDATE STATISTICS HIGH FOR TABLE x_forms (formdesc, formcd);
UPDATE STATISTICS MEDIUM FOR TABLE prdct_end DISTRIBUTIONS ONLY ;
UPDATE STATISTICS LOW FOR TABLE prdct_end (carrinit);
UPDATE STATISTICS HIGH FOR TABLE prdct_end (prdctcd, endcd);
Produced the following:
QUERY:
------
select count(*)
from x_forms x,prdct_END e
where e.endcd = x.formcd
and e.prdctcd = 'D&OHC'
and UPPER(x.internaldesc) like '%INSURED ENT%'
Estimated Cost: 5
Estimated # of Rows Returned: 1
1) informix.e: INDEX PATH
(1) Index Keys: prdctcd carrinit
Lower Index Filter: informix.e.prdctcd = 'D&OHC'
2) informix.x: INDEX PATH
Filters: informix.upper(informix.x.internaldesc )LIKE '%INSURED
ENT%'
(1) Index Keys: formcd
Lower Index Filter: informix.x.formcd = informix.e.endcd
The question is: Why the different behavior?
Paul Marvin
marvinp@execrisk.com