Hi Dave, How about using CQD join_order_by_user 'on' instead?
--Qifan On Fri, Apr 15, 2016 at 1:26 PM, Dave Birdsall <[email protected]> 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
