How to find functional index in sysindexes?
SangGyu Jeong asked how to find functional indexes in sysindexes, using part1 = 0. Art Kagel said it works but missed some cases and suggested matching the indexkeys column in sysindices against '*<[0-9]*>*', cast to LVARCHAR or CHAR(1024) because it is a UDT. Paul Watson suggested the same query and Art's myschema code. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello Community,
Below is the process I tested after receiving questions from customers about how to find the functional index.
Is this the right way?
---- test step
$ dbaccess stores_demo -Database selected.
> CREATE TABLE circles ( radius FLOAT );Table created.
> CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float
WITH (NOT VARIANT);> >
> RETURN 3.14159 * radius * radius;
END FUNCTION;>
Routine created.
> CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );Index created.
>
>
>
>
select owner, idxname from sysindexes where tabid > 99 and idxname[1,1] <> ' ' and part1 = 0;owner informix
idxname cust_idx1
owner informix
idxname ts_data_location_spix
owner informix
idxname areaofcircleindex
3 row(s) retrieved.
>
Thanks for reading.
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
#Informix
That should work, but this may be more reliable:
select idxname from sysindices where indexkeys::lvarchar matches '*<[0-9]*>*';Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Oops. Your version may miss indexes that combine an initial column key with a function result also in the key following that. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Wow, it's amazing that there is a way to check like this. Thank you. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------
Look in sysindices and indexkeys. Something like ....
select idxname
from sysindices
where indexkeys matches '*<[0-9]*>*'Cheers
Paul
Paul Watson
Oninit LLC
+1-913-387-7529
www.oninit.com
Oninit® is a registered trademark of Oninit LLC
FYI - the easiest way to find any sql that answers schema questions is to just look at Art's myschema code - the answer will be in their somewhere Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
Great minds Paul. Note that you have to explicitely cast the indexkeys column to LVARCHAR or CHAR(1024) in order to compare it to a string. It is defined as a UDT, indexkeyarray(1024), that doesn't have an implicit cast to any string type defined. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Yep - was in a rush and didn't have the sysindices schema handy Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
Thanks for the advice! ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------