Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Index Placement for PK and FK

  • 1.  Index Placement for PK and FK

    Posted Thu July 09, 2020 03:59 PM
    Running Informix 12.10.FC10 in a Linux environment.

    I've looked through a lot of documentation, but haven't found anything about index storage with this specific situation: fragmented table, different page size than database, and implicit index on PK and/or FK.

    I have something similar to the following table created in a database that defaults to a 2K dbspace, but the table is fragmented on multiple 8K dbspaces:

    CREATE TABLE my_8k_tbl
    (
    pk_col BIGSERIAL not null,
    fk_col INTEGER not null,
    ix1_col INTEGER not null,
    ix2_col datetime year to minute,
    PRIMARY KEY (pk_col) CONSTRAINT pk_col_constr
    )
    FRAGMENT BY RANGE(pk_col) INTERVAL(12345) STORE IN (8K1, 8K2)
    PARTITION p0 VALUES < 12345 IN 8K1
    EXTENT SIZE...
    ;

    CREATE INDEX ix1 ON my_8k_tbl (ix1_col) using btree;
    CREATE INDEX ix2 ON my_8k_tbl (ix2_col) using btree;

    ALTER TABLE my_8k_tbl ADD CONSTRAINT (FOREIGN KEY (fk_col) REFERENCES fk_tbl);


    The PK index and the FK index are implicitly defined and are created on the 2K space of the database, not the 8K space(s) of the table.  The ix1 and ix2 indexes are both on the 2 8K dbspaces and fragmented just as the table.

    As shown above, should the PK and FK indexes be on the 2K page or the 8K page?  Do the PK and FK indexes need to be created first to store them on the 8K dbspaces with the table?  If so, should this be an RFE to put the implicits with the table, not the db?  Or does everyone expect them to go with the DB, not the table?

    Gary Andrus



    This e-mail may contain confidential or privileged information. If you believe you have received this e-mail in error, please notify the sender by reply e-mail and then delete this e-mail immediately.


  • 2.  RE: Index Placement for PK and FK

    Posted 30 days ago

    Gary,

     

    As shown above, should the PK and FK indexes be on the 2K page or the 8K page?  Do the PK and FK indexes need to be created first to store them on the 8K dbspaces with the table?  If so, should this be an RFE to put the implicits with the table, not the db?  Or does everyone expect them to go with the DB, not the table?

     

    yup. You do make a point but indexes do get created  away from the table in many cases too.

     

    If you want both the PK & FK indexes in the 8K dbspace then  you need to first create the indexes explicitly  in that dbspace as you stated.  Afterwards you define your PK & FK  and since these are constraints they will "share" the index  AFAIK.  

     

    Mark

     






  • 3.  RE: Index Placement for PK and FK

    Posted 30 days ago
    The indices will be named automagically and have a leading space. Later engines allow you to rename them, once renamed you can move them to where ever you want

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 4.  RE: Index Placement for PK and FK

    Posted 30 days ago

    You can rename them even in older engine versions.

    DELIMIDENT is your friend here. ;)

    export DELIMIDENT=1
    dbaccess mydb << EOF
    RENAME INDEX " 123_456" TO ix__mytable__col1__col2;
    ALTER FRAGMENT ON INDEX ix__mytable__col1__col2 INIT IN my8kdbsp;
    EOF


    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 5.  RE: Index Placement for PK and FK

    Posted 30 days ago

    Don't think that always works, or at least it hasn't in my experience

     

    [Best pic I have seen of you in many years :-P ]

     






  • 6.  RE: Index Placement for PK and FK

    Posted 30 days ago
    Hello Gary,
    As you mentioned, it seems that the internal index created by the primary key or foreign key is stored in the database's primary dbspace.
    I think it is better to create a pk or fk index by specifying the dbspace of the desired pagesize, and specify the index name in the constraint.
    -- creating table, default database dbspace is 'datadbs' (pagesize:2kb)
    > CREATE TABLE my_8k_tbl
    > (
    > pk_col BIGSERIAL not null,
    > fk_col INTEGER not null,
    > ix1_col INTEGER not null,
    > ix2_col datetime year to minute,
    > PRIMARY KEY (pk_col) CONSTRAINT pk_col_constr
    > )
    > FRAGMENT BY RANGE(pk_col) INTERVAL(12345) STORE IN (DBS8K1, DBS8K2)
    > PARTITION p0 VALUES < 12345 IN DBS8K1
    > ;
    
    Table created.
    
    -- Checking pagesize of the table
    > select b.name dbsname, b.pagesize, a.tabname
    > from sysmaster:systabnames a, sysmaster:sysdbspaces b
    > where sysmaster:partdbsnum(a.partnum) = b.dbsnum and a.tabname='my_8k_tbl';
    
    
    
    dbsname   dbs8k1
    pagesize  8192
    tabname   my_8k_tbl
    
    1 row(s) retrieved.
    
    
    -- Checking pagesize of the index created by the primary key
    > select b.name dbsname, b.pagesize, a.tabname
    > from sysmaster:systabnames a, sysmaster:sysdbspaces b
    > where sysmaster:partdbsnum(a.partnum) = b.dbsnum
    > and a.tabname in (select idxname from sysconstraints where constrname='pk_col_constr');
    
    
    
    dbsname   datadbs
    pagesize  2048
    tabname    116_24
    
    1 row(s) retrieved.
    ​


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



  • 7.  RE: Index Placement for PK and FK

    Posted 30 days ago
    Thanks to all for the replies.  Confirmed what I suspected.

    ------------------------------
    Gary Andrus
    ------------------------------



  • 8.  RE: Index Placement for PK and FK

    Posted 30 days ago

    Hi Gary,

    I'd normally start with what the manual says. However the Informix manual doesn't seem to have much to say on about where any implicit indices go. I can only find:

    "You can specify a dbspace for the table that is different from the storage location for the database, or fragment the table among dbspaces, or among named fragments in one or more dbspaces.
    "If you specify no IN clause nor fragmentation scheme, the new table is stored in the same dbspace where the current database is stored."

    Since it's not explicitly documented what the behaviour should be for related indices it is difficult to raise it as a bug. (Unless you can find a description elsewhere in the manual that I have missed.)

    Personally I avoid this inline syntax and have gone as far to write scripts to manipulate schema files to remove it for primary keys and foreign keys for the reason you have found. I tend to think this syntax is there for compatibility; it certainly reduces your level of control and on large busy systems this kind of thing starts to become very important.

    Lurking in the background is the problem that a unique index of any kind, i.e. primary key or unique constraint, cannot follow the table schema unless the lead key is the same as the range column. While your case seems to have a sensible solution, if you were to add "UNIQUE (fk_col)" to your CREATE TABLE statement this index would have to be detached. Where does the index get placed then? It is not clear to me that it should go in any of the dbspaces listed for the intervals or partition "p0". So it goes in the default dbspace.

    I support the idea behind an RFE but I think it would need to cover the two cases being discussed:

    1. A unique index or PK which has a lead key the same as the range column.
    2. A unique index or PK which does not.

    Ben.

    ------------------------------
    Benjamin Thompson
    ------------------------------