8/2 SQL 進階課程的建置檔

drop user Aha;
drop user Andy;
drop user Ann;
drop user Brian;
drop user Charles;
drop user Cloud;
drop user Clover;
drop user Danniel;
drop user Dennis;
drop user Dylan;
drop user Edgar;
drop user Edward;
drop user Eric;
drop user Frank;
drop user Howard;
drop user James;
drop user Jay;
drop user Jimmy;
drop user John;
drop user Joseph;
drop user Ken;
drop user kidd;
drop user Leon;
drop user Niles;
drop user Rainmo;
drop user Ray;
drop user Richie;
drop user Steven;
drop user Vincent;
drop user Walter;
drop user Xiandong;
drop user Yh;
drop user Yihhann;
drop user Apple;
drop user Banana;
drop user Cobby;
drop user Emily;
drop user Franky;
drop user Gimmy;
drop user Hocky;
drop user Ivy;
drop user Jacky;
drop user Kenny;
drop user Lemon;


create user Aha identified by Aha123 quota 100m on users;
create user Andy identified by Andy123 quota 100m on users;
create user Ann identified by Ann123 quota 100m on users;
create user Brian identified by Brian123 quota 100m on users;
create user Charles identified by Charles123 quota 100m on users;
create user Cloud identified by Cloud123 quota 100m on users;
create user Clover identified by Clover123 quota 100m on users;
create user Danniel identified by Danniel123 quota 100m on users;
create user Dennis identified by Dennis123 quota 100m on users;
create user Dylan identified by Dylan123 quota 100m on users;
create user Edgar identified by Edgar123 quota 100m on users;
create user Edward identified by Edward123 quota 100m on users;
create user Eric identified by Eric123 quota 100m on users;
create user Frank identified by Frank123 quota 100m on users;
create user Howard identified by Howard123 quota 100m on users;
create user James identified by James123 quota 100m on users;
create user Jay identified by Jay123 quota 100m on users;
create user Jimmy identified by Jimmy123 quota 100m on users;
create user John identified by John123 quota 100m on users;
create user Joseph identified by Joseph123 quota 100m on users;
create user Ken identified by Ken123 quota 100m on users;
create user kidd identified by kidd123 quota 100m on users;
create user Leon identified by Leon123 quota 100m on users;
create user Niles identified by Niles123 quota 100m on users;
create user Rainmo identified by Rainmo123 quota 100m on users;
create user Ray identified by Ray123 quota 100m on users;
create user Richie identified by Richie123 quota 100m on users;
create user Steven identified by Steven123 quota 100m on users;
create user Vincent identified by Vincent123 quota 100m on users;
create user Walter identified by Walter123 quota 100m on users;
create user Xiandong identified by Xiandong123 quota 100m on users;
create user Yh identified by Yh123 quota 100m on users;
create user Yihhann identified by Yihhann123 quota 100m on users;
create user Apple identified by Apple123 quota 100m on users;
create user Banana identified by Banana123 quota 100m on users;
create user Cobby identified by Cobby123 quota 100m on users;
create user Emily identified by Emily123 quota 100m on users;
create user Franky identified by Franky123 quota 100m on users;
create user Gimmy identified by Gimmy123 quota 100m on users;
create user Hocky identified by Hocky123 quota 100m on users;
create user Ivy identified by Ivy123 quota 100m on users;
create user Jacky identified by Jacky123 quota 100m on users;
create user Kenny identified by Kenny123 quota 100m on users;
create user Lemon identified by Lemon123 quota 100m on users;


create role rl_index;
grant connect,resource to rl_index;

create public synonym COUNTRIES for hr.COUNTRIES;
create public synonym  DEPARTMENTS for hr.DEPARTMENTS;
create public synonym EMPLOYEES for hr.EMPLOYEES;
create public synonym JOBS for hr.JOBS;
create public synonym  JOB_HISTORY for hr.JOB_HISTORY;
create public synonym LOCATIONS for hr.LOCATIONS;
create public synonym REGIONS for hr.REGIONS;


grant select on hr.COUNTRIES to rl_index;
grant select on hr.DEPARTMENTS to rl_index;
grant select on hr.EMPLOYEES to rl_index;
grant select on hr.JOBS to rl_index;
grant select on hr.JOB_HISTORY to rl_index;
grant select on hr.LOCATIONS to rl_index;
grant select on hr.REGIONS to rl_index;

grant rl_index to Aha ;
grant rl_index to Andy ;
grant rl_index to Ann ;
grant rl_index to Brian ;
grant rl_index to Charles ;
grant rl_index to Cloud ;
grant rl_index to Clover ;
grant rl_index to Danniel ;
grant rl_index to Dennis ;
grant rl_index to Dylan ;
grant rl_index to Edgar ;
grant rl_index to Edward ;
grant rl_index to Eric ;
grant rl_index to Frank ;
grant rl_index to Howard ;
grant rl_index to James ;
grant rl_index to Jay ;
grant rl_index to Jimmy ;
grant rl_index to John ;
grant rl_index to Joseph ;
grant rl_index to Ken ;
grant rl_index to kidd ;
grant rl_index to Leon ;
grant rl_index to Niles ;
grant rl_index to Rainmo ;
grant rl_index to Ray ;
grant rl_index to Richie ;
grant rl_index to Steven ;
grant rl_index to Vincent ;
grant rl_index to Walter ;
grant rl_index to Xiandong ;
grant rl_index to Yh ;
grant rl_index to Yihhann ;
grant rl_index to Apple ;
grant rl_index to Banana ;
grant rl_index to Cobby ;
grant rl_index to Emily ;
grant rl_index to Franky ;
grant rl_index to Gimmy ;
grant rl_index to Hocky ;
grant rl_index to Ivy ;
grant rl_index to Jacky ;
grant rl_index to Kenny ;
grant rl_index to Lemon ;

alter user scott identified by systex7720 account unlock;


create public synonym emp for scott.emp;
create public synonym dept for scott.dept;
create public synonym bonus for scott.dept;
create public synonym SALGRADE for scott.SALGRADE;

grant select on scott.emp to rl_index;
grant select on scott.dept to rl_index;
grant select on scott.dept to rl_index;
grant select on scott.SALGRADE to rl_index;

8/2 SQL 進階課程的練習檔

1、開啟isqlplus

2、登入自已的帳號

3、開始 autotrace
set autot on;

4、全表格掃描
select * from employees;

5、修改List 長度

6、掃描-1
全表格掃描

select * from employees;
Rowid 掃描

select * from employees where rowid='AAAAAAAAAAAAAAA';

7、索引掃描

create table big1 as select * from dba_objects;

create index big1_idx on big1(object_id);

8、收集apple 帳號下，big1 table & index 統計值

execute dbms_stats.gather_table_stats('apple','big1',cascade=>true);

select object_name from big1 where object_id=1000;

9、快速索引掃描

select count(object_id) from big1 ;

10、強迫使用index

Select object_id,object_name from big1 where object_id > 1000;

select /*+INDEX(big1 big1_idx)*/ object_id,object_name from big1 where object_id > 1000;

10、建立IOT Table

create table IOT_Big2(
OWNER   VARCHAR2(30),
OBJECT_NAME   VARCHAR2(128),
SUBOBJECT_NAME   VARCHAR2(30),
OBJECT_ID   NUMBER,
DATA_OBJECT_ID   NUMBER,
OBJECT_TYPE   VARCHAR2(19),
CREATED   DATE,
LAST_DDL_TIME   DATE,
TIMESTAMP   VARCHAR2(19),
STATUS   VARCHAR2(7),
TEMPORARY   VARCHAR2(1),
GENERATED   VARCHAR2(1),
SECONDARY   VARCHAR2(1),
constraint pk_object_id primary key(object_id))
organization index tablespace users;

複制資料
insert into iot_big2 select * from big1;

收集資料& 看差異
execute dbms_stats.gather_table_stats('apple','iot_big2',cascade=>true);

select * from user_tables;


建立額外的index在iot table 上

create index big2_idx1 on iot_big2(object_name);

11、index unusable

alter table big1 move;

. rebuild index

alter index big1_idx rebuild online;

12、尋找欄位基數

select column_name,num_distinct from user_tab_cols where table_name='BIG1';

13、clustering_factor 示範

create table t1 as select * from dba_objects order by object_name;

create table t2 as select * from t1 order by object_id;

create index t1_id_idx on t1(object_id);
create index t2_id_idx on t2(object_id);

execute dbms_stats.gather_table_stats('apple','t1');

execute dbms_stats.gather_table_stats('apple','t2');


select table_name,num_rows,blocks from user_tables where table_name in ('T1','T2');


select index_name,clustering_factor,blevel,leaf_blocks from dba_indexes where index_name in ('t1_id_idx','t2_id_idx');


Select object_id,object_name from t1 where object_id between 2000 and 3500;

Select object_id,object_name from t2 where object_id between 2000 and 3500;

14、複合index示範

create table big2 as select * from dba_objects;


create index big2_idx1 on big2(object_id);

execute dbms_stats.gather_table_stats('apple','big2');

set autot on
select object_name from big2 where object_id=1000;

create index big2_idx2 on big2(object_name);

drop index big2_idx1;
drop index big2_idx2;

create index big2_idx3 on big2(object_id,object_name);

select object_name from big2 where object_id=1000;

15、反轉index

清查目前的index
select index_name ,table_name, index_type from user_indexes;

drop index big2_idx3;

建立反轉index

create index big2_id_idx4 on big2(object_id) reverse;

基於函數索引

create index big1_name_idx on big1(object_name);
execute dbms_stats.gather_table_stats('apple','big1');


select object_id,object_name,object_type from big1 where object_name='TAB$';
select object_id,object_name,object_type from big1 where upper(object_name)='TAB$';
select object_id,object_name,object_type from big1 where object_name=upper('tab$');

drop index big1_name_idx;
drop index big1_name_idx2;
create index big1_name_idx2 on big1(upper(object_name));


16、bad sql

null & not null

select * from dept where deptno =10;
select * from dept where deptno is not null;


select ename from emp where empno != 0;