1 Reply Latest reply: Oct 16, 2012 11:28 AM by Chrisjenkins-Oracle RSS

    slow query

    user11979167
      Hello,

      A query generated by Hibernate is very slow on Timesten 11.2.2.

      Here is a simplified version of the query.
      It seems that the optimizer cannot push "t.id2=a.id" predicate inside the sub-query.
      So instead of using the index on t1 and t2, it builds a temporary table containing all values from t1 and t2.

      I tried updating the stats with ttOptUpdateStats, but it didn't help.
      Do you have any suggestions to get a better execution plan?
      Or is this kind of query too complex for Timesten?
      Table TIMESTEN.T:
        Columns:
         *ID1                             TT_BIGINT NOT NULL
         *ID2                             TT_BIGINT NOT NULL
      
      Table TIMESTEN.T1:
        Columns:
         *ID                              TT_BIGINT NOT NULL
          BLA                             VARCHAR2 (20) INLINE
      
      Table TIMESTEN.T2:
        Columns:
         *ID                              TT_BIGINT NOT NULL
          BLA                             VARCHAR2 (20) INLINE
      
      select t.id2,a.id,a.bla
      from   t,
             (
                select id,bla from t1
                union
                select id,bla from t2
             ) a
      where t.id1=40
      and   t.id2=a.id;
      
      Query Optimizer Plan:
      
        STEP:                1
        LEVEL:               2
        OPERATION:           RowLkRangeScan
        TBLNAME:             T
        IXNAME:              T
        INDEXED CONDITION:   T.ID1 = 40
        NOT INDEXED:         <NULL>
      
        STEP:                2
        LEVEL:               3
        OPERATION:           TblLkRangeScan
        TBLNAME:             T1
        IXNAME:              T1
        INDEXED CONDITION:   <NULL>
        NOT INDEXED:         <NULL>
      
        STEP:                3
        LEVEL:               2
        OPERATION:           OrderBy
        TBLNAME:             <NULL>
        IXNAME:              <NULL>
        INDEXED CONDITION:   <NULL>
        NOT INDEXED:         <NULL>
      
        STEP:                4
        LEVEL:               3
        OPERATION:           TblLkRangeScan
        TBLNAME:             T2
        IXNAME:              T2
        INDEXED CONDITION:   <NULL>
        NOT INDEXED:         <NULL>
      
        STEP:                5
        LEVEL:               2
        OPERATION:           OrderBy
        TBLNAME:             <NULL>
        IXNAME:              <NULL>
        INDEXED CONDITION:   <NULL>
        NOT INDEXED:         <NULL>
      
        STEP:                6
        LEVEL:               3
        OPERATION:           UnionMergeSort
        TBLNAME:             <NULL>
        IXNAME:              <NULL>
        INDEXED CONDITION:   <NULL>
        NOT INDEXED:         <NULL>
      
        STEP:                7
        LEVEL:               2
        OPERATION:           TmpTable
        TBLNAME:             <NULL>
        IXNAME:              <NULL>
        INDEXED CONDITION:   <NULL>
        NOT INDEXED:         T.ID2 = A.ID
      
        STEP:                8
        LEVEL:               1
        OPERATION:           NestedLoop
        TBLNAME:             <NULL>
        IXNAME:              <NULL>
        INDEXED CONDITION:   <NULL>
        NOT INDEXED:         <NULL>
        • 1. Re: slow query
          Chrisjenkins-Oracle
          I suspect that we don't do that optimisation as yet (I'm looking into that). This version of the query has a better plan:

          select t.id2, t1.id, t1.bla
          from t, t1
          where t.id1 = 40
          and t.id2 = t1.id
          union
          select t.id2, t2.id, t2.bla
          from t, t2
          where t.id1 = 40
          and t.id2 = t2.id;

          Chris