rebuild_table's Index
时间：2010-10-19 来源：hbjxzl
----------重建索引，还是不错的procude，建议大家参考参考
Purpose: This Procedure will rebuild Invalid indexes on the Table. And it can be used upon Partitioned and non partitioned tables.
What we should know before running this procedure:
The user running this procedure should have proper grants to run this procedure.
What to know before running this procedure.
This procedure will only rebuild indexes that are invalid. And status of the index will be in unusable state.
Ex: exec Rebuild_Index ('TDF1496','dly_test');
Here TDF1496 is the Schema: The owner of the table
And DLY_TEST is the Table name
EXEC Rebuild_Index (‘SCHEMA_NAME’,’TABLE_NAME’);
CREATE OR REPLACE PROCEDURE SHODS01.Rebuild_Index (
p_schema_name IN VARCHAR2,
p_table_name IN VARCHAR2)
AS
v_no_table_found EXCEPTION;
v_table_count NUMBER;
v_sql VARCHAR2 (2000);
BEGIN
SELECT COUNT ( * )
INTO v_table_count
FROM all_tables
WHERE UPPER (table_name) = UPPER (p_table_name)
AND UPPER (owner) = UPPER (p_schema_name);
IF (v_table_count = 0)
THEN
RAISE v_no_table_found;
ELSE
BEGIN
dbms_index_utl.build_table_indexes (
p_schema_name || '.' || p_table_name,
TRUE,
'ALL',
TRUE,
TRUE,
8);
END;
END IF;
EXCEPTION
WHEN v_no_table_found
THEN
raise_application_error (
-20735,
'Please Check the table name you may be entered wrong table name or table does not exists');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
END;
/
Example on a Partitioned Table:
Create Table:
CREATE TABLE DLY_TEST
(
BATCH_DTE_CYMD DATE NOT NULL,
ACCT_NO number
)
TABLESPACE TSHODSTAB1
PARTITION BY RANGE (BATCH_DTE_CYMD)
(
PARTITION P_ACT_RLS_D_20100301 VALUES LESS THAN (TO_DATE(' 2010-03-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE TS201003)
/
CREATE UNIQUE INDEX DLY_test_I1U ON DLY_test
(ACCT_NO, BATCH_DTE_CYMD)
COMPRESS LOCAL (
PARTITION P_ACT_RLS_D_20100301
TABLESPACE TS201003)
/
Create partitions on the table using the Gen_Part_DLY procedure.
set serveroutput on
set define off
variable v_error varchar2(100);
exec Gen_Part_DLY ('TDF1496','DLY_TEST','02-MAR-2010','15-mar-2010','NONE','M',:v_error);
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100302 values less than (to_date (' 2010-03-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100303 values less than (to_date (' 2010-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100304 values less than (to_date (' 2010-03-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100305 values less than (to_date (' 2010-03-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100308 values less than (to_date (' 2010-03-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100309 values less than (to_date (' 2010-03-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100310 values less than (to_date (' 2010-03-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100311 values less than (to_date (' 2010-03-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100312 values less than (to_date (' 2010-03-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100315 values less than (to_date (' 2010-03-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
PL/SQL procedure successfully completed.
Now make indexes unusable using the following:
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100301 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100302 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100303 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100304 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100305 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100308 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100309 unusable;
Make sure that the indexes are invalid:
select index_name,partition_name,status from dba_ind_partitions where index_name like 'DLY%' order by partition_name;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
DLY_TEST_I1U P_ACT_RLS_D_20100301 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100302 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100303 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100304 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100305 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100308 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100309 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100310 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100311 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100312 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100315 USABLE
11 rows selected.
Now run the procedure Rebuild indexes: This procedure will only rebuild indexes that are invalid.
Set Serveroutput on
exec rebuild_index4 ('TDF1496','DLY_TEST');
PL/SQL procedure successfully completed.
If the Indexes are huge then this will take some time.
Now Verify the Status of the Indexes :
select index_name,partition_name,status from dba_ind_partitions where index_name like 'DLY%' order by partition_name;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
DLY_TEST_I1U P_ACT_RLS_D_20100301 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100302 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100303 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100304 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100305 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100308 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100309 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100310 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100311 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100312 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100315 USABLE
11 rows selected.
How To rebuild indexes on a normal table :
Create a test table :
CREATE TABLE TDF1496.DLY_TEST1
(
BATCH_DTE_CYMD1 DATE NOT NULL,
ACCT_NO1 NUMBER
)
TABLESPACE TSHODSTAB1;
CREATE UNIQUE INDEX TDF1496.YDLY_TEST_I1U1 ON TDF1496.DLY_TEST1
(ACCT_NO1, BATCH_DTE_CYMD1);
After the table is created make sure that the index is valid :
select index_name,STATUS from dba_indexes where index_name like 'YDLY%';
INDEX_NAME STATUS
------------------------------ --------
YDLY_TEST_I1U1 VALID
1 row selected.
Now invalidate the Index using this command :
alter index YDLY_TEST_I1U1 unusable;
Verify the status of the index
select index_name,STATUS from dba_indexes where index_name like 'YDLY%';
INDEX_NAME STATUS
------------------------------ --------
YDLY_TEST_I1U1 UNUSABLE
1 row selected.
Run the Procedure:
Set Serveroutput on
exec rebuild_index ('TDF1496','DLY_TEST1');
Verify the status of the index :
select index_name,STATUS from dba_indexes where index_name like 'YDLY%';
INDEX_NAME STATUS
------------------------------ --------
YDLY_TEST_I1U1 VALID
1 row selected.
What we should know before running this procedure:
The user running this procedure should have proper grants to run this procedure.
What to know before running this procedure.
This procedure will only rebuild indexes that are invalid. And status of the index will be in unusable state.
Ex: exec Rebuild_Index ('TDF1496','dly_test');
Here TDF1496 is the Schema: The owner of the table
And DLY_TEST is the Table name
EXEC Rebuild_Index (‘SCHEMA_NAME’,’TABLE_NAME’);
CREATE OR REPLACE PROCEDURE SHODS01.Rebuild_Index (
p_schema_name IN VARCHAR2,
p_table_name IN VARCHAR2)
AS
v_no_table_found EXCEPTION;
v_table_count NUMBER;
v_sql VARCHAR2 (2000);
BEGIN
SELECT COUNT ( * )
INTO v_table_count
FROM all_tables
WHERE UPPER (table_name) = UPPER (p_table_name)
AND UPPER (owner) = UPPER (p_schema_name);
IF (v_table_count = 0)
THEN
RAISE v_no_table_found;
ELSE
BEGIN
dbms_index_utl.build_table_indexes (
p_schema_name || '.' || p_table_name,
TRUE,
'ALL',
TRUE,
TRUE,
8);
END;
END IF;
EXCEPTION
WHEN v_no_table_found
THEN
raise_application_error (
-20735,
'Please Check the table name you may be entered wrong table name or table does not exists');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
END;
/
Example on a Partitioned Table:
Create Table:
CREATE TABLE DLY_TEST
(
BATCH_DTE_CYMD DATE NOT NULL,
ACCT_NO number
)
TABLESPACE TSHODSTAB1
PARTITION BY RANGE (BATCH_DTE_CYMD)
(
PARTITION P_ACT_RLS_D_20100301 VALUES LESS THAN (TO_DATE(' 2010-03-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE TS201003)
/
CREATE UNIQUE INDEX DLY_test_I1U ON DLY_test
(ACCT_NO, BATCH_DTE_CYMD)
COMPRESS LOCAL (
PARTITION P_ACT_RLS_D_20100301
TABLESPACE TS201003)
/
Create partitions on the table using the Gen_Part_DLY procedure.
set serveroutput on
set define off
variable v_error varchar2(100);
exec Gen_Part_DLY ('TDF1496','DLY_TEST','02-MAR-2010','15-mar-2010','NONE','M',:v_error);
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100302 values less than (to_date (' 2010-03-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100303 values less than (to_date (' 2010-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100304 values less than (to_date (' 2010-03-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100305 values less than (to_date (' 2010-03-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100308 values less than (to_date (' 2010-03-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100309 values less than (to_date (' 2010-03-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100310 values less than (to_date (' 2010-03-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100311 values less than (to_date (' 2010-03-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100312 values less than (to_date (' 2010-03-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100315 values less than (to_date (' 2010-03-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
PL/SQL procedure successfully completed.
Now make indexes unusable using the following:
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100301 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100302 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100303 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100304 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100305 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100308 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100309 unusable;
Make sure that the indexes are invalid:
select index_name,partition_name,status from dba_ind_partitions where index_name like 'DLY%' order by partition_name;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
DLY_TEST_I1U P_ACT_RLS_D_20100301 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100302 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100303 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100304 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100305 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100308 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100309 UNUSABLE
DLY_TEST_I1U P_ACT_RLS_D_20100310 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100311 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100312 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100315 USABLE
11 rows selected.
Now run the procedure Rebuild indexes: This procedure will only rebuild indexes that are invalid.
Set Serveroutput on
exec rebuild_index4 ('TDF1496','DLY_TEST');
PL/SQL procedure successfully completed.
If the Indexes are huge then this will take some time.
Now Verify the Status of the Indexes :
select index_name,partition_name,status from dba_ind_partitions where index_name like 'DLY%' order by partition_name;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
DLY_TEST_I1U P_ACT_RLS_D_20100301 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100302 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100303 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100304 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100305 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100308 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100309 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100310 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100311 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100312 USABLE
DLY_TEST_I1U P_ACT_RLS_D_20100315 USABLE
11 rows selected.
How To rebuild indexes on a normal table :
Create a test table :
CREATE TABLE TDF1496.DLY_TEST1
(
BATCH_DTE_CYMD1 DATE NOT NULL,
ACCT_NO1 NUMBER
)
TABLESPACE TSHODSTAB1;
CREATE UNIQUE INDEX TDF1496.YDLY_TEST_I1U1 ON TDF1496.DLY_TEST1
(ACCT_NO1, BATCH_DTE_CYMD1);
After the table is created make sure that the index is valid :
select index_name,STATUS from dba_indexes where index_name like 'YDLY%';
INDEX_NAME STATUS
------------------------------ --------
YDLY_TEST_I1U1 VALID
1 row selected.
Now invalidate the Index using this command :
alter index YDLY_TEST_I1U1 unusable;
Verify the status of the index
select index_name,STATUS from dba_indexes where index_name like 'YDLY%';
INDEX_NAME STATUS
------------------------------ --------
YDLY_TEST_I1U1 UNUSABLE
1 row selected.
Run the Procedure:
Set Serveroutput on
exec rebuild_index ('TDF1496','DLY_TEST1');
Verify the status of the index :
select index_name,STATUS from dba_indexes where index_name like 'YDLY%';
INDEX_NAME STATUS
------------------------------ --------
YDLY_TEST_I1U1 VALID
1 row selected.
相关阅读 更多 +
