Re: Function Based Index

From: Purav Chovatia <puravc_at_gmail.com>
Date: Fri, 14 Sep 2012 01:00:42 +0530
Message-ID: <CADrzpjHC9WAEwj+s2BqY5RzzAmQjP0fpn_2KKnWCc68zz1_QyA_at_mail.gmail.com>

In my reply to Stephano, I had mentioned that for once I did see CBO choosing the plan with index range scan. I had created the FBI on 7th Sep and after creating it, when I tried to verify that indeed uses the index,I had tried explain plan and this is what I had seen (I connect to server using putty and putty logging is always enabled and hence could mine out from the putty logs):
SQL> explain plan for Delete FROM ixxx_mxxxxxxxxx_mxxxxxx WHERE (trunc(RECTIMESTAMP) < trunc(sysdate) - 30) and rownum<1000; Explained.

SQL> set lines 130
set head off
--alter session set cursor_sharing=EXACT; select plan_table_output from
table(dbms_xplan.display('PLAN_TABLE',null,'ALL')); SQL> SQL> SQL>
Plan hash value: 3246626243

---

| Id  | Operation                     | Name                         | Rows
 | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------


|   0 | DELETE STATEMENT              |                              |

999 | 29970 |  7627   (1)| 00:01:32 |


|   1 |  DELETE                       | ixxx_mxxxxxxxxx_mxxxxxx      |

  |       |            |          |
|*  2 |   COUNT STOPKEY               |                              |
  |       |            |          |


|   3 |    TABLE ACCESS BY INDEX ROWID| ixxx_mxxxxxxxxx_mxxxxxx      |

690K|    19M|  7627   (1)| 00:01:32 |
|*  4 |     INDEX RANGE SCAN          | ixxx_mxxxxxxxxx_mxxxxxx_IDX2 |
124K|       |    83   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

---

   1 - DEL$1
   3 - DEL$1 / ixxx_mxxxxxxxxx_mxxxxxx_at_DEL$1
   4 - DEL$1 / ixxx_mxxxxxxxxx_mxxxxxx_at_DEL$1

Predicate Information (identified by operation id):

---

   2 - filter(ROWNUM<1000)
   4 - access(TRUNC(INTERNAL_FUNCTION("RECTIMESTAMP"))<TRUNC(SYSDATE_at_!)-30)

Column Projection Information (identified by operation id):

---

   2 - (cmp=4) "ixxx_mxxxxxxxxx_mxxxxxx".ROWID[ROWID,10],

       "ixxx_mxxxxxxxxx_mxxxxxx"."MSISDN"[VARCHAR2,16], "ixxx_mxxxxxxxxx_mxxxxxx"."IMSI"[NUMBER,22],

       "RECTIMESTAMP"[DATE,7],
TRUNC(INTERNAL_FUNCTION("RECTIMESTAMP"))[DATE,7], ROWNUM[4]    3 - "ixxx_mxxxxxxxxx_mxxxxxx".ROWID[ROWID,10], "ixxx_mxxxxxxxxx_mxxxxxx"."MSISDN"[VARCHAR2,16],

       "ixxx_mxxxxxxxxx_mxxxxxx"."IMSI"[NUMBER,22], "RECTIMESTAMP"[DATE,7],
       TRUNC(INTERNAL_FUNCTION("RECTIMESTAMP"))[DATE,7]
   4 - "ixxx_mxxxxxxxxx_mxxxxxx".ROWID[ROWID,10],

TRUNC(INTERNAL_FUNCTION("RECTIMESTAMP"))[DATE,7] 35 rows selected.

Next day morning when I was in office and I checked to see how long did it take during the nightly cleanup, I found it had taken long and eventually figured out that it chose FTS. And since then, explain plan and all other such attempts have been showing FTS.

Thanks.

On Wed, Sep 12, 2012 at 12:20 AM, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> wrote:

> ----- Original Message -----
> From: "Mark W. Farnham" <mwf_at_rsiz.com>
> To: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org>
> Sent: Tuesday, September 11, 2012 3:08 PM
> Subject: RE: Function Based Index
>
>
> |I look forward to Jonathan's posting. In the meantime, it seems likely to
> me
> | the bug will not exhibit if you rework the code as
> |
>
> Done,
>
>    http://jonathanlewis.wordpress.com/2012/09/11/fbi-delete/
>
> I can't claim that it explains this specific example - but it certainly
> look relevant and may give some clues.
>
> Regards
>
> Jonathan Lewis
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 13 2012 - 14:30:42 CDT