Oracle 9i & 10g programming Art-in-depth database architecture-Learning notes (ongoing updates)

--20170322

--1.0

--Update the statistics of the table
Begin
Dbms_stats.set_table_stats (user, ' EMP ', numrows = 10000);
End

Begin
Dbms_stats.set_table_stats (user, ' DEPT ', numrows = 10000);
End

--Re-view the statistics of the table
Select T.table_name,t.num_rows,t.blocks from User_tables t where T.table_name in (' EMP ', ' DEPT ');
/*
DEPT 10000 100
EMP 10000 100
*/

--2.0

--View the number of blocks of data occupied by the index, cluster factor
Select T1.index_name,t2.num_rows,t2.blocks, t1.clustering_factor from user_indexes t1 inner joins user_tables T2 on T1 . table_name=t2.table_name

/*
PK_YW_ZJFPJL 97 35 85
PK_YW_ZJFPDQ 3 5 1
Pk_yw_yjfpls_9 57 5 11
Pk_yw_yjfpls_8 35 5 1
Pk_yw_yjfpls_7 15 5 1
Pk_yw_yjfpls_6 19 5 1
*/

If the cluster factor is close to the number of blocks, the table is quite orderly and well organized. That is, an index entry in a leaf block points to a row in the same data block;

If the cluster factor is close to the number of rows, the order of the tables may be very random. That is, an index entry on the same leaf block is unlikely to point to a row in the same data block
--3.0

--type the use of the rules, pay attention to the red place OH
Create or replace type Emp_type
As object(
EMPNO Number (4),
Ename VARCHAR2 (10),
JOB VARCHAR2 (9),
MGR Number (4),
HireDate DATE,
SAL number (7,2),
COMM Number (7,2)
);

Create or replace type Emp_tab_type as table of Emp_type;

Oracle 9i & 10g programming Art-in-depth database architecture-Learning notes (ongoing updates)