Re: Function Based Index
Date: Fri, 14 Sep 2012 00:21:07 +0530
Message-ID: <CADrzpjFSSiW11TRzp3qX1yKd6pPH7ztMsy3zQN2kfV+1P7Zh3A_at_mail.gmail.com>
Tim,
Actually that 200k is out of 13million rows. I think thats not too much especially when the indexed column is a date column. Also, very importantly, at a time it deletes only 999 rows (so deletes in a loop).
I agree that DML is a lot different then qqueries but this is at midnight when the system is very silent. Also, if it actually does an index range scan then it has to do less IO and not 500k of IO. Also, hardly any physical IO because the table is in the KEEP pool (may be that is why it prefers FTS over IRS).
I have tried forcing index usage via hint and the delete completes in 2 minutes (with FTS it takes 20 minutes).
But yes, if I do an explain plan with and without a hint, the CBO works out the plan with index as costly!
As suggested by you, tried getting plan for select and for select it does come up with a plan that uses the index:
SQL> explain plan for select count(*) from ixxx_mxxxxxxxxx_mxxxxxx where trunc(rectimestamp) < (trunc(sysdate)-30) and rownum < 1000;
Explained.
SQL> select plan_table_output from
table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
Plan hash value: 333948679
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 8184 (1)| 00:01:39 | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | COUNT STOPKEY | | | | | |
|* 3 | INDEX RANGE SCAN| ixxx_mxxxxxxxxx_mxxxxxx_IDX2 | 204K| 1598K| 8184 (1)| 00:01:39 |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$1
3 - SEL$1 / ixxx_mxxxxxxxxx_mxxxxxx_at_SEL$1
Predicate Information (identified by operation id):
2 - filter(ROWNUM<1000)
3 - access(TRUNC(INTERNAL_FUNCTION("RECTIMESTAMP"))<TRUNC(SYSDATE_at_!)-30)
Column Projection Information (identified by operation id):
1 - (#keys=0) COUNT(*)[22]
27 rows selected.
SQL> explain plan for delete from ixxx_mxxxxxxxxx_mxxxxxx where trunc(rectimestamp) < (trunc(sysdate)-30) and rownum < 1000;
Explained.
SQL> select plan_table_output from
table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
Plan hash value: 2818768165
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 999 | 30969 | 36273 (3)| 00:07:16 | | 1 | DELETE | ixxx_mxxxxxxxxx_mxxxxxx | | | | | |* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| ixxx_mxxxxxxxxx_mxxxxxx | 204K| 6195K| 36273 (3)| 00:07:16 |
Query Block Name / Object Alias (identified by operation id):
1 - DEL$1
3 - DEL$1 / ixxx_mxxxxxxxxx_mxxxxxx_at_DEL$1
Predicate Information (identified by operation id):
2 - filter(ROWNUM<1000)
3 - filter(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],
ROWNUM[4]
3 - "ixxx_mxxxxxxxxx_mxxxxxx".ROWID[ROWID,10],
"ixxx_mxxxxxxxxx_mxxxxxx"."MSISDN"[VARCHAR2,16],
"ixxx_mxxxxxxxxx_mxxxxxx"."IMSI"[NUMBER,22], "RECTIMESTAMP"[DATE,7]
32 rows selected.
SQL> On Tue, Sep 11, 2012 at 5:40 PM, Tim Gorman <tim_at_evdbt.com> wrote:
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 13 2012 - 13:51:07 CDT