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;