Feb 23

Limitations of FAST LOAD

There are few limitations which we have on Fastload.

These limitations are necessary for Fastload to load data with the lightning fast speed into tables.

  • NO SECONDARY INDEXES ARE ALLOWED ON TARGET TABLE – Fastload can load tables only with primary indexes defined on it. If we have a secondary index on the table then Fastload will not load that table. We get a error message if we load such type of table –

If secondary index exist already, we need to drop them.

After loading the table through Fastload we can easily recreate them on the table.

CREATE INDEX (Column-name/s) ON dbname.tablename —— > creates Non Unique Secondary Index


CREATE UNIQUE INDEX (Column-name/s) ON dbname.tablename —— > creates Unique Secondary Index

  • NO REFERENTIAL INTEGRITY IS ALLOWED – Fastload cannot load data into tables that are defined with Referential Integrity (RI). This would require too much system checking to prevent referential constraints to a different table
  • NO TRIGGERS ARE ALLOWED AT LOAD TIME – Fastload is much too focused on speed to pay attention to the needs of other tables, which is what Triggers are all about. Additionally, these require more than one AMP and more than one table. Fastload does one table only. Simply ALTER the Triggers to the DISABLED status prior to using Fastload.
  • DUPLICATE ROWS ARE NOT SUPPORTED – Multiset tables are a table that allow duplicate rows — that is when the values in every column are identical. When Fastload finds duplicate rows, they are discarded. While Fastload can load data into a multi-set table, Fastload will not load duplicate rows into a multi-set table because Fastload discards duplicate rows

Related Post –Teradata Utilities : FASTLOAD – Part 1


Skip to comment form

  1. ashu

    hi admin,

    I am new to teradata.
    I read that table has to be empty in fastload.
    can you tell why


  2. mini

    Hi Admin,
    I read your post and it is very informative but i have a question.
    I want to ask why table has to be empty in fastload


  3. hi

    hi ,

    can you let me know why fload doesn;t allow duplicate rows? the logic behind it? i read some where that
    FastLoad discards duplicate rows, because it doesn’t have/store any information about the input record sequence like MultiLoad’s Match Tag (ApplySeq+DMLSeq+ImportSeq+SMTSeq+SourceSeq), thus it simply doesn’t know, if a row was duplicate within the data or was sent twice because of a restarted FastLoad (in Application Phase).

    my question is if it doesn’t know about data then how it discards it?

  4. Vijay

    Why Fast load(and mload) doesn’t support SI ?

    1. admin

      As you must be knowing that SI requires a sub table for storing SI values. Now the whole purpose of using FLOAD or MLOAD is the SPEED by which we transfer files from client to host system. So this loading of subtable is a overhead for these utilities and they loose the performance edge because of this loading. That’s why SI is not supported by FLOAD and MLOAD.

      Though MLOAD supports only NUSI becuase its subtable resides in the same AMP as that of base row.
      for more on SI please refer – http://www.teradatatech.com/?p=815

  5. KrisV

    How to release a fastload lock on a table without the table being dropped and recreated.

    1. admin

      if somehow fastload fails and our table has been locked by that session –
      RDBMS error 2652: Operation not allowed: _db_._table_ is being Loaded.

      then there is 1 possible things which we can do to release fastload lock without dropping the actual table
      Run the dummy fastload script which contains only begin loading and end loading. e.g.
      logon serverid/username,password;
      begin loading ……. ;
      end loading;

  6. KrisV

    How to release a fastload lock on a table without the table being dropped and recreated?

  7. swathi


    If possible can you please explain about secondary index and usage of secondary index while primary index is present.

    1. admin

      very soon i’ll post about SI 🙂

      for now you can assume that SI is the next possible path to reach the particular row in AMP, if we cant achieve that with PI.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>