DROP AND RECREATE INDEX IN STORED PROCEDURE 11 G

To do DDL in a procedure you need execute immediate:

begin
execute immediate('truncate table tablename');
execute immediate('drop index table_index');
...
end;
/

DROP INDEX IndexName1 ON TableName;
DROP INDEX IndexName2 ON TableName;
truncate table TableName
begin transaction
--insert data
commit
CREATE INDEX IndexName1 ON TableName (FieldName);
CREATE INDEX IndexName2 ON TableName (FieldName);

(You may need to insert a "GO;" after each DROP and INSERT line)

hth

Mike

That will not work since they posted "In my stored procedure".

>>(You may need to insert a "GO;" after each DROP and INSERT line)

Not in Oracle.

What does  execute immediate('alter index your_index unusable') do?  Is it equivalent to un indexing the indexed field?

sort of,  the index is still there
but, as the word implies,  it's "unusable",

so, even though the columns are indexed, the index won't be maintained by other operations (like the truncate, or inserts, or updates or deletes)

nor will it be included in the optimizer for selects, updates or deletes

in that sense the columns aren't indexed anymore because the index has no utility


after it is unusable it's just wasted space, but it has the advantage of being a complete definition which makes the rebuild easy.

Not quite, but close. It will still retain the characteristics of the index but allow for things like inserts to ignore the index, thereby improving performance on inserts, for example. One note, to be safe (although it's generally the default) you should issue the command -
alter session set skip_unusable_indexes=true;

You can also store the index DDL in a variable, drop the index and recreate it.

DECLARE
  lv_index_ddl VARCHAR2(32676);
BEGIN
  SELECT dbms_metadata.get_ddl('INDEX','<index_name>','<schema_name>')
  INTO lv_index_ddl
  FROM DUAL;
 
  Drop index

  Truncate table

  Insert data
  Commit

  EXECUTE IMMEDIATE lv_index_ddl;

 EXCEPTION
   --handle exception
END