Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle index storage

Re: Oracle index storage

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 20 Oct 1998 20:43:21 GMT
Message-ID: <363ff40f.116735136@192.86.155.100>


A copy of this was sent to tofran_at_hotmail.com (if that email address didn't require changing) On Tue, 20 Oct 1998 09:59:56 GMT, you wrote:

>Can I get some info/help about how Oracle treats index storage ?
>
>My problem is that I have a big table with heavy inserts and deletes and my
>only unique index on that table is constantly growing, so I have to make a
>reorganisation on that index often.
>

(you are using alter index rebuild to do it fast right?)

>I suspect that deleted rows only are marked as deleted in index as the index
>grows in extents despite of the deletes I make on it.
>
>Is there any methods to reuse the storage in index that are deleted ?
>

You might want to see a thread we had going in here a year ago about this very topic. Check out

http://www.dejanews.com/dnquery.xp?search=thread&recnum=%3c346f7df4.91847219@newshost%3e%231/3&svcclass=dnserver

I searched on dejanews using powersearch with:

subject = fragmentation in oracle
forum = comp.databases.oracle
author = tkyte_at_us.oracle.com
date from/to: Nov 1 1997 Nov 30 1997

Basically, in an index, if you delete space we will not reuse it except for the same key value (but we can give space back to the index over time).... So yes -- index space can be reused AND index space can be reclaimed.

Lets say you are getting 3 key values per block. Say also that Block 1 has the values 1, 2, 3. If you delete the row with the value 2, then block 1 will have the value 2 on it deleted *but* the only value that can be put back there will be the number 2 (so until another row with the number 2 comes along, 1 and 3 will be the only values on this particular block.. So, thats how we can reuse space -- by reusing those values.

Now, lets say you delete 1 and 3 at some time -- we can reuse this block. Thats how we can reclaim space -- by deleting colocated entries.

You might be interest in one specific post in this thread: http://www.dejanews.com/getdoc.xp?AN=288550578 . In that 3 cases are discussed and how space is utilized by the index is looked at... It might answer some of your questions.

>NOTE!! I can not use update as it is new data with different key I get
>constantly.
>
>I will appriciate any comments and thanks in advance.
>
>Best Regards
>Tom Frank
>tofran_at_hotmail.com
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Oct 20 1998 - 15:43:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US