Because of this behavior, I have huge waste of space in the tablespace to index.Īllocate space for parallel process slave is documented in the book 'Data warehousing database Oracle 10 g 2', chapter 25 "run in parallel to assistance. Same thing can be demonstrated by the queries on DBA_SEGMENTS and DBA_FREE_SPACE. But it also means that the space is not released. I understand that during the reconstruction of the parallel process slave create segments in the tablespace of the index of destination so that spend a lot more space than this segment takes finally. When you are finished, query on DBA_IND_PARTITIONS shows that, for all the index, partition value SCOPES is much greater than the value of used BYTES, for example one of the partitions has 106 DEGREES (1 MB each so he made 106 MB space) while only 15 MB for the BYTES. Nightly batch processing allows a few partitions index unusable then inserts/adds one part of the data and rebuilds the index withĪLTER INDEX. Table and index are stored in different areas of storage. I have a large table partitioned on a monthly basis with a local couple of bitmap index on this subject.
Can someone explain to me what is happening in the different stages of the index rebuild? Is there something I can do to eliminate the blocking of the DML statements during the period when redo online record switch?Īnother very useful reference - of many articles:Ĭo-author of "Expert Oracle practices: Oracle Database Administration of the Oak Table.Ĭoncerns the RDBMS EE 10.2.0.2 on a box with 16CPUs.
I read that the DML statements are not affected by executing ALTER INDEX REBUILD online, but experience shows that the blocking of the above sessions. When the new version of the index was created again online records switching station, the original copy of the index disappears, the ALTER INDEX command ends, and blocking for DML ceases. They are each 100 MB.ĭuring the time that online redo logs, DML execution sessions cannot fill.
#HOW DO I KNOW WHEN OUTLOOK REBUILD INDEX IS FINISHED SERIES#
At about minute seven journal alerts shows the first of a series of five online redo logs switches. The database alert log shows no activity for the first six minutes, and DML is allowed on the associated table. It takes about ten minutes to run the command. Sometimes, I pass command ALTER INDEX REBUILD online on the indexes that are 500 MB in size.
SQL> alter index temp_idx parallel 1 logging SQL> create index temp_idx on temp(no) nologging parallel 5 SQL> select degree, logging from user_indexes where index_name = 'TEMP_IDX' How can I check the existing nologging and parallel properties of an index? SQL> create table temp (no integer, name varchar2(100)) the SQL above to change the properties of the index?Ģ. But now I'm confused because I think that this will change the index property, as in, when we create an index specify us certain parallels and logging properties.ġ. I use NOLOGGING and PARALLEL thinking it will speed up the process of movement. XYZ ALTER INDEX REBUILD TABLESPACE TS_INDX01 NOLOGGING PARALLEL 8 I want to move some indexes to a different tablespace.
0 kommentar(er)
