1.NO PI in  Teradata?

CREATE TABLE <TABLE> (
PK INTEGER NOT NULL
) NO PRIMARY INDEX ;

NOPI tables come with some further restrictions, find below the most important ones:

– Only MULTISET tables can be created
– No identity columns can be used
– NoPI tables cannot be partitioned with a PPI
– No statements with an update character allowed (UPDATE,MERGE INTO,UPSERT), still you can use INSERT,DELETE and SELECT
– No Permanent Journal possible
– Cannot be defined as Queue Tables
– No Queue Tables allowed
– Update Triggers cannot update a NOPI table (probably introduces with a later release)
– No hash indexes are allowed (use join indexes instead)

The following features can be used as usual together with NOPI tables:

– Fallback protection of the table
– Secondary Indexes (USI, NUSI)
– Join Indexes
– CHECK and UNIQUE constraints
– Triggers
– Collection of statistics

• The information about NOPI tables, as in the case of any table type, is stored in DBC.Tables.  The tablekind is ‘O’:
• (Fastloads, TPUMP array insert loads) is faster than loading tables with a Primary Index.
• After distributing the rows randomly across the AMPs we are already finished. No hashing and redistribution is needed. No sorting is needed. Furthermore, as the rows are assigned randomly to the AMPs, our data will always be distributed evenly across all AMPs and no skewing will occur.only the acquisition phase of the loading utilities is executed
• rows are always appended to the end of the table’s data blocks, thus any overhead usually caused by sorting the rows by rowhash into the data blocks is avoided.
• Bulk loaded small tables will always be skewed. Round robin is done on block level and not the row level. Some AMPs will receive data blocks and the rest will receive no data.
• If you execute an INSERT…SELECT statement, from a primary index table into a NOPI table, AMP-local copying of the rows will be applied. This means, all rows stay on their current AMP, and if the primary index table is skewed you, will end up with a skewed NOPI table.
• Skewing can happen, by executing an archive job on a source system, and a restore of the backup on a different target system.
• NOPI tables are useful in certain situations, but without a Primary Index, row access is limited to All-AMP full tables scans
• NOPI tables are useful in certain situations, but without a Primary Index, row access is limited to All-AMP full tables scans. In order to offset this impact, you could add an unique secondary index (USI) to your NOPI table.mainly consider using them during the ETL-Process, in situations when anyway a full table scan is required.

2.  How do you determine the number of sessions?

• Teradata performance and workload
• Client platform type
• performance and workload
• Channel performance for channel attached systems
• Network topology and performance for network attached systems
• Volume of data to be processed by the application

3. What is use of compress in terdata?Explain?

• Compression is used to Minimize the table size, for example when the table size is increasing anonymously We can use Compression to reduce the size of the table Conditions:
• Compression can be declared at the time of table creation
• We can compress up to 256 column values(not columns)
• We can’t compress variable length fields (vartext,varchar.
• The compressed value is stored in column header and is to be used as default of that column unless a value is present. e.g. Dept of Taxes in Washington has a database that tracks all people working in Washington. Around 99.9% of the tax payers would have Washington as their state code on their address. Instead of storing “Washington” in millions of records the compress will store the value “Washington” in the table header. Now, a value can be assumed in a row as a default unless another value exists inside the column.

4.What is explain in teradata?

•   The EXPLAIN modifier preceding an SQL statement generates an English translation of the optimizer’s plan.     It is fully parsed and optimized, but not executed.
• EXPLAIN returns: Text showing how a statement will be processed (a plan).
• An estimate of how many rows will be involved.
• A relative cost of the request (in units of time).
  • This information is useful for:
  •  Predicting row counts.
  •  Predicting performance.
  • Testing queries before production.
  • Analyzing various approaches to a problem
• EXPLAIN may also be accessed using Teradata Visual Explain.
• The execution strategy provides what an optimizer does but not why it chose them.
• The EXPLAIN facility is used to analyze all joins and complex queries.

5. What is the difference between Access Logging and Query Logging in Teradata?

• Access Logging is concerned with security (i.e. who’s is doing what). In access logging you ask the database to log who’s doing what on a given object. The information stored is based on the object not the SQL fired or the user who fired it.
• Query Logging (DBQL) is used for debugging (i.e. what’s happening around ?). Incase of DBQL database keep on tracking various parameters i.e. the SQLs, Resource, Spool Usage, Steps and other things which help you understand

6.Commands used within Ferret Utility?

• SHOWSPACE –
  • amount of Disk Cylinder Space is in use
  • amount of Disk Cylinder Space is available in the system.
• This will give you an information about
  • Permanent Space cylinders,
  • Spool Space cylinders,
  • Temporary Space cylinders,
  • Journaling cylinders,
  • Bad cylinders and
  • Free cylinders.
  • For each of these 5 things it will present you 3 parameters i.e.
    • Average Utilization per cylinder,
    • % of total available cylinders and
    • number of cylinders.
•  SHOWBLOCK

7.Explain Ferret Utility in Teradata?

• Ferret (File Reconfiguration tool) is an utility which is used to display and set Disk Space Utilization parameters within Teradata RDBMS. When you select the Ferret Utility parameters, it dynamically re configures the data on disks.
• We can run this utility through Teradata Manager; to start the Ferret Utility type (START FERRET) in the database window.

8.How to find the number of AMPs in any system?

SELECT HASHAMP()+1 ; [ We have to do +1 because the HASHAMP number starts with 0]

9.What are the options that are not available for global temporary tables ?

GTT  definitions are created in Data dictionary.  These table provide separate instance to each user who refers to them . The following options are not available for global temporary tables:

• Any kind of constraints like check/referential cannot be applied on table –
• Identity columns since data in GTT are materialized only for session –
• Permanent Journaling cannot be done as data in tables are instances only to user in that session –
• PPI cannot be applied as data does not get stored in PERM , only TEMP space is utilized here.

10.What are the options not available for volatile tables in teradata ?

The following options are not available for volatile tables because table definition is not stored in data dictionary

•  Default values for columns –
• Title clause for columns –
• Named Indexes for table –
• Compression on columns/table level since table data are spooled –
• Stats cannot be collected since data is materialized only for session –
• Identity columns as these again would need entry in IDcol tables –
• PPI cannot be done on tables – Any kind of constraints like check/referential cannot be applied on table

11.What is the acceptable range for skew factor in a table?

There is no particular range for skew factor.  In case of production systems, it is suggested to keep skew factor between 5-10.

There are various considerations for skew factor –

• Number of AMPS
• Size of row in a table
• number of records in a table
• PI of a table
• Frequent access of table (Performance consideration)
• whether table getting loaded daily /monthly or how frequently data is being refreshed

12.What is multivalued compression in Teradata?

Multi valued compression or just MVC is a compression technique applied on columns in Teradata .  MVC has a unique feature of compressing up-to 255 distinct values per column in a given table. The advantage of compression are

• Reduced Storage cost by storing more of a logical data than physical data.
• Performance is greatly improves due to  reduced retrieval of physical data for that column.
• Tables having compression always have an advantage since optimizer considers reduced I/O as one of the factors for generating EXPLAIN plan.

• An index cannot be Created on a view.
• It cannot contain an ORDER BY clause.
• All the derived columns and aggregate columns used in the view must have an AS clause (alias defined).
• A view cannot be used to UPDATE if it contains the following :
  • Data from more than one table (JOIN VIEW)
  • The same column twice
  • Derived columns
  • A DISTINCT clause
  • A GROUP BY clause

19. Difference between Stored Procedure and Macro?

20. What are the scenarios in which Full Table Scans occurs?

 21..What do High confidence, Low confidence and No confidence mean in EXPLAIN plan?

 22. What is Teradata Virtual storage?

 23.How to start / stop a database in windows?

24.What are the functions of a Teradata DBA?

 25.How to eliminate Product Joins in a Teradata SQL query?

26. How to find Teradata Release and Version details?

27. How to calculate the tablesize , database size  and free space left in a database in teradata?

29.How to find number of records present in Each AMP or a Node for a given Table through SQL?

Select HASHAMP(HASHBUCKET(HASHROW(PRIMARY_INDEX_COLUMNS))) AS AMP_NO,COUNT(*) 
from DATABASENAME.TABLE_NAME GROUP BY 

33.What is a join index ? What are benefits of using Join index?

It is a index that is maintained in a system .It maintains rows  joined on two or more tables. Join index is useful for queries where the index structure contains all the columns referenced by one or more joins, thereby allowing the index to cover all or part of the query Benefits if using join index is – to eliminate base table access – Aggregate processing is eliminated by creating aggregate join index – It reduces redistribution of data since data is materialized by JI. – Reduces complex join conditions by using covering queries

34.Collect stats collects demographic data about the columns or indices of a table.

• This statistics is used by the PE to optimize the plan which can be viewed by explain command.
• When collect stats is executed,the statistics is stored  for use of PE.
• The Optimizer plans an execution strategy for every SQL query submitted to it.
• We have also seen that the execution strategy for any query may be subject to change depending on various factors.
• For the Optimizer to consistently choose the optimum strategy, it must be provided with reliable, complete, and current demographic information regarding all of these factors. The best way to assure that the Optimizer has all the information it needs to generate optimum execution strategies is to COLLECT STATISTICS.

35.What is collect State in Teradata ? What it use and how it works?

• Statistics tell the Optimizer how many rows/ value there are.
• The Optimizer uses statistics to plan the best way to access data.
• May improve performance of complex queries and joins.
• NUSI Bit Mapping requires collected statistics.
• Helpful in accessing a column or index with uneven value distribution.
• Stale statistics may mislead the Optimizer into poor decisions.
• Statistics remain valid across a reconfiguration of the system.
• COLLECT is resource intensive and should be done during off hours.

36.When should the statistics be collected?

Here are some excellent guidelines on when to collect statistics:

• All non unique indices
• Non index join columns
• Primary index of small tables
• Secondary indexes mentioned in the tables
• Primary Index of a Join Index
• Secondary Indices defined on any join index

• Join index columns that frequently appear on any additional join index columns that frequently appear in WHERE search conditions
• Columns that frequently appear in WHERE search conditions or in the WHERE clause of joins.

37.How to make sure Optimizer chooses NUSI over Full Table Scan (FTS)?

• A optimizer would prefer FTS over NUSI, when there are no Statistics defined on NUSI columns.
• It would prefer scanning for full table instead of going for Subtables in NUSI  since optimizer does not have information about subtables of NUSI
• It is always suggested to collect statistics whenever NUSI columns are defined on the table. Verify whether index is being used by checking in Explain plan.

38.What does” Pseudo Table” Locks mean in EXPLAIN Plan in Teradata?

• It is a false lock which is applied on the table to prevent  two users from getting conflicting locks with all-AMP requests.
• PE will determine an particular AMP to manage all AMP LOCK requests for given table and Put Pseudo lock on the table.
• Pseudo tables are the dummy tables, Whenever we place a request which involves full table scan and needs data to be retrieved from all AMPS then the parsing engine identifies a gatekeeper AMP which can command all other AMP to lock for a particular request from the user and does not allow multiple user to access the same table to prevent global deadlock.

39.How do you whether table is locked or not?

Just run the following query on the table.

Lock Table DBNAME.TABLENAME write nowait Select * from DBNAME.TABLENAME;

If this table is locked currently then , then the above statement would fail as Nowait will not wait for locks to be released on that  table .

40.What are the types of Join Strategies available in Teradata?

Join Strategies are used by the optimizer to choose the best plan to join tables based on the given join condition.

• Merge (Exclusion)
• Nested
• Row Hash
• Product (including Cartesian Product joins)

There are different types of merge join strategies available.

But in general , while joining two tables the data will be redistributed or duplicated across all AMPs to make sure joining rows are in the same AMPs.

If the two tables are joined based on PI, no redistribution/duplication will happen as the rows will be in the same AMP and performance will be better.

If one table PI is used and Other table PI not used, redistribution/duplication of the table will happen based on the table size.In these cases Secondary Indexes will be helpful.

Case 1 – P.I = P.I joins

Case 2 – P.I = non Index joins

Case 3 – non Index = non Index joins

Case1 – there is no redistribution of data over amp’s. Since amp local joins happen as data are present in same AMP and need not be re-distributed. These types of joins on unique primary index are very fast.

Case2 – data from second table will be re-distributed on all amps since joins are happening on PI vs. non Index column. Ideal scenario is when small table is redistributed to be joined with large table records on same amp -Data in small table is duplicated to Every AMP where it is joined locally with large table

case3 – data from both the tables are redistributed on all AMPs. This is one of the longest processing queries; Care should be taken to see that stats are collected on these columns

Nested Join

——————-

Nested Join is one of the most precise join plans   suggested by Optimizer .Nested Join works on UPI/USI used in Join statement and is used to retrieve the single row from first table . It then checks for one more matching rows in second table based on being used in the join using an index (primary or secondary) and returns the matching results.

Example:

Select EMP.Ename , DEP.Deptno, EMP.salary

from

EMPLOYEE EMP ,

DEPARTMENT DEP

Where EMP.Enum = DEP.Enum

and EMp.Enum= 2345;    — this results in nested join

 Hash join

—————-

Hash join is one of the plans suggested by Optimizer based on joining conditions. We can say Hash Join to be close relative of Merge based on its functionality. In case of merge join, joining would happen in same amp.   In Hash Join, one or both tables which are on same amp are fit completely inside the AMP’s Memory   . Amp chooses to hold small tables in its memory for joins happening on ROW hash.

Advantages of Hash joins are

1. They are faster than Merge joins since the large table doesn’t need to be sorted.
2. Since the join happening b/w table in AMP memory and table in unsorted spool, it happens so quickly.

 Exclusion Join

————————-

These type of joins are suggested by optimizer when following are used in the queries

– NOT IN

– EXCEPT

– MINUS

– SET subtraction operations

Select EMP.Ename , DEP.Deptno, EMP.salary

from

EMPLOYEE EMP

WHERE EMP.Enum NOT IN

( Select Enum from

DEPARTMENT DEP

where Enum is NOT NULL );

Please make sure to add an additional WHERE filter “with <column> IS NOT NULL” since usage of NULL in a NOT IN <column> list will return no results.

Exclusion join for following NOT In query has 3 scenarios

Case 1: matched data in “NOT IN” sub Query will disqualify that row

Case 2: Non-matched data in “NOT IN” sub Query will qualify that row

Case 3: Any Unknown result in “NOT IN” will disqualify that row – (‘NULL’ is a typical example of this scenario).

 There are 3 types of joining strategies:

• 1- duplicate all rows of one table onto every AMP (The duplication of all rows is done when the non-PI column is on a small table),
• 2- redistribute the rows of one table by hashing the non-PI join column and sending them to the AMP containing the matching PI row,
• 3- redistribute both tables by hashed join column value Now, in this case if the table A joining column is Non – PI then, TD redistributes the rows by hashing the non-PI join column and sending them to the AMP containing the matching PI row of Table B. 2. From the same above example now the optimizer is taking Table B (100 million records) and it is distributing it, In this it might happen that the Joining column of Table B is Non – PI and that of Table A is PI, due to which it is trying to redistribute.

1. Big and small table joins – duplicate small tables

2. Column used in join is PI for one table – redistribute the other table in spool.

3. Column used in join is non PI for both tables – redistribute both tables in spool. In the above case, table A has got 10 million and table B has got 100 million rows. Since table a is 1/10th of table b, TD redistributes table a on all amps. In the second scenario, redistribution of table b by TD is highly unlikely, because TD optimizer perfectly chooses the join strategy. IF it chooses this scenario, then the query has to be tuned. Try to use a nested join, which would have better performance.

41.  PPI(Partition Primary Index).

• PPI is used to improve performance for large tables when you submit queries that specify a range constraint.
• PPI allows you to reduce the number of rows to be processed by using partition elimination.
• PPI will increase performance for incremental data loads, deletes, and data access when working with large tables with range constraints

Lets take Order_Table, where we have both January and February dates in column Order_Date.

After Partitioned Table,
The example of AMPs on the top of the page. This table is not partitioned.

Each AMP always sorts its rows by the Row-ID in order to do a Binary Search on Primary Index queries.

Types of partitioning:

RANGE_N Partitioning

Below is the example for RANGE_N Partition by day.
CREATE TABLE ORDER_TABLE

Case_N Partitioning
CREATE TABLE ORDER_TABLE

PARTITION BY CASE_N

The UNKNOWN Partition is for an Order_Total with a NULL value. The NO CASE Partition is for partitions that did not meet the CASE criteria.

Multi-Level Partitioning:
You can have up to 15 levels of partitions within partitions.

 Character Based Partitioning(New Feature V13.10) :
There are four new data types available for Character Based PPI. They are CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC.

Example for RANGE Based Character PPI

Example for CASE Based Character PPI

Ex-Query: Find all Products between Apples and Grapes?

• A table can have up to 65,535 Partitions.
• Partitioning never determines which AMP gets row.
• Partitioning determines how an AMP will sort the row on its own.
• Table can have up to 15 levels of partitions.
• A table cannot have an UPI as the Primary Index if the Partition table does not include PI.
• Total 3 forms of Partitioning Simple, RANGE and CASE.

• They provide efficient searches by using partition elimination at the various levels or combination of levels.
• They reduce the I/O for range constraint queries
• They take advantage of dynamic partition elimination
• They provide multiple access paths to the data, and an MLPPI provides even more partition elimination and more partitioning expression choices, (i.e., you can use last name or some other value that is more readily available to query on.)
• The Primary Index may be either a UPI or a NUPI; a NUPI allows local joins to other similar entities
• Row hash locks are used for SELECT with equality conditions on the PI columns.
• Partitioned tables allow for fast deletes of data in a partition.
• They allow for range queries without having to use a secondary index.
• Specific partitions maybe archived or deleted.
• May be created on Volatile tables; global temp tables, base tables, and non-compressed join indexes.
• May replace a Value Ordered NUSI for access.
• Tables that hold yearly information don’t have to be split into 12 smaller tables to avoid Full Table Scans (FTS). This can make modeling and querying easier.
• Fastload and Multiload work with PPI tables, but not with all Secondary Indexes.
• Range queries don’t have to utilize a Full Table Scan.
• Deletions of entire partitions are lightning fast.
•  PPI provides an excellent solution instead of using Secondary Indexes

Disadvantage of partitioned tables:

• PPI is basically used for Range-based or Category-based data storage purposes. When it comes to Range queries, there is no need of Full table scan utilization as it straightaway moves to the consequent partition thus skipping all the other partitions.
• A two-byte Partition number is added to the ROW-ID and it is now called a ROW KEY. The two-bytes per row will add more Perm Space to a table.
• Joins to Non-Partitioned Tables can take longer and become more complicated for Teradata to perform.
• Basic select queries utilizing the Primary Index can take longer if the Partition number is not also mentioned in the WHERE clause of the query.
• You can’t have a Unique Primary Index (UPI) if the Partition Number is not at least part of the Primary Index. You must therefore create a Unique Secondary Index to maintain uniqueness.
• Rows in a partitioned table are 2 bytes longer.
  Access via the Primary Index may take longer.
  Full table joins to a NPPI table with the same PI may take longer.

42.How to Choose Primary Index(PI) in Teradata?

• Frequently using column.
• Columns which have more unique value
• Columns which are using in join clause
• Not regularly changing column values
• By default it takes as first column as PI
• Access Demographics Identify index candidates that maximize one-AMP operations. Columns most frequently used for access (Value and Join).
• Distribution Demographics Identify index candidates that optimize parallel processing. Columns that provide good distribution
• Volatility Demographics Identify index candidates with low maintenance I/O.
• Data Distribution and Join frequency of the Column.If a Column is used for joining most of the tables then it is wise to choose the column as PI candidate.

For example, We have an Employee table with EMPID and DEPTID and this table needs to be joined to the Department Table based on DEPTID. It is not a wise decision to choose DEPTID as the PI of the employee table. Reason being, employee table will have thousands of employees whereas number of departments in a company will be less than 100. So choosing EMPID will have better performance in terms of distribution.

• One Primary Index per table.
• A Primary Index value can be unique or non-unique.
• The Primary Index value can be NULL.
• The Primary Index value can be modified.
• The Primary Index of a populated table cannot be modified.
• A Primary Index has a limit of 64 columns.

43.Can we define PARTITION PRIMARY INDEX (PPI) on a Primary Index column in Table? Explain Why?

 PPI cannot be defined on PI column in Table. Since PI is used to locate an AMP and store the data based on HASH value (ROW ID ) it cannot be used as PPI column. In case of PPI , The data stored in AMP’s are Partitioned based on PPI column after they are row hashed (ROW KEY = ROW ID +PPI value ) P.S: If you want to create UPI on table, then PPI column can be added as part of PI .

44.What are Secondary Indexes (SI) , types of SI and disadvantages of Secondary Indexes in Teradata?

• Secondary Indexes provide another path to access data.
• Teradata allows up to 32 secondary indexes per table.
• Keep in mind; row distribution of records does not occur when secondary indexes are defined.
• The value of secondary indexes is that they reside in a subtable and are stored on all AMPs, which is very different from how the primary indexes (part of base table) are stored.
• Keep in mind that Secondary Indexes (when defined) do take up additional space. Secondary Indexes are frequently used in a WHERE clause.
• The Secondary Index can be changed or dropped at any time.
• However, because of the overhead for index maintenance, it is recommended that index values should not be frequently changed.
• There are two different types of Secondary Indexes, Unique Secondary Index (USI), and Non-Unique Secondary Index (NUSI).
• Unique Secondary Indexes are extremely efficient. A USI is considered a two-AMP operation. One AMP is utilized to access the USI subtable row (in the Secondary Index subtable) that references the actual data row, which resides on the second AMP.
• A Non-Unique Secondary Index is an All-AMP operation and will usually require a spool file. Although a NUSI is an All-AMP operation, it is faster than a full table scan.

Secondary indexes can be useful for:

• Satisfying complex conditions
• Processing aggregates
• Value comparisons
• Matching character combinations
• Joining tables

: Advantages:

1. A secondary index might be created and dropped dynamically
2.      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 chooses 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.
6. Secondary Indexes need more I/O.
7. Secondary Index slows down the process because of Sub-table maintenance.
8. Secondary Index requires more Physical Storage.

45. What is the purpose of indexes? Answer:  

• An index is a mechanism that can be used by the SQL query optimizer to make table access more performant.
• Indexes enhance data access by providing a more-or-less direct path to stored data and avoiding the necessity to perform full table scans to locate the small number of rows you typically want to retrieve or update.

46.How does indexing improve query performance?

• By creating index on single or multiple columns, query takes index access path instead of full table scan.
• Full table scan is costilier operation where it has to visit all rows in the table whereas index operation just accesses the index sub table with required hash value and joins with the basetable using rowid. This improves response time and performance of the query.
• Teradata automatically Creates the Primary index , if not specified by the DDl for Table .
  • PI
  • Secondary Index
  • PPI
  • Join Index
  • HASH Index

• Indexing is a way to physically reorganise the records to enable some frequently used queries to run faster. The index can be used as a pointer to the large table.
• It helps to locate the required row quickly and then return it back to the user.
• or The frequently used queries need not hit a large table for data. they can get what they want from the index itself. – cover queries.
• Index comes with the overhead of maintanance.
• Teradata maintains its index by itself. Each time an insert/update/delete is done on the table the indexes will also need to be updated and maintained.
• Indexes cannot be accessed directly by users. Only the optimizer has access to the index.

• Hash indexes are similar to single-table simple join indexes in that they are used for denormalizing a single table by storing rows in a separate physical object from the base table.
• Hash indexes are limited to a SINGLE table and are beneficial in improving efficiency. Like join indexes, Hash index rows are stored in a different object than the base table rows.
• This allows for faster access without needing access to the underlying rows. Also like their relative, they function as an alternative to the base rows instead of providing a different access path.
• Like join indexes, hash indexes are known as “covered queries” because they are utilized when they contain all the columns requested by the query. If all of the columns are covered, the optimizer will usually choose to access the rows via the hash index instead of the base table rows.
• In a situation where the hash index partially covers the query, the optimizer may use the Row ID included with the Hash Index to access the other columns in the data row. Join indexes and hash indexes are both transparently maintained by Teradata. When the base table changes, the Hash Index table is automatically updated.
• This automatic task is referred to as an update burden.
• Being that Hash Indexes are strikingly similar in functionality to secondary indexes, they should be carefully considered because they carry this update burden.
• Hash indexes however, can offer marginal performance gains over a secondary index when certain columns are frequently needed from one table to join with many others. In these situations, the Hash index partially covers the query, but fully covers the rows from the base table, effectively eliminating any need to access the base table.
• Hash Indexes: • Can be ordered by hash or by values to facilitate range queries. • Automatically has the Row ID of the base table row included in the Hash Index which the RDBMS software can use to access columns not included in a “covered query”

48.What is Value-Ordered NUSIs ?

• When we create a NUSI on the table a subtable is built on all AMP’s.
• Each Subtable contains a row for the NUSI and corresponding row ids to the base rows located on the same AMP.
• Rows in the subtable are sequenced by the row hash of the NUSI value.
• This way of storing rows is convenient in storing rows with particular NUSI, but not good for range queries.
• This is where Value Ordered NUSI becomes useful VALUE ordered NUSI allows rows in the subtable to be stored in order of data value , rather than hash of the value. This is useful when performing range queries.

53 .What is a join index ? What are benefits of using join index ?

 It is a index that is maintained in a system .It maintains rows joined on two or more tables. Join index is useful for queries where the index structure contains all the columns referenced by one or more joins, thereby allowing the index to cover all or part of the query

54.Create Table: When you are designing/creating the table, check the below steps.

55..What are the MultiLoad Utility limitations?

56. What are TPUMP Utility Limitations?

57. What are TPUMP Utility Limitations?

58.  What is the difference between FastLoad and MultiLoad?

59.What is the difference between Multiload & Fastload in terms of Performance?