9.3 optimizer - odd behaviour?

I've always held to the idea that index searches won't happen if you are querying on a field which is not the top field in a composite index. e.g., our "contract" table (4.5M rows) has index (amongst others): CREATE INDEX xyz ON contract(policy_number, policy_systype) There are no other indexes using either of these fields. So the query SELECT policy_systype, count(*) FROM contract GROUP BY 1 ORDER BY 1 should be unindexed, yes? But sqexplain shows: QUERY: ------ select policy_systype, count(*) from contract group by 1 order by 1 Estimated Cost: 11660067 Estimated # of Rows Returned: 10 Temporary Files Required For: Order By Group By 1) dba.contract: INDEX PATH <<<-----------!!!!!!!!! (1) Index Keys: policy_number policy_systype (Key-Only) (Serial, fragment s: ALL) What's causing this then?

malc_p@btinternet.com wrote: > I've always held to the idea that index searches won't happen if you > are querying on a field which is not the top field in a composite > index. > e.g., our "contract" table (4.5M rows) has index (amongst others): > CREATE INDEX xyz ON contract(policy_number, policy_systype)> > There are no other indexes using either of these fields. > > So the query > > SELECT policy_systype, count(*) FROM contract GROUP BY 1 ORDER BY 1> > should be unindexed, yes? > > But sqexplain shows: > > QUERY: > > ------ > > select policy_systype, count(*) from contract> > group by 1 > > order by 1 > > > > Estimated Cost: 11660067 > > Estimated # of Rows Returned: 10 > > Temporary Files Required For: Order By Group By > > > > 1) dba.contract: INDEX PATH <<<-----------!!!!!!!!! > > > > (1) Index Keys: policy_number policy_systype (Key-Only) (Serial, > fragment > s: ALL) > > > What's causing this then? > The fact that the cost is smaller than a sequential scan. And the cost is presumably smaller because the index key is much smaller than the data and thus you read way less pages through the index than through scanning the data. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Ah! The wonders of the optimizer never fail to amaze me. Hadn't considered that.

It is because everything (selected columns and columns in where/group/having) are all in the index. That is quite rare! David.