Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3180
  • Last Modified:

DROP AND RECREATE INDEX IN STORED PROCEDURE 11 G

In my stored procedure, I would like to truncate the table, drop index, and recreate after inserting data into the table.

What is the syntax for drop/ create index

Begin:

Truncate Table
Drop index1
Drop  index2

loop
--- other code
Insert data
commit;
end loop;

Create index1
Create index2

End;
/
0
cookiejar
Asked:
cookiejar
1 Solution
 
slightwv (䄆 Netminder) Commented:
To do DDL in a procedure you need execute immediate:

begin
execute immediate('truncate table tablename');
execute immediate('drop index table_index');
...
end;
/
0
 
DcpKingCommented:
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
0
 
slightwv (䄆 Netminder) Commented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
rather than dropping and recreating,  why not mark it unusable and then rebuild?

functionally equivalent, but by using rebuild you don't have to figure out how to preserve the list of columns, ascending/descending, storage options etc.

execute immediate('alter index your_index unusable');
execute immediate('truncate table your_table');
execute immediate('alter index your_index rebuild');


also, note, minor tweak, I mark the index unusable prior to truncating.  Eliminates the need to update the index as part of the truncate.
0
 
cookiejarAuthor Commented:
What does  execute immediate('alter index your_index unusable') do?  Is it equivalent to un indexing the indexed field?
0
 
sdstuberCommented:
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.
0
 
awking00Commented:
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;
0
 
GunasekaraneswaranCommented:
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
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now