composite indexes and filter
Hi, can someone explain me why a composite index can not be used for following filter when index idx(a, b ,c) is defined. WHERE a>=12 AND b=15 I refer to IBM document: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.perf.doc/perf381.htm Regards ... Joerg
J'rg R'schenschmidt said: > > Hi, > > can someone explain me why a composite index can not be used > for following filter when index idx(a, b ,c) is defined. > > WHERE a>=12 AND b=15 > > I refer to IBM document: > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.perf.doc/perf381.htm And your version is? Platform? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
Jï¿œrg Rï¿œschenschmidt wrote: > Hi, > > can someone explain me why a composite index can not be used > for following filter when index idx(a, b ,c) is defined. > > WHERE a>=12 AND b=15 > > I refer to IBM document: > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.perf.doc/perf381.htm The compound index on (a,b,c) CAN be used, but only for a >= partial key search on 'a' and an index level filter on b. Not for any direct look up of values of a & b together. What would it search for? (12,15) (13,15) ...? What it WILL do is use the index directly to find a=12, then scan every index key >= 12 and compare the 'b' portion of the key to 15 without having to fetch the data pages at all. Isn't that good enough? It's more than DB2, Oracle, MySQL, or PostGRES can do! Art S. Kagel