以下是小编帮大家整理的oracle里的常用命令数据库教程，本文共8篇，仅供参考，希望能够帮助到大家。
篇1：oracle里的常用命令数据库教程
oracle
第一章：日志管理1.forcing log switches
sql>alter system switch logfile;
2.forcing checkpoints
sql>alter system checkpoint;
3.adding online redo log groups
sql>alter database add logfile [group 4]
sql>('/disk3/log4a.rdo','/disk4/log4b.rdo') size 1m;
4.adding online redo log members
sql>alter database add logfile member
sql>'/disk3/log1b.rdo' to group 1,
sql>'/disk4/log2b.rdo' to group 2;
5.changes the name of the online redo logfile
sql>alter database rename file 'c:/oracle/oradata/oradb/redo01.log'
sql>to 'c:/oracle/oradata/redo01.log';
6.drop online redo log groups
sql>alter database drop logfile group 3;
7.drop online redo log members
sql>alter database drop logfile member 'c:/oracle/oradata/redo01.log';
8.clearing online redo log files
sql>alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo';
9.using logminer analyzing redo logfiles
a. in the init.ora specify utl_file_dir = ' '
b. sql>execute dbms_logmnr_d.build('oradb.ora','c:\oracle\oradb\log');
c. sql>execute dbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log',
sql>dbms_logmnr.new);
d. sql>execute dbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log',
sql>dbms_logmnr.addfile);
e. sql>execute dbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora');
f. sql>select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters
sql>v$logmnr_logs);
g. sql>execute dbms_logmnr.end_logmnr;
第二章：表空间管理
1.create tablespaces
sql>create tablespace tablespace_name datafile 'c:\oracle\oradata\file1.dbf' size 100m,
sql>'c:\oracle\oradata\file2.dbf' size 100m minimum extent 550k [logging/nologging]
sql>default storage (initial 500k next 500k maxextents 500 pctinccease 0)
sql>[online/offline] [permanent/temporary] [extent_management_clause]
2.locally managed tablespace
sql>create tablespace user_data datafile 'c:\oracle\oradata\user_data01.dbf'
sql>size 500m extent management local uniform. size 10m;
3.temporary tablespace
sql>create temporary tablespace temp tempfile 'c:\oracle\oradata\temp01.dbf'
sql>size 500m extent management local uniform. size 10m;
4.change the storage setting
sql>alter tablespace app_data minimum extent 2m;
sql>alter tablespace app_data default storage(initial 2m next 2m maxextents 999);
5.taking tablespace offline or online
sql>alter tablespace app_data offline;
sql>alter tablespace app_data online;
6.read_only tablespace
sql>alter tablespace app_data read only|write;
7.droping tablespace
sql>drop tablespace app_data including contents;
8.enableing automatic extension of data files
sql>alter tablespace app_data add datafile 'c:\oracle\oradata\app_data01.dbf' size 200m
sql>autoextend on next 10m maxsize 500m;
9.change the size fo data files manually
sql>alter database datafile 'c:\oracle\oradata\app_data.dbf' resize 200m;
10.Moving data files: alter tablespace
sql>alter tablespace app_data rename datafile 'c:\oracle\oradata\app_data.dbf'
sql>to 'c:\oracle\app_data.dbf';
11.moving data files:alter database
sql>alter database rename file 'c:\oracle\oradata\app_data.dbf'
sql>to 'c:\oracle\app_data.dbf';
第三章：表
1.create a table
sql>create table table_name (column datatype,column datatype]....)
sql>tablespace tablespace_name [pctfree integer] [pctused integer]
sql>[initrans integer] [maxtrans integer]
sql>storage(initial 200k next 200k pctincrease 0 maxextents 50)
sql>[logging|nologging] [cache|nocache]
2.copy an existing table
sql>create table table_name [logging|nologging] as subquery
3.create temporary table
sql>create global temporary table xay_temp as select * from xay;
on commit preserve rows/on commit delete rows
4.pctfree = (average row size - initial row size) *100 /average row size
pctused = 100-pctfree- (average row size*100/available data space)
5.change storage and block utilization parameter
sql>alter table table_name pctfree=30 pctused=50 storage(next 500k
sql>minextents 2 maxextents 100);
6.manually allocating extents
sql>alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf');
7.move tablespace
sql>alter table employee move tablespace users;
8.deallocate of unused space
sql>alter table table_name deallocate unused [keep integer]
9.truncate a table
sql>truncate table table_name;
10.drop a table
sql>drop table table_name [cascade constraints];
11.drop a column
sql>alter table table_name drop column comments cascade constraints checkpoint 1000;
alter table table_name drop columns continue;
12.mark a column as unused
sql>alter table table_name set unused column comments cascade constraints;
alter table table_name drop unused columns checkpoint 1000;
alter table orders drop columns continue checkpoint 1000
data_dictionary : dba_unused_col_tabs
第四章：索引
1.creating function-based indexes
sql>create index summit.item_quantity on summit.item(quantity-quantity_shipped);
2.create a B-tree index
sql>create [unique] index index_name on table_name(column,.. asc/desc) tablespace
sql>tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]
sql>[logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0
sql>maxextents 50);
3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows
4.creating reverse key indexes
sql>create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k
sql>next 200k pctincrease 0 maxextents 50) tablespace indx;
5.create bitmap index
sql>create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k
sql>pctincrease 0 maxextents 50) tablespace indx;
6.change storage parameter of index
sql>alter index xay_id storage (next 400k maxextents 100);
7.allocating index space
sql>alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf');
8.alter index xay_id deallocate unused;
第五章：约束
1.define constraints as immediate or deferred
sql>alter session set constraint[s] = immediate/deferred/default;
set constraint[s] constraint_name/all immediate/deferred;
2. sql>drop table table_name cascade constraints
sql>drop tablespace tablespace_name including contents cascade constraints
3. define constraints while create a table
sql>create table xay(id number(7) constraint xay_id primary key deferrable
sql>using index storage(initial 100k next 100k) tablespace indx);
primary key/unique/references table(column)/check
4.enable constraints
sql>alter table xay enable novalidate constraint xay_id;
5.enable constraints
sql>alter table xay enable validate constraint xay_id;
第六章：LOAD数据
1.loading data using direct_load insert
sql>insert /*+append */ into emp nologging
sql>select * from emp_old;
2.parallel direct-load insert
sql>alter session enable parallel dml;
sql>insert /*+parallel(emp,2) */ into emp nologging
sql>select * from emp_old;
3.using sql*loader
sql>sqlldr scott/tiger \
sql>control = ulcase6.ctl \
sql>log = ulcase6.log direct=true
第七章：reorganizing data
1.using expoty
$exp scott/tiger tables(dept,emp) file=c:\emp.dmp log=exp.log compress=n direct=y
2.using import
$imp scott/tiger tables(dept,emp) file=emp.dmp log=imp.log ignore=y
3.transporting a tablespace
sql>alter tablespace sales_ts read only;
$exp sys/.. file=xay.dmp transport_tablespace=y tablespace=sales_ts
triggers=n constraints=n
$copy datafile
$imp sys/.. file=xay.dmp transport_tablespace=y datafiles=(/disk1/sles01.dbf,/disk2
/sles02.dbf)
sql>alter tablespace sales_ts read write;
4.checking transport set
sql>DBMS_tts.transport_set_check(ts_list =>'sales_ts' ..,incl_constraints=>true);
在表transport_set_violations 中查看
sql>dbms_tts.isselfcontained 为true 是， 表示自包含
第八章： managing password security and resources
1.controlling account lock and password
sql>alter user juncky identified by oracle account unlock;
2.user_provided password function
sql>function_name(userid in varchar2(30),password in varchar2(30),
old_password in varchar2(30)) return boolean
3.create a profile : password setting
sql>create profile grace_5 limit failed_login_attempts 3
sql>password_lock_time unlimited password_life_time 30
sql>password_reuse_time 30 password_verify_function verify_function
sql>password_grace_time 5;
4.altering a profile
sql>alter profile default failed_login_attempts 3
sql>password_life_time 60 password_grace_time 10;
5.drop a profile
sql>drop profile grace_5 [cascade];
6.create a profile : resource limit
sql>create profile developer_prof limit sessions_per_user 2
sql>cpu_per_session 10000 idle_time 60 connect_time 480;
7. view =>resource_cost : alter resource cost
dba_Users,dba_profiles
8. enable resource limits
sql>alter system set resource_limit=true;
第九章：Managing users
1.create a user: database authentication
sql>create user juncky identified by oracle default tablespace users
sql>temporary tablespace temp quota 10m on data password expire
sql>[account lock|unlock] [profile profilename|default];
2.change user quota on tablespace
sql>alter user juncky quota 0 on users;
3.drop a user
sql>drop user juncky [cascade];
4. monitor user
view: dba_users , dba_ts_quotas
第十章：managing privileges
1.system privileges: view =>system_privilege_map ,dba_sys_privs,session_privs
2.grant system privilege
sql>grant create session,create table to managers;
sql>grant create session to scott with admin option;
with admin option can grant or revoke privilege from any user or role;
3.sysdba and sysoper privileges:
sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile,
alter tablespace begin/end backup,recover database
alter database archivelog,restricted session
sysdba: sysoper privileges with admin option,create database,recover database until
4.password file members: view:=>v$pwfile_users
5.O7_dictionary_accessibility =true restriction access to view or tables in other schema
6.revoke system privilege
sql>revoke create table from karen;
sql>revoke create session from scott;
7.grant object privilege
sql>grant execute on dbms_pipe to public;
sql>grant update(first_name,salary) on employee to karen with grant option;
8.display object privilege : view =>dba_tab_privs, dba_col_privs
9.revoke object privilege
sql>revoke execute on dbms_pipe from scott [cascade constraints];
10.audit record view :=>sys.aud$
11. protecting the audit trail
sql>audit delete on sys.aud$ by access;
12.statement auditing
sql>audit user;
13.privilege auditing
sql>audit select any table by summit by access;
14.schema object auditing
sql>audit lock on summit.employee by access whenever successful;
15.view audit option : view=>all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,dba_obj_audit_opts
16.view audit result: view=>dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_session,dba_audit_statement
第十一章： manager role
1.create roles
sql>create role sales_clerk;
sql>create role hr_clerk identified by bonus;
sql>create role hr_manager identified externally;
2.modify role
sql>alter role sales_clerk identified by commission;
sql>alter role hr_clerk identified externally;
sql>alter role hr_manager not identified;
3.assigning roles
sql>grant sales_clerk to scott;
sql>grant hr_clerk to hr_manager;
sql>grant hr_manager to scott with admin option;
4.establish default role
sql>alter user scott default role hr_clerk,sales_clerk;
sql>alter user scott default role all;
sql>alter user scott default role all except hr_clerk;
sql>alter user scott default role none;
5.enable and disable roles
sql>set role hr_clerk;
sql>set role sales_clerk identified by commission;
sql>set role all except sales_clerk;
sql>set role none;
6.remove role from user
sql>revoke sales_clerk from scott;
sql>revoke hr_manager from public;
7.remove role
sql>drop role hr_manager;
8.display role information
view: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,role_sys_privs,role_tab_privs,session_roles
第十二章: BACKUP and RECOVERY
1. v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat
2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size
3. Monitoring Parallel Rollback
>v$fast_start_servers , v$fast_start_transactions
4.perform. a closed database backup (noarchivelog)
>shutdown immediate
>cp files /backup/
>startup
5.restore to a different location
>connect system/manager as sysdba
>startup mount
>alter database rename file '/disk1/../user.dbf' to '/disk2/../user.dbf';
>alter database open;
6.recover syntax
--recover a mounted database
>recover database;
>recover datafile '/disk1/data/df2.dbf';
>alter database recover database;
--recover an opened database
>recover tablespace user_data;
>recover datafile 2;
>alter database recover datafile 2;
7.how to apply redo log files automatically
>set autorecovery on
>recover automatic datafile 4;
8plete recovery:
--method 1(mounted databae)
>copy c:\backup\user.dbf c:\oradata\user.dbf
>startup mount
>recover datafile 'c:\oradata\user.dbf;
>alter database open;
--method 2(opened database,initially opened,not system or rollback datafile)
>copy c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline)
>recover datafile 'c:\oradata\user.dbf' or
>recover tablespace user_data;
>alter database datafile 'c:\oradata\user.dbf' online or
>alter tablespace user_data online;
--method 3(opened database,initially closed not system or rollback datafile)
>startup mount
>alter database datafile 'c:\oradata\user.dbf' offline;
>alter database open
>copy c:\backup\user.dbf d:\oradata\user.dbf
>alter database rename file 'c:\oradata\user.dbf' to 'd:\oradata\user.dbf'
>recover datafile 'e:\oradata\user.dbf' or recover tablespace user_data;
>alter tablespace user_data online;
--method 4(loss of data file with no backup and have all archive log)
>alter tablespace user_data offline immediate;
>alter database create datafile 'd:\oradata\user.dbf' as 'c:\oradata\user.dbf''
>recover tablespace user_data;
>alter tablespace user_data online
5.perform. an open database backup
>alter tablespace user_data begin backup;
>copy files /backup/
>alter database datafile '/c:/../data.dbf' end backup;
>alter system switch logfile;
6.backup a control file
>alter database backup controlfile to 'control1.bkp';
>alter database backup controlfile to trace;
7.recovery (noarchivelog mode)
>shutdown abort
>cp files
>startup
8.recovery of file in backup mode
>alter database datafile 2 end backup;
9.clearing redo log file
>alter database clear unarchived logfile group 1;
>alter database clear unarchived logfile group 1 unrecoverable datafile;
10.redo log recovery
>alter database add logfile group 3 'c:\oradata\redo03.log' size 1000k;
>alter database drop logfile group 1;
>alter database open;
or >cp c:\oradata\redo02.log' c:\oradata\redo01.log
>alter database clear logfile 'c:\oradata\log01.log';
篇2：Oracle 9i 约束条件数据库教程
约束条件就是Oracle数据库系统提供的对数据的完整性进行制约的机制，
Oracle 9i 约束条件数据库教程
。Oracle 9i允许创建5种约束条件。参见表7.8。创建检查约束条件
（1）在【管理目标导航器】中按照7.6节修改数据表结构的步骤进行操作。
（2）切换到图7.61所示的编辑表的【约束条件】选项卡。
（3）上述创建检查约束条件的SQL码如下?br> DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
ALTER TABLE “SCOTT”.“STUDENT”
ADD (CONSTRAINT “研究生编号检查约束条件”
CHECK(student_id>= and student_id<=0909))
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
【参见光盘文件】：\第7章\ createcheck.sql。
（4）读者也可以直接在【SQLPlus Worksheet】中执行createcheck.sql 文件完成检查约束条件的创建，如图7.62所示，
测试检查约束条件
（1）在7.63所示的【表数据编辑器】界面中按照图示内容输入，单击“应用(P)”按钮。
（2）上述输入数据的SQL代码如下。
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
INSERT INTO “SCOTT”.“STUDENT”
(“STUDENT_ID” ,“NAME” ,“PROFESSIONAL” ,“BIRTHDAY” ,“DIRECTOR_ID” )
VALUES (20010101 ,'纪晓芙' ,'软件工程' ,TO_DATE('15-7月 -1971', 'dd-Mon-yyyy HH:MI:SS AM') ,01)
DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
【参见光盘文件】：\第7章\ testcheck.sql。
（3）出现如图7.64所示界面。
（4）读者也可以直接在【SQLPlus Worksheet】中执行testcheck.sql 文件完成检查约束条件的测试，结果如图7.65所示。
篇3：删除Oracle 9i数据库数据库教程
（1）启动【数据库配置助手】，一直到出现如图6.44所示的【操作】界面，
删除Oracle 9i数据库数据库教程
。（2）出现如图6.45所示的【数据库】界面，
（3）出现如图6.46所示的【概要】界面。
（4）出现如图6.47所示的【删除确认】界面。
（5）成功删除数据库后出现如图6.48所示的【成功境】界面。单击“否”按钮?br>
篇4：ORACLE NUMBER类型详解数据库教程
1>.NUMBER类型细讲：
Oracle number datatype 语法：NUMBER[(precision [, scale])]
简称：precision -->p
scale -->s
NUMBER(p, s)
范围： 1 <= p <=38, -84 <= s <= 127
保存数据范围：-1.0e-130 <= number value < 1.0e+126
保存在机器内部的范围： 1 ~ 22 bytes
有效为：从左边第一个不为0的数算起的位数，
s的情况：
s >0
精确到小数点右边s位，并四舍五入。然后检验有效位是否 <= p。
s < 0
精确到小数点左边s位，并四舍五入。然后检验有效位是否 <= p + s。
s = 0
此时NUMBER表示整数。
eg:
Actual Data Specified As Stored As
----------------------------------------
123.89 NUMBER 123.89
123.89 NUMBER(3) 124
123.89 NUMBER(6,2) 123.89
123.89 NUMBER(6,1) 123.9
123.89 NUMBER(4,2) exceeds precision (有效位为5, 5 >4)
123.89 NUMBER(6,-2) 100
.01234 NUMBER(4,5) .01234 (有效位为4)
.00012 NUMBER(4,5) .00012
.000127 NUMBER(4,5) .00013
.0000012 NUMBER(2,7) .0000012
.00000123 NUMBER(2,7) .0000012
1.2e-4 NUMBER(2,5) 0.00012
1.2e-5 NUMBER(2,5) 0.00001
123.2564 NUMBER 123.2564
1234.9876 NUMBER(6,2) 1234.99
12345.12345 NUMBER(6,2) Error (有效位为5+2 >6)
1234.9876 NUMBER(6) 1235 (s没有表示s=0)
12345.345 NUMBER(5,-2) 12300
1234567 NUMBER(5,-2) 1234600
12345678 NUMBER(5,-2) Error (有效位为8 >7)
123456789 NUMBER(5,-4) 123460000
1234567890 NUMBER(5,-4) Error (有效位为10 >9)
12345.58 NUMBER(*, 1) 12345.6
0.1 NUMBER(4,5) Error (0.10000, 有效位为5 >4)
0.01234567 NUMBER(4,5) 0.01235
0.09999 NUMBER(4,5) 0.09999
篇5：Oracle 9i的数据类型数据库教程
Oracle 9i共提供了16种标量数据类型，如表7.4所示，
Oracle 9i的数据类型数据库教程
。表7.4 Oracle 9i的标量数据类型名称含义Char用于描述定长的字符型数据，长度<=字节varchar2用于描述变长的字符型数据，长度<=4000字节nchar用来存储Unicode字符集的定长字符型数据，长度<=1000字节nvarchar2用来存储Unicode字符集的变长字符型数据，长度<=1000字节number用来存储整型或者浮点型数值Date用来存储日期数据Long用来存储最大长度为2GB的变长字符数据Raw用来存储非结构化数据的变长字符数据，长度<=2000字节Long raw用来存储非结构化数据的变长字符数据，长度<=2GBrowid用来存储表中列的物理地址的二进制数据，占用固定的10个字节Blob用来存储多达4GB的非结构化的二进制数据Clob用来存储多达4GB的字符数据nclob用来存储多达4GB的Unicode字符数据Bfile用来把非结构化的二进制数据存储在数据库以外的操作系统文件中urowid用来存储表示任何类型列地址的二进制数据float用来存储浮点数
篇6：创建交叉报表(oracle)数据库教程
oracle|创建
创建交叉报表
create table t1(
goodid number(10) not null,
saledate date not null,
salesum number(10)
);
要求生成本年度每个月的产品销售状况表
m1 m2 m3 ... m12
g1
g2
.
.
.
gn
下面是生成报表的sql
SELECT goodid,
SUM(decode(to_char(saledate,'mm'),'01',salesum)) “01”,
SUM(decode(to_char(saledate,'mm'),'02',salesum)) “02”,
SUM(decode(to_char(saledate,'mm'),'03',salesum)) “03”,
SUM(decode(to_char(saledate,'mm'),'04',salesum)) “04”,
SUM(decode(to_char(saledate,'mm'),'05',salesum)) “05”,
SUM(decode(to_char(saledate,'mm'),'06',salesum)) “06”,
SUM(decode(to_char(saledate,'mm'),'07',salesum)) “07”,
SUM(decode(to_char(saledate,'mm'),'08',salesum)) “08”,
SUM(decode(to_char(saledate,'mm'),'09',salesum)) “09”,
SUM(decode(to_char(saledate,'mm'),'10',salesum)) “10”,
SUM(decode(to_char(saledate,'mm'),'11',salesum)) “11”,
SUM(decode(to_char(saledate,'mm'),'12',salesum)) “12”
from t1
where to_char(saledate,'yyyy') = ''
group by goodid
order by goodid;
篇7：输出oracle 对象源码数据库教程
oracle|对象
way 1:
通过spool输出到文件:
set serveroutput on
spool 'c:\log.txt'
DECLARE
v_text VARCHAR2(1000);
--i number(3):=0;
CURSOR cur IS
SELECT text
FROM sys.DBA_SOURCE
WHERE WNER = 'SCOTT'
AND NAME = 'TESTCCB';
BEGIN
OPEN cur;
LOOP
FETCH cur
INTO v_text;
--DBMS_OUTPUT.PUT_LINE(' cityname = ' ||v_name||' count='|| i);
DBMS_OUTPUT.put_line(v_text);
--i := i+ 1;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
/
spool off
way2:
利用 utl_file 包直接写出到文件
要求oracle用户对os文件系统有操作权限
DECLARE
v_text VARCHAR2(1000);
v_dir VARCHAR2(256);
v_owner VARCHAR2(128);
v_obj VARCHAR2(128);
l_output utl_file.file_type;
CURSOR cur IS
SELECT text
FROM dba_source
WHERE WNER = v_owner
AND NAME = v_obj;
BEGIN
v_owner := 'SCOTT';
v_obj := 'EMP';
SELECT t.directory_path INTO v_dir FROM all_directories t;
l_output := utl_file.fopen(v_dir, 'tab.txt', 'w');
utl_file.new_line(l_output);
utl_file.put_line(l_output,
'-- output owner :' || v_owner || ' object: ' ||
v_obj);
OPEN cur;
LOOP
FETCH cur
INTO v_text;
EXIT WHEN cur%NOTFOUND;
utl_file.new_line(l_output);
utl_file.put_line(l_output, v_text);
END LOOP;
utl_file.new_line(l_output);
utl_file.put_line(l_output, '-- output finished! ');
utl_file.fclose(l_output);
CLOSE cur;
END;
/
篇8：Oracle常}集(三)数据库教程
oracle
151. 如何O控 SGA 中字典n^的命中率？
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100
“miss ratio”,
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 “Hit ratio”
from v$rowcache
where gets+getmisses 0
group by parameter, gets, getmisses;
152. 如何O控 SGA 中共用存^的命中率，小於1% ？
select sum(pins) “Total Pins”, sum(reloads) “Total Reloads”,
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) “hit
radio”,sum(reloads)/sum(pins) “reload percent”
from v$librarycache;
153. 如何@示所有Y料煳锛的e和大小？
select count(name) num_instances ,type ,sum(source_size)
source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size
,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size)
+sum(error_size) size_required
from dba_object_size
group by type order by 2;
154. O控 SGA 中重做日I存^的命中率，小於1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
155. O控w和硬碟的排序比率，最好使它小於 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)',
'sorts (disk)');
156. 如何O控前Y料煺l在\行什麽SQLZ句？
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
157. 如何O控字典n^？
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) “LIB CACHE” FROM
V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) “ROW
CACHE” FROM V$ROWCACHE;
SELECT SUM(PINS) “EXECUTIONS”, SUM(RELOADS) “CACHE MISSES WHILE
EXECUTING” FROM V$LIBRARYCACHE;
後者除以前者,此比率小於1%,接近0%好，
SELECT SUM(GETS) “DICTIONARY GETS”,SUM(GETMISSES) “DICTIONARY
CACHE GET MISSES”
FROM V$ROWCACHE
158. O控 MTS
select busy/(busy+idle) “shared servers busy” from v$dispatcher;
此值大於0.5r，敌杓哟
select sum(wait)/sum(totalq) “dispatcher waits” from v$queue where
type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_serversr，敌杓哟
159. 如何知道前用舻ID
SQL>SHOW USER;
OR
SQL>select user from dual;
160. 如何查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY
segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP
BY segment_name);
162. 如何知道表在表空g中的存ηr
select segment_name,sum(bytes),count(*) ext_quan from dba_extents
where
tablespace_name='&tablespace_name' and segment_type='TABLE' group
by tablespace_name,segment_name;
163. 如何知道索引在表空g中的存ηr
select segment_name,count(*) from dba_extents where
segment_type='INDEX' and wner='&owner'
group by segment_name;
164、如何知道使用CPU多的用session
11是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40)
prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by
value desc;
165. 如何知道O器日I文件
以8I例
$ORACLE_HOME/NETWORK/LOG/LISTENER.LOG
166. 如何知道O器滴募
以8I例
$ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA
167. 如何知道TNS B接文件
以8I例
$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA
168. 如何知道Sql*Net h境文件
以8I例
$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA
169. 如何知道警告日I文件
以8I例
$ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG
170. 如何知道基本Y
以8I例
$ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL
171. 如何知道建立Y料字典D
以8I例
$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL
172. 如何知道建立用Y料字典D
以8I例
$ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL
173. 如何知道建立快照用Y料字典D
以8I例
$ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL
本v主要v的是SQLZ句的化方法! 主要基於ORACLE9I的.
174. /*+ALL_ROWS*/
表明φZ句Kx窕於_N的化方法,K@得最佳吞吐量,使Y源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
175. /*+FIRST_ROWS*/
表明φZ句Kx窕於_N的化方法,K@得最佳回rg,使Y源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
176. /*+CHOOSE*/
表明如果Y料字典中有L表的yY,⒒於_N的化方法,K@得最佳的吞吐量;
表明如果Y料字典中]有L表的yY,⒒於t_N的化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
177. /*+RULE*/
表明φZ句Kx窕於t的化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
178. /*+FULL(TABLE)*/
表明Ρ磉x袢局呙璧姆椒.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE
EMP_NO='CCBZZP';
179. /*+ROWID(TABLE)*/
提示明_表明χ付ū砀ROWIDM行L.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE
ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='CCBZZP';
180. /*+CLUSTER(TABLE)*/
提示明_表明χ付ū磉x翊呙璧脑L方法,它只Υ匚锛有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
181. /*+INDEX(TABLE INDEX_NAME)*/
表明Ρ磉x袼饕的呙璺椒.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE
FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
182. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明Ρ磉x袼饕N绲呙璺椒.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE
DPT_NO='CCBZZP';
183. /*+INDEX_COMBINE*/
指定表x顸cDL路,如果INDEX_COMBINE中]有提供作档乃饕,⑦x癯鳇cD索引的
布林M合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM
BSEMPMS
WHERE SAL<5000000 AND HIREDATE
184. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
提示明_命令化器使用索引作L路.
例如:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL<60000;
185. /*+INDEX_DESC(TABLE INDEX_NAME)*/
表明Ρ磉x袼饕降绲呙璺椒.
例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE
DPT_NO='CCBZZP';
186. /*+INDEX_FFS(TABLE INDEX_NAME)*/
χ付ǖ谋绦锌焖偃索引呙,而不是全表呙璧霓k法.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE
DPT_NO='TEC305';
187. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
提示明_M行绦幸的x,瘟兴饕的呙韬掀.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM
BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';
188. /*+USE_CONCAT*/
Σ樵中的WHERE後面的ORl件M行DQUNION ALL的M合查.
例如:
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND
SEX='M';
189. /*+NO_EXPAND*/
於WHERE後面的OR 或者IN-LIST的查Z句,NO_EXPAND⒆柚蛊浠於化器ζ溥M行U展.
例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND
SEX='M';
190. /*+NOWRITE*/
禁止Σ樵K的查重操作.
191. /*+REWRITE*/
可以⒁D作.
192. /*+MERGE(TABLE)*/
能σD的各查M行相的合.
例如:
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A
(SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE
A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
193. /*+NO_MERGE(TABLE)*/
於有可合愕囊D不再合.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS
A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE
A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
194. /*+ORDERED*/
根表出F在FROM中的序,ORDERED使ORACLE依此序ζ溥B接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2
B,TABLE3 C
WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
195. /*+USE_NL(TABLE)*/
⒅付ū砼c嵌套的B接的行源M行B接,K把指定表作炔勘.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/
BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS
WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
196. /*+USE_MERGE(TABLE)*/
⒅付ǖ谋砼c其他行源通^合闩判蜻B接方式B接起.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS
WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
197. /*+USE_HASH(TABLE)*/
⒅付ǖ谋砼c其他行源通^哈希B接方式B接起.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
198. /*+DRIVING_SITE(TABLE)*/
制cORACLE所x竦奈恢貌煌的表M行查绦.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE
BSEMPMS.DPT_NO=DEPT.DPT_NO;
199. /*+LEADING(TABLE)*/
⒅付ǖ谋碜B接次序中的首表.
200. /*+CACHE(TABLE)*/
M行全表呙r,CACHE提示能⒈淼z索K放置在n^存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
201. /*+NOCACHE(TABLE)*/
M行全表呙r,CACHE提示能⒈淼z索K放置在n^存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
202. /*+APPEND*/
直接插入到表的最後,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
203. /*+NOAPPEND*/
通^在插入Z句生存期韧VK行模式映R插入.
insert /*+noappend*/ into test1 select * from test4 ;
ORACLE化器
. x用m合的ORACLE化器
ORACLE的化器共有3N:
a. RULE (基於t) b. COST (基於成本) c. CHOOSE (x裥)
O置缺省的化器,可以通^init.ora文件中OPTIMIZER_MODE档母鞣N明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS
. 你然也在SQL句或是(session)ζ溥M行覆w.
了使用基於成本的化器(CBO, Cost-Based Optimizer) , 你必常\行analyze
命令,以增加Y料熘械奈锛yY(object statistics)的蚀_性.
如果Y料斓化器模式O置x裥(CHOOSE),那麽H的化器模式⒑褪欠襁\行^analyze命令有P.
如果table已被analyze^, 化器模式⒆映CBO , 反之,Y料裼RULE形式的化器.
在缺省情r下,ORACLE裼CHOOSE化器, 了避免那些不必要的全表呙(full table scan) ,
你必量避免使用CHOOSE化器,而直接裼没於t或者基於成本的化器.
2. LTable的方式
ORACLE 裼煞NL表中的方式:
a. 全表呙
全表呙杈褪琼序地L表中每l. ORACLE裼靡淮巫x入多Y料K(database block)的方式化全表呙.
b. 通^ROWIDL表
你可以裼没於ROWID的L方式情r,提高L表的效率, ,
ROWID包含了表中的物理位置Y..ORACLE裼盟饕(INDEX)F了Y料和存放Y料的物理位置(ROWID)之g的M.
通常索引提供了快速LROWID的方法,因此那些基於索引列的查就可以得到性能上的提高.
3. 共用SQLZ句
了不重徒馕鱿嗤的SQLZ句,在第一次解析之後, ORACLESQLZ句存放在w中.@K位於系y全局^域SGA(system
global area)的共用池(shared buffer pool)中的w可以被所有的Y料煊艄灿.
因此,你绦幸SQLZ句(有r被Q一游)r,如果它
和之前的绦羞^的Z句完全相同, ORACLE就能很快@得已被解析的Z句以及最好的
绦新. ORACLE的@功能大大地提高了SQL的绦行阅K省了w的使用.
可惜的是ORACLE只蔚谋硖峁└咚倬n(cache buffering) ,@功能K不m用於多表B接查.
Y料管理T必在init.ora中@^域O置合m的,@w^域越大,就可以保留更多的Z句,然被共用的可能性也就越大了.
你向ORACLE 提交一SQLZ句,ORACLE首先在@Kw中查找相同的Z句.
@Y需要注明的是,ORACLE烧袢〉氖且环N栏衿ヅ,要_成共用,SQLZ句必
完全相同(包括空格,Q行等).
共用的Z句必M足三l件:
A. 字元的比^:
前被绦械恼Z句和共用池中的Z句必完全相同.
例如:
SELECT * FROM EMP;
和下列每一都不同
SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;
B. Z句所指的物件必完全相同:
例如:
用 物件名 如何L
Jack sal_limit private synonym
Work_city public synonym
Plant_detail public synonym
Jill sal_limit private synonym
Work_city public synonym
Plant_detail table owner
考]一下下列SQLZ句能否在@用糁g共用.
SQL
能否共用
原因
select max(sal_cap) from sal_limit;
不能
每用舳加幸private synonym - sal_limit , 它是不同的物件
select count(*0 from work_city where sdesc like 'NEW%';
能
用粼L相同的物件public synonym - work_city
select a.sdesc,b.location from work_city a , plant_detail b where
a.city_id = b.city_id
不能
用jack 通^private synonymLplant_detail 而jill 是表的所有者,物件不同.
C. SQLZ句中必使用相同的名字的定(bind variables)
例如：
第一M的SQLZ句是相同的(可以共用),而第二M中的Z句是不同的(即使在\行r,x於不同的定迪嗤的值)
a.
select pin , name from people where pin = :blk1.pin;
select pin , name from people where pin = :blk1.pin;
b.
select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;
4. x褡钣行率的表名序(只在基於t的化器中有效)
ORACLE的解析器按照挠业阶蟮捻序理FROM子句中的表名,因此FROM子句中在最後的表(基A表 driving
table)⒈蛔钕忍理. 在FROM子句中包含多表的情r下,你必x裼l底钌俚谋碜基A表.ORACLE理多表r,
\用排序及合愕姆绞竭B接它.首先,呙璧谝表(FROM子句中最後的那表)KτM行派序,然後呙璧诙表(FROM子句中最後第二表),最後⑺有牡诙表中z索出的c第一表中合mM行合.
例如:
表 TAB1 16,384 l
表 TAB2 1 l
xTAB2作基A表 (最好的方法)
select count(*) from tab1,tab2 绦rg0.96秒
xTAB2作基A表 (不佳的方法)
select count(*) from tab2,tab1 绦rg26.09秒
如果有3以上的表B接查, 那就需要x窠徊姹(intersection table)作基A表,
交叉表是指那被其他表所引用的表.
例如:
EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT *
FROM LOCATION L ,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
⒈认铝SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
5. WHERE子句中的B接序．
ORACLE裼米韵露上的序解析WHERE子句,根@原理,表之g的B接必在其他WHEREl件之前,
那些可以^V掉最大盗坑的l件必在WHERE子句的末尾.
例如:
(低效,绦rg156.3秒)
SELECT …
FROM EMP E
WHERE SAL >50000
AND JOB = ‘MANAGER’
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
(高效,绦rg10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL >50000
AND JOB = ‘MANAGER’;
6. SELECT子句中避免使用 ‘ * ‘
你想在SELECT子句中列出所有的COLUMNr,使用BSQL列引用 ‘*’
是一方便的方法.不幸的是,@是一非常低效的方法. H上,ORACLE在解析的^程中, ’*’ 依次DQ成所有的列名,
@工作是通^查Y料字典完成的, @意味著⒑馁M更多的rg.
7. p少LY料斓拇
绦忻lSQLZ句r, ORACLE在炔绦辛嗽S多工作: 解析SQLZ句, 估算索引的利用率, 定 , xY料K等等.
由此可, p少LY料斓拇 , 就能H上p少ORACLE的工作量.
例如,
以下有三N方法可以z索出雇T等於0342或0291的T.
方法1 (最低效)
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME , SALARY , GRADE
FROM EMP
WHERE EMP_NO = 291;
方法2 (次低效)
DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME,SALARY,GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …,..,.. ;
…..
OPEN C1(291);
FETCH C1 INTO …,..,.. ;
CLOSE C1;
END;
方法3 (高效)
SELECT A.EMP_NAME , A.SALARY , A.GRADE,
B.EMP_NAME , B.SALARY , B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291;
注意:
在SQL*Plus , SQL*Forms和Pro*C中重新O置ARRAYSIZE, 可以增加每次Y料煸L的z索Y料量
,建h值200
8. 使用DECODE函p少理rg
使用DECODE函悼梢员苊庵呙柘嗤或重瓦B接相同的表.
例如:
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%’;
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE ‘SMITH%’;
你可以用DECODE函蹈咝У氐玫较嗤Y果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
似的,DECODE函狄部梢赃\用於GROUP BY 和ORDER BY子句中.
9. 整合,oP的Y料煸L
如果你有蔚馁Y料觳樵Z句,你可以把它整合到一查中(即使它之g]有PS)
例如:
SELECT NAME
FROM EMP
WHERE EMP_NO = 1234;
SELECT NAME
FROM DPT
WHERE DPT_NO = 10 ;
SELECT NAME
FROM CAT
WHERE CAT_TYPE = ‘RD’;
上面的3查可以被合愠梢:
SELECT E.NAME , D.NAME , C.NAME
FROM CAT C , DPT D , EMP E,DUAL X
WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = ‘RD’;
(g者按: m然袢∵@N方法,效率得到提高,但是程式的可x性大大降低,所以x者 是要嗪庵g的利弊)
10. h除重陀
最高效的h除重陀方法 ( 因使用了ROWID)
DELETE FROM EMP E
WHERE E.ROWID >(SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);
11. 用TRUNCATE替代DELETE
h除表中的r,在通常情r下, 回L段(rollback segments ) 用泶娣趴梢员换偷馁Y.
如果你]有COMMIT事,ORACLE①Y料恢偷h除之前的B(蚀_地f是
恢偷绦h除命令之前的r)
而\用TRUNCATEr,
回L段不再存放任何可被恢偷馁Y.命令\行後,Y料不能被恢.因此很少的Y源被{用,绦rg也很短.
(g者按: TRUNCATE只在h除全表m用,TRUNCATE是DDL不是DML)
12. 量多使用COMMIT
只要有可能,在程式中量多使用COMMIT, @映淌降男阅艿玫教岣,需求也因COMMIT所放的Y源而p少:
COMMIT所放的Y源:
a. 回L段上用於恢唾Y料的Y.
b. 被程式Z句@得的i
c. redo log buffer 中的空g
d. ORACLE管理上述3NY源中的炔炕ㄙM
(g者按: 在使用COMMITr必要注意到事盏耐暾性,F中效率和事胀暾性往往是~和熊掌不可得兼)
13. 算l
和一般的^c相反, count(*) 比count(1)稍快 , 然如果可以通^索引z索,λ饕列的等耘f是最快的. 例如
COUNT(EMPNO)
(g者按: 在CSDN中,曾Υ擞羞^相崃业挠,
作者的^cK不十分蚀_,通^H的y,上述三N方法K]有@著的性能差e)
14. 用Where子句替QHAVING子句
避免使用HAVING子句, HAVING 只在z索出所有之後才Y果集M行^V. @理需要排序,等操作.
如果能通^WHERE子句限制的的,那就能p少@方面的_N.
例如:
低效:
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
高效
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
GROUP BY REGION
(g者按: HAVING 中的l件一般用於σ恍┘合函档谋容^,如COUNT() 等等.
除此而外,一般的l件在WHERE子句中)
15. p少Ρ淼牟樵
在含有子查的SQLZ句中,要特e注意p少Ρ淼牟樵.
例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
Update 多Column 例子:
低效:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
16. 通^炔亢堤岣SQL效率.
SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
通^{用下面的函悼梢蕴岣咝率.
FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2
AS
TDESC VARCHAR2(30);
CURSOR C1 IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1 INTO TDESC;
CLOSE C1;
RETURN (NVL(TDESC,’ ’));
END;
FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2
AS
ENAME VARCHAR2(30);
CURSOR C1 IS
SELECT ENAME
FROM EMP
WHERE EMPNO=EMP;
BEGIN
OPEN C1;
FETCH C1 INTO ENAME;
CLOSE C1;
RETURN (NVL(ENAME,’ ’));
END;
SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),
H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
FROM EMP_HISTORY H
GROUP BY H.EMPNO , H.HIST_TYPE;
(g者按: 常在中看到如 ’能不能用一SQL出….’ 的N子, 殊不知}s的SQL往往奚了绦行率.
能蛘莆丈厦娴倪\用函到Q}的方法在H工作中是非常有意x的)
17. 使用表的e名(Alias)
在SQLZ句中B接多表r,
使用表的e名K把e名字首於每Column上.@右,就可以p少解析的rgKp少那些由Column歧x引起的Z法e`.
(g者注:
Column歧x指的是由於SQL中不同的表具有相同的Column名,SQLZ句中出F@Columnr,SQL解析器o法判噙@Column的w)
18. 用EXISTS替代IN
在S多基於基A表的查中,了M足一l件,往往需要α硪表M行接.在@N情r下, 使用EXISTS(或NOT
EXISTS)通常⑻岣卟樵的效率.
低效:
SELECT *
FROM EMP (基A表)
WHERE EMPNO >0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB’)
高效:
SELECT *
FROM EMP (基A表)
WHERE EMPNO >0
AND EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’)
(g者按: 相碚f,用NOT EXISTS替QNOT IN ⒏@著地提高效率,下一中⒅赋)
19. 用NOT EXISTS替代NOT IN
在子查中,NOT IN子句绦幸炔康呐判蚝秃. o在哪N情r下,NOT IN都是最低效的
(因它ψ硬樵中的表绦辛艘全表遍). 了避免使用NOT IN ,我可以把它改成外B接(Outer Joins)或NOT
EXISTS.
例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT=’A’);
了提高效率.改:
(方法一: 高效)
SELECT ….
FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = ‘A’
(方法二: 最高效)
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT ‘X’
FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);
20. 用表B接替QEXISTS
通常碚f , 裼帽磉B接的方式比EXISTS更有效率
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);
(更高效)
SELECT ENAME
FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CAT = ‘A’ ;
(g者按: 在RBO的情r下,前者的绦新桨括FILTER,後者使用NESTED LOOP)
更多推荐
oracle里的常用命令数据库教程
发布评论