I would like to share some PL/SQL code that enables us DBA's to do index maintenance. In our data warehouse there were a couple of procedures that saved very basic index information.
Then the indexes where dropped and after the warehouse load they were recreated.
The pitfall of these simple procedures was that they were not able to cope with partitioned indexes and other complex structures. These more complex indexes were rebuild as normal non-partitioned indexes afterwards.
To be able to also rebuild partitioned and other index structures I rewrote the complete procedure, making effective use of the DBMS_METADATA package available in the SYS account.
This DBMS_METADATA package has a function called GET_DDL that is able to return the DDL of any object in the database, like tablespaces, tables, indexes and others.
If you e.g. would want to know the DDL of the EMP table, simple use the following query:
SQL > select dbms_metadata.get_ddl ('TABLE','EMP') from dual;
Ofcourse the above statement needs to be executed as the owner of the table. If you want to execute it as SYS then you must supply the function with the table owner, like:
SQL> select sys.dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
The result is a CLOB variable containing the DDL to (re)create the EMP table of user SCOTT:
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10 CHAR),
"JOB" VARCHAR2(9 CHAR),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMP_VALID_JOB" CHECK (job in ('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT')) ENABLE,
PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 1017
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 1017
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
The procedures I wrote uses a simple table for administration purposes. This table exists of four columns:
INDEX_NAME of type VARCHAR(30)
TABLE_NAME of type VARCHAR(30)
QUERY of type CLOB
STMT_ORDER of type NUMBER(3)
The first three column are quite self-explaining. They contain the table_name, and index_name that is being dropped, and of course the query that is needed to recreate the index afterwards.
The last column STMT_ORDER determines the order of queries. This is important e.g. when creating primary-key indexes with extra constraints. The primary key needs to exist, before a constraint can be added to it.
The DDL structure of this table looks like:
CREATE TABLE WBX_DDL_INDEXES
(
INDEX_NAME VARCHAR2 (30 CHAR)
, TABLE_NAME VARCHAR2 (30 CHAR)
, QUERY CLOB
, STMT_ORDER NUMBER (3)
)
LOB (
QUERY)
STORE AS
(
TABLESPACE USERS
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE LOGGING)
TABLESPACE USERS
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX WBX_DDL_INDEXES_PK
ON WBX_DDL_INDEXES (INDEX_NAME, TABLE_NAME, STMT_ORDER)
NOLOGGING
TABLESPACE TOOLS
NOPARALLEL;
ALTER TABLE WBX_DDL_INDEXES ADD
( CONSTRAINT WBX_DDL_INDEXES_PK PRIMARY KEY
(INDEX_NAME, TABLE_NAME, STMT_ORDER)
USING INDEX WBX_DDL_INDEXES_PK ENABLE VALIDATE);
There are two procedures written, the first responsible for dropping a given index, the second for recreating it. Note that the procedure take a table_name as argument, where one would maybe expect a table_name, index_name combination.
However, since we are using this is a large datawarehouse, all indexes of a given table are dropped. We therefore don't need to specify the index_name.
The "DROP_INDEX" procedure looks like:
procedure drop_index
(p_table in varchar2
)
is
q clob;
l_count number(3);
-- Use a cursor with a complex statement to determine all indexes and their types
cursor c1(p_table varchar2)
is
select ui.index_name, ui.table_name, ui.index_type, ui.uniqueness, uc.constraint_type
from user_indexes ui
left outer join user_constraints uc
on ui.index_name=uc.index_name
and ui.table_name=uc.table_name
where ui.table_name=p_table
and ui.index_type <> 'LOB'
order by uc.constraint_type;
begin
l_count := 1;
for r1 in c1(p_table)
loop
if r1.constraint_type = 'P'
then
q := sys.dbms_metadata.get_ddl('CONSTRAINT', r1.index_name);
begin
insert into wbx_ddl_indexes (index_name, table_name, query, stmt_order)
values ( r1.index_name, p_table, q, l_count);
exception
when dup_val_on_index then
null;
end;
-- if the index is a primary key, drop it using an "ALTER TABLE.."
execute immediate 'alter table '||p_table||' drop primary key';
else
q := sys.dbms_metadata.get_ddl('INDEX', r1.index_name);
begin
insert into wbx_ddl_indexes (index_name, table_name, query, stmt_order)
values ( r1.index_name, p_table, q, l_count);
exception
when dup_val_on_index then
null;
end;
-- if the index is a normal index, use the "DROP INDEX" statement
execute immediate 'drop index '||r1.index_name;
end if;
l_count := l_count+1;
commit;
end loop;
end;
The "CREATE_INDEX" procedure looks like:
procedure create_index
(p_table in varchar2
)
is
q clob;
exec_q varchar2(32767);
l_index varchar2(30);
l_length number(10);
-- Use a cursor to go through all saved index statements
cursor c1(l_table varchar2)
is
select index_name, query,stmt_order
from wbx_ddl_indexes
where table_name=l_table
order by stmt_order asc;
begin
for r1 in c1(p_table)
loop
begin
select index_name
into l_index
from user_indexes where table_name=p_table and index_name=r1.index_name;
exception
when no_data_found then
-- Index does not exist, create it
select query into exec_q
from wbx_ddl_indexes
where index_name=r1.index_name
and table_name=p_table
and stmt_order=r1.stmt_order;
execute immediate exec_q;
end;
-- As soon as the index is created, update the administration
delete
from wbx_ddl_indexes
where index_name = r1.index_name
and table_name=p_table
and stmt_order=r1.stmt_order;
commit;
end loop;
end;
It is too much to exactly explain the procedure here themselves. Anyone who can read PL/SQL code should be able to understand the procedures.
Feel free however to post questions or send me an Email.
No comments:
Post a Comment