What are the advantages and dis-advantages of secondary Indexes in Teradata?...


What are the advantages and dis-advantages of secondary Indexes?
Answer:

Advantages:
1. A secondary index might be created and dropped dynamically
2.A table may have up to 32 secondary indexes.
3. Secondary index can be created on any column. .Either Unique or Non-Unique
4. It is used as alternate path or Least frequently used cases.  ex. defining SI on non indexed column can improve the performance, if it is used in  join or filter condition of a given query.
5. Collecting Statistics on SI columns make sure Optimizer choses SI if it is better than doing Full Table Scans

Disadvantages
1. Since Sub tables are to be created, there is always an overhead for additional spaces.
2. They require additional I/Os to maintain their sub tables.
3. The Optimizer may, or may not, use a NUSI, depending on its selectivity.
4. If the base table is Fallback, the secondary index sub table is Fallback as well.
5. If statistics are not collected accordingly, then the optimizer would go for Full Table Scan.


Referred: Teradatainterview portal.

Comments

Post a Comment

Popular posts from this blog

Hadoop Commands

hadoop archive The  hadoop archive  command creates a Hadoop archive, a file that contains other files. A Hadoop archive always has a  *.har  extension. hadoop classpath The  hadoop classpath  command prints the class path needed to access the Hadoop jar and the required libraries. hadoop daemonlog The  hadoop daemonlog  command gets and sets the log level for each daemon. hadoop distcp The  hadoop distcp  command is a tool used for large inter- and intra-cluster copying. hadoop fs The  hadoop fs  command runs a generic file system user client that interacts with the MapR file system. hadoop jar The  hadoop jar  command runs a program contained in a JAR file. Users can bundle their MapReduce code in a JAR file and execute it using this command. hadoop job The  hadoop job  command enables you to manage MapReduce jobs. hadoop mfs The  hadoop mfs  command displays directory inf...
Read more

Database Size : Calculation in Teradata

SELECT DatabaseNameI, PermSpace, SpoolSpace, TempSpace,CreateTimeStamp FROM DBC.DBase WHERE DatabaseNameI = '[databasename]'; Permanent Space: Permanent space is the maximum amount of space allocated to the user/database to hold data rows. Perm space is used for database object (permanent tables, indexes etc) creation and to hold their data. The amount of permanent space is divided among the number of AMPs.Whenever per AMP limit exceeds the allocated space of AMP, 'No more room in database' error message is generated. Spool Space: Spool space is the unused permanent space which is used by the system to keep the intermediate results of the SQL query. Users without spool space cannot execute any query. Data is active up to the current session only. Spool space is divided among the number of AMPs. Whenever per AMP limit exceeds the allocated space, the user will get a spool space error. Temporary Space: Temporary space is the unused permanent space whic...
Read more

The difference between SET and MULTISET tables is?

The difference between SET and MULTISET tables is? SET tables  – SET tables did not allow duplicate values in the table. 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. MULTISET tables  – MULTISET tables allow duplicate values in table. 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. · ...
Read more