Hi All,
one of my customer sent me a query generate by JDBC driver 15.00.00.20 to get some metadata. the execution of the query is not performant. I analyse the query and find that the where condition on databasename and tablename are write with LIKE (below I report only the where condition part of the query):
WHERE t.TableKind IN ('O','T','V')
AND t.DatabaseName (NOT CASESPECIFIC) LIKE TRIM(TRAILING FROM 'Q_TC0_KPE_KPI') (NOT CASESPECIFIC)
AND t.TableName (NOT CASESPECIFIC) LIKE TRIM(TRAILING FROM 'RET_ABBINAMENTO_CAMPO') (NOT CASESPECIFIC)
With this where condition TERADATA optimization generate not a good plan even if we define the correspondant stats on where condition columns (as you know, if we use like TD cannot use the stats information).
I change the quey simply substituing the LIKE operator with = (equal) operator:
WHERE t.TableKind IN ('O','T','V')
AND t.DatabaseName (NOT CASESPECIFIC) = TRIM(TRAILING FROM 'Q_TC0_KPE_KPI') (NOT CASESPECIFIC)
AND t.TableName (NOT CASESPECIFIC) = TRIM(TRAILING FROM 'RET_ABBINAMENTO_CAMPO') (NOT CASESPECIFIC)
In this way I can run the query with a very short elapsed (with the original version the query never end because in the plan we have a product join between 2 big spool !).
Is there a way to modify the query generate by JDBC driver ?
Why the driver use LIKE instead of '=' ?
thank you very match.
Hi all,
I forget to report the whole query:
SELECT CAST (NULL AS VARCHAR(30)) AS TABLE_CAT,
t.DatabaseName AS TABLE_SCHEM,
t.TableName AS TABLE_NAME,
c.ColumnName AS COLUMN_NAME,
CAST ((
CASE c.ColumnType
WHEN 'A1' THEN 2003
WHEN 'AN' THEN 2003
WHEN 'AT' THEN 92
WHEN 'BF' THEN -2
WHEN 'BO' THEN 2004
WHEN 'BV' THEN -3
WHEN 'CF' THEN 1
WHEN 'CO' THEN 2005
WHEN 'CV' THEN 12
WHEN 'D' THEN 3
WHEN 'DA' THEN 91
WHEN 'F' THEN 6
WHEN 'GF' THEN 1
WHEN 'GV' THEN 12
WHEN 'I1' THEN -6
WHEN 'I2' THEN 5
WHEN 'I' THEN 4
WHEN 'I8' THEN -5
WHEN 'JN' THEN 1111
WHEN 'N' THEN 2
WHEN 'SZ' THEN 93
WHEN 'TS' THEN 93
WHEN 'TZ' THEN 92
WHEN 'UT' THEN (
CASE u.TypeKind
WHEN 'D' THEN 2001
WHEN 'S' THEN 2002 ELSE 1111
END)
WHEN 'XM' THEN 2009 ELSE 1111
END) AS INTEGER) AS DATA_TYPE,
CAST ((
CASE c.ColumnType
WHEN '++' THEN 'TD_ANYTYPE'
WHEN 'A1' THEN 'SYSUDTLIB.' || TRIM(TRAILING
FROM c.ColumnUDTName)
WHEN 'AN' THEN 'SYSUDTLIB.' || TRIM(TRAILING
FROM c.ColumnUDTName)
WHEN 'AT' THEN 'TIME'
WHEN 'BF' THEN 'BYTE'
WHEN 'BO' THEN 'BLOB'
WHEN 'BV' THEN 'VARBYTE'
WHEN 'CF' THEN 'CHAR'
WHEN 'CO' THEN 'CLOB'
WHEN 'CV' THEN 'VARCHAR'
WHEN 'D' THEN 'DECIMAL'
WHEN 'DA' THEN 'DATE'
WHEN 'DH' THEN 'INTERVAL DAY TO HOUR'
WHEN 'DM' THEN 'INTERVAL DAY TO MINUTE'
WHEN 'DS' THEN 'INTERVAL DAY TO SECOND'
WHEN 'DY' THEN 'INTERVAL DAY'
WHEN 'F' THEN 'FLOAT'
WHEN 'GF' THEN 'GRAPHIC'
WHEN 'GV' THEN 'VARGRAPHIC'
WHEN 'HM' THEN 'INTERVAL HOUR TO MINUTE'
WHEN 'HR' THEN 'INTERVAL HOUR'
WHEN 'HS' THEN 'INTERVAL HOUR TO SECOND'
WHEN 'I1' THEN 'BYTEINT'
WHEN 'I2' THEN 'SMALLINT'
WHEN 'I' THEN 'INTEGER'
WHEN 'I8' THEN 'BIGINT'
WHEN 'JN' THEN 'JSON'
WHEN 'MI' THEN 'INTERVAL MINUTE'
WHEN 'MO' THEN 'INTERVAL MONTH'
WHEN 'MS' THEN 'INTERVAL MINUTE TO SECOND'
WHEN 'N' THEN 'NUMBER'
WHEN 'PD' THEN 'PERIOD(DATE)'
WHEN 'PM' THEN 'PERIOD(TIMESTAMP WITH TIME ZONE)'
WHEN 'PS' THEN 'PERIOD(TIMESTAMP)'
WHEN 'PT' THEN 'PERIOD(TIME)'
WHEN 'PZ' THEN 'PERIOD(TIME WITH TIME ZONE)'
WHEN 'SC' THEN 'INTERVAL SECOND'
WHEN 'SZ' THEN 'TIMESTAMP WITH TIME ZONE'
WHEN 'TS' THEN 'TIMESTAMP'
WHEN 'TZ' THEN 'TIME WITH TIME ZONE'
WHEN 'XM' THEN 'XML'
WHEN 'YM' THEN 'INTERVAL YEAR TO MONTH'
WHEN 'YR' THEN 'INTERVAL YEAR'
WHEN 'UT' THEN 'SYSUDTLIB.' || TRIM(TRAILING
FROM c.ColumnUDTName) ELSE TRIM (c.ColumnType)
END) AS VARCHAR(500)) AS TYPE_NAME,
(
CASE
WHEN c.COLUMNTYPE = 'UT' AND u.TypeKind = 'D' THEN CAST ((
CASE
WHEN BaseTypes.COLUMNTYPE = 'AT' THEN 15
WHEN (BaseTypes.COLUMNTYPE = 'CF' OR BaseTypes.COLUMNTYPE = 'CO' OR BaseTypes.COLUMNTYPE = 'CV' OR BaseTypes.COLUMNTYPE = 'GF' OR BaseTypes.COLUMNTYPE = 'GV' OR BaseTypes.COLUMNTYPE = 'JN' OR BaseTypes.COLUMNTYPE = 'XM') AND (c.CHARTYPE = 2 OR c.CHARTYPE = 4) THEN c.ColumnLength / 2
WHEN BaseTypes.COLUMNTYPE = 'D' THEN c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'DA' THEN 10
WHEN BaseTypes.COLUMNTYPE = 'DH' THEN 4 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'DM' THEN 7 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'DS' THEN 17 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'DY' THEN 1 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'F' THEN 15
WHEN BaseTypes.COLUMNTYPE = 'HM' THEN 4 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'HR' THEN 1 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'HS' THEN 14 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'I1' THEN 3
WHEN BaseTypes.COLUMNTYPE = 'I2' THEN 5
WHEN BaseTypes.COLUMNTYPE = 'I' THEN 10
WHEN BaseTypes.COLUMNTYPE = 'I8' THEN 19
WHEN BaseTypes.COLUMNTYPE = 'MI' THEN 1 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'MO' THEN 1 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'MS' THEN 11 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'N' THEN (
CASE
WHEN c.DECIMALTOTALDIGITS = -128 THEN 40 ELSE c.DECIMALTOTALDIGITS
END)
WHEN BaseTypes.COLUMNTYPE = 'PD' THEN 28
WHEN BaseTypes.COLUMNTYPE = 'PM' THEN 72
WHEN BaseTypes.COLUMNTYPE = 'PS' THEN 60
WHEN BaseTypes.COLUMNTYPE = 'PT' THEN 38
WHEN BaseTypes.COLUMNTYPE = 'PZ' THEN 50
WHEN BaseTypes.COLUMNTYPE = 'SC' THEN 8 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'SZ' THEN 32
WHEN BaseTypes.COLUMNTYPE = 'TS' THEN 26
WHEN BaseTypes.COLUMNTYPE = 'TZ' THEN 21
WHEN BaseTypes.COLUMNTYPE = 'YM' THEN 4 + c.DECIMALTOTALDIGITS
WHEN BaseTypes.COLUMNTYPE = 'YR' THEN 1 + c.DECIMALTOTALDIGITS ELSE c.ColumnLength
END) AS INTEGER) ELSE CAST ((
CASE
WHEN c.COLUMNTYPE = 'AT' THEN 15
WHEN (c.COLUMNTYPE = 'CF' OR c.COLUMNTYPE = 'CO' OR c.COLUMNTYPE = 'CV' OR c.COLUMNTYPE = 'GF' OR c.COLUMNTYPE = 'GV' OR c.COLUMNTYPE = 'JN' OR c.COLUMNTYPE = 'XM') AND (c.CHARTYPE = 2 OR c.CHARTYPE = 4) THEN c.COLUMNLENGTH / 2
WHEN c.COLUMNTYPE = 'D' THEN c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'DA' THEN 10
WHEN c.COLUMNTYPE = 'DH' THEN 4 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'DM' THEN 7 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'DS' THEN 17 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'DY' THEN 1 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'F' THEN 15
WHEN c.COLUMNTYPE = 'HM' THEN 4 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'HR' THEN 1 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'HS' THEN 14 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'I1' THEN 3
WHEN c.COLUMNTYPE = 'I2' THEN 5
WHEN c.COLUMNTYPE = 'I' THEN 10
WHEN c.COLUMNTYPE = 'I8' THEN 19
WHEN c.COLUMNTYPE = 'MI' THEN 1 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'MO' THEN 1 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'MS' THEN 11 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'N' THEN (
CASE
WHEN c.DECIMALTOTALDIGITS = -128 THEN 40 ELSE c.DECIMALTOTALDIGITS
END)
WHEN c.COLUMNTYPE = 'PD' THEN 28
WHEN c.COLUMNTYPE = 'PM' THEN 72
WHEN c.COLUMNTYPE = 'PS' THEN 60
WHEN c.COLUMNTYPE = 'PT' THEN 38
WHEN c.COLUMNTYPE = 'PZ' THEN 50
WHEN c.COLUMNTYPE = 'SC' THEN 8 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'SZ' THEN 32
WHEN c.COLUMNTYPE = 'TS' THEN 26
WHEN c.COLUMNTYPE = 'TZ' THEN 21
WHEN c.COLUMNTYPE = 'YM' THEN 4 + c.DECIMALTOTALDIGITS
WHEN c.COLUMNTYPE = 'YR' THEN 1 + c.DECIMALTOTALDIGITS ELSE c.COLUMNLENGTH
END) AS INTEGER)
END) AS COLUMN_SIZE,
CAST (NULL AS INTEGER) AS BUFFER_LENGTH,
CAST ((
CASE
WHEN (c.COLUMNTYPE = 'N' OR c.COLUMNTYPE = 'UT' AND BaseTypes.COLUMNTYPE = 'N') AND c.DECIMALFRACTIONALDIGITS = -128 THEN 0 ELSE c.DECIMALFRACTIONALDIGITS
END) AS INTEGER) AS DECIMAL_DIGITS,
CAST (10 AS INTEGER) AS NUM_PREC_RADIX,
CAST((
CASE c.Nullable
WHEN 'Y' THEN 1
WHEN 'N' THEN 0 ELSE 2
END) AS INTEGER) AS NULLABLE,
c.CommentString AS REMARKS,
c.DefaultValue AS COLUMN_DEF,
CAST (NULL AS INTEGER) AS SQL_DATA_TYPE,
CAST (NULL AS INTEGER) AS SQL_DATETIME_SUB,
CAST (c.ColumnLength AS INTEGER) AS CHAR_OCTET_LENGTH,
CAST ((ROW_NUMBER () OVER (PARTITION BY TABLE_SCHEM, TABLE_NAME
ORDER BY c.ColumnID)) AS INTEGER) AS ORDINAL_POSITION,
TRIM((
CASE c.Nullable
WHEN 'Y' THEN 'YES'
WHEN 'N' THEN 'NO' ELSE ''
END)) AS IS_NULLABLE,
CAST (NULL AS VARCHAR(30)) AS SCOPE_CATLOG,
CAST (NULL AS VARCHAR(30)) AS SCOPE_SCHEMA,
CAST (NULL AS VARCHAR(30)) AS SCOPE_TABLE,
CAST((
CASE c.ColumnType
WHEN 'UT' THEN (
CASE u.TypeKind
WHEN 'D' THEN CAST ((
CASE BaseTypes.ColumnType
WHEN 'A1' THEN 2003
WHEN 'AN' THEN 2003
WHEN 'AT' THEN 92
WHEN 'BF' THEN -2
WHEN 'BO' THEN 2004
WHEN 'BV' THEN -3
WHEN 'CF' THEN 1
WHEN 'CO' THEN 2005
WHEN 'CV' THEN 12
WHEN 'D' THEN 3
WHEN 'DA' THEN 91
WHEN 'F' THEN 6
WHEN 'GF' THEN 1
WHEN 'GV' THEN 12
WHEN 'I1' THEN -6
WHEN 'I2' THEN 5
WHEN 'I' THEN 4
WHEN 'I8' THEN -5
WHEN 'JN' THEN 1111
WHEN 'N' THEN 2
WHEN 'SZ' THEN 93
WHEN 'TS' THEN 93
WHEN 'TZ' THEN 92
WHEN 'UT' THEN (
CASE NULL
WHEN 'D' THEN 2001
WHEN 'S' THEN 2002 ELSE 1111
END)
WHEN 'XM' THEN 2009 ELSE 1111
END) AS INTEGER) ELSE NULL
END) ELSE NULL
END) AS SMALLINT) AS SOURCE_DATA_TYPE
FROM DBC.TABLESV t
JOIN DBC.COLUMNSV c
ON t.DatabaseName = c.DatabaseName
AND t.TableName = c.TableName
LEFT OUTER JOIN DBC.UDTInfo u
ON c.ColumnUDTName = u.TypeName
LEFT OUTER JOIN DBC.COLUMNSV BaseTypes
ON u.TypeKind = 'D'
AND 'SYSUDTLIB' = BaseTypes.DatabaseName (NOT CASESPECIFIC)
AND u.TypeName (NOT CASESPECIFIC) = BaseTypes.TableName (NOT CASESPECIFIC)
WHERE t.TableKind IN ('O','T','V')
AND t.DatabaseName (NOT CASESPECIFIC) LIKE TRIM(TRAILING FROM 'Q_TC0_KPE_KPI') (NOT CASESPECIFIC)
AND t.TableName (NOT CASESPECIFIC) LIKE TRIM(TRAILING FROM 'RET_ABBINAMENTO_CAMPO') (NOT CASESPECIFIC)
ORDER BY TABLE_SCHEM,
TABLE_NAME,
ORDINAL_POSITION;
Regarding the question: "Is there a way to modify the query generate by JDBC driver ?"
No, there is no may for the user to modify the query that is composed by the Teradata JDBC Driver for the DatabaseMetaData.getColumns method.
However, if you are the application developer, you can choose to compose and execute the Data Dictionary query in your application, rather than calling the DatabaseMetaData.getColumns method.
Regarding the question: "Why the driver use LIKE instead of '=' ?"
The behavior of the DatabaseMetaData.getColumns method is dictated by the JDBC API Specification.
In particular, the DatabaseMetaData.getColumns method is defined with a pattern arguments, so the application can use the percent-sign % and underscore _ wildcard characters for the LIKE predicate.
We are currently working on a Teradata JDBC Driver enhancement (JDBC RFC 161165) to examine the pattern arguments of the DatabaseMetaData.getColumns method, and compose the query with the equals = operator rather than the LIKE predicate if the pattern argument does not contain percent-sign % or underscore _ wildcard characters.
Thank you very much for your quick reply. Can you tell me when the JDBC RFC 161165 will be ready ?
regards,
Sorry, no, we do not yet have a scheduled availability date for JDBC RFC 161165.
JDBC RFC 161165 shipped to Teradata JDBC Driver 15.00.00.28 and Teradata JDBC Driver 15.10.00.07 on July 2, 2015.
However, it's common for database names, table names, and column names to include underscore characters, e.g. "RETAIL_SALES". An underscore character is a LIKE-predicate pattern wildcard character. The JDBC RFC 161165 feature can only use an equal operator ( = ) instead of a LIKE predicate when the search pattern does not include any LIKE-predicate pattern wildcard characters.
Therefore, in order to take advantage of the JDBC RFC 161165 feature, your application must either:
1. Change all pattern argument strings to prefix every LIKE-predicate pattern wildcard character with a backslash ( \ ) escape character.
or
2. Use the new feature provided by JDBC RFC 179996 "Connection parameter LITERAL_UNDERSCORE=ON/OFF for DatabaseMetaData pattern".
JDBC RFC 179996 shipped to Teradata JDBC Driver 15.10.00.14 on December 11, 2015.
Documentation for the LITERAL_UNDERSCORE connection parameter is available here: