oracle常用
创建、删除用户：
create user lgehr identified by lgehr default tablespace users quota 1000M on users;
grant create session, create table, create view to lgehr;
grant dba to lgehr;
drop user lgehr cascade;
创建、删除表空间：
CREATE TABLESPACE "LGEHR"
LOGGING
DATAFILE ‘F:\TABLESPACE\LGEHR.dbf‘ SIZE 2000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
drop tablespace "TB42" including contents and datafiles;
创建多记录测试表：
create table t3 as select rownum as id ,rownum+1 as id2,rpad(‘*‘,1000,‘*‘) as contents from dual connect by level<=100;
trace报告设置：
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出
显示执行耗时：
set timing on
索引：
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create index idx1_object_id on t(object_id);
set autotrace on
select count(*) from t; t表含有索引列object_id，但object_id列没有设置not null，查询不走索引 【因为索引不能存储空值】
select count(*) from t where object_id is not null; 语句中用了is not null过滤，查询走索引
select count(*) from t; 设置为非空列，查询走索引，alter table t modify OBJECT_ID not null;
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
