Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, October 15, 2015

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.


.

No comments: