Re: Function Based Index
- From: Purav Chovatia <puravc@xxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Fri, 14 Sep 2012 00:21:07 +0530
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@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<1000)
3 - access(TRUNC(INTERNAL_FUNCTION("RECTIMESTAMP"))<TRUNC(SYSDATE@!)-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@DEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<1000)
3 - filter(TRUNC(INTERNAL_FUNCTION("RECTIMESTAMP"))<TRUNC(SYSDATE@!)-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@xxxxxxxxx> wrote:
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
