Informix

nested-group-icon.png

DB2

Expand all | Collapse all

How to find functional index in sysindexes?

  • 1.  How to find functional index in sysindexes?

    Posted 13 days ago
    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
    ------------------------------


  • 2.  RE: How to find functional index in sysindexes?

    Posted 13 days ago
    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
    ------------------------------



  • 3.  RE: How to find functional index in sysindexes?

    Posted 13 days ago
    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


    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.








  • 4.  RE: How to find functional index in sysindexes?

    Posted 12 days ago
    Wow, it's amazing that there is a way to check like this. Thank you.​

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 5.  RE: How to find functional index in sysindexes?

    Posted 12 days ago
    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





  • 6.  RE: How to find functional index in sysindexes?

    Posted 12 days ago
    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





  • 7.  RE: How to find functional index in sysindexes?

    Posted 12 days ago
    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
    ------------------------------



  • 8.  RE: How to find functional index in sysindexes?

    Posted 12 days ago
    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





  • 9.  RE: How to find functional index in sysindexes?

    Posted 12 days ago
    @Paul Watson
    Thanks for the advice!​

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------