Welcome To Datawarehouse World

  Primary Index Secondary Index It can be created while creating a table. It can able to create after table creation too. It can't be modified… If we need to change the PI then needs to drop the table and needs to recreate it along with new PI… It can be modified. Only one PI can able to create it in table We can create around 64 columns oriented SI.. It will perform the full table scan It will not perform the full table scan and it will improve the performance. If we didn’t define the PI then TD will automatically will assign the PI It’s an optional

 Secondary Index: 1. It can be created to improve the performance. 2. Its an optional. 3. It will not do the table full scan...     Note: primary index will do the table full scan. 4. Secondary index will be stored in Separate subtable and it will required some additional space in AMP... 5. Secondary index can able to create once the table got created.     Note: Primary index can able to create while creating a table. 6.Secondary index has a limit of 64 columns only. 7.Secondary index can be changed. 8. The below is the syntax for creating a Secondary index. Create Unique index (Movie_No) on Movie_List;

 Temp Space :  It's a unused space in Permanent Space and the Global Temp Table is used the Temp space.

 Spool Space is the unused permanent space which is used by the system to execute the sql query. Note:  Each and every users are having the Spool space limit...if the spool space limit got exceeded then will get a spool space error message. 

 Permanent Space:  They are just maximum amount of space ETL user or user or Database can use it. The amount of permanent space is divided by the number of Amps. Note:  If allocated space exceeds in AMP then we will get an out of space / memory issue. 

      SELECT e.emp_num, e.name, f.emp_num, f.name, e.country      FROM employee AS e, employee AS f      WHERE e.country = f.country      AND   e.emp_num < f.emp_num      ORDER BY e.emp_num, f.emp_num; Referred : Teradata Document.