Create Tables,index,cluster, Partition,....ect

1.Partion

CREATE TABLESPACE O321B
LOGGING
DATAFILE 'c:/O321A.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

CREATE TABLE City(
City_Code char(3),
City_Name varchar2(16),
CONSTRAINT City_PK PRIMARY KEY (City_Code))TABLESPACE O321B;



create table Supplier
(
Supplier_Number number(6) primary key,
Supplier_Name varchar2(64),
Address_Number number(5),
City char(3),
constraint Supplier_City_Null check (City is not null),
constraint Supplier_City_FK foreign key (City) references City (City_Code))


partition by list(City)(
partition south values('DUD','CHC') TABLESPACE O321A,
partition north values('WLG','AKL') TABLESPACE O321A);

SELECT * FROM Supplier PARTITION(south);

SELECT * FROM Supplier PARTITION(north);

2.CREATE CLUSTER

create table Order_Head
(
…
) CLUSTER Order_Cluster (Order_Number);
OR
CREATE CLUSTER Supplier_Cluster(City CHAR(3));
3.CREATE INDEX
1. In table
Create index St_Index on Student(St_age);
2. In cluster

CREATE INDEX Supplier_Cluster_Index
ON CLUSTER Supplier_Cluster;

4.ANALYZE

ANALYZE TABLE Student COMPUTE STATSTICS;
ANALYZE INDEX Student_Index COMPUTE STATSTICS;


5.Data Replication

CREATE MATERIALIZED VIEW Supplier_2
REFRESH COMPLETE
NEXT ROUND(SYSDATE)
AS SELECT * FROM SUPPLIER;
L.txtمرفق