trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Qifan Chen <qifan.c...@esgyn.com>
Subject Re: Forcing join order
Date Fri, 15 Apr 2016 18:39:54 GMT
Hi Dave,

How about using CQD join_order_by_user 'on' instead?

--Qifan

On Fri, Apr 15, 2016 at 1:26 PM, Dave Birdsall <dave.birdsall@esgyn.com>
wrote:

> 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
>



-- 
Regards, --Qifan

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