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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

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: