Mike
>J holding the next integer that T has for S
You mean for each i, the next value of i with that s?
>(U having no row for the last integer of each string).
I do not understand that at all.
PB
Mike Spreitzer wrote:
Suppose I have a table T with two column, S holding strings (say,
VARCHAR(200)) and I holding integers. No row appears twice. A given
string appears many times, on average about 100 times. Suppose I have
millions of rows. I want to make a table U holding those same columns
plus one more, J holding the next integer that T has for S (U having no
row for the last integer of each string). I could index T on (S,I) and
write this query as
select t1.*, t2.I as J from T as t1, T as t2
where t1.S=t2.S and t1.I < t2.I
and not exists (select * from T as t12 where t12.S=t1.S and t1.I < t12.I
and t12.I < t2.I)
but the query planner says this is quite expensive to run: it will
enumerate all of T as t1, do a nested enumeration of all t2's entries for
S=t1.S, and inside that do a further nested enumeration of t12's entries
for S=t1.S --- costing about 10,000 times the size of T. There has to be
a better way!
Thanks,
Mike Spreitzer
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.364 / Virus Database: 270.12.80/2187 - Release Date: 06/19/09 06:53:00
