Functional indexes - experiences?
Poster built a functional index (an SPL 'not variant' function wrapping UPPER) on a 1.7M-row table to allow case-insensitive searches, but queries stayed slow even with optimizer directives. He answered himself: the WHERE clause must call the function (e.g. WHERE fi_test(npname_name) MATCHES ...) for the index to be used, after which it worked well. A follow-up raised a second issue: creating such indexes is very slow (11 hours for 8M rows with a substr-wrapping SPL function); Art Kagel suggested writing the index function in C, but no further results were posted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all
another (possibly dam' fool) question:
We have a clients table in one of our db's, containing
1.7M rows.
Programming vagaries mean that one of the fields
(which before was never used for searching, only
display) contains mixed-case, but of course now
there's a requirement to query by this field.
We can obviously run an sql to upshift the data in the
table and rewrite the programs which insert to it to
force upper case thus making searches quicker, but I
thought 'how about I force a functional index on this
field', so I did this:
create function "dba".fi_test(fv_1 char(60)) returns
char(60)with (not variant);
define fv_2 char(60);
begin
let fv_2 = upper(fv_1);
end
return fv_2;
end function;
create index uc_npname onclientmain(fi_test(npname_name));
OK, this built ok and I updated stats for the table,
index and function but even using optimizer directives
to force the index the query is s-l-o-w (even against
a value where there are only a half-dozen entries).
Can anyone point out if I'm doing something wrong
here?
(An absolute value match query is instantaneous).
More info on request.
Malc
's
OK, I;ve sussed it.
The query (of course, doh!) has to include a call to
the function, as in
SELECT npname_name from clientmain
where fi_test(npname_name) matches .....^^^^^^^^
... and it works a treat!
---- Från WASTESON, ROLF +46 8 738 47 68 INF300 2004-12-
We have used functional indexes to get better
performance when searching columns that need different
types of manipulations (substring, cast....). It works
fine. But! When we recreate the index it takes a
whole lot of time.
On a rather slow Sun 4500 creating a functional index
combining a substring and a cast to date took 11 hours
for 8 million rows. It seems as if the call of the
fuction for each row takes a lot of resources.
Unfortunately built in functions like substr or upper
are not possible to use directly in the index expression
but need to be wrapped in a function.
Does anyone have an idea how to make it more efficient?
Regards,
Rolf Wasteson
InfoData AB, Stockholm, Sweden
********************************************************
From: malc_p@btinternet.com
To: ids@iiug.org
Date: Fri, 26 Nov 2004 06:42:34 -0500
Subject: Re: Functional indexes - experiences? [3787]
Message-Id: <200411261142.iAQBgY1i008132@ace.iiug.org>
's OK, I;ve sussed it.
The query (of course, doh!) has to include a call to
the function, as in
SELECT npname_name from clientmain
where fi_test(npname_name) matches .....^^^^^^^^
.. and it works a treat!
---- 04-12-05 21.19 ---- Sänt till
-> ids@iiug.org
Write the function to be used by the index in 'C'.
Art S. Kagel
----- Original Message -----
From: Rolf.Wastes.... <rolf.wasteson@infodata.se>
At: 12/ 5 15:38
> ---- Från WASTESON, ROLF +46 8 738 47 68 INF300 2004-12-
>
> We have used functional indexes to get better
> performance when searching columns that need different
> types of manipulations (substring, cast....). It works
> fine. But! When we recreate the index it takes a
> whole lot of time.
>
> On a rather slow Sun 4500 creating a functional index
> combining a substring and a cast to date took 11 hours
> for 8 million rows. It seems as if the call of the
> fuction for each row takes a lot of resources.
> Unfortunately built in functions like substr or upper
> are not possible to use directly in the index expression
> but need to be wrapped in a function.
>
> Does anyone have an idea how to make it more efficient?
>
> Regards,
> Rolf Wasteson
> InfoData AB, Stockholm, Sweden
>
> ********************************************************
>
>
> From: malc_p@btinternet.com
> To: ids@iiug.org
> Date: Fri, 26 Nov 2004 06:42:34 -0500
> Subject: Re: Functional indexes - experiences? [3787]
> Message-Id: <200411261142.iAQBgY1i008132@ace.iiug.org>
>
> 's OK, I;ve sussed it.
> The query (of course, doh!) has to include a call to
> the function, as in
> SELECT npname_name from clientmain
> where fi_test(npname_name) matches .....> ^^^^^^^^
> . and it works a treat!
>
>
>
> ---- 04-12-05 21.19 ---- Sänt till
> -> ids@iiug.org
Could you provide a script wich you created your functional index?
Chucho!
-----Original Message-----
From: "rolf.wastes...." <rolf.wasteson@infodata.se>
To: ids@iiug.org
Date: Sun, 5 Dec 2004 15:25:18 -0500 (EST)
Subject: Re: Functional indexes - experiences? [3844]
---- Från WASTESON, ROLF +46 8 738 47 68 INF300 2004-12-
We have used functional indexes to get better
performance when searching columns that need different
types of manipulations (substring, cast....). It works
fine. But! When we recreate the index it takes a
whole lot of time.
On a rather slow Sun 4500 creating a functional index
combining a substring and a cast to date took 11 hours
for 8 million rows. It seems as if the call of the
fuction for each row takes a lot of resources.
Unfortunately built in functions like substr or upper
are not possible to use directly in the index expression
but need to be wrapped in a function.
Does anyone have an idea how to make it more efficient?
Regards,
Rolf Wasteson
InfoData AB, Stockholm, Sweden
********************************************************
From: malc_p@btinternet.com
To: ids@iiug.org
Date: Fri, 26 Nov 2004 06:42:34 -0500
Subject: Re: Functional indexes - experiences? [3787]
Message-Id: <200411261142.iAQBgY1i008132@ace.iiug.org>
's OK, I;ve sussed it.
The query (of course, doh!) has to include a call to
the function, as in
SELECT npname_name from clientmain
where fi_test(npname_name) matches .....^^^^^^^^
.. and it works a treat!
---- 04-12-05 21.19 ---- Sänt till
-> ids@iiug.org
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
---- Från WASTESON, ROLF +46 8 738 47 68 INF300
2004-12-07 18:13:00+0100
The index defintion is
create index tkyrkoperson_persnrkort on tkyrkoperson (persnrkort(persnr));The column persnr is char(12). There is also an ordinary index on column
persnr. Update
statistics was not run on the table and its index before creation of the
functional index
(but that should not make any difference). The table is fragmented by round
robin to four
dbspaces.
The function persnrkort looks like:
create function persnrkort(persnr char(12))
returning char(10) with (not variant);return substr(persnr,3);
end function;
This is just one example, we have had the same experience with similar
functional indexes.
Regards,
Rolf
******************************************************************
From: jeansagi@myrealbox.com
To: rolf.wasteson@infodata.se
Date: Mon, 6 Dec 2004 13:25:04 -0500
Cc: ids@iiug.org
Subject: Re: Re: Functional indexes - experiences? [3844]
Content-Transfer-Encoding: Quoted-Printable
Message-Id: <1102357504.8c72425cjeansagi@myrealbox.com>
Content-Type: text/plain; charset = "CP1252"
---------------------------------------------------
Note: This message was written with the unsupported
character set 'CP1252'. Some characters may have
been substituted.
---------------------------------------------------
Could you provide a script wich you created your functional index?
Chucho!
-----Original Message-----
From: "rolf.wastes...." <rolf.wasteson@infodata.se>
To: ids@iiug.org
Date: Sun, 5 Dec 2004 15:25:18 -0500 (EST)
Subject: Re: Functional indexes - experiences? [3844]
---- Fr¿n WASTESON, ROLF +46 8 738 47 68 INF300 2004-12-
We have used functional indexes to get better
performance when searching columns that need different
types of manipulations (substring, cast....). It works
fine. But! When we recreate the index it takes a
whole lot of time.
On a rather slow Sun 4500 creating a functional index
combining a substring and a cast to date took 11 hours
for 8 million rows. It seems as if the call of the
fuction for each row takes a lot of resources.
Unfortunately built in functions like substr or upper
are not possible to use directly in the index expression
but need to be wrapped in a function.
Does anyone have an idea how to make it more efficient?
Regards,
Rolf Wasteson
InfoData AB, Stockholm, Sweden
********************************************************
From: malc_p@btinternet.com
To: ids@iiug.org
Date: Fri, 26 Nov 2004 06:42:34 -0500
Subject: Re: Functional indexes - experiences? [3787]
Message-Id: <200411261142.iAQBgY1i008132@ace.iiug.org>
's OK, I;ve sussed it.
The query (of course, doh!) has to include a call to
the function, as in
SELECT npname_name from clientmain
where fi_test(npname_name) matches .....^^^^^^^^
.. and it works a treat!
---- 04-12-05 21.19 ---- S¿nt till
-> ids@iiug.org
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
---- 04-12-07 18.13 ---- Sänt till
-> IDS@IIUG.ORG