Join Index | Object File System Tables | VantageCloud Lake - Join Index - Teradata Vantage

Neither single table join index (STJI) or multi table join index can be built in object storage; that is, the table-level STORAGE option is not supported for these indexes. However, STJI can be built on Object File System (OFS) tables if the index structure is supported on the primary cluster. This is not the case for a multi table join index. Do not create an STJI or a multi table join index on tables accessed using Native Object Store.

Single-table join indexes (STJI) can be created on an Object File System table. There is no new syntax for this. STJI is created in Block Storage (TD_NDSSTORAGE). You need to manually perform garbage collection periodically on STJIs created on Object File System tables, to clear the historical data.

Additionally, if the STJI has ROWID of the base table projected, it can be used as an access path (NUSI path) to support tactical queries on Object File System tables.

For more detailed information, see the Orange Book Using VantageCloud™ Lake Architecture - A Practical Guide on https://docs.teradata.com/. You must sign in to access the Orange Books.

Here are some examples showing the usage of STJI.

SHOW TABLE fs_tbl;

Result:

CREATE MULTISET TABLE OFS_USER.fs_tbl , NO FALLBACK ,
     STORAGE = NOS_STORAGE
     (
      StoreNo INTEGER,
      OrderTotal INTEGER,
      StoreName VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      OrderDate DATE FORMAT 'YY/MM/DD')
NO PRIMARY INDEX ;

• STJI can be created as a TD row table with PI.

  CREATE JOIN INDEX jiofs AS
  SELECT  storeno, ordertotal
  FROM fs_tbl;

  SHOW JOIN INDEX jiofs;

  Result:

  CREATE JOIN INDEX OFS_USER.jiofs ,FALLBACK ,CHECKSUM = DEFAULT, AS
  SELECT OFS_USER.fs_tbl.StoreNo ,OFS_USER.fs_tbl.OrderTotal
  FROM OFS_USER.fs_tbl
  PRIMARY INDEX ( StoreNo );

• STJI can be used if it fully covers a query.
  • If the query has NO conditions:

    EXPLAIN SELECT storeno, ordertotal FROM fs_tbl fs1;

    *** Help information returned. 16 rows.
    *** Total elapsed time was 1 second.
    
    Explanation
    ---------------------------------------------------------------------------
      1) First, we lock ofs_user.JIOFS in TD_MAP1 for access on a
         reserved RowHash to prevent global deadlock.
      2) Next, we lock ofs_user.JIOFS in TD_MAP1 for access.
      3) We do an all-AMPs RETRIEVE step in TD_MAP1 from ofs_user.JIOFS
         (Load Committed) by way of an all-rows scan with a condition of (
         "((ofs_user.JIOFS.TD_ROWLOADID_INS <= (:ReadLoadID1 )) AND
         ((ofs_user.JIOFS.TD_ROWLOADID_DEL = 0) OR
         (ofs_user.JIOFS.TD_ROWLOADID_DEL > (:ReadLoadID1 )))) AND
         ((1=1))") into Spool 1 (group_amps), which is built locally on the
         AMPs.  The size of Spool 1 is estimated with high confidence to be
         16 rows (688 bytes).  The estimated time for this step is 0.02
         seconds.
      4) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 1 are sent back to the user as the result of
         statement 1.  The total estimated time is 0.02 seconds.
    

    SELECT storeno, ordertotal FROM fs_tbl fs1;

    *** Query completed. 10 rows found. 2 columns returned.
    *** Total elapsed time was 1 second.
    
        StoreNo   OrderTotal
    -----------  -----------
           8888          800
           4444          400
           9999          900
           5555          500
           2222          200
           6666          600
           1111          100
           3333          300
           7777          700
          10000         1000

  • If the query has conditions:

    EXPLAIN SELECT storeno, ordertotal FROM fs_tbl fs1 WHERE ordertotal >= 500;

    *** Help information returned. 16 rows.
    *** Total elapsed time was 1 second.
    
    Explanation
    ---------------------------------------------------------------------------
      1) First, we lock ofs_user.JIOFS in TD_MAP1 for access on a
         reserved RowHash to prevent global deadlock.
      2) Next, we lock ofs_user.JIOFS in TD_MAP1 for access.
      3) We do an all-AMPs RETRIEVE step in TD_MAP1 from ofs_user.JIOFS
         (Load Committed) by way of an all-rows scan with a condition of (
         "((ofs_user.JIOFS.TD_ROWLOADID_INS <= (:ReadLoadID1 )) AND
         ((ofs_user.JIOFS.TD_ROWLOADID_DEL = 0) OR
         (ofs_user.JIOFS.TD_ROWLOADID_DEL > (:ReadLoadID1 )))) AND
         (ofs_user.JIOFS.OrderTotal >= 500)") into Spool 1 (group_amps),
         which is built locally on the AMPs.  The size of Spool 1 is
         estimated with high confidence to be 12 rows (516 bytes).  The
         estimated time for this step is 0.02 seconds.
      4) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 1 are sent back to the user as the result of
         statement 1.  The total estimated time is 0.02 seconds.
    
    sel storeno, ordertotal from fs_tbl fs1 where ordertotal >= 500;
    
    *** Query completed. 6 rows found. 2 columns returned.
    *** Total elapsed time was 1 second.
    
        StoreNo   OrderTotal
    -----------  -----------
           8888          800
           7777          700
           9999          900
           5555          500
           6666          600
          10000         1000

• STJI cannot be used if it does not fully cover a query.

  EXPLAIN SELECT storeno, ordertotal, orderdate
  FROM fs_tbl fs1;

  *** Help information returned. 27 rows.
  *** Total elapsed time was 1 second.
  
  Explanation
  ---------------------------------------------------------------------------
    1) First, we lock OFS_USER.fs1 in TD_MAP1 for access on a reserved
       RowHash to prevent global deadlock.
    2) Next, we lock OFS_USER.fs1 in TD_MAP1 for access.
    3) We do a single-AMP RETRIEVE step from root metadata of
       OFS_USER.fs1 by way of an all-rows scan with no residual
       conditions into Spool 4 (all_amps), which is redistributed by the
       hash code of (OFS_USER.fs1.Field_1028) to all AMPs in TD_Map1.
       The size of Spool 4 is estimated with high confidence to be 1 row
       (117 bytes).  The estimated time for this step is 0.26 seconds.
    4) We do an all-AMPs RETRIEVE step in TD_MAP1 from leaf metadata of
       OFS_USER.fs1 by way of external metadata in Spool 4 (Last Use)
       with no residual conditions into Spool 5 (all_amps), which is
       redistributed by the rowkey of (OFS_USER.fs1.Field_1028) to all
       AMPs in TD_Map1.  Then we do a SORT to order Spool 5 by the sort
       key as the field_id list( 4).  The size of Spool 5 is estimated
       with high confidence to be 6 rows (702 bytes).  The estimated time
       for this step is 0.27 seconds.
    5) We do an all-AMPs RETRIEVE step in TD_MAP1 from OFS_USER.fs1
       by way of external metadata in Spool 5 (Last Use) with no residual
       conditions into Spool 1 (group_amps), which is built locally on
       the AMPs.  The size of Spool 1 is estimated with high confidence
       to be 16 rows (816 bytes).  The estimated time for this step is
       0.02 seconds.
    6) Finally, we send out an END TRANSACTION step to all AMPs involved
       in processing the request.
    -> The contents of Spool 1 are sent back to the user as the result of
       statement 1.  The total estimated time is 0.54 seconds.