fragmentation
Baqir Hussain, December 22, 2002 - 10:52 pm UTC
Tom,
I 100% agree with you on rebuilding indexes does not help at all and I stopped doing it. I gather statistics on a table in every two weeks due to more than 10% of DML activity. One of the 5 indexes shows "blevel high" and cost and elapsed both go high. I tried to rebuild it but still showing "blevel high" and query was taking more elapsed time with high cost. Then I have to drop and recreate it and the particular query started to working fine. I have few question:
1. Please explain why I am getting this kind of behavior?
2. What is the best way to drop and recreate indexes by keeping in mind that the tablespace would not grow exponentially?
3. Since the tablespace has grown too much due to indexes rebuilding. How can I fix this problem to get the tablespace into its actuall size??
Appreciate your help in this regard.
December 23, 2002 - 11:10 am UTC
Don't know what to tell you as I cannot reproduce (well, actually, was having a really hard time getting a blevel of 5 -- couldn't get it).
The rebuild would take the blevel down -- so I cannot explain that, perhaps you did not reanalyze the index? rebulid leaves the stats intact.
if you drop and create - the tablespace will not grow, it is when you rebuild and for a moment have 2 of them that it will.
if you alter index rebuild NEW_TABLESPACE -- for each index (effectively moving them), you'll be able to drop this tablespace, although -- why bother? they are just going to grow again!
indexes rebuild
Baqir Hussain, December 23, 2002 - 5:15 pm UTC
Tom,
There is almost 10GByte of waste space,therefore, I would like to move to a new tablespace as
alter index <index_name> rebuild tablespace <new_tablespace>;
My question is:
1. Do I have to gather statistics on a table which indexes have been moved to new tablespace??
Thanks
December 23, 2002 - 6:35 pm UTC
you can if you like -- else the old stats will be in place. you can alter index rebuild compute statistics all in one step.
Bitmap Index in a LMT [Oracle9i Rel. 2 on Windows 2000]
Prince, November 15, 2003 - 3:26 am UTC
Respected Tom,
I have a table VERIFIC with 24,000,000 rows in it. There is a bitmap index VERIFIC_IS_USED_IDX on its column IS_USED [having data type CHAR(1)]. I have created a LMT named INDX02 with uniform extent allocation of size 80K and issued the following command:-
SQL> alter index VERIFIC_IS_USED_IDX
2 rebuild tablespace INDX02
3 storage (initial 80K)
4 nologging;
Result information from dba_segments is as under
OWNER = APPS1
SEGMENT_NAME = VERIFIC_IS_USED_IDX
INITIAL_EXTENT = 80K
NEXT_EXTENT = 80K
PCT_INCREASE = 0
EXTENTS = 117
BYTES = 9,584,640
This index of size 9.14MB has been extended to 800MB just after one day. I am really surprised to see the expansion behaviour of a bitmap index using LMT. I was mentaly prepared for some expansion but this rate is too high :). Now I am thinking if either there was any fault at my end like wrong initial extent size (i.e. 80K) or anything else?
This table was created 2 years before. What extent size do you recommend for such kind of index?
Regards,
November 15, 2003 - 9:09 am UTC
are you updating that column?
if so, you have just done the worst thing on the planet with regards to bitmaps. they are known to deteriorate rapidly when updated a row at a time. it is the very NATURE of the bitmap index for that to happen.
Now, you tell me -- why are you indexing this column. what ist he goal here? is it that you need to find one of "where is_used = 'Y' " or where is_used = 'N' rapidly?
if so, there is a much much better way. tell us the GOAL of this here index.
GOAL
Prince, November 15, 2003 - 10:25 am UTC
Well tom! good question. You're somehow right about the reason of index is to fasten the reporting on the base of this column. Moreover when I have tried to replicate this table asynchronously, replication errors occur like "No Data Found." so when I put the bitmap index on that column it says nothing and hence replication is working fine. yes this column is highly updateable and you are right possible flag values are Y and N. so what do you recommend now to fullfil both of the needs.
Regards,
November 15, 2003 - 11:37 am UTC
what do you mean by "fasten the reporting on the base of this column". what question do you ask of this data that requires this index (then I can tell you what you need to do)
this bitmap index could not in any way fix the "NO DATA FOUND" error with async replication -- that had nothing to do with anything.
To make fast the reporting based on this column
Prince, November 15, 2003 - 12:04 pm UTC
I mean to say.. Yes I feel need to find one of "where is_used = 'Y' " or where is_used = 'N' rapidly?
Secondly that column is also having a foreign key constraint on it. What do you say Tom! should I not index this column for a successful replication process too.
Thanks for your prompt response.
November 16, 2003 - 10:25 am UTC
just full scan then and stop when you find the first one? why do you need an index at all??
you have a foreign key on a Y/N column??? sounds like
is_used char(1) check (is_used in ('Y','N'))
would be more appropriate.
indexes have no bearing whatsoever on replication, none.
How to find Tablespace fragmentation?
A reader, November 21, 2003 - 5:47 am UTC
November 21, 2003 - 4:51 pm UTC
use LMT's (locally managed) and you'll never ever worry about it again.
Follow-up.
Kashif, April 22, 2004 - 8:38 pm UTC
Hi Tom,
You mentioned in one of your posts above:
"Now, you tell me -- why are you indexing this column. what ist he goal here?
is it that you need to find one of "where is_used = 'Y' " or where is_used = 'N'
rapidly?
if so, there is a much much better way. tell us the GOAL of this here index."
but I'm not sure if you did actually give the poster the better mechanism, was it the full scanning and stopping when the first occurrence is found? If so then how would that be achieved exactly? An example would be useful. Thanks.
Kashif
April 23, 2004 - 10:36 am UTC
they had a bitmap index
on a column
that was updated frequently
that is a recipe for disaster. They were never clear as to "what" they needed to do. a btree or just a full scan would find the first y/n record as fast as they needed - a bitmap index was just "not appropriate" given that they WRITE to the table so frequently. bitmaps are only appropriate in a read mostly environment (eg: a warehouse)