Re: Function Based Index
From: Purav Chovatia <puravc_at_gmail.com>
Date: Fri, 14 Sep 2012 00:49:18 +0530
Message-ID: <CADrzpjG-XMc8+WAcCxX_Qf5L4suJzz++=ztZOz7gjHCiE=C4Dg_at_mail.gmail.com>
SQL> delete
--+ gather_plan_statistics
from ixxx_mxxxxxxxxx_mxxxxxx
where rowid in
(select rowid from ixxx_mxxxxxxxxx_mxxxxxx where trunc(rectimestamp) < (trunc(sysdate) - 30) and rownum < 1000); 2 3 4 5 999 rows deleted.
delete --+ gather_plan_statistics from ixxx_mxxxxxxxxx_mxxxxxx where rowid in (select rowid from ixxx_mxxxxxxxxx_mxxxxxx where trunc(rectime stamp) <
Date: Fri, 14 Sep 2012 00:49:18 +0530
Message-ID: <CADrzpjG-XMc8+WAcCxX_Qf5L4suJzz++=ztZOz7gjHCiE=C4Dg_at_mail.gmail.com>
SQL> delete
--+ gather_plan_statistics
from ixxx_mxxxxxxxxx_mxxxxxx
where rowid in
(select rowid from ixxx_mxxxxxxxxx_mxxxxxx where trunc(rectimestamp) < (trunc(sysdate) - 30) and rownum < 1000); 2 3 4 5 999 rows deleted.
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'COST ALLSTATS LAST')); SQL_ID gpv9y26jksysc, child number 0
delete --+ gather_plan_statistics from ixxx_mxxxxxxxxx_mxxxxxx where rowid in (select rowid from ixxx_mxxxxxxxxx_mxxxxxx where trunc(rectime stamp) <
(trunc(sysdate) - 30) and rownum < 1000)
Plan hash value: 1050206636
| Id | Operation | Name |Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | DELETE STATEMENT | | 1 | | 8186 (100)| 0 |00:00:00.04 | 11602 | | |
|
| 1 | DELETE | ixxx_mxxxxxxxxx_mxxxxxx | 1 | | | 0 |00:00:00.04 | 11602 | | |
|
| 2 | NESTED LOOPS | | 1 | 1 | 8186 (1)| 999 |00:00:00.01 | 1473 | | |
|
| 3 | VIEW | VW_NSO_1 | 1 | 999 | 8184 (1)| 999 |00:00:00.01 | 6 | | |
|
| 4 | SORT UNIQUE | | 1 | 1 | | 999 |00:00:00.01 | 6 | 73728 | 73728 |
|
|* 5 | COUNT STOPKEY | | 1 | | | 999 |00:00:00.01 | 6 | | |
|
|* 6 | INDEX RANGE SCAN | ixxx_mxxxxxxxxx_mxxxxxx_IDX2 | 1 | 204K| 8184 (1)| 999 |00:00:00.01 | 6 | | |
|
| 7 | TABLE ACCESS BY USER ROWID| ixxx_mxxxxxxxxx_mxxxxxx | 999 | 1 | 1 (0)| 999 |00:00:00.01 | 1467 | | | | --------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
5 - filter(ROWNUM<1000)
6 - access("ixxx_mxxxxxxxxx_mxxxxxx"."SYS_NC00004$"<TRUNC(SYSDATE_at_!)-30)
26 rows selected.
SQL> On Tue, Sep 11, 2012 at 7:38 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
> 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 > > set linesize 140 pagesize 40 null ~; > delete > --+ gather_plan_statistics > from <your_table> > where rowid in > (select rowid from <your_table> where <fbi_text> = <true value cast as the > fbi return type if needed> and rownum < 1000); > > set linesize 140 pagesize 40 null ~; > select * from table(dbms_xplan.display_cursor(format=>'COST ALLSTATS > LAST')); > > rollback; > > and if you show us the results, we'll have more of a clue what you're > doing. > > The text of your create index for the fbi might also help. > > This approach to coding a delete usually is a decent sledge hammer to get > the CBO to use and index, since the index is the only reference and it > contains the rowid. > Your choice of a monolith size is another issue you may want to revisit. > > R, > > mwf > -----Original Message----- > From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] > On Behalf Of Jonathan Lewis > Sent: Tuesday, September 11, 2012 8:49 AM > To: oracle-l_at_freelists.org > Subject: Re: Function Based Index > > Purav, > > If you've got the execution plan it's a good idea to show it - it can make > it easier to explain the problem. > As it is, I've just run up a little test case - I'll be writing it up some > time this evening probably- that suggests it's a bug. > > A simple delete by index is usually costed simple as the cost of "select > rowid from table", but for a function-based index the operation "table > access by rowid" also appears in the plan. In some versions of Oracle this > operation is NOT costed (or given a cost of zero) so the delete is cheap, > in > other versions of Oracle it is costed, and can make the delete very > expensive. > > Regards > > Jonathan Lewis > http://jonathanlewis.wordpress.com/all_postings > > Author: Oracle Core (Apress 2011) > http://www.apress.com/9781430239543 > > ----- Original Message ----- > From: "Purav Chovatia" <puravc_at_gmail.com> > To: "Stefano Cislaghi" <s.cislaghi_at_gmail.com> > Cc: <Brandon.Allen_at_oneneck.com>; <oracle-l_at_freelists.org> > Sent: Tuesday, September 11, 2012 9:36 AM > Subject: Re: Function Based Index > > > | KEEP pool is configured for the instance. And the table's buffer_pool > | attribute is set to KEEP. > | Also, we use ALTER TABLE TABLE_NAME CACHE; > | Could that be the reason for either of the observations that I have made? > | > | The table has approx.14million rows. There are 3 columns and 1 of those > is > | a DateTime column which is updated with sysdate value if the record > exists > | else a new record is inserted with sysdate value in the DateTime column. > | Most of the times, the record exists and hence it results in an update. > | > | FBI is on the DateTime column. > | > | DML (expected to use the FBI): delete from <table_name> where > | trunc(datetime) < (trunc(sysdate) - 31) and rownum < 1000; > | > | Table contains data for last 31 days. The number of rows to be deleted is > | very small, say just over 200k. > | > | Pls let me know if I should provide any other info. > | > | Thanks > | > | On Mon, Sep 10, 2012 at 9:28 PM, Stefano Cislaghi > <s.cislaghi_at_gmail.com>wrote: > | > | > I agree. Anyway if you do not provide us more information and the > | > query anything we might say are only theory and guessworks. > | > > | > Ste > | > > | > On 10 September 2012 17:48, Allen, Brandon <Brandon.Allen_at_oneneck.com> > | > wrote: > | > > Maybe the CBO is choosing not to use the index because the percentage > of > | > blocks it thinks you will need is too high? I don't know exactly how > the > | > CBO calculates it, but the general rule of thumb is to only use an > index if > | > you're going to read less than 5% of the table's blocks. If the values > in > | > your look up column are skewed, you could try running the query with an > | > unpopular value such that Oracle will be more likely to use an index > (make > | > sure you flush the old plans out of the pool first), and then, if it > does > | > load the plan with the index, you can create a sql plan baseline to > make it > | > stick with that plan for future executions - that is assuming you're on > | > 11g; create a stored outline if you're on 10g. > | > > > | > > Regards, > | > > Brandon > | > > > | > > > | > > > | > > ________________________________ > | > > > | > > Privileged/Confidential Information may be contained in this message > or > | > attachments hereto. Please advise immediately if you or your employer > do > | > not consent to Internet email for messages of this kind. Opinions, > | > conclusions and other information in this message that do not relate to > the > | > official business of this company shall be understood as neither given > nor > | > endorsed by it. > | > > -- > | > > http://www.freelists.org/webpage/oracle-l > | > > > | > > > | > > | > > | > > | > -- > | > http://www.stefanocislaghi.eu > | > > | > The SQLServerAgent service depends on the MSSQLServer service, which > | > has failed due to the following error: The operation completed > | > successfully. > | > > | > | > | -- > | http://www.freelists.org/webpage/oracle-l > | > | > | > | > | ----- > | No virus found in this message. > | Checked by AVG - www.avg.com > | Version: 2012.0.2221 / Virus Database: 2437/5261 - Release Date: 09/10/12 > | > > -- > http://www.freelists.org/webpage/oracle-l > > > -- > http://www.freelists.org/webpage/oracle-l > > >
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 13 2012 - 14:19:18 CDT