| Create index script [message #180629] |
Tue, 04 July 2006 08:25  |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
Hi,
I've to drop & recreate the existing indexes.
I've wrritten the following script for the same,
select 'create ' || tab.INDEX_TYPE || ' ' || tab.INDEX_NAME || ' on ' || tab.TABLE_NAME || '(' ||col.column_name || '); '
from user_indexes tab,user_ind_columns col
order by tab.TABLE_NAME,tab.INDEX_NAME;
The problem in this script is,if a index is defined on multiple columns(X,Y,Z) of same table(TAB),
then the script gives the following output,
create normal index INDX_01 on TAB(x);
create normal index INDX_01 on TAB(y);
create normal index INDX_01 on TAB(z);
I wants to re-write the query in such a way that the output should
look this,
create normal index INDX_01 on TAB(x,y,z);
Please help in rewriting this script.
Thanks,
Gajini
|
|
|
|
| Re: Create index script [message #180639 is a reply to message #180629] |
Tue, 04 July 2006 08:59  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>> I've to drop & recreate the existing indexes.
Why not just rebuild the indexes (online or offline. Offline is better if you can afford to. If you can afford to drop the indexes, you can do it offline).
sql> alter index myindex rebuild;
If you are looking to extract the DDL of index,
use DBMS_METADATA.
Above all, why do you want to rebuild the indexes?
I wants to re-write the query in such a way that the output should
look this,
create normal index INDX_01 on TAB(x,y,z);
Can you define 'normal' index ( or 'abnormal' index, if that matters). Where did you find this term 'normal index' (in oracle world)?
[Updated on: Tue, 04 July 2006 08:59] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
