Help Center> Data Warehouse Service> Tool Guide> DSC - SQL Syntax Migration Tool> SQL Syntax Migration Reference> Teradata Syntax Migration> Query Optimization Operators
View PDF

Query Optimization Operators

This section contains the migration syntax for migrating Teradata query optimization operators. The migration syntax decides how the supported keywords/features are migrated.

Use the inToExists configuration parameter to configure the migration behavior of IN/NOT IN to EXISTS/NOT EXISTS.

By default, this parameter is set to FALSE. To enable the query optimization feature, this parameter must be set to TRUE.

IN and NOT IN Conversion

Teradata queries containing the IN / NOT IN have been optimized and converted to the EXISTS / NOT EXISTS operator when being migrated to GaussDB A, and GaussDB(DWS) SQL query. The IN / NOT IN operator supports single and multiple columns. The DSC will migrate IN / NOT IN statements only when they exist within the WHERE or ON clause. The following examples show IN to EXISTS but are also applicable for NOT IN to NOT EXISTS.

IN to EXISTS Simple Conversion

In the following example, the keyword IN is provided in the input file. For better optimization, the tool replaces it with the EXISTS keyword during the migration process.

  • The migration of IN /NOT IN statements with nested IN / NOT IN are not supported and the migrated scripts will be invalid. 
    UPDATE tab1
   SET b = 123
 WHERE b IN ('abc') 
   AND b IN ( SELECT  i 
                FROM tab2 
                WHERE j NOT IN (SELECT m 
                                 FROM tab3
                               )
             )
;

    For migration of IN/NOT IN statements with sub-queries, comments between the IN / NOT IN operator and the sub-query (see example) are not supported.

    Example:

    SELECT * 
  FROM categories 
 WHERE category_name
    IN --comment 
        ( SELECT category_name 
            FROM categories1 ) 
 ORDER BY category_name;

  • Migrating IN / NOT IN statements with object names containing $ and #
    • Tools will not migrate the query if TABLE name or TABLE ALIAS starts with $. 
      SELECT Customer_Name
  FROM Customer_t $A
 WHERE Customer_ID IN( SELECT Customer_ID FROM Customer_t );
    • Tools may migrate the query incorrectly if COLUMN name starts with #. 
      SELECT Customer_Name
  FROM Customer_t
 WHERE #Customer_ID IN( SELECT #Customer_ID FROM Customer_t );

Input-IN

SELECT ...
   FROM tab1 t
   WHERE t.col1 IN (SELECT icol1 FROM tab2 e)
 ORDER BY col1

Output

SELECT ...
   FROM tab1 t
   WHERE EXISTS (SELECT icol1
                    FROM tab2 e
                   WHERE icol1 = t.col1
                 )
 ORDER BY col1;

Input-IN with multiple columns along with Aggregate function

SELECT deptno, job_id, empno, salary, bonus
   FROM emp_t
  WHERE ( deptno, job_id, CAST(salary AS NUMBER(10,2))+CAST(bonus AS NUMBER(10,2)) ) 
                IN ( SELECT deptno, job_id, 
                      MAX(CAST(salary AS NUMBER(10,2))+CAST(bonus AS NUMBER(10,2)))
                        FROM emp_t 
                       WHERE hire_dt >= CAST( '20170101' AS DATE FORMAT 'YYYYMMDD' ) 
                       GROUP BY deptno, job_id )
    AND hire_dt IS NOT NULL;

Output

SELECT deptno, job_id, empno, salary, bonus
   FROM emp_t MAlias1
  WHERE EXISTS ( SELECT deptno, job_id, 
                            MAX(CAST(salary AS NUMBER(10,2))+CAST(bonus AS NUMBER(10,2)))
                      FROM emp_t 
                     WHERE hire_dt >= CAST( '20170101' AS DATE)
                           AND deptno  = MAlias1.deptno
                           AND job_id   = MAlias1.job_id
                     GROUP BY deptno, job_id 
                    HAVING MAX(CAST(salary AS NUMBER(10,2))+CAST(bonus AS NUMBER(10,2)))
                            = CAST(MAlias1.salary AS NUMBER(10,2))+CAST(MAlias1.bonus AS NUMBER(10,2)) )
    AND hire_dt IS NOT NULL;