Subject Re: [firebird-support] Re: ORDER BY on large VARCHAR columns
Author Ann W. Harrison
At 12:52 PM 10/28/2004, robert_difalco wrote:
> >
> > Creating a floating point column that's used just for sorting that
> > varchar.
>Cool, if you check out my first post on this subject, that was my
>first guess; save that I was thinking an int64 instead of a float.

The nice thing about floating point is that there's lots of room
between any two existing values... not infinite, but quite a bit.

>My only issue is wondering how long an INSERT could take if I have
>10,000,000 records (this is really the upper limit of how large this
>table may grow).

As I said, the crude approach will take quite a while. If you keep
a histogram table of representative varchars and numbers to limit the
number of records you need to sort, you can control how long inserts
take by adjusting the granularity of the histogram table.

>I also wonder if with this FLOAT or INT64 column, if I could do a
>binary search to find the new INSERT position. Just not exactly sure
>how to do that (i.e. look at previous records from my first SELECT).

Probably, though SQL is much better at finding a group and searching
within a group than bouncing back and forth.