Re: Function Based Index
- From: Purav Chovatia <puravc@xxxxxxxxx>
- To: mwf@xxxxxxxx
- Date: Fri, 14 Sep 2012 00:49:18 +0530
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@!)-30)
26 rows selected.
SQL>
On Tue, Sep 11, 2012 at 7:38 PM, Mark W. Farnham <mwf@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Jonathan Lewis
> Sent: Tuesday, September 11, 2012 8:49 AM
> To: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxx>
> To: "Stefano Cislaghi" <s.cislaghi@xxxxxxxxx>
> Cc: <Brandon.Allen@xxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
> 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@xxxxxxxxx>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@xxxxxxxxxxx>
> | > 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.
> | > > --
> | > > //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.
> | >
> |
> |
> | --
> | //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
> |
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
