2 Replies Latest reply: Mar 26, 2012 2:51 AM by Paul Horth RSS

    trunc causing Full Table Scans

    825925
      I have a situtaion here where my query is as follows.

      SQL> select count(1) from HBSM_SM_ACCOUNT_INFO where OPTIONAL_PARM5='MH' and CUST_STATUS in ('UP','UUP') and trunc(FIRST_ACTVN_DATE) = trunc(sysdate);

      COUNT(1)
      ----------
      6

      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 3951750498

      ---------------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
      ---------------------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 10 | 13904 (1)| 00:02:47 | | |
      | 1 | SORT AGGREGATE | | 1 | 10 | | | | |
      | 2 | PARTITION LIST SINGLE| | 1 | 10 | 13904 (1)| 00:02:47 | 12 | 12 |
      |* 3 | TABLE ACCESS FULL | HBSM_SM_ACCOUNT_INFO | 1 | 10 | 13904 (1)| 00:02:47 | 12 | 12 |
      ---------------------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      3 - filter(("CUST_STATUS"='UP' OR "CUST_STATUS"='UUP') AND
      TO_DATE(INTERNAL_FUNCTION("FIRST_ACTVN_DATE"))=TO_DATE(TO_CHAR(SYSDATE@!)))

      16 rows selected.


      If I remove the trunc clause from the query the performance definitely improves the the results are wrong.

      SQL> select count(1) from HBSM_SM_ACCOUNT_INFO where OPTIONAL_PARM5='MH' and CUST_STATUS in ('UP','UUP') and FIRST_ACTVN_DATE = trunc(sysdate);

      COUNT(1)
      ----------
      0


      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      Plan hash value: 454529511

      ---------------------------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
      ---------------------------------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 40 | 47 (0)| 00:00:01 | | |
      |* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| HBSM_SM_ACCOUNT_INFO | 1 | 40 | 47 (0)| 00:00:01 | 12 | 12 |
      |* 2 | INDEX RANGE SCAN | IND_FIRST_ACTVN_DATE | 51 | | 4 (0)| 00:00:01 | | |
      ---------------------------------------------------------------------------------------------------------------------------


      Can someone please help me whereby I can get the right data and I can also prevent these full table scans.
        • 1. Re: trunc causing Full Table Scans
          Paul  Horth
          Unless you are using a functional index, applying any function to an indexed column prevents the use of the index.

          The way round it in your case is to realise that
          select count(1) from HBSM_SM_ACCOUNT_INFO where OPTIONAL_PARM5='MH' and CUST_STATUS in ('UP','UUP') and trunc(FIRST_ACTVN_DATE) = trunc(sysdate)
          Is really asking that FIRST_ACTVN_DATE should be sometime today. You could therefore rewrite it as
          select count(1) from HBSM_SM_ACCOUNT_INFO where OPTIONAL_PARM5='MH' and CUST_STATUS in ('UP','UUP')
          and FIRST_ACTVN_DATE >= trunc(sysdate)
          and FIRST_ACTVN_DATE < trunc(sysdate) + 1
          Note, this still might not use the index depending on how many rows are within today's date versus how many are outside today's date.

          Also, when posting, remember to put your code between
           tags and to post create table scripts and sample data inserts.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: trunc causing Full Table Scans
            825925
            Hello Paul,

            That really helped thank you.

            Cheers!!!!!!