You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "David Wayne Birdsall (JIRA)" <ji...@apache.org> on 2016/06/07 17:00:24 UTC
[jira] [Created] (TRAFODION-2037) Improve DDL concurrency
David Wayne Birdsall created TRAFODION-2037:
-----------------------------------------------
Summary: Improve DDL concurrency
Key: TRAFODION-2037
URL: https://issues.apache.org/jira/browse/TRAFODION-2037
Project: Apache Trafodion
Issue Type: Improvement
Components: sql-cmu
Affects Versions: 2.1-incubating
Environment: All
Reporter: David Wayne Birdsall
Assignee: David Wayne Birdsall
In CmpSeabaseDDL::getSeabaseUserTableDesc (core/sql/sqlcomp/CmpSeabaseDDLtable.cpp), the code executes the following query:
select trim(O.catalog_name || '.' || '\"' || O.schema_name || '\"' || '.' || '\"' || O.object_name || '\"' ) constr_name, trim(O2.catalog_name || '.' || '\"' || O2.schema_name || '\"' || '.' || '\"' || O2.object_name || '\"' ) table_name from %s.\"%s\".%s U, %s.\"%s\".%s O, %s.\"%s\".%s O2, %s.\"%s\".%s T where O.object_uid = U.foreign_constraint_uid and O2.object_uid = T.table_uid and T.constraint_uid = U.foreign_constraint_uid and U.unique_constraint_uid = %Ld order by 2, 1
The plan for this query does a full scan of TABLE_CONSTRAINTS, and joins that to OBJECTS_UNIQ_IDX. So all rows of TABLE_CONSTRAINTS are read, and many if not most rows of OBJECTS_UNIQ_IDX.
Analyzing the query plan, the full scan is inherent. The only known information we have for TABLE_CONSTRAINTS is CONSTRAINT_UID, which is the second column of the key. The first column has high UEC so MDAM is not a possibility.
Creating this large read set conflicts with many write transactions to metadata, decreasing DDL concurrency.
As an experiment, I added an index to the metadata on TABLE_CONSTRAINTS(CONSTRAINT_UID). I found I had to add a CQS as well to force it to avoid a full scan. With this change, I found that DDL concurrency was much improved. So, the proposal in this JIRA is to add this index and CQS.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)