Re: Function Based Index
- From: Purav Chovatia <puravc@xxxxxxxxx>
- To: Stefano Cislaghi <s.cislaghi@xxxxxxxxx>
- Date: Tue, 11 Sep 2012 14:06:45 +0530
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
Other related posts:
