1. Forum
  2. USENET
  3. comp.databases.ingres

  • Files underlying partitioned tables

    From Roy Hann@specially@processed.almost.meat to comp.databases.ingres on Mon Feb 15 13:11:35 2021
    From Newsgroup: comp.databases.ingres

    I can locate the files belonging to table_type = 'P' tables using
    iifile_info in the usual way. The ingredient I am missing is a
    convenient way to relate a logical (table_type = 'T') table to its P
    tables.

    Anyone able to assist?

    Roy
    --- Synchronet 3.21b-Linux NewsLink 1.2
  • From Martin Bowes@martin.bowes@ndph.ox.ac.uk to Roy Hann (roy.hann@rationalcommerce.com) on Mon Feb 15 13:44:57 2021
    From Newsgroup: comp.databases.ingres

    Is this what you want?
    select varchar(t1.table_name, 32) as partition, varchar(t2.table_name, 32) as parent
    from iitables t1 join iitables t2 on t1.table_reltid = t2.table_reltid and t2.table_reltidx = 0
    where t1.table_type = 'P'
    Marty
    -----Original Message-----
    From: Roy Hann <specially@processed.almost.meat>
    Sent: 15 February 2021 13:12
    To: info-ingres@lists.planetingres.org
    Subject: [Info-ingres] Files underlying partitioned tables
    I can locate the files belonging to table_type = 'P' tables using iifile_info in the usual way. The ingredient I am missing is a convenient way to relate a logical (table_type = 'T') table to its P tables.
    Anyone able to assist?
    Roy
    _______________________________________________
    Info-ingres mailing list
    Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres
    --- Synchronet 3.21b-Linux NewsLink 1.2
  • From Karl Schendel@schendel@kbcomputer.com to Roy Hann (roy.hann@rationalcommerce.com) on Mon Feb 15 10:09:34 2021
    From Newsgroup: comp.databases.ingres


    On Feb 15, 2021, at 8:44 AM, Martin Bowes <martin.bowes@ndph.ox.ac.uk> wrote:

    Is this what you want?

    select varchar(t1.table_name, 32) as partition, varchar(t2.table_name, 32) as parent
    from iitables t1 join iitables t2 on t1.table_reltid = t2.table_reltid and t2.table_reltidx = 0
    where t1.table_type = 'P'
    What Marty said.
    Just to elaborate a bit, every table is identified by a unique number. For ordinary
    tables, views and partitioned masters, the number is in iirelation.reltid, and the
    reltidx value is zero. Secondary indexes put the number in iirelation.reltidx and reltid is the table ID of the base table. Physical partitions also put the number in iirelation.reltidx, except that the sign bit is set (note: not negated!
    just the sign bit), and reltid is the table ID of the partitioned master.
    So, reltid is the table or base table or partitioned master. reltidx is zero or the
    index table ID or the physical partition table ID, the latter with the sign bit set.
    Karl
    -----Original Message-----
    From: Roy Hann <specially@processed.almost.meat>
    Sent: 15 February 2021 13:12
    To: info-ingres@lists.planetingres.org
    Subject: [Info-ingres] Files underlying partitioned tables

    I can locate the files belonging to table_type = 'P' tables using iifile_info in the usual way. The ingredient I am missing is a convenient way to relate a logical (table_type = 'T') table to its P tables.

    Anyone able to assist?

    Roy
    _______________________________________________
    Info-ingres mailing list
    Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres _______________________________________________
    Info-ingres mailing list
    Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres
    --- Synchronet 3.21b-Linux NewsLink 1.2
  • From Roy Hann@specially@processed.almost.meat to comp.databases.ingres on Mon Feb 15 15:30:46 2021
    From Newsgroup: comp.databases.ingres

    Martin Bowes wrote:

    Is this what you want?

    select varchar(t1.table_name, 32) as partition, varchar(t2.table_name, 32) as parent
    from iitables t1 join iitables t2 on t1.table_reltid = t2.table_reltid and t2.table_reltidx = 0
    where t1.table_type = 'P'

    That's the one! :-)

    Roy

    --- Synchronet 3.21b-Linux NewsLink 1.2