| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Simple SQL waiting on 'log file sync'
There's an article on my website that
mentions the idea, with a schematic of
the state an index can get into.
FIFO - first in first out.
We insert trades (say) into a table,
and want to ensure that trades are
processed in the order they appear,
so we have a flag showing the state
of the trade and a time_stamp, and
create an index
(flag, time_stamp)
Our processor has a query:
select from trades where
flag = 'A' order by time_stamp
That's the basic index for a FIFO
Some trades cannot be processed, and get left in the index - so you end up with an index that looks like the one in the article - nearly empty everywhere, but packed in the recent past.
A regular coalesce minimises the side-effects
You can use function-based indexes in these circumstances as a good way of reducing the problem and minimising the work and size of index.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 4th Nov 2005
Sorry to bother you with my questions. A last question, could you
please elobrate about your last point:
(In this respect, it's a bit like
> the index that is being used as a FIFO queue,
> which is the index most likely to degenerate
> into 99% empty with most of it's data in 1%
> of the leaf blocks).
Just elobration of FIFO queue.
On 11/24/05, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Given that we are currently guessing that
> the problem is an unlucky pattern of empty
> blocks appearing on the truncate, you might
> try to do a coalesce on the index that causes
> the problem immediately after the truncate.
>
> The 'maintain global indexes' simply does
> a bulk delete (like an sql*load in reverse)
> from the index, it doesn't rebuild it. And this
> is why you can get the empty blocks.
>
> It's important to note that the problem ISN'T
> just the truncate/maintain - it is also an unlucky
> side-effect of the nature of the index that is
> being maintained. (In this respect, it's a bit like
> the index that is being used as a FIFO queue,
> which is the index most likely to degenerate
> into 99% empty with most of it's data in 1%
> of the leaf blocks).
>
>
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> Cost Based Oracle: Fundamentals
>
> http://www.jlcomp.demon.co.uk/appearances.html
> Public Appearances - schedule updated 4th Nov 2005
>
> ----- Original Message -----
> From: "The Human Fly" <sjaffarhussain_at_gmail.com>
> To: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
> Cc: "oracle-l" <oracle-l_at_freelists.org>
> Sent: Thursday, November 24, 2005 9:13 AM
> Subject: Re: Simple SQL waiting on 'log file sync'
>
>
> You are 100% correct in your advice. I felt the same. Can you please
> suggest me any other workaround to achieve the same, I mean to bypass
> this oddity sequences what I am following.
> It would be a great help and great relieaf for me, if you could give
> any workaround.
> Otherwise, should I include index coalesce as a practice after all the
> prior sequences done?
> My question is, when I am truncating partitions and updating global
> indexes, wont that I am rebuilding them? If so, index still left with
> empty leafs?
>
> Thanks for your time Jonathan.
>
>
>
>
-- Best Regards, Syed Jaffar Hussain OCP 8i & 9i DBA, Banque Saudi Fransi, Saudi Arabia ---------------------------------------------------------------------------------- "Winners don't do different things. They do things differently." -- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 24 2005 - 03:49:32 CST
 |
 |