• Structure vs Index

    From nikosv@21:1/5 to All on Thu Nov 24 01:55:24 2022
    When you set a PK in creating a table,a secondary index is created on the key.The table is heap with a secondary btree index by default.

    When you don't set a PK at the table's creation time and instead modify
    the table's structure to btree unique on the key afterwards, it acts like a PK constraint in not allowing duplicates.

    However how is this enforced ? I searched through the catalogue like iiindexes, iiconstraints etc and I can't find anything.Also, in that case when doing a select straight on the key does it use the structure to retrieve the rows? Does it mean that I don't need to manually assign an index on the table's key and can just rely on the
    structure?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Roy Hann@21:1/5 to nikosv on Mon Nov 28 09:46:11 2022
    nikosv wrote:

    When you set a PK in creating a table,a secondary index is created on
    the key.The table is heap with a secondary btree index by default.

    That is true, but for performance reasons you may want to have a unique physical key using the same column(s) and then, knowing you have it, and
    also for performance reasons, you might declare the PK using INDEX =
    BASE TABLE STRUCTURE to avoid creating a redundant secondary index.

    When you don't set a PK at the table's creation time and instead modify
    the table's structure to btree unique on the key afterwards, it acts
    like a PK constraint in not allowing duplicates.

    That is true, but it is only a coincidence that can arise when the
    efficient physical key happens to also be your primary logical key.
    Admittedly that will often be the case so it is easy to overlook the
    fact that the physical and logical keys are different things doing
    different jobs. The physical key is locating the storage space where a
    row physically resides, and clustering associated rows on a page. The
    primary key is uniquely distinguishing a fact so you can confidently
    refer to it by value.


    However how is this enforced ? I searched through the catalogue like iiindexes, iiconstraints etc and I can't find anything.

    The secondary index that gets created is physically organized as a
    unique btree by default. When you insert a row with a duplicate primary
    key you get a conflict on the secondary index. When you rely on a
    unique physical key on the base table the same thing happens but on the
    base table.

    Also, in that case when doing a select straight on the key does it use the structure
    to retrieve the rows? Does it mean that I don't need to manually assign
    an index on the table's key and can just rely on the structure?

    Yes. But whether it is best to exploit the secondary index that
    supports the constraint or to enforce the constraint using the base
    table structure is a decision you might want to think about. It's not irreversible so you can try both. These days it would have to be a
    pretty extreme table or a pretty demanding application before it makes
    enough difference to fret about it. I think I'd do whatever is easiest
    and ignore what goes on under the covers until it proves it's a problem
    worth spending time solving. (┤Premature optimization is the root of
    all evilí, to quote Knuth.)

    Roy

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From G Jones@21:1/5 to All on Mon Nov 28 03:23:45 2022
    However how is this enforced ? I searched through the catalogue like iiindexes, iiconstraints etc and I can't find anything.

    See iikey_columns.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From nikosv@21:1/5 to All on Mon Nov 28 04:51:36 2022
    Hi Roy,
    thanks for replying. I'll take your points into consideration in trying to find the optimal solution.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)