At 01:43 AM 27/10/2004 +0000, you wrote:

><unordained_00@c...> wrote:
> > ... they can't be chopped up -and- indexing just the first 250 or
>so chars wouldn't at least help
> > somewhat? in theory it would reduce by orders of magnitude how
>many comparisons would be needed,
> > even if the index couldn't do the entire sort by itself ... or,
>heck, four or five varchar fields,
> > using substring? doesn't need to be hierarchical, you've got a set
>limit on the length ...
> >
>
>Well, I have a UDF to perform regex searches on this column, so I
>can't really split them up as I have no idea where the wildcards may
>be in the search pattern. It's just a very problematic field.

Ach!! Let's take a Windows file path as an example.

create table blah (
id integer not null primary key,
....
drive varchar(50),
level1 varchar(50),
level2 varchar(50),
level3 varchar(50),
level4 varchar(820),
Full_path varchar(1024));

create trigger ba_blah for blah
active before insert or update
as
declare NeedToNull char = 'T';
begin
if (new.drive is not null) then
begin
new.Full_path = new.drive;
NeedToNull := 'F';
end
if (new.level1 is not null) then
begin
if (NeedToNull = 'T') then
begin
new.Full_path = new.level1;
NeedToNull = 'F';
end
else
new.Full_path = new.Full_path || '\' || new.level1;
if (new.level2 is not null) then
begin
if (NeedToNull = 'T') then
begin
new.Full_path = new.level2;
NeedToNull = 'F';
end
else
new.Full_path = new.Full_path || '\' || new.level2;
if (new.level3 is not null) then
begin
if (NeedToNull = 'T') then
begin
new.Full_path = new.level3;
NeedToNull = 'F';
end
else
new.Full_path = new.Full_path || '\' || new.level3;
if (new.level4 is not null) then
begin
if (NeedToNull = 'T') then
begin
new.Full_path = new.level4;
NeedToNull = 'F';
end
else
new.Full_path = new.Full_path || '\' || new.level4;
if (NeedToNull = 'T') then
new.Full_path = null;
end

>Also, there is a requirement that they be displayed -- fully
>qualified, sorted, and paged (i.e. 15-20 at a time).

NP. If they are broken apart, you can put an index on each level except
the last (the one that potentially contains the big string).

>Can Firebird use a partial (i.e. substring) INDEX and have that
>index help it sort this table? I didn't think so, but maybe I'm not
>sure what you mean by "indexing just the first 250 or so chars".

An index can't be larger than 253 bytes. In many character sets it is
less, in some cases a LOT less. Also, multi-segment indexes eat even more
out of those 253 bytes.

./hb