RE: Function Based Index
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 11 Sep 2012 10:08:05 -0400
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
Other related posts:
