This discussion is archived
1 Reply Latest reply: Oct 16, 2012 9:28 AM by ChrisJenkins RSS

slow query

user11979167 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points