Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt
You are on page 1/ 2
SELECT DatabaseName
, TableName , Vproc , CurrentPerm , PeakPerm FROM DBC.TableSize WHERE DatabaseName= SASDB1? AND TableName=?CM_FINANCIAL_ACCOUNT? ORDER BY Vproc; It is a Teradata specified Join, which is used as equivalent to product join. There is no On clause in case of CROSS join SELECT EMP.ename , DPT.Dname FROM employee EMP CROSS JOIN Department DPT WHERE EMp.deptno = DPT.depto ; What is use of compress in teradata?Explain? Answers: 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:1.Compression can be declared at the time of table creation2.We can c ompress up to 256 column values(not columns) 3.We can't compress variable length fields (vartext,varchar..) Explanation for 2 condition:create table tab1(::Order_type char(25) compress ('air','sea','r oad'):)in the above example order type have 3 fields, one should be selected by the user, so one of the field will repeat for every order, like these column val ues we can use compress statement because these are repeating for entire table, like these column values TD supports 256 col generally NCR people will provides ready made scripts for these type of compressions However, we can store only one value per column and not 3(air, sea, road). The compressed value is stored in column header and is to be used as default of that column unless a val ue is present. e.g. Dept of Taxes in Washington has a database that tracks all people working in Was hington. Around 99.9% of the tax payers would have Washington as their state cod e on their address. Instead of storing Washington in millions of records the compr ess will store the value Washington in the table header. Now, a value can be assum ed in a row as a default unless another value exists inside the column
Teradata performance tuning and optimization
Answers: 1. collecting statistics 2. Explain Statements 3. Avoid Product Joins when possible 4. select appropriate primary index to avoid skewness in storage 5. Avoid Redistribution when possible 6. Use sub-selects instead of big "IN" lists 7. Use derived tables 8. Use GROUP BY instead of DISTINCT ( GROUP BY sorts the data locally on the VPR OC. DISTINCT sorts the data after it is redistributed)
9. Use Compression on large tables
You can find the Table Space Size of your table from this Data Dictionary Table DBC.TABLESIZE SELECT DATABASENAME, TABLENAME, SUM(CURRENTPERM) FROM DBC.TABLESIZE WHERE DATABA SENAME = AND TABLENAME = GROUP BY DATABASENAME , TABLENAME;
