Oracle "Table"

Oracle table is a very very important chapter when i was studing the DBA course, Once I become a software engineer i feel very happy that i can forget the complex table structure concept which made me struggle in my OCP exam , leaving all those to DBA to handle. However I also feel lucky that i had studied that so that i can design a good table structure and erase lots of pain to my colleagues. Now I will do a tutorial about how it goes.

Table has lots of variaties ,not only including those table we create with the help of Hibernate/Ibatis or whatever, but something below:

• Normal Table
• Partition Table
• Index Organised Table(IoT)
• Cluster Table
• Temporary Table
• Nested Table (Tables in Table)
• Object Table

High water mark : This is like a location identifier. It's the location that the biggest data block occupies. as the graph shown above. When you delete the data , it still won't go down , unless you use "rebuild , truncate , shrink , alter table, move" which can bring down the high water mark. This is very important concept as when Oracle do a full table scan , it will scan those areas below  HWM, so it will greatly affect the efficiency of full scan if the HWM is too high. 

PCTFREE : PCTFREE = 20% , when user is inserting data, data blocks will keep 20% redundancy or space so that data won't be inserted into this block fully. so the space left will be used for users to do some update manipulations . by default  ： 10 %.

PCTUSED: PCTUSED = 40%, when user is doing delete , the "usage rate of data block"  <=  40% >= 60%, then let user do the insert manipulation.

ASSM : auto segment management, if this is on in tablespace, then you can only set PCTFREE, if off, you can set PCTFREE and PCTUSED both.

Create tablespace test01 datafile 'D:/app/test01.dbf' size 10m autoextend on //create table space
Create table t1(name varchar2(10)) tablespace test01; //create a table under test01
Select * from dba_extents where owner = 'scott' and segment_name = 't1';//check how many space used
Alter table scott.t1 allocate extent (datafile 'D:/app/test01.dbf' size 1m) // expand the storing space under tablespace t1, in reality useless.

Movement :

Alter table t1 move [tablespace user] //erase fragments in table t1[below HWM]

Alter table t1 move tablespace use2 // mave table into another tablespace, after Movement, index will be all gone, must rebuild

Analyze table t1 compute statistics for table ; // analyse table and generate statistics
Select table_name, blocks, num_rows from user_tables where table_name = 't2';
//check how many data block occupied by one table [below HWM] row by row，you must analyse first!

Shrink : the tablespace must set ASSM =Auto

Alter table t1 enable row movement [cascade] // turn on row movement function
Alter table t1 shrink space; // shrink table fragment, bring down HWM

Truncate :delete all table data but keep table structure, HWM is setted to the minimum, can't rollback

Drop table t1 [cascade constraints] [purge] //delete the table
Alter table t1 set unused column phone; 
Alter table t1 drop unused columns; //remove column, oracle will remove in some leisure time

Index organised table (IOT): it's ordered, data and index are stored in the same place, so when query, you only visit one data block(data and index together), compared with normal table (data and index seperated, occupy 2 blocks) , So if you query data using primary key very often , you can create a table as IOT.

Create table iot_student (sno, sname constraint pk primary key(sno)) organization index pctthreshhold 30 overflow tablespace users; // create a IOT 

Drop table iot_student; purge recyclebin; //remove IOT

Pctthreshhold //this param tells block proportion，when data exceeds this proportion，oracle will put all other columns except for primary key into overflow segment。

Cluster table : one data block holds data related to 2 related tables, when doing a join query, only need to scan one data block, very fast. steps :

• create a cluster segment
• create a relationship linking 2 tables with this cluster segment
• create an index for this cluster

Create cluster cluster1(code_key number); //create cluster
Create table student(sno1 number, sname varchar2(10)) cluster cluster1(sno1); //create table to link the cluster
Create table address(sno2 number, zz varchar2(10)) cluster cluster1(sno2); // create table to link the cluster

// student and address are in the same block, if query need join s.sno1 = a.sno2  the spped will be very fast

Create index index1 on cluster cluster1; // create a index for the cluster

Select * from user_clusters; // check all the cluster infor
Select * from user_clu_columns; // check which columns lined to which clusters
Drop table student; drop table address;drop cluster cluster1; // delete cluster

Temporary table : table is used and owned by each user session.

Create global temporary table tmp_student(sno int, sname, varchar(20)) on commit preserve rows; // create temp table，remove all data when quiting this session
Disconnect

drop table tmp_student; //delete the temp table

Create global temporary table tmp_student(sno int, sname, varchar(20)) on commit delete rows; // create temp table, then commit/rollback, all data will be removed

Select table_name, temporary , duration from user_tables where table_name = 'tmp_student'
// check temp table infor，temporary= whether it is temp table，duration = when to delete data

Here i only demostrate a few of tables ,if you are interested, please leave a msg.