Re: Two index query optimization problem

In article <1992Dec12.000653.7004@anasaz> devin@anasaz (Devin Wala) writes: > > >There are two indexes on the table: > > index1: unique index on A, B; > index2: unique index on C, D, A, B; > >The problem I'm having is that I can't run a query with A and B mentioned >in the WHERE clause that will use index2. I suspect that the optimizer >is looking at the query and saying; hey, I got A, I got B, I've got a unique >index1 on both A and B. Look no further, I am set dudes and dudettes, and >I am outta here! (optimizers talk like that you know). > >This would be fine if my queries were of the form: > > WHERE C=value AND D=value A=value AND B=value; > >However, one of my queries is of the form: > > WHERE C=value AND D=value A>value AND B=value; > >so it doesn't _really_ have a value for A. I do need to have both >indexes for performance reasons. I don't think that setting optimization >to low is an option in this case since it effects the whole database >back end and not just the single query. We are running Online V 4.10.UF2, >SQL V 4.00.UG2 on a Pyramid MI Server. > If uou split your query unto two statements: where c=value and d=value and b=value into temp Q; select * from Q where a>value... It'll work fine, but take a little longer than you anticipate. PAX Steve Glines