Skip to Main Content
  • Questions

  • Dropping and recreating indexes using dynamic DDL

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jermin.

Asked: September 15, 2010 - 7:14 pm UTC

Last updated: September 17, 2010 - 7:08 am UTC

Version: 9.2.0.1.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I need to do the following

At the begining (one off) Store indexes DDL for a specific TABLE using DBMS_METADATA.GET_DDL in a table "test_t_index_ddl".


Drop the indexes, load data then re-create the indexes using the stored details.

I can NOT truncate the data in test_t_index_ddl every time (just in case of load failure and re-run the job "in this case no indexes will be there").
So I need to Merge the data in that table with Oracle current details to get up todate dynamic DDL. Then use these DDLs to re-create the indexes.





CREATE TABLE test_t ("DEPTNO" NUMBER(2) NOT NULL,
"DNAME" VARCHAR2(14 byte), "LOC" VARCHAR2(13 byte),
CONSTRAINT "t_PRIMARY_KEY" PRIMARY KEY("DEPTNO")
)
/


CREATE BITMAP INDEX LOC_INDEX ON TEST_T(DNAME);



create table test_t_index_ddl as select index_name, DBMS_METADATA.GET_DDL('INDEX',u.index_name) || ';' TEXT
FROM ALL_indexes u
WHERE
table_name = 'TEST_T'
/


NOW I need to compare the DDL in the table test_t_index_ddl with Oracle (in case something changed)


SQL> select a.*
2 from
3 (select index_name,
4 DBMS_METADATA.GET_DDL('INDEX',u.index_name) || ';' TEXT
5 FROM user_indexes u
6 WHERE
7 TABLE_NAME = 'TEST_T'
8 ) a,
9 test_t_index_ddl T
10 where a.index_name =t.index_name
11 and t.text <> a.text ;
and t.text <> a.text
*
ERROR at line 11:
ORA-00932: inconsistent datatypes: expected - got CLOB



Please advise how can I achieve that?


Many thanks.

and Tom said...

change the requirement, what you need to do is either:

o set the indexes unusable - leaving them in place.
o delete and reload the data (lots of work here - delete is expensive)
o rebuild the indexes

that way you'll NEVER lose any (you do not use unique indexes in this case, you use only non-unique indexes - but that is OK, you don't need unique indexes for primary keys or uniqueness)

something like this:

ops$tkyte%ORA9IR2> create table t as select * from all_users;

Table created.

ops$tkyte%ORA9IR2> create index t_pk on t(user_id);

Index created.

ops$tkyte%ORA9IR2> create bitmap index t_idx on t(username);

Index created.

ops$tkyte%ORA9IR2> alter table t add constraint t_pk primary key(user_id);

Table altered.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> alter index t_pk unusable;

Index altered.

ops$tkyte%ORA9IR2> alter index t_idx unusable;

Index altered.

ops$tkyte%ORA9IR2> alter session set skip_unusable_indexes=true;

Session altered.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> alter table t modify constraint t_pk DISABLE keep index;

Table altered.

ops$tkyte%ORA9IR2> delete from t;

32 rows deleted.

ops$tkyte%ORA9IR2> insert into t select * from all_users;

32 rows created.

ops$tkyte%ORA9IR2> alter index t_idx rebuild;

Index altered.

ops$tkyte%ORA9IR2> alter index t_pk rebuild;

Index altered.

ops$tkyte%ORA9IR2> alter table t modify constraint t_pk enable;

Table altered.



OR


a) use partitioning and load a partition at a time.

advantage here is that you can direct path load (skip undo/redo on the table if you want), direct path loads maintain indexes very efficiently (you don't have to full scan the table over and over and over for each index rebuild!), and you can use truncate to remove the old data (NO DELETE!!)

ops$tkyte%ORA9IR2> create table t
  2  (
  3  part_id, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID
  4  )
  5  PARTITION BY list (part_id)
  6  (
  7    PARTITION part1 VALUES (1),
  8    PARTITION part2 VALUES (2)
  9  )
 10  as
 11  select 1 part_id, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID
 12    from all_objects where rownum <= 10000;

Table created.

ops$tkyte%ORA9IR2> create index t_idx on t(owner,object_name) local;

Index created.

<b>now part1 has your data, part2 is EMPTY...</b>

ops$tkyte%ORA9IR2> insert /*+ APPEND */ into t
  2  select 2 part_id, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID
  3    from all_objects where rownum <= 10000;

10000 rows created.

<b>now part2 has your new data - but no one can see it right now - the indexes are already 'rebuilt' and everything, all we need to do is commit to 'release' the new data - but we have the old data in part1 to worry about - no worries though:</b>

ops$tkyte%ORA9IR2> alter table t truncate partition part1;

Table truncated.



The data is now released in part2 and part1 is empty waiting for the next load...


OR

a) create new table
b) load new table
c) index new table
d) drop old, rename new




I know this does not technically answer your question - but only because I don't think you want to do what you were planning on doing.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jermin Dawoud, September 17, 2010 - 12:46 am UTC 

Thanks Tom.

Actually this request is for MV (which is refreshed "Complete" once a week).

I've tried "option 1" provided by you and found that once the MV refreshed, all the indexes are valid.  Also using this way, time taken to refresh the "real" MV is about double the time taken if the indexes are dropped first then re-created - please see the following.

Note:  Fast refresh can NOT be used for this MV.
And I can not use partitioning for MV in 9.2



SQL> CREATE MATERIALIZED VIEW mv_t 
  2  NOLOGGING
  3  PARALLEL ( DEGREE DEFAULT INSTANCES 1 )
  4  BUILD IMMEDIATE
  5  REFRESH FORCE ON DEMAND
  6  WITH PRIMARY KEY
  7  as
  8  select * from all_users;

Materialized view created.

SQL> 
SQL> 
SQL> create index t_pk on mv_t(user_id);

Index created.

SQL> create bitmap index t_idx on mv_t(username);

Index created.

SQL> alter table mv_t add constraint t_pk primary key(user_id);

Table altered.

SQL> alter index t_pk unusable;

Index altered.


SQL> alter index t_idx unusable;

Index altered.




  1  select index_name, status
  2  from user_indeXes
  3* where table_name = 'MV_T'
SQL> /

INDEX_NAME                     STATUS
------------------------------ --------
T_PK                           UNUSABLE
T_IDX                          UNUSABLE

SQL> begin
  2  dbms_mview.refresh ('MV_T','C');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> select index_name, status
  2  from user_indeXes
  3  where table_name = 'MV_T';

INDEX_NAME                     STATUS
------------------------------ --------
T_PK                           VALID
T_IDX                          VALID

Tom Kyte
September 17, 2010 - 7:08 am UTC

You didn't mention it was an MV, you are not using my option 1 at all - quite different.

If you want a complete refresh, you don't have to use a materialized view at all, you can just use the steps - have you considered that.

Instead of having a materialized view schedule a job, you schedule your own job that consists of those steps.

It would be easier and safer than the approach you are thinking about.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.