• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1119
  • Last Modified:

Create and drop index dynamically

Hi,

I need to create procedure which dynamically enables and disables the index from a table and takes in two parameters owner and table_name:-

I have the following query:-

select 'alter index '||index_name||' unusable;' from all_indexes where owner = 'USTA_LKUP_MART' and table_name = 'MEMBR_LKUP_TB'

This should do the following tasks from the given table_name and owner.
alter index MEMBR_LKUP_BM5 unusable;
alter index MEMBR_LKUP_BM6 unusable;
alter index MEMBR_LKUP_BM7 unusable;
alter index MEMBR_LKUP_BM8 unusable;
alter index MEMBR_LKUP_BM9 unusable;
alter index XPKMEMBER_LOOKUP unusable;
alter index MEMBR_LKUP_BM1 unusable;
alter index MEMBR_LKUP_BM10 unusable;
alter index MEMBR_LKUP_BM2 unusable;
alter index MEMBR_LKUP_BM3 unusable;
alter index MEMBR_LKUP_BM4 unusable;
 
select 'alter index '||index_name||' unusable;' from all_indexes where owner = 'USTA_LKUP_MART' and table_name = 'MEMBR_LKUP_TB'

 
second query should be  
 
select 'alter index '||index_name||' rebuild;' from all_indexes where owner = 'USTA_LKUP_MART' and table_name = 'MEMBR_LKUP_TB'

This should do the following tasks from the given table_name.
 
alter index MEMBR_LKUP_BM5 rebuild;
alter index MEMBR_LKUP_BM6 rebuild;
alter index MEMBR_LKUP_BM7 rebuild;
alter index MEMBR_LKUP_BM8 rebuild;
alter index MEMBR_LKUP_BM9 rebuild;
alter index XPKMEMBER_LOOKUP rebuild;
alter index MEMBR_LKUP_BM1 rebuild;
alter index MEMBR_LKUP_BM10 rebuild;
alter index MEMBR_LKUP_BM2 rebuild;
alter index MEMBR_LKUP_BM3 rebuild;
alter index MEMBR_LKUP_BM4 rebuild;


Thanks
0
pagesheetal
Asked:
pagesheetal
  • 8
  • 5
  • 4
1 Solution
 
sdstuberCommented:
why do you want to do this?  would invisible indexes be better?
0
 
sdstuberCommented:
this should do it, but I still question if it's needed.

Also note, I modified your query to do lookup based on table_owner, not owner.
because an index can be owned by someone other than the table owner

And the generated sql uses quotes around owner and index_name to ensure non-standard indexes will be supported.

Also I added a third parameter so you can pick whether to disable (mark unusable) or enable (rebuild)

CREATE OR REPLACE PROCEDURE index_toggle(p_table_owner   IN VARCHAR2,
                                         p_table_name    IN VARCHAR2,
                                         p_toggle        IN VARCHAR2
                                        )
IS
BEGIN
    IF UPPER(p_toggle) IN ('ENABLE', 'DISABLE')
    THEN
        FOR i
            IN (SELECT    'alter index "'
                       || owner
                       || '"."'
                       || index_name
                       || CASE UPPER(p_toggle)
                              WHEN 'DISABLE' THEN '" unusable'
                              WHEN 'ENABLE' THEN '" rebuild'
                          END
                           sql_statement
                  FROM all_indexes
                 WHERE table_owner = p_table_owner AND table_name = p_table_name)
        LOOP
            DBMS_OUTPUT.put_line(i.sql_statement);

            EXECUTE IMMEDIATE i.sql_statement;
        END LOOP;
    END IF;
END;
0
 
pagesheetalAuthor Commented:
Hi,

Thank you. When I tried to compile the procedure I got invalid sql statement.
I need to write a procedure because that is the requirement.

Thanks
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
pagesheetalAuthor Commented:
Hi sdstuber,

I did not understand by invisible index.

Thanks
0
 
sdstuberCommented:
I don't know why you got a compilation error, that is the exact code I compiled on my system.

invisible indexes were introduces in 11g.

http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#BABDHCJD

"because that is the requirement."  - that doesn't really explain anything.

What is the purpose of this procedure?  Why do you want to mark all indexes unusable and then rebuild them?  What is it you're hoping to gain by doing so?
0
 
johnsoneSenior Oracle DBACommented:
Be aware that if you have an index marked unusable for some reason, the enable part of this process will rebuild it.  If you want this functionality, you need to modify this process so it can "remember" which indexes it marked unusable and only rebuild those indexes.
0
 
pagesheetalAuthor Commented:
Hi  sdstuber,

Well we have lot of bitmap indexes on the tables and when we are trying to load data into the table with the indexes it is giving a error of undo tablespace.  The DBA suggested that we need to disable the index and enable them dynamically after the insert. Please help me out because we are going on UAT testing tommorow and I need to get this procedure running before that.

Thanks
0
 
sdstuberCommented:
thanks,  yes disabling the indexes should help on the insert


what happens when you compile? what are the errors? what lines?
0
 
sdstuberCommented:
also, to address johnsone's comment above, you could add the following clause

AND (UPPER(p_toggle) = 'DISABLE' OR (status = 'UNUSABLE' AND UPPER(p_toggle) = 'ENABLE'))

Open in new window

0
 
pagesheetalAuthor Commented:
Hi,

Where should I add this statement.

Thanks
0
 
johnsoneSenior Oracle DBACommented:
I'm not sure that adding to the where clause would catch it.

Let's say I did this:

alter index indx1 unusable;
exec index_toggle (...,...,'DISABLE');
exec index_toggle (...,...,'ENABLE');

The second call to INDEX_TOGGLE with rebuild INDX1.  I want INDX1 to stay in an unusable state.  Probably not a real call for this, but I had a case where I ran into a big problem with a very similar situation.  We couldn't drop the index because it was vendor supplied and they expected it there by name, but it created problems for our queries so with permanently disabled it.  Then dynamic things like this come along and enable things we don't want enabled.  Just trying to save someone the middle of the night phone call.
0
 
pagesheetalAuthor Commented:
Hi  johnsone,

So where do you want me to add this statement then if not in where clause.

Thanks
0
 
sdstuberCommented:
>>> "Where should I add this statement. "
It's a where clause condition so add it to the end of the query


johnsone's exceptions can be extended to the disabling as well.  You might not want to disable everything and possibly not rebuild everything.

Also, if you have indexes that are the structure for an index-organized table you'll get an error.

Also note some indexes, like LOB indexes won't be disabled by the unusable.
That's probably ok for your usage, but may make the results look odd.
0
 
sdstuberCommented:
>>> So where do you want me to add this statement then if not in where clause.

you're not understanding his concern.

No sql clause can accomplish what he is saying by itself.

IF (and it is only a hypothetical) you have unusable indexes that were made so by some means other than this procedure and IF you want to keep those indexes unusable after running the enable option of this procedure THEN you will need to keep track of them somehow.
For instance, creating a new table and storing them there and then querying that table as a NOT IN filter when doing the enable to skip those indexes.

Do you need that sort of functionality?
0
 
johnsoneSenior Oracle DBACommented:
It is a big if.  But, the possibility exists and I just wanted to point out that is a shortfall of doing it the way it is being done here.  If it is not a concern to your process, then ignore it and go with what has already been posted.

If it is a concern and you need to account for it, the procedure needs to store which indexes it disables somewhere.  Without knowing the big picture of the whole process, we cannot recommend any one way.  Some options are table (probably the most versatile), temporary table, pl/sql table and that is just to name a few.  It all depends on your process.
0
 
sdstuberCommented:
I'm not discounting your concern at all.  It's a valid point.  I just want to make sure the asker understands what it is you've been saying and note any other possible exceptions that haven't been mentioned explicitly.

We certainly can't know every condition that might make enabling or disabling a particular index appropriate for the asker's system.  

I gave a procedure that provides the basic framework (it does work "as is") but, as with any answer, it will always fall on the readers to decide if and how to extend or limit it for their particular system and business rules.
0
 
johnsoneSenior Oracle DBACommented:
Totally understood.

It is a pit I fell into once, and was pointing it out because I wasn't sure that the asker would catch that one.

It should definitely be considered a framework.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 8
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now