Send the DBAs on courses or get news ones !
Jim, January 09, 2002 - 7:14 pm UTC
Ted,
Sounds like your DBAs need some training or you need
new ones. Tom made a VERY important note about
losing indexes.
One of the most common performance problems I fix for clients is a result of missing indexes.
I have seen indexes go missing during a drop and create by many DBAs but I have never had any go missing using the rebuild option.
Missing indexes can cause dramas and VERY VERY unhappy
users and management ... although it makes me look
really good when I can resolve a performance problem
very rapidly whilst their DBAs run for cover
Issues with rebuilding
Tony, January 09, 2002 - 7:52 pm UTC
This is regarding point 4.
I started using rebuilding indexes since 8.0.4 (NO LMTS). But reserving space for old and new became a big problem for me. The reason was that my PL/SQL package (with number of procedures) was automatically dropping the oldest partition and creating a new partition (rolling window), as such all global indexes became UNUSABLE, which is understandable. But when I started rebuilding them in the same package (different procedure - get all UNUSABLE indexes one by one and rebuild), the script start falling apart because of space issues. On following the problem with Oracle support, the answer was that in the same session though the new index is built, the space to be released by old index is not released till you come out of the session. Is it still true for 8i?
January 10, 2002 - 7:53 am UTC
It is not true for the SESSION:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from t;
17220 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from t;
34440 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from t;
68880 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop tablespace test_idx;
Tablespace dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM> host rm -f /tmp/test_idx.dbf
ops$tkyte@ORA817DEV.US.ORACLE.COM> create tablespace test_idx
2 datafile '/tmp/test_idx.dbf' size 512k
3 autoextend on
4 next 512k
5 extent management local uniform size 64k;
Tablespace created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx1 on t(owner,object_name,object_type,timestamp,status,created,last_ddl_time)
2 nologging tablespace test_idx;
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx2 on t(object_name,owner,object_type,timestamp,status,created,last_ddl_time)
2 nologging tablespace test_idx;
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx3 on t(object_name,object_type,owner,timestamp,status,created,last_ddl_time)
2 nologging tablespace test_idx;
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx4 on t(object_name,object_type,timestamp,owner,status,created,last_ddl_time)
2 nologging tablespace test_idx;
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx5 on t(object_name,object_type,timestamp,status,owner,created,last_ddl_time)
2 nologging tablespace test_idx;
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> @free 1
ops$tkyte@ORA817DEV.US.ORACLE.COM> -- free.sql
ops$tkyte@ORA817DEV.US.ORACLE.COM> --
ops$tkyte@ORA817DEV.US.ORACLE.COM> -- This SQL Plus script lists freespace
....
%
% MaxPoss Max
Tablespace Name KBytes Used Free Used Largest
---------------- ------------ ------------ ------------ ------ ------------
...
*TEST_IDX 72,192 72,064 128 99.8 128
.....
14 rows selected.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter index t_idx1 rebuild nologging;
Index altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter index t_idx2 rebuild nologging;
Index altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter index t_idx3 rebuild nologging;
Index altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter index t_idx4 rebuild nologging;
Index altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter index t_idx5 rebuild nologging;
Index altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> @free 1
% MaxPoss Max
Tablespace Name KBytes Used Free Used Largest ---------------- ------------ ------------ ------------ ------ ------------ *TEST_IDX 86,528 72,064 14,464 83.3 14,400
so, there the tablespace did not double.
Now, if I do it in a single call, eg:
begin
execute immediate 'alter index t_idx1 rebuild ...';
...
execute immediate 'alter index t_idx5 rebuild ...';
end;
/
then yes, you are correct, the space is not available to be reused until the completion of the call.
Early iterations of "REBUILD ONLINE"
Connor, January 10, 2002 - 4:08 am UTC
The DBA in question may have been scared off by early versions of 'rebuild online' which could be a little flakey (Metalnk Note:125149.1 being an example).
But I've never seen issues with plain ol' rebuild.
hth
Connor
review
Irfan, October 07, 2002 - 3:31 am UTC
I am using Locally managed tablespace oracle 9i. Recently
we inserted massive amount of data in the tables. We have
indexes associated with lot of tables index and tables are in the same tablespace.
Question 1. Is it nessary to rebuild indexes every time we
insert large amount of data?
Question 2. What is the full syntex of the command to rebuild the indexes?
Thanks
Review
Irfan, October 07, 2002 - 12:55 pm UTC
Thanks for reply, I hope that I am not irritating you. One last question.
So, Index rebuild is not required after a large amount of
insert or delete. Then in what circumstances do we actually
rebuild or recreate the indexes.
Thanks and regards.
October 07, 2002 - 1:08 pm UTC
Bitmap indexes -- more so then b*tree.
B*tree -- so very infrequently..... Lots less them many people might say (me, I have bigger fish to fry then rebuilding indexes on a recurring basis)...
I use it mostly to MOVE them from place to place.
rebuild index bypass cache?
A reader, October 08, 2002 - 7:24 pm UTC
Hi
When we rebuild index do we use buffer cache at all? If we do if we rebuild with nologging option will we bypass buffer cache and write them directly to datafiles like direct load?
October 09, 2002 - 6:45 am UTC
the index is rebuilt to temporary extents which will be written directly after being cached for a bit (sort areas and such).
it'll not "bypass" the cache, it'll read from the cache when necessary but the index itself is written to disk directly regardless of logging/nologging.
rebuilding indexes after massive load, dropping indexes
Andrew, October 09, 2002 - 12:11 pm UTC
Tom,
but when one loads a lot of data in the hevaily indexed table... isn't it sometimes advantageous to drop all indexes, load the data and recreate indexes again? because inserting a record in an indexed table will require the modification to an index as wwell and if there are millions of records then the overhead becomes bigger then rebuilding an index.
and of course the missing index is probably the single most common reason for performance troubles... But indexes don't disappear on its own. DBA has to be meticulous, have to run monitoring scripts daily, have to READ those reports daily, etc.
October 09, 2002 - 4:51 pm UTC
sure it is. it can be much faster (in fact, i demonstrated how to load into the table without maintaining the indexes -- they would need a rebuild then). that was much faster then maintaining the indexes during the load.
if you use the "unusable" trick -- you don't need to look for what is NOT THERE (hey, if you aren't here, raise your hand). But you can easily look for things that are there but UNUSABLE
but -- you do not necessarily need to rebuild after a load into an indexed table.
Index rebuild
kiro, October 11, 2002 - 3:22 am UTC
Hi Tom
Great job, but I have still one missing point.
Q1)When rebuild index extra space for new index is taken from INDEX TABLESPACE or from TEMP?
Q2)And for partitioned(local and global) index - if we rebuild entire index when system releases old space after every index partition was rebulid or when entire index was rebuild?
thanks
kiro
October 11, 2002 - 8:12 pm UTC
q1) from the tablespace the index will ultimately reside in. We allocate a temp segment in that tablespace -- put the index into it -- when done turn that "temp" segment into an "index" segment (better then building the thing in TEMP and them moving temp to "real", less IO and you need the space in the real tablespace anyway.
q2) after the entire process is done -- unless you rebuild the individual partitions that is
kiro, October 18, 2002 - 4:15 am UTC
Thanks Tom!
please expand on statement
David, December 04, 2002 - 9:29 am UTC
Tom,
Great thread. In your followup to "Andrew, from Calgary" you stated, "but -- you do not necessarily need to rebuild after a load into an indexed table". Could you expand on this (i.e. when would you not?).
Inserting a million rows into our 70 mill row table took 22 seconds (!!!) when dropping indexes before loading. It used to take 5 hours. However, I'm not clear on the best process to use (drop/recreate or unusable/rebuild).
December 04, 2002 - 12:22 pm UTC
You would not rebuild if you never disabled them. You need not disable indexes in order to load.
You might do a direct path load -- it'll maintain indexes for us at the end. No rebuild needed.
You might be adding 100,000 rows to a 10,000,000 row table. I would not disable indexes for that. No rebuild needed. Adding 1,000,000 rows to a 10,000,000 row table -- I would give it some serious thought (to disable, load, enable)
I like the unusable/rebuild for reasons stated above (the gist of the question was this point exactly)
further clarification
David, December 04, 2002 - 1:41 pm UTC
So, there are two good ways to approach a big load:
1. Mark index unusable
2. insert /*+ append */ into big_table
3. alter index rebuild? or mark index usable? If I don't rebuild will it have the new rows indexed?
*OR*
1. Drop index
2. insert /*+ append */ into big_table
3. Recreate index
This is what I am doing now, but the index creation takes almost three hours.
Am I on track?
December 04, 2002 - 4:10 pm UTC
there are an infinite number of good ways. two is way too limiting. And there are SO many things to consider that I cannot even begin to tell you which way to go without knowing your circumstances. Anyway...
if you set the index unusable -- you'll need to rebuild it (else they stay unusable -- not maintained -- forever)
the other one is the one I don't recommend for obvious reasons
But -- look, the rebuild will take 3 hours as well. Consider this -- it would take many times LONGER to load it with the index on see.
You can speed up the index rebuild/create via:
o parallel
o nologging (in an archive log mode database)
more detail
David, December 04, 2002 - 9:55 pm UTC
I am doing parallel and nologging. It seems that the slow part is a sort that takes place as the unique index is being built. I'm thinking this because when I monitor v$session_longops I see a message of "sort output x blocks of [about] 425,000 blocks". Also, v$session_waits shows a "db file scattered read" for this SID - sometimes as high as 15 seconds. Any thoughts on speeding this part up?
December 05, 2002 - 7:17 am UTC
increase sort_area_size if not already.
increase db_file_multi_block_read_count, if not at OS limits already.
make sure the data you are reading is spread out across multiple devices (else your PQ might just be introducing massive disk contention).
speed up your disk access -- db file scattered read is sequential IO (believe it or not). we are doing big reads and are being made to wait for them. 15 seconds -- that must be cumulative? I cannot imagine waiting 15 seconds for a disk read to return unless the disks are totally botched.
how the inded get missed during index rebuild
Ashiq, December 05, 2002 - 12:05 am UTC
Hello Tom,
From ur comment ,ie "I know what a missing index will do to my system"
How are index get missed up during rebuild.What is the reasong for that and what problem it'll cause for our application???
December 05, 2002 - 7:48 am UTC
You missed the point.
If I use rebuild (meaning i set them unusable), I cannot lose them. I cannot accidently have one "go missing". People will call and say "hey, I'm getting a strange error about index such and such being unusable -- whats up with that".
If I use DROP / CREATE -- what will happen if the DROP succeeds, but the create fails for whatever reason (script bombs out, out of temp, whatever). Index has gone missing -- no one checks the script log -- and then you spend the next day or two trying to figure out why performance has gone down the tubes ("oh they say the next day, we 'lost' an index").
So, if you use ususable/rebuild -- you'll not lose an index, you'll be told your script bombed, you fix it and the system runs smoothing (except for that minor annoyed person who hit your error)
If you use drop/create -- you'll lose an index someday (maybe many days). You'll not be told your script bombed. Your users will just experience slower response times (if they get the answer back at all), you'll be flooded with calls about "slowness" and then hopefully you can actually track down the index that has gone missing this time.
Temp
Martin Guillen, December 05, 2002 - 9:01 am UTC
Hi tom:
Recalling from this thread : ..."We allocate a temp
segment in that tablespace -- put the index into it -- when done turn that
"temp" segment into an "index" segment (better then building the thing in TEMP
and them moving temp to "real"...
Actually temp space is used (for sorts or something like that, I think):
PEPE> create table u (a int, b varchar2(100));
Table created.
1 begin
2 for i in 1..1000000 loop
3 insert into u values (i, '*');
4 end loop;
5* end;
PEPE> /
PL/SQL procedure successfully completed.
PEPE> desc u;
Name Null? Type
------ -------- --------------------------------------------
A NUMBER(38)
B VARCHAR2(100)
PEPE> create unique index u_idx_uni on u(a);
Index created.
SQL> create temporary tablespace TEMP_TINY tempfile '/opt/oracle/oradata/dbstart/TEMP_TINY.DBF' size 2M autoextend off;
Tablespace created.
SQL> select file_name, bytes/1024/1024 "MB" from dba_temp_files;
FILE_NAME MB
------------------------------------------- ----------
/opt/oracle/oradata/dbstart/TEMP.DBF 100
/opt/oracle/oradata/dbstart/TEMP_TINY.DBF 2
SQL> alter user pepe temporary tablespace temp_tiny;
User altered.
PEPE> select count(*) from u;
COUNT(*)
----------
1000000
PEPE> alter index u_idx_uni rebuild;
alter index u_idx_uni rebuild
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_TINY
PEPE> drop index u_idx_uni;
Index dropped.
PEPE> create index u_idx_uni on u(a);
create index u_idx_uni on u(a)
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_TINY
SQL> alter user pepe temporary tablespace temp;
User altered.
PEPE> create unique index u_idx_uni on u(a);
Index created.
My question is: How much space in temp is needed for index rebuild/create?
Thank you,
Martin.
December 05, 2002 - 11:13 am UTC
sure, it depends on your sort_area_size totally.
wait time
David, December 11, 2002 - 11:13 am UTC
Regarding your followup of my question above...
The seconds_in_wait column of v$session_wait had 15 seconds for my SID. Is this a cumulative value or an individual wait? We are using raid 5.
December 11, 2002 - 10:16 pm UTC
look at wait_time for the last wait time
Fragmentaion of index in LMT
A reader, March 04, 2003 - 4:50 am UTC
Hi Tom,
In our database all the index and data tablespaces are LMT tablespaces. We are aware that the LMT tablespaces doesnt have fragmentation. But I have read some article, stating that the index objects will have holes while there are frequent deletions in data, and hence they are fragmented and the only way to correct them is to rebuild the indexes. Even the indexes are in the LMT this will happen.
So just correct me whether my understanding is correct or not.
As my indexes are in LMT, so if I rebuild them, will there be any use out of that?
March 04, 2003 - 5:57 pm UTC
read
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6601312252730 <code>
it is the extremely rare index that needs to be rebuilt. Ignore the myths that:
o index space is not reused (it is all of the time)
o indexes on sequences are always 50% empty (they are not, you will find them to be very full actually)
o indexes need to be rebuilt on a schedule (not)
Multi-Org Environments
Matt, September 04, 2003 - 3:02 am UTC
Tom,
We are running Oracle Apps multi-org with fine grained access control - a decision has been made for some of our batch routines to partition the load tables on organization.
Problem is we currently have global indexes (based on the initial pre-partitioned tables) which become unusable when truncate of the partition occurs. Obviously if we are truly multi-org ( which actually is unlikely ) and want to have multiple batch processes running at the same time we want to avoid this.
Should we be considering converting all the global indexes to local - bearing in mind we'll have sys_context appended to all queries anyway.
Cheers,
Matt
September 04, 2003 - 8:59 am UTC
depends on how you use the indexes -- if you are 100% sure that the local indexes would ALWAYS partition prune -- local is OK. if not, global is sort of needed to avoid having to range scan N index partitions for each query.
9i allows you to do partition operations and maintain global indexes (they don't have to go unusable)
Rebuild all indexes of a table
A reader, September 28, 2003 - 9:50 pm UTC
Hi tom,
How could I rebuild all indexes for a table? Why there is no such command as "alter table X rebuild indexes"?
September 29, 2003 - 7:27 am UTC
begin
for x in ( select index_name from user_indexes where table_name = 'T' )
loop
execute immediate 'alter index ' || x.index_name || ' rebuild';
end loop;
end;
/
help with partitioned indexes problem
Fernando Sanchez, March 08, 2004 - 9:20 am UTC
Sceanario:
We have a partitioned table with 2 local indexes, having the following default values (USER_PART_INDEXES) DEFAULT_TABLESPACE_NAME='RECHARGE_10_I'
DEFAULT_INITIAL_EXTEND=10240
We found in Alert_SID.log six times the error 'ORA-01652: unable to extend temp segment by 10240 in TABLESPACE RECHARGE_10_I' but when relaunching the partitioning process several times we got the expected result with the partitions and indexes allocated OK in their right tablespaces.
We don't understand why we don't get the error again without having changed the tablespace or why the error happened once.
March 08, 2004 - 1:52 pm UTC
how are you doing this? a drop+create inside of a single block of code? a little more detail please.
Giving details
fernando sanchez, March 09, 2004 - 5:20 am UTC
Executed Code:
--------
v_str1:='alter table '||p_table_name||' add partition '||p_table_name||
'_'||p_rango||' values less than (to_date('''||
p_rango||''',''YYYYMMDD'')) tablespace '||p_tablespace_name;
dbms_output.put_line(v_str);
dbms_sql.parse(v_crs, v_str, dbms_sql.native);
for r_indices in c_indices(p_table_name) loop
v_str2:='alter index '||r_indices.index_name||' rebuild partition '||
p_table_name||'_'||p_rango||' tablespace '||
replace(p_tablespace_name,'_D','_I');
dbms_output.put_line(v_str);
dbms_sql.parse(v_crs, v_str, dbms_sql.native);
end loop;
dbms_sql.close_cursor(v_crs);
v_retorno:=0;
return v_retorno;
exception
when others then
init('crear_particion');
errlib.err_to_defaults('No se ha podido crear la particion error:'||sqlcode||' '||sqlerrm);
if dbms_sql.is_open(v_crs) then
dbms_sql.close_cursor(v_crs);
end if;
v_retorno:=1;
return v_retorno;
---------------
The error occurs when v_str1 is executed and table partition was created but the local indexes associated (with default values) that have to be created raise exception when others with ORA-1652 we told you.
Thanks in advance.
I hope that it's clear but if you need more information please let us know.
alter index rebuild online
Gabriel, April 21, 2004 - 2:44 pm UTC
Hello Tom,
Can you please tell me what are the exact steps executed by Oracle during and alter index rebuild online (the temporay indexes it creates and stuff like that) or give me a link on that?
I am asking that because of the following situation:
I have a database trigger that stops all ddl on the database even from object owners that uses ora_sysevent and
ora_dict_obj_owner. The triggers is supposed to be disabled eevry time scheduled work is planned offhours. The problem arised when someone came up with the idea of scheduling the rebuild of indexes durring lowhours (not offhours). I know we're not supposed to do daily rebuilds but I don't have a say...The triggered fired on the first day the procesdure ran and I got the phone call.... durring debugging I first let alter index go through, then create index (because it create temp indexes), then drop index (because it drops the temp indexes) and it came up with create IOT table?????? the problem with the trigger is fixed since then with syscontext and dbms_application info but I would like to understand what are the steps oracle takes when doing alter index rebuild online.
Thank you very much,
April 21, 2004 - 9:10 pm UTC
the online index rebuild needs to set up the equivalent of replication here.
it needs to keep track of the rows added to the table during the rebuild so it can apply them.
there is lots of magic that goes on and changes from release to release.
if you want to see the magic,
drop table t;
create table t ( x int );
insert into t select rownum from all_users;
create index t_idx on t(x);
alter session set sql_trace=true;
alter index t_idx rebuild online;
and edit the trace file!
What's faster?
Peter Tran, May 13, 2004 - 12:06 am UTC
Hi Tom,
You said above, "9i allows you to do partition operations and maintain global indexes (they don't have to go unusable)".
Is using the "update global indexes" clause in 9i while executing a partition DDL (e.g. drop a partition) be faster than say, rebuilding in parallel?
Thanks,
-Peter
May 13, 2004 - 10:15 am UTC
depends on who you ask :)
if you ask the end users, rebuilding in parallel takes forever -- since they are basically "not going to be doing anything"
if you ask the administrator, "update global indexes" takes lots longer -- since you are generating the redo/undo for the operation.
So, which is more relevant?
Totally depends. If you have a scheduled "we are down so go away" window, rebuild would probably be the path to go. If you don't, maintaining the indexes is really the only choice -- but then, it really doesn't matter "how long" as much since the admins can just goto lunch and the end users keep on querying.
Index rebuild
Js, June 07, 2004 - 1:45 pm UTC
hi,
I have a doubt about index rebuilding.
If we rebuild an index. Would it be used existing index.
If yes,
then 1 . Would it be always fast full scan ?
2 . Would I require exact double space for
[ existing + new ] mean I cann't rebuild
my 2 gb index in 3 gb tablespace ?
Thanks
Js
June 07, 2004 - 2:48 pm UTC
the answer is maybe...
if the existing index *can* be used, sure.
if the existing index cannot be used - no. (eg: it was set unusable)
if it does use the index, the IO wait event I see if a db file scattered read with multi-block read counts for p3, so yes, it fast full scans it (9205 anyway)
As for size you might need:
a) lots less than 2x. index as it exists is full of leaf blocks with 1 row per leaf block. It has 1,000 leaf blocks. After rebuild, new index has but 10 leaf blocks.
b) lots more than 2x. index as it exists is tightly packed, awesomely packed. by rebuilding however you reintroduce the pctfree of 20% you set when you created it. old index is much smaller than new index.
c) 2x. new index and old index are the same size.
Re: Index rebuild
Js, June 07, 2004 - 11:21 pm UTC
:) great !!! hai ji ..
But Sir,
If existing index can be used then
Oracle will create new index first using the older index and then
it will drop the older one ...
In this case , do I need 2x space ?
Thanks,
Js
June 08, 2004 - 8:17 am UTC
The new index is created in temporary extents in the target tablespace, the old index remains intact during this process. At the very end -- the old index is dropped and the temporary extents are converted into permanent ones and the new index becomes "the index". The temporary extents are used so that if the index rebuild fails in the middle or the system shuts down in the middle -- SMON will automagically clean up the resulting mess without user interaction (and also explains why you might get a confusing "unable to allocate temp space in tablespace indexes" -- when your temp tablespace is set to something else -- they are temporary extents holding permanent data that will be converted to permanent extents at the successful completion of the process).
So, you need room for both the entire OLD index and the entire NEW index at some point. As stated that might be:
a) 110% of the space
b) 200% of the space (2x)
c) 300% of the space
or any number really -- as the new index might be
a) smaller
b) the same size
c) larger
than the existing index.
Thanks ..
Js, June 08, 2004 - 12:38 pm UTC
Thanks Sir.
For your reply.
Js
Further clarification on when space is available for reuse
Rob, July 14, 2004 - 4:40 pm UTC
Tom:
In one of your replies in this thread you said:
"then yes, you are correct, the space is not available to be reused until the completion of the call."
I have a package that cleans old data out of a bunch of tables (approx. 50 tables). The package disables constraints, saves ddl of indexes in a separate table and drops indexes. It creates a scratch table of data to keep, truncates original table, insert append from scratch table to perm table, recreates indexes, enables constraints including PK, UK, FK's. The package does this for each table one after the other.
There are multiple procs in the package. They are called in turn by the main proc in the package. The main proc is called by sqlplus.
I am having a problem of running out of space and having the pkg abort. There is enough space so that when I recreated the index manually in a new sqlplus session after the abort it works fine.
My question is when is the space in the tablespace available for reuse. Does "completion of the call" that you mentioned above mean that no space can be reused until after the call to the package finishes. FYI I am using version 8.1.7.4 on Solaris.
July 15, 2004 - 11:33 am UTC
completion of call in this case would be the stored procedure call itself.
probably want to use two tablespaces for this -- tablespace A and tablespace B, move the data back and forth between the two.
ORA-14185 when rebuild local partitioned index
A reader, August 13, 2004 - 8:14 am UTC
Hi
when I try to rebuild an index partition as follows
SQL> ALTER INDEX CMO_CON_FK_I
2 REBUILD PARTITION CMO_CON_FK08 NOLOGGING;
REBUILD PARTITION CMO_CON_FK08 NOLOGGING
*
ERROR at line 2:
ORA-14185: incorrect physical attribute specified for this index partition
Why?
August 13, 2004 - 4:34 pm UTC
i tried in 8i and 9i, you'll need to give me more details -- like a test case and version related information.
tkyte@ORA8I> CREATE TABLE t
2 (
3 temp_date date,
4 x int,
5 y int
6 )
7 PARTITION BY RANGE (temp_date)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users,
10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace tools,
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 ;
Table created.
tkyte@ORA8I>
tkyte@ORA8I> create index t_idx on t(x) local;
Index created.
tkyte@ORA8I>
tkyte@ORA8I> alter index t_idx rebuild partition part1 nologging;
Index altered.
tkyte@ORA8I>
It´s 8.1.7.4 on Sun Solaris
A reader, August 14, 2004 - 4:35 am UTC
Hi
I know it´s strange, I swear I have done this in the past and it worked
SQL> alter index nwt30.CBT_COS_FK_I
2 rebuild partition CBT_COS_FK01 nologging;
rebuild partition CBT_COS_FK01 nologging
*
ERROR at line 2:
ORA-14185: incorrect physical attribute specified for this index partition
Let´s see the index attributes...
SQL> select index_name, locality, partitioning_type, alignment
2 from dba_part_indexes where index_name = 'CBT_COS_FK_I';
INDEX_NAME LOCALI PARTITI ALIGNMENT
------------------------------ ------ ------- ------------
CBT_COS_FK_I LOCAL HASH PREFIXED
Is it because it´s hash partitioned...?
August 14, 2004 - 1:35 pm UTC
ops$tkyte@ORA9IR2> CREATE TABLE t
2 (
3 temp_date date,
4 x int,
5 y int
6 )
7 PARTITION BY hash( x ) partitions 2
8 ;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(x) local;
Index created.
ops$tkyte@ORA9IR2> column partition_name new_val P
ops$tkyte@ORA9IR2> select partition_name from user_ind_partitions where index_name = 'T_IDX';
PARTITION_NAME
------------------------------
SYS_P228
SYS_P229
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index t_idx rebuild partition &P nologging;
old 1: alter index t_idx rebuild partition &P nologging
new 1: alter index t_idx rebuild partition SYS_P229 nologging
alter index t_idx rebuild partition SYS_P229 nologging
*
ERROR at line 1:
ORA-14185: incorrect physical attribute specified for this index partition
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index t_idx modify partition &p nologging;
old 1: alter index t_idx modify partition &p nologging
new 1: alter index t_idx modify partition SYS_P229 nologging
alter index t_idx modify partition SYS_P229 nologging
*
ERROR at line 1:
ORA-14192: cannot modify physical index attributes of a Hash index partition
ops$tkyte@ORA9IR2> !oerr ora 14192
14192, 00000, "cannot modify physical index attributes of a Hash index partition"
// *Cause: User attempted to modify one of INITRANS/MAXTRANS/LOGGING/STORAGE
// clause for an index partition of a Hash partitioned index
// *Action: Remove the physical attributes one is trying to modify
//
ops$tkyte@ORA9IR2> alter index t_idx nologging;
Index altered.
ops$tkyte@ORA9IR2> alter index t_idx rebuild partition &P;
old 1: alter index t_idx rebuild partition &P
new 1: alter index t_idx rebuild partition SYS_P229
Index altered.
ops$tkyte@ORA9IR2> alter index t_idx logging;
Index altered.
ops$tkyte@ORA9IR2>
Index Rebuild
Mason, August 18, 2004 - 10:58 am UTC
I saw Tom has been very patient and helpful, although I can found people keep asking dump questions, but he kept providing answers with examples for people to try out.
nologging index and rebuild
A reader, September 13, 2004 - 3:14 pm UTC
Hi
I am rebuilding indexes with this command
alter index x rebuild;
if I do that even I set index to logging
alter index x logging
the redo generated is always minimum
if I do
alter index rebuild logging then it generates large amounts of redo
is this normal?
September 13, 2004 - 4:06 pm UTC
ops$tkyte@ORA9IR2> create table t as select * from all_objects;
Table created.
ops$tkyte@ORA9IR2> create index t_idx on t(object_name, object_type, owner, object_id, last_ddl_time, created);
Index created.
ops$tkyte@ORA9IR2> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
<b>test in noarchivelog mode</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index t_idx LOGGING;
Index altered.
ops$tkyte@ORA9IR2> @mystat "redo size"
NAME VALUE
------------------------------ ----------
redo size 2398792
ops$tkyte@ORA9IR2> alter index t_idx rebuild;
Index altered.
ops$tkyte@ORA9IR2> @mystat2
NAME V DIFF
------------------------------ ---------- ----------
redo size 4707320 2308528
<b>No archivelog mode, logging index, 2.3meg of redo</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index t_idx NOLOGGING;
Index altered.
ops$tkyte@ORA9IR2> @mystat "redo size"
NAME VALUE
------------------------------ ----------
redo size 4709444
ops$tkyte@ORA9IR2> alter index t_idx rebuild;
Index altered.
ops$tkyte@ORA9IR2> @mystat2
NAME V DIFF
------------------------------ ---------- ----------
redo size 4767772 58328
<b>no logging, 58k of redo</b>
ops$tkyte@ORA9IR2> @connect "/ as sysdba"
ops$tkyte@ORA9IR2> set termout off
sys@ORA9IR2> set termout on
sys@ORA9IR2> alter system set log_archive_start=true scope=spfile;
System altered.
sys@ORA9IR2> alter system set log_archive_dest='/tmp/' scope=spfile;
System altered.
sys@ORA9IR2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA9IR2> startup mount
ORACLE instance started.
Total System Global Area 269554896 bytes
Fixed Size 451792 bytes
Variable Size 201326592 bytes
Database Buffers 67108864 bytes
Redo Buffers 667648 bytes
Database mounted.
sys@ORA9IR2> alter database archivelog;
Database altered.
sys@ORA9IR2> alter database open;
Database altered.
sys@ORA9IR2> @connect /
sys@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index t_idx LOGGING;
Index altered.
ops$tkyte@ORA9IR2> @mystat "redo size"
NAME VALUE
------------------------------ ----------
redo size 2784
ops$tkyte@ORA9IR2> alter index t_idx rebuild;
Index altered.
ops$tkyte@ORA9IR2> @mystat2
NAME V DIFF
------------------------------ ---------- ----------
redo size 2311296 2308512
<b>Logging, 2.3meg</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index t_idx NOLOGGING;
Index altered.
ops$tkyte@ORA9IR2> @mystat "redo size"
NAME VALUE
------------------------------ ----------
redo size 2313420
ops$tkyte@ORA9IR2> alter index t_idx rebuild;
Index altered.
ops$tkyte@ORA9IR2> @mystat2
NAME V DIFF
------------------------------ ---------- ----------
redo size 2371688 58268
<b>no logging 58k</b>
ops$tkyte@ORA9IR2>
<b>in noarch, even if i try over and over:</b>
ops$tkyte@ORA9IR2> set echo off
ops$tkyte@ORA9IR2> @mystat "redo size"
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'
NAME VALUE
------------------------------ ----------
redo size 1360
ops$tkyte@ORA9IR2> alter index t_idx logging;
Index altered.
ops$tkyte@ORA9IR2> alter index t_idx rebuild;
Index altered.
ops$tkyte@ORA9IR2> @mystat2
old 1: select a.name, b.value V, b.value-&V diff
new 1: select a.name, b.value V, b.value- 1360 diff
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'
NAME V DIFF
------------------------------ ---------- ----------
redo size 2311936 2310576
ops$tkyte@ORA9IR2> @mystat
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'
NAME VALUE
------------------------------ ----------
redo size 2311936
ops$tkyte@ORA9IR2> alter index t_idx rebuild;
Index altered.
ops$tkyte@ORA9IR2> @mystat2
old 1: select a.name, b.value V, b.value-&V diff
new 1: select a.name, b.value V, b.value- 2311936 diff
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'
NAME V DIFF
------------------------------ ---------- ----------
redo size 4619564 2307628
ops$tkyte@ORA9IR2> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
<b>do you have a different test?</b>
archive log mode
A reader, September 13, 2004 - 4:40 pm UTC
Hi
my database is in archive log, not sure what has that to do with rebulding indexes :-?
QL> alter index part_t_idx unusable;
Index altered.
SQL> alter index part_t_idx logging;
Index altered.
Elapsed: 00:00:00.01
SQL> select b.sid,
2 b.username,
3 c.name,
4 a.value
5 from v$sesstat a, v$session b, v$statname c
6 where a.statistic# = c.statistic#
7 and a.sid = b.sid
8 and a.sid = (select sid from v$mystat where rownum < 2)
9 and c.name like '%redo size%'
10 order by a.value
11 ;
SID USERNAME NAME VALUE
---------- ------------------------------ ---------------------------------------------------------------- ----------
26 TEST01 redo size 37732104
Elapsed: 00:00:00.18
SQL> alter index part_t_idx rebuild partition part_t_p1;
Index altered.
Elapsed: 00:00:15.63
SQL> select b.sid,
2 b.username,
3 c.name,
4 a.value
5 from v$sesstat a, v$session b, v$statname c
6 where a.statistic# = c.statistic#
7 and a.sid = b.sid
8 and a.sid = (select sid from v$mystat where rownum < 2)
9 and c.name like '%redo size%'
10 order by a.value
11 ;
SID USERNAME NAME VALUE
---------- ------------------------------ ---------------------------------------------------------------- ----------
26 TEST01 redo size 37809832
SQL> select indeX_name, def_logging from user_part_indexes where index_name = 'PART_T_IDX';
INDEX_NAME DEF_LOG
------------------------------ -------
PART_T_IDX YES
SQL> alter index part_t_idx unusable;
Index altered.
Elapsed: 00:00:00.00
SQL> select b.sid,
2 b.username,
3 c.name,
4 a.value
5 from v$sesstat a, v$session b, v$statname c
6 where a.statistic# = c.statistic#
7 and a.sid = b.sid
8 and a.sid = (select sid from v$mystat where rownum < 2)
9 and c.name like '%redo size%'
10 order by a.value
11 ;
SID USERNAME NAME VALUE
---------- ------------------------------ ---------------------------------------------------------------- ----------
26 TEST01 redo size 37827128
Elapsed: 00:00:00.20
SQL> alter index part_t_idx rebuild partition part_t_p1 logging;
Index altered.
Elapsed: 00:00:16.01
SQL> select b.sid,
2 b.username,
3 c.name,
4 a.value
5 from v$sesstat a, v$session b, v$statname c
6 where a.statistic# = c.statistic#
7 and a.sid = b.sid
8 and a.sid = (select sid from v$mystat where rownum < 2)
9 and c.name like '%redo size%'
10 order by a.value
11 ;
SID USERNAME NAME VALUE
---------- ------------------------------ ---------------------------------------------------------------- ----------
26 TEST01 redo size 74623288
Elapsed: 00:00:00.20
SQL>
September 13, 2004 - 8:49 pm UTC
logging has everything to do with archivelog/noarchivelog mode (check out a CTAS in each for example, or an insert /*+ append */ in each -- it is a consideration)
but anyway -- give us a test case that everyone on the planet can run -- like I give you all of the time. The devil *is always in the details* and we quite simple have no details here. Need create tables, insert intos, etc -- reproducible.
For example, something like this
-------------- mystat.sql -------------
set echo off
set verify off
column value new_val V
define S="&1"
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on
---------------- mystat2.sql --------------
set echo off
set verify off
select a.name, b.value V, b.value-&V diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on
--------------- test case ----------------
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE t
2 (
3 dt date,
4 x char(30)
5 )
6 PARTITION BY RANGE (dt)
7 (
8 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
9 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
10 PARTITION junk VALUES LESS THAN (MAXVALUE)
11 )
12 ;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t select to_date( '01-mar-2003'), 'x' from all_objects;
30675 rows created.
ops$tkyte@ORA9IR2> create index t_idx on t(dt,x) local;
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index t_idx logging;
Index altered.
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> set echo off
NAME VALUE
------------------------------ ----------
redo size 14204536
ops$tkyte@ORA9IR2> alter index t_idx rebuild partition part1;
Index altered.
ops$tkyte@ORA9IR2> @mystat2 "redo size"
ops$tkyte@ORA9IR2> set echo off
NAME V DIFF
------------------------------ ---------- ----------
redo size 16015932 1811396
ops$tkyte@ORA9IR2> alter index t_idx nologging;
Index altered.
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> set echo off
NAME VALUE
------------------------------ ----------
redo size 16019348
ops$tkyte@ORA9IR2> alter index t_idx rebuild partition part1;
Index altered.
ops$tkyte@ORA9IR2> @mystat2 "redo size"
ops$tkyte@ORA9IR2> set echo off
NAME V DIFF
------------------------------ ---------- ----------
redo size 16049348 30000
ops$tkyte@ORA9IR2> alter index t_idx logging;
Index altered.
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> set echo off
NAME VALUE
------------------------------ ----------
redo size 16052764
ops$tkyte@ORA9IR2> alter index t_idx rebuild partition part1;
Index altered.
ops$tkyte@ORA9IR2> @mystat2 "redo size"
ops$tkyte@ORA9IR2> set echo off
NAME V DIFF
------------------------------ ---------- ----------
redo size 17864160 1811396
<b>now, modify that to reproduce your situation (range partition, hash partition, composite, whatever) and make it show that "logging generated no log"</b>
test case
A reader, September 14, 2004 - 3:29 am UTC
Hi
I tested with dummy data,
Tru64, Oracle 8.1.7.4
--------------------------------
SQL> CREATE TABLE IMP$APP
2 (
3 ID NUMBER(15),
4 IMP NUMBER,
5 DATE_X VARCHAR2(10)
6 )
7 PARTITION BY RANGE (ID)
8 (
9 PARTITION IMP$APP_PART_P1 VALUES LESS THAN (30000000),
10 PARTITION IMP$APP_PART_P2 VALUES LESS THAN (60000000)
11 );
Table created.
SQL> alter table IMP$APP NOLOGGING;
Table altered.
SQL> INSERT /*+ APPEND */ into imp$app
2 select * from IMP_APP_ODS;
5549361 rows created.
SQL> commit;
Commit complete.
SQL> CREATE INDEX IMP$APP_IDX1 ON IMP$APP
2 (ID, IMP, DATE_X) NOLOGGING
3 LOCAL;
Index created.
SQL> alter table IMP$APP logging;
Table altered.
SQL> alter index IMP$APP_IDX1 logging;
Index altered.
SQL> @mystat1 "redo size"
NAME VALUE
------------------- ----------
redo size 618272
SQL>
SQL> alter index IMP$APP_IDX1 rebuild partition IMP$APP_PART_P1;
Index altered.
SQL> @mystat2 "redo size"
SQL> set echo off
NAME V DIFF
------------------- ---------- ----------
redo size 719536 101264
SQL> alter index IMP$APP_IDX1 nologging;
Index altered.
SQL> alter index IMP$APP_IDX1 unusable;
Index altered.
SQL> @mystat1 "redo size"
SQL> set echo off
NAME VALUE
------------------- ----------
redo size 724856
SQL>
SQL> alter index IMP$APP_IDX1 rebuild partition IMP$APP_PART_P1;
Index altered.
SQL> @mystat2 "redo size"
SQL> set echo off
NAME V DIFF
------------------- ---------- ----------
redo size 825264 100408
SQL> alter index IMP$APP_IDX1 logging;
Index altered.
SQL> alter index IMP$APP_IDX1 unusable;
Index altered.
SQL> @mystat1 "redo size"
SQL> set echo off
NAME VALUE
-------------------- ----------
redo size 830048
SQL>
SQL> alter index IMP$APP_IDX1 rebuild partition IMP$APP_PART_P1;
Index altered.
SQL> @mystat2 "redo size"
SQL> set echo off
NAME V DIFF
-------------------- ---------- ----------
redo size 930456 100408
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> select index_name, table_name, def_logging from user_part_indexes where index_name = 'IMP$APP_IDX1';
INDEX_NAME TABLE_NAME DEF_LOG
------------------------------ ------------------------------ -------
IMP$APP_IDX1 IMP$APP YES
1 row selected.
As you can see the redo generated is always around 100K no matter the index is logging or not, also please keep in mind I set index unusable before rebuild. I also noticed that to rebuild index using parallel processes we MUST use parallel clause in the rebuild syntax, by setting parallel degree at index level such as "alter index x parallel 8" doesnt make "alter index x rebuild" go parallel neither
Cheers
September 14, 2004 - 8:55 am UTC
versions are always useful (i think in addition to "give me a testcase that I too can run from start to finish" I'm going to demand that info as well.... and truth be told -- i could not run your example, I had to make up data, be nice to cut and paste, remove prompts and run sometimes....)
contact support, reference: <Bug:1406284> it affected all 8i releases, an index rebuild of a partition would default to nologging regardless of the mode of the index.
alright, seems only happens with 8.1.7.4
A reader, September 14, 2004 - 3:57 am UTC
Hi
Further tests and seems only happens with 8.1.7.4, BUG? Seems in 8.1.7.4 index is always rebuilt with nologging if the log clause is not specified!
Red Hat Linux, Oracle 8.1.7.4
-----------------------------
SQL>CREATE TABLE t
2 (
3 dt date,
4 x char(30)
5 )
6 PARTITION BY RANGE (dt)
7 (
8 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
9 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
10 PARTITION junk VALUES LESS THAN (MAXVALUE)
11 )
12 ;
Table created.
SQL>insert into t select to_date( '01-mar-2003'), 'x' from dba_source;
38441 rows created.
SQL>create index t_idx on t(dt,x) local;
Index created.
SQL>alter index t_idx logging;
Index altered.
SQL>@mystat "redo size"
SQL>set echo off
NAME VALUE
-------------------- ----------
redo size 4460944
SQL>alter index t_idx rebuild partition part1;
Index altered.
SQL>@mystat2 "redo size"
SQL>set echo off
NAME V DIFF
-------------------- ---------- ----------
redo size 4491428 30484
SQL>
SQL>alter index t_idx nologging;
Index altered.
SQL>@mystat "redo size"
SQL>set echo off
NAME VALUE
-------------------- ----------
redo size 4494436
SQL>
SQL>alter index t_idx rebuild partition part1;
Index altered.
SQL>@mystat2 "redo size"
SQL>set echo off
NAME V DIFF
-------------------- ---------- ----------
redo size 4524164 29728
SQL>
SQL>alter index t_idx logging;
Index altered.
SQL>@mystat "redo size"
SQL>set echo off
NAME VALUE
-------------------- ----------
redo size 4527172
SQL>
SQL>alter index t_idx rebuild partition part1;
Index altered.
SQL>@mystat2 "redo size"
SQL>set echo off
NAME V DIFF
-------------------- ---------- ----------
redo size 4557576 30404
SQL>select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
Red Hat Linux, Oracle 9.2.0.4
-----------------------------
SQL>CREATE TABLE t
2 (
3 dt date,
4 x char(30)
5 )
6 PARTITION BY RANGE (dt)
7 (
8 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
9 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
10 PARTITION junk VALUES LESS THAN (MAXVALUE)
11 )
12 ;
Table created.
SQL>insert into t select to_date( '01-mar-2003'), 'x' from dba_source;
115125 rows created.
SQL>create index t_idx on t(dt,x) local;
Index created.
SQL>alter index t_idx logging;
Index altered.
SQL>@mystat "redo size"
NAME VALUE
-------------------- ----------
redo size 12911656
SQL>
SQL>alter index t_idx rebuild partition part1;
Index altered.
SQL>@mystat2 "redo size"
SQL>set echo off
NAME V DIFF
-------------------- ---------- ----------
redo size 19717132 6805476
SQL>
SQL>alter index t_idx nologging;
Index altered.
SQL>@mystat "redo size"
SQL>set echo off
NAME VALUE
-------------------- ----------
redo size 19720524
SQL>
SQL>alter index t_idx rebuild partition part1;
Index altered.
SQL>@mystat2 "redo size"
SQL>set echo off
NAME V DIFF
-------------------- ---------- ----------
redo size 19776908 56384
SQL>
SQL>alter index t_idx logging;
Index altered.
SQL>@mystat "redo size"
SQL>set echo off
NAME VALUE
-------------------- ----------
redo size 19780300
SQL>
SQL>alter index t_idx rebuild partition part1;
Index altered.
SQL>@mystat2 "redo size"
SQL>set echo off
NAME V DIFF
-------------------- ---------- ----------
redo size 26561012 6780712
SQL>
SQL>select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
September 14, 2004 - 8:55 am UTC
see above.
Adding index in the middle of updating
Sean, November 12, 2004 - 11:10 am UTC
Hi Tom,
We are updating a 5 millions rows table without index which takes about 3 hr to finish. Something like this:
UPDATE test1
SET c1 = :sys_b_0,
c2 = :sys_b_1
WHERE c3 = :sys_b_2 -- there is no index on c3.
When I try to add index in the middle of updating, I got "ORA-00054: resource busy and acquire with NOWAIT specified" error.
I can avoid the error by adding "online". But I am not sure whether it will save time by doing now or by waiting until the update is done.
Thanks so much for your help.
November 12, 2004 - 1:30 pm UTC
so, does it take 3 hours to full scan this table? (no, my laptop won't take 3 hours to full scan that much data, minutes at most)
the columns c1, c2 -- are they indexed? (if so, they are the root cause most likely). if not, 5 millions rows to be updated will take a while -- the amount of undo and redo done....
is this a 'batch update' or what -- what is happening to this table as you are updateing it?
Adding index while updating is going
Sean, November 12, 2004 - 4:08 pm UTC
Hi Tom,
I have to tell you the whole story. I found that this process is constantly using 30% CPU for a long time, at least 30 min. The batch job takes about 3 hr and I actually dont know how long it takes to update.
I then found this query from OEM using process id and that there is no index on this table at all. Developers told me they update about 500 records, so I decide to add index on it because of "where" clause.
Then came to question whether adding index while the table is being updated or wait until update is done.
Thanks so much for your help.
November 12, 2004 - 8:40 pm UTC
adding the index whilst the update is running would only make things worse
a) the query plan could not change -- the update would continue as before
b) they would be contending with eachother IO wise
c) you would be adding load to the system
d) the index could go bonkers if you were indexing the columns being modified
for the future -- to find the 500 rows to update, index = a good idea.
right now, for this execution of theupdate -- index is not something to add.
Rebuilding local unusable indexes in parallel
J B, July 14, 2006 - 11:16 am UTC
Hi Tom,
I have written a procedure to rebuild all unusable local indexes in parallel for which the partition date is less than sysdate.
However when executing I am faced with an error message
Date : 14/07/2006 15:00:33 Error generated while rebuilding the UNUSABLE local indexes with an error message ORA-00900: invalid SQL statement
The statement gets executed well at the SQL prompt, then why it gives an error in the procedural code.
Please see where I am wrong and rectify me.
Thanks
Create or replace Procedure Rebuild_local_indexes AS
Begin
declare
l_start_rebuild_unusable_index NUMBER := dbms_utility.get_time;
l_tname VARCHAR2(30);
l_partition_name VARCHAR2(30);
l_index_name VARCHAR2(30);
rebuild_partition_index VARCHAR2(32367);
l_partition_dt DATE;
Path VARCHAR2(100) ;
Filename VARCHAR2(100) := 'Rebuildlocal.txt';
err_num NUMBER;
err_msg VARCHAR2(500);
output_file utl_file.file_type;
BEGIN
BEGIN
Select value into path from v$parameter where name = 'utl_file_dir';
EXCEPTION
When NO_DATA_FOUND then
err_num := SQLCODE;
err_msg := Substr(SQLERRM,1,500);
if (err_msg = 'ORA-01403: no data found') then
err_msg := 'Grant Privilege on V$parameter missing to the Schema Owner';
end if;
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || ' Error generated while getting the value of utl_file_dir with an error message ' || err_msg);
utl_file.fclose(output_file);
return;
end;
output_file := utl_file.fopen (path,filename, 'a');
for x in (Select * from Rolling_tables where table_name = 'CLICK_DATA' and status = 'Y') loop
l_tname := x.table_name;
for table_specific_partitions in (Select user_indexes.index_name iname, user_ind_partitions.partition_name part_name from user_indexes,user_ind_partitions
Where user_indexes.index_name = user_ind_partitions.index_name
And user_indexes.table_name = l_tname
And user_ind_partitions.Status = 'UNUSABLE') loop
begin
l_partition_name := table_specific_partitions.part_name;
l_index_name := table_specific_partitions.iname;
Select to_date(Substr(get_high_value(l_partition_name),11,19),'YYYY-MM-DD hh24:mi:ss') into l_partition_dt from dual;
If l_partition_dt < sysdate then
begin
rebuild_partition_index := 'exec dbms_utility.exec_ddl_statement(' || '''' || 'alter index ' || l_index_name || ' rebuild partition ' || l_partition_name || ' parallel (degree 16)' || '''' || ')';
utl_file.put_line (output_file,rebuild_partition_index);
execute immediate rebuild_partition_index;
EXCEPTION
When OTHERS then
err_num := SQLCODE;
err_msg := Substr(SQLERRM,1,500);
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || ' Error generated while rebuilding the UNUSABLE local indexes with an error message ' || err_msg);
utl_file.fclose(output_file);
return;
end;
end if;
end;
end loop;
end loop;
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || ' The total time spent to rebuild unusable local indexes is ' || round((dbms_utility.get_time - l_start_rebuild_unusable_index)/100,2) || ' secs');
END;
END;
/
July 14, 2006 - 12:40 pm UTC
you are generating an invalid sql statement.
perhaps a little bit of logging and debug code would help?
(querying v$parameter for the utl_file_dir like that is just asking for trouble - think about it - utl_file_dir = /a,/b,/c, that is valid as is having many utl_file_dir's on separate lines!!!)
look to your log file, lets see the sql statement you think you executed (you know, log the offending sql statement!!!)
Rebuilding local indexes in parallel
J B Khanna, July 14, 2006 - 1:22 pm UTC
This is the statement it generates and fails to qualify as a valid SQL statement.
exec dbms_utility.exec_ddl_statement('alter index IDX_CLICK_DATA_INSERTDT rebuild partition P_20060718_CLICK_DATA parallel (degree 16)')
Date : 14/07/2006 15:00:33 Error generated while rebuilding the UNUSABLE local indexes with an error message ORA-00900: invalid SQL statement
Appreciate help
July 14, 2006 - 1:52 pm UTC
Ahh, indeed, that is NOT valid sql.
exec (or execute) is a sqlplus'ism. shorthand for begin/end;
you can see this:
ops$tkyte@ORA9IR2> exec hello_world
BEGIN hello_world; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'HELLO_WORLD' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
See how:
exec X
is just shorthand for
BEGIN X; END;
you use begin/end.
but what might be "easier" would just be to code
rebuild_partition_index := 'alter index ' || l_index_name ||
' rebuild partition ' || l_partition_name ||
' parallel (degree 16)';
instead of:
rebuild_partition_index := 'exec
dbms_utility.exec_ddl_statement(' || '''' || 'alter index ' || l_index_name || '
rebuild partition ' || l_partition_name || ' parallel (degree 16)' || '''' ||
')';
and execute immediate just the alter index statement.
No need to use dynamic sql to dynamical invoke a routine (exec_ddl_statement) to dynamically execute sql!
Just execute the dynamic sql of interest :)
Local Indexes
J B Khanna, July 14, 2006 - 2:58 pm UTC
Thanks Tom for your advice!
index creation
Ramu, July 17, 2006 - 9:20 am UTC
Tom,
I have an big table and able to access by the following conditions on it. Its a single table
Access, I have created index on these 4 columns same sequence process_table (STATE, DATE, TYPE, AMOUNT)
Why it is not using the index even though provided the index hint (I have analyzed table and index)
select * from process_table
WHERE STATE in ('Ready')
AND DATE < sysdate
AND TYPE IN ( 'Payment' , 'Ledger')
AND AMOUNT != 0
Can please give your suggestions on better way of creating the index on this table.
Thanks in advance
Ramu
July 17, 2006 - 3:01 pm UTC
you give no example to work with....
and I see no index hint
A reader, April 04, 2007 - 6:16 am UTC
Hi Tom, what options I have to deal with this case:
Using Oracle 10gR1.
We have a DB that contains a data model of a product (Documentum) along with custom application data. Our concern is about a lot of unused indexes/space regarding product data model. That indexes must be there for a good reason but I guess the product can be configured with a lot of components that we actually don't use in our setup, so currently they are useless.
I could go ahead and drop the indexes, but I'm concerned about future activation of new functionalities that would use them and support issues about altering its datamodel. I also want to keep track of that changes, but writing them down is risky (outdated, lost). A bit paranoid here ;)
Alter index unusable seems a good option because of the index definition is kept inside the DB, to rebuild them just in case we need it. It frees us from the index maintenance overhead, keeps track of the index, but it doesn't free allocated segments. I wonder why not, if that index segments are not usable anymore.
Disk space is cheap, but they sum up about 150Gb on disk, on backup time, on tape transfer, tape space, and restore time.
Can we get the space back ?
April 04, 2007 - 10:25 am UTC
you need to talk to the makers of your product, there is nothing I can say to help you here - I cannot tell you to do something that would make their product unsupportable.
You could ask them "can we move these segments to a locally managed tablespace with system allocated extent sizes so as to have them consume 64k apiece instead of ...."
As for the indexes on actual data - you need to talk with THEM about doing anything with them.
10g - alter index unusable/rebuild nologging
Jack, April 11, 2007 - 10:33 am UTC
We had been using the alter index unusable/alter index rebuild nologging when doing large data loads across db links. It had worked well until our 10.2.0.3 upgrade. This version does not allow this when the index is unique. Aside from dropping the unique requirement from the index, leaving the index in a usable state or dropping and recreating the index is there any other option for getting the performance back to what it was in 9i?
April 11, 2007 - 11:45 am UTC
are you sure about that? unique has always been that way - can you show me the step by steps you were doing ( SIMPLIFIED - please do not post big code)
ops$tkyte%ORA9IR2> create table t ( x int );
Table created.
ops$tkyte%ORA9IR2> create unique index t_idx on t(x);
Index created.
ops$tkyte%ORA9IR2> alter index t_idx unusable;
Index altered.
ops$tkyte%ORA9IR2> alter session set skip_unusable_indexes=true;
Session altered.
ops$tkyte%ORA9IR2> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-01502: index 'OPS$TKYTE.T_IDX' or partition of such index is in unusable
state
ops$tkyte%ORA9IR2> drop index t_idx;
Index dropped.
ops$tkyte%ORA9IR2> create index t_idx on t(x);
Index created.
ops$tkyte%ORA9IR2> alter index t_idx unusable;
Index altered.
ops$tkyte%ORA9IR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA9IR2> select status from user_indexes where index_name = 'T_IDX';
STATUS
--------
UNUSABLE
10g - alter index unusable/rebuild nologging- followup
Jack, April 11, 2007 - 12:14 pm UTC
Thanks Tom, here is the relevant information:
DBMS version: 9.2.0.5
Index: (note this is on a materialized view)
CREATE UNIQUE INDEX lifetime_sales_isbn_idx
ON lifetime_sales_mv(isbn)
storage ( INITIAL 10K
NEXT 10K
PCTINCREASE 0)
Procedure statements:
execute immediate 'alter index LIFETIME_SALES_ISBN_IDX unusable';
dbms_mview.refresh( 'LIFETIME_SALES_MV', 'C' );
execute immediate 'alter index LIFETIME_SALES_ISBN_IDX rebuild nologging';
produced no errors in 9.2.0.5, does error in 10.2.0.3
April 11, 2007 - 1:14 pm UTC
ahh - got it.
in 9i, the refresh was
truncate + insert /*+ APPEND */
the truncate validated the index - and hence the subsequent rebuild was not necessary (it was ALREADY OK!!!!!)
in 10g, the refresh is:
delete + insert
so, one approach is to do a truncate + insert /*+ APPEND */ yourself, instead of using refresh - but remember that the truncate will make the index USABLE again, so just SKIP the rebuild - it was never necessary!
10g - alter index unusable/rebuild nologging
Michel Cadot, April 11, 2007 - 2:53 pm UTC
You can also use the option "atomic_refresh=>false" on refresh to go back to 9i behaviour.
Regards
Michel
April 11, 2007 - 5:50 pm UTC
thanks!
why the change in 10g
reader, April 12, 2007 - 11:29 am UTC
in 10g, the refresh is:
delete + insert
Truncating the MV when Complete refresh was done would minimize resources as there is not much redo and undo. Then, why oracle is doing delete and insert in 10g for complete refresh. any other special reson?
Thanks.
April 13, 2007 - 12:13 pm UTC
so the data doesn't disappear - the truncate and insert /*+ append */ made the data in the MV "go away" - no undo implies no read consistency, the truncate would disappear the data and commit - MV appears empty.
How to determine which indexes are UNUSABLE
Apurva, April 13, 2007 - 3:43 am UTC
Tom,
How can I determine through a SQL query which of my indexes are UNSUSABLE?
The STATUS column in USER_INDEXES table is misleading
Thanks a lot!
April 13, 2007 - 2:06 pm UTC
what do you mean by misleading?
ops$tkyte%ORA9IR2> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
EMP_DEPT_CLUSTER_IDX VALID
ENAME_IDX VALID
FIDX_NEU VALID
I1_APP_TIPO VALID
I2_APP_TIPO VALID
I_MEM_TEST_TAB_1 VALID
SYS_C002858 VALID
SYS_C002888 VALID
SYS_C002935 VALID
SYS_C003089 VALID
SYS_IOT_TOP_32298 VALID
TABLE1_FK_I VALID
T_IDX VALID
13 rows selected.
ops$tkyte%ORA9IR2> alter index ename_idx unusable;
Index altered.
ops$tkyte%ORA9IR2> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
EMP_DEPT_CLUSTER_IDX VALID
ENAME_IDX UNUSABLE
FIDX_NEU VALID
I1_APP_TIPO VALID
I2_APP_TIPO VALID
I_MEM_TEST_TAB_1 VALID
SYS_C002858 VALID
SYS_C002888 VALID
SYS_C002935 VALID
SYS_C003089 VALID
SYS_IOT_TOP_32298 VALID
TABLE1_FK_I VALID
T_IDX VALID
13 rows selected.
How to determine which indexes are UNUSABLE
Laura, April 13, 2007 - 10:18 am UTC
dba_indexes and dba_ind_partitions will show the needed information.
@Laura
Apurva, April 13, 2007 - 10:57 am UTC
dba_indexes and dba_ind_partitions will show the needed information
Laura,
dba_indexes and dba_ind_partitions have a column called STATUS -- but this column doesn't tell you if the index is UNUSABLE.
An index might be VALID as per the STATUS column but still be UNUSABLE
Global Partioned Index Rebuild
Rob Mathews, June 04, 2007 - 7:17 pm UTC
What is the difference (internally) between rebuild (online) of global non partitioned and global partitioned index (hash)?
As we are doing "online" rebuild of index, in either case Full Table Scan will be performed on the table. But in case of global partitioned index, there will be full table scan multiple times (equal to number of table partitions).
Also as index partitions are going to be smaller, I guess, it may be possible to merge journal table entries faster (may be without getting a lock on the table).
I want to have better manageability in terms of getting the index rebuild; will I be able to achieve that in "Global Partitioned Indexes"?
I want to have your expert opinion/comments on this.
Thanks
June 06, 2007 - 12:45 pm UTC
I'm not sure what you want to have comments on?
you rebuild index partitions (whether there be a single partition or many).
define what you expect to achieve manageability wise here?
and - why you think you need to rebuild.
Index Rebuild
A reader, June 08, 2007 - 1:49 am UTC
Can you please let me know if "online" index rebuild of partitioned index and nonpartitioned index different internally? If I have 16 index hash partitions, will it do 16 times full table scan of the table?
My table is very hot and I get enqueue/library cache pile ups at the final phase of index merge. My understanding is I will be able to mitigate this impact by having partitioned index and rebuilding one partition at a time.
My tables are using sequence number as the index key and there are a lot of puging happens in a day. My index gets larger and larger, by compressing at some intervals, I get less buffer reads and hence the performance improvement.
June 09, 2007 - 10:25 am UTC
you rebuild individual partitions, not the entire partitioned index. So, you'll be executing N commands.
have you looked at coalesce instead of rebuild.
Some help with alter index rebuild partition...
Fábio, August 31, 2007 - 2:54 pm UTC
Hi Tom!
I know you're not fan on rebuilding index, me neither!
So let me explain my situation...
I'm doing and export/import via database link. The one that is getting all the export is empty, but has all the tables and index already created. The previous DBA usually drop the indexes on the new Database and then create them again. But now the responsibility is mine and I have some doubts.
Well, there is 2 main questions:
1º Can I just import the data without dropping the index and then just rebuild? Or on this case is better coalesce or none of them? ( +/- 1.000.000 rows )
2º Our index has 192 partitions! There is a away to do the rebuilt in 1 single command? I know you answered no on the previous question but writing the command 192 times isn't that funny...
Thanks in advance.
September 05, 2007 - 9:29 am UTC
... I'm doing and export/import via database link. ...
no you are not :) You might be connecting over the network, but that is not a database link (expDP and impDP - the datapump, they might use a dblink, but not the venerable old time tools exp and imp)
1) you would not need to rebuild, if the indexes are in place, when you imp - it just does inserts - the index would be fine. However, if the objects are empty, it makes sense to create the indexes AFTER the load.
2) see
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_pclxut.htm#sthref5311
Dblink
Fábio, September 05, 2007 - 2:50 pm UTC
Yes, you're right :)
I'm using impdp and expdp... like the old time through a pipe instead of a network. But I'm not that old! :D
Thanks once more!
rebuilding indexes in different tablespace
Visitor, November 01, 2012 - 10:32 am UTC
Hi Tom,
Over a period of time, indexes have been created into default tablespace of that schema. If I had to correct them I understand that you dont like dropping and re-creating indexes due to the associated risks.
So, is there a good way to move partitioned indexes into appropriate tablespaces.
Thanks in advance.
November 01, 2012 - 10:21 pm UTC
alter index I rebuild <whatever parameters you want...>
Difference between rebuild index and dropping-recreating indexes
Pandy, August 20, 2013 - 1:21 pm UTC
Hi Tom,
This is a very good thread.I have one question, we recently had a rebuild of indexes on few of our application tables (to free up some DB space) but then we started facing the performance issues with those tables, then our expert consultants were said to drop and re-create those indexes to fix that performance issue. So we did so and the problem were resolved. I believe, ideally rebuilding an indexes should not create such issues.
So my question is what exactly is the difference between rebuilding an index and dropping/re-creating an index ?? Which one to choose ?? Thanks a lot in advance..!!