Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Thanks for the question, Ankit kumar.

Asked: October 05, 2016 - 2:20 pm UTC

Last updated: October 05, 2016 - 3:31 pm UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

I want to create a table based on existing table where indexing of existing table is also copied.

I have a table named 'A' having index defined on few columns. I want to create a table 'B' same as 'A' and having the same indexing.
Using
Create table B select * from A;

here indexing of A is not copied to created table B

and Chris said...

Correct. Create table as select doesn't copy the indexes! You need to copy them manually.

You could create a script to generate the definitions by querying user_ind_columns along the lines of:

create table t (
  x int,
  y int,
  z int
);

create index i1 on t(x);
create index i2 on t(y, z);
create index i3 on t(z, x);

select 'create index ... on ... (' ||
       listagg(column_name, ',') within group (order by column_position) ||
       ');' inds
from   user_ind_columns
where  table_name = 'T'
group  by index_name;

INDS
create index ... on ... (X);
create index ... on ... (Y,Z);
create index ... on ... (Z,X);

Or, if you also want to copy grants, triggers, etc. you could look into using dbms_redefinition.copy_table_dependents:

http://docs.oracle.com/database/121/ARPLS/d_redefi.htm#ARPLS67523
https://oracle-base.com/articles/10g/online-table-redefinition-enhancements-10gr1#redef-including-dependents

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