Use partitioned indexes

SHAIKDB>create table part1 (i int,j int ,k int,l number,

      constraint iunique unique(i),

      constraint junique unique(j))

     partition by range(i)

    ( partition p1 values less than (10),

    partition p2 values less than (100),

    partition p3 values less than (10000));  

Table created.

SHAIKDB>begin

 2  for i in 1..1000 loop

 3  insert into part1 values (i,i,1,0);

 4  commit;

 5  end loop;

 6  end;

 7  /

PL/SQL procedure successfully completed.

SHAIKDB>select count(*) from part1 partition (p1);

 COUNT(*)

----------

    9

SHAIKDB>select count(*) from part1 partition (p2);

 COUNT(*)

----------

   90

SHAIKDB>select count(*) from part1 partition (p3);

 COUNT(*)

----------

      901

create table part2 (a int not null,b int,c number, constraint afkey foreign key(a) references part1(i))

    partition by reference(afkey);

Table created.

create table part3 (x int,y number,z number not null,constraint zfkey foreign key(z) references part1(j))

       partition by reference (zfkey);

Table created

SHAIKDB>select table_name,partition_name,high_value from dba_tab_partitions where table_name like 'PART_';

TABLE_NAME PARTITION_NAME         HIGH_VALUE

---------- ------------------------------ ----------

PART1       P1                 10

PART1       P2                 100

PART1       P3                 10000

PART2       P1

PART2       P2

PART2       P3

PART3       P1

PART3       P2

PART3       P3

.SHAIKDB>begin

    for i in 1..1000 loop

    insert into part2 values(i,i,i);

    commit;

    end loop;

    end;

    /  2    3    4    5    6    7  

PL/SQL procedure successfully completed.

SHAIKDB>begin

    for i in 1..1000 loop

    insert into part3 values(i,i,i);

    commit;

    end loop;

    end;

    /  2    3    4    5    6    7  

PL/SQL procedure successfully completed.

select table_name,partition_name,high_value from dba_tab_partitions where table_name like 'PART_';

TABLE_NAME PARTITION_NAME         HIGH_VALUE

---------- ------------------------------ ----------

PART1       P1                 10

PART1       P2                 100

PART1       P3                 10000

PART2       P1

PART2       P2

PART2       P3

PART3       P1

PART3       P2

PART3       P3

9 rows selected

SHAIKDB>create index part1idx on part1(k);

Index created.

.