trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dave Birdsall <dave.birds...@esgyn.com>
Subject Forcing join order
Date Fri, 15 Apr 2016 18:26:49 GMT
Hi,



I’m looking at the following query, which is found in the DDL code in
method CmpSeabaseDDL::getSeabaseUserTableDesc (the version here is slightly
edited, to fill in text that is supplied by the method):



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 UNIQUE_REF_CONSTR_USAGE U, OBJECTS O, OBJECTS O2, TABLE_CONSTRAINTS 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 = 4795865420607325863 order by 2, 1;



When I manually prepare this query in sqlci and look at it’s plan, I get
the following:



>>set schema trafodion."_MD_";



--- SQL operation complete.

>>

>>prepare s1 from

+>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 UNIQUE_REF_CONSTR_USAGE U,
OBJECTS O, OBJECTS O2, TABLE_CONSTRAINTS 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 = 4795865420607325863 order by 2, 1;



--- SQL command prepared.

>>

>>explain options 'f' s1;



LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD

---- ---- ---- --------------------  --------  --------------------
---------



11   .    12   root
5.00E+002

8    10   11   nested_join
5.00E+002

9    .    10   probe_cache
    5.00E+000

.    .    9    trafodion_vsbb_scan             UNIQUE_REF_CONSTR_US
5.00E+000

7    .    8    sort
1.00E+002

4    6    7    nested_join
1.00E+002

5    .    6    probe_cache
1.00E+000

.    .    5    trafodion_index_scan            OBJECTS_UNIQ_IDX
1.00E+000

1    3    4    nested_join
1.00E+002

2    .    3    probe_cache
1.00E+000

.    .    2    trafodion_index_scan            OBJECTS_UNIQ_IDX
1.00E+000

.    .    1    trafodion_scan                  TABLE_CONSTRAINTS
1.00E+002



--- SQL operation complete.

>>

>>showshape

+>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 UNIQUE_REF_CONSTR_USAGE U,
OBJECTS O, OBJECTS O2, TABLE_CONSTRAINTS 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 = 4795865420607325863 order by 2, 1;

=

control query shape nested_join(sort(nested_join(nested_join(

scan(TABLE 'T', path 'TRAFODION."_MD_".TABLE_CONSTRAINTS', forward

, blocks_per_access 1 , mdam off),

scan(TABLE 'O2', path 'TRAFODION."_MD_".OBJECTS_UNIQ_IDX', forward

, blocks_per_access 2 , mdam off)),

scan(TABLE 'O', path 'TRAFODION."_MD_".OBJECTS_UNIQ_IDX', forward

, blocks_per_access 2 , mdam off))),

scan(TABLE 'U', path 'TRAFODION."_MD_".UNIQUE_REF_CONSTR_USAGE', forward

, blocks_per_access 1 , mdam off));



--- SQL operation complete.



Now, this is an atrocious plan from a concurrency standpoint. We are doing
a full scan of TABLE_CONSTRAINTS, with no key values or executor
predicates. Then we do two nested joins into OBJECTS (using index-only
scans of OBJECTS_UNIQ_IDX index). These are unique-key accesses, but since
we are reading ALL of the constraints, we are going to touch an awful lot
of rows. Finally, we join the result to UNIQUE_REF_CONSTR_USAGE, using a
unique access. Here, and only here, do we benefit from reduction due to the
constant supplied in the where predicate.



I have been debugging some issues with DDL concurrency, and I think this
unnecessary inclusion of the entire TABLE_CONSTRAINTS table and a large
part of OBJECTS_UNIQ_INDX in the read set of a transaction that is doing
writes elsewhere may be limiting that concurrency.



What I’d like to do is force a plan where we read UNIQUE_REF_CONSTR_USAGE
as the outer-most table. Our constant predicate in the WHERE clause covers
the leading key of this table. I’ve made some attempts using CONTROL QUERY
SHAPE to do this but no luck so far. For example:



>>set schema "_MD_";



--- SQL operation complete.

>>

>>-- let's try forcing UNIQUE_REF_CONSTR_USAGE first

>>control query shape
join(join(join(scan('U','UNIQUE_REF_CONSTR_USAGE'),cut),cut),cut);



--- SQL operation complete.

>>

>>prepare s2 from

+>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 UNIQUE_REF_CONSTR_USAGE U, OBJECTS O, OBJECTS O2, TABLE_CONSTRAINTS
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 = 4795865420607325863 order by 2, 1;



*** ERROR[2105] This query could not be compiled because of incompatible
Control Query Shape (CQS) specifications. Inspect the CQS in effect.



*** ERROR[8822] The statement was not prepared.



>>



But so far after many tries, I haven’t been able to find a magic CQS. Is
there another way to influence join order?



Thanks,



Dave

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message