1 Reply Latest reply: Jul 31, 2012 10:00 AM by Gennady Sigalaev RSS

    Problem in Index Scan in Timesten

    user10366531
      I m cheking Explain plan for below query in timesten,

      explain plan for UPDATE TBLTCUSTOMERRESERVATION SET CLOSINGREMARK = 0 where RESERVATIONIDENTIFIER='loadtest_onlymonetory8370' and RATINGGROUPID ='' AND RATINGSERVICEID = '';

      Query Optimizer Plan:

      STEP: 1
      LEVEL: 1
      OPERATION: RowLkSerialScan
      TBLNAME: TBLTCUSTOMERRESERVATION
      IXNAME: <NULL>
      INDEXED CONDITION: <NULL>
      NOT INDEXED: FALSE


      STEP: 2
      LEVEL: 1
      OPERATION: RowLkUpdate
      TBLNAME: TBLTCUSTOMERRESERVATION
      IXNAME: <NULL>
      INDEXED CONDITION: <NULL>
      NOT INDEXED: <NULL>


      While passing the values instead of null in below query :

      explain plan for UPDATE TBLTCUSTOMERRESERVATION SET CLOSINGREMARK = 0 where RESERVATIONIDENTIFIER='loadtest_onlymonetory8370' and RATINGGROUPID = 0 AND RATINGSERVICEID = 0;

      Query Optimizer Plan:

      STEP: 1
      LEVEL: 1
      OPERATION: RowLkRangeScan
      TBLNAME: TBLTCUSTOMERRESERVATION
      IXNAME: IDX_GRP_RATEID
      INDEXED CONDITION: TBLTCUSTOMERRESERVATION.RESERVATIONIDENTIFIER = 'loadtest_onlymonetory8370' AND TBLTCUSTOMERRESERVATION.RATINGGROUPID = 0 AND TBLTCUSTOMERRESERVATION.RATINGSERVICEID = 0
      NOT INDEXED: <NULL>


      STEP: 2
      LEVEL: 1
      OPERATION: RowLkUpdate
      TBLNAME: TBLTCUSTOMERRESERVATION
      IXNAME: <NULL>
      INDEXED CONDITION: <NULL>
      NOT INDEXED: <NULL>


      kindly help me why it is not scanning indexes when we are passing values in "WHERE" clause as null or '';
        • 1. Re: Problem in Index Scan in Timesten
          Gennady Sigalaev
          Hi user10366531,

          First of all, your first query will not work properly, because of null values.
          UPDATE TBLTCUSTOMERRESERVATION SET CLOSINGREMARK = 0 where RESERVATIONIDENTIFIER='loadtest_onlymonetory8370' and RATINGGROUPID ='' AND RATINGSERVICEID = '';
                                                                                                                 ||
          UPDATE TBLTCUSTOMERRESERVATION SET CLOSINGREMARK = 0 where RESERVATIONIDENTIFIER='loadtest_onlymonetory8370' and RATINGGROUPID =null AND RATINGSERVICEID = null;
          Secondly, almost all relational databases (not only TimesTen and Oracle DB) ignore NULL values, because index structure doesnt store NULL values.
          There is one way to resolve this task - it is using function based index (with NVL function for instance). Unfortunately, I dont have an opportunity to test it in TimesTen.
          In Oracle DB you can use the following:
          create index test_null_idx on emp  (nvl(name,'null'));
          regards,
          Gennady