We know that in Teradata all the tables are either SET or MULTISET.
The difference between SET and MULTISET tables is –
SET tables – SET tables did not allow duplicate values in the table.
e.g.
COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4
A b c d
H g y k
A b c d (Not allowed)
Syntax
CREATE SET TABLE <table_name> ………
………
MULTISET tables – MULTISET tables allow duplicate values in table.
e.g.
COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4
A b c d
H g y k
A b c d (allowed)
Syntax
CREATE MULTISET TABLE <table_name>………
………
If not specified in the DDL of the table then Teradata will create table as default SET. A SET table force Teradata to check for the duplicate rows every time a new row is inserted or updated in the table. This is an overhead on the resource if we need to insert massive amount of rows.
Which table to choose?
Before creating the table it’s very important to know what kind of data is required in the table and based on that we must define SET or MULTISET.
Remember that SET table causes an additional overhead of checking for the duplicate records. So we need to follow few points to save Teradata from this additional overhead.
- If you are using any GROUP BY or QUALIFY statement on the source table then it’s highly recommended to define target table as MULTISET. As GROUP BY and QUALIFY will remove the duplicate records from the source.
- If the source table has UPI (Unique Primary Index) then also there is no need of SET target table. As UPI will never allows duplicate PI in the same table.
So with the help of little bit of awareness about SET and MULTISET we can save a lot of time while loading the table.
Key Points to remember.
- If we are inserting data using INSERT into SEL from clause then SET table check for duplicate rows will removed automatically and there will be no DUPLICATE ROW ERROR.
- If we are inserting data using INSERT into VALUES clause then SET table check for duplicate rows will not be removed automatically and there will be DUPLICATE ROW ERROR.
10 comments
1 ping
aditya says:
April 8, 2013 at 1:39 pm (UTC 0)
What does “Merge block ratio” mean in every Teradata table definition?
Kumar says:
April 3, 2013 at 11:15 am (UTC 0)
HI Admin,
Multiset table is clear but wanted to know in which scenario we can use the SET table? For ex: i am creating a SET table with UPI. In this case, SET table itself will take care of removing duplicates or it won’t allow duplicates, then what is the need of UPI?
Please clarify.
admin says:
April 3, 2013 at 1:31 pm (UTC 0)
defining table as SET if it has UPI, make no sense.
you should use SET table only with NUPI, in case your requirement is not to include complete duplicate rows then you can use SET with NUPI
Kumar says:
April 4, 2013 at 4:24 am (UTC 0)
Thanks Admin…:)
MIKE says:
March 8, 2013 at 8:10 am (UTC 0)
Please explain Fallback option in create statement
admin says:
March 15, 2013 at 6:21 pm (UTC 0)
Fallback is a data protection feature in teradata at table level. Fallback protected tables stores the mirror image of the data of the original table. In case of failure, this fallback copy is used to replace the original table.
Dinesh Saraswat says:
February 20, 2013 at 1:23 pm (UTC 0)
Very Clear and Crisp explanation of every concept whichever explained.
deepak shrivastava says:
January 22, 2013 at 1:28 pm (UTC 0)
The content of the complete website is fantastic. It’s brief and clear. User gets a clear understanding of what it is. It’s very useful.
admin says:
January 22, 2013 at 2:51 pm (UTC 0)
Thanks
Anurag says:
November 19, 2012 at 1:04 pm (UTC 0)
This is an awesome post. Brief yet powerful. Small yet complete. I thought I knew it (SET- MULTISET) completely, but it still added to my knowledge
SET or MULTISET tables » TeraData Tech | BlinkMoth Software Industries says:
April 21, 2012 at 8:43 pm (UTC 0)
[...] Post From Teradata – Google Blog Search: set tables, multiset tables, set and multiset tables in teradata , set and multiset, multi set in [...]