Oracle基础

Oracle基础

 

sys/oracle1 - 选数据库- SYSDBA

先创建命名空间 - 创建用户 - 授权用户

 

如：

-- //创建临时表空间

SELECT * FROM DBA_DATA_FILES;
CREATE TEMPORARY TABLESPACE AREADEV_TMP
TEMPFILE 'O:\ORACLE\PRODUCT\10.2.0\ORADATA\EIPDB102\AREADEV_TMP01.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

  

-- //创建数据表空间

CREATE TABLESPACE AREADEV_DATA 
LOGGING
DATAFILE 'O:\ORACLE\PRODUCT\10.2.0\ORADATA\EIPDB102\AREADEV_DATA01.DBF' 
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

  

areadev/areapwd - 选数据库 - normal

创建表

 

之前居然从来没见到过此类写法，诸如

　　select * from t_spolicy d，V_POLICYEMPLOYEE g where d.policyno = g.policyno（+）

　　查阅相关资料才发现此法就是外联的另外一种表现形式其等同于

　　select * from t_spolicy d left join V_POLICYEMPLOYEE g on d.policyno = g.policyno，

　　同理 select * from t_spolicy d，V_POLICYEMPLOYEE g where d.policyno（+） = g.policyno，

　　就等同于select * from t_spolicy d right join V_POLICYEMPLOYEE g on d.policyno = g.policyno

 

 

ORACLE分页SQL语句 

1.根据ROWID来分

select * from t_xiaoxi where rowid in(
	select rid from (
		select rownum rn,rid from(
			select rowid rid,cid from t_xiaoxi  order by cid desc
		) where rownum<10000
	) where rn>9980)
	order by cid desc;

 

执行时间0.03秒

 

2.按分析函数来分

select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;

 

执行时间1.01秒

 

3.按ROWNUM来分

select * from (
	select t.*,rownum rn from(
		select * from t_xiaoxi order by cid desc) t 
	where rownum<10000)
where rn>9980;

 

执行时间0.1秒

其中t_xiaoxi为表名称，cid为表的关键字段，取按CID降序排序后的第9981-9999条记录，t_xiaoxi表有70000多条记录

个人感觉1的效率最好，3次之，2最差

 

以前分页习惯用这样的SQL语句:

select * from
	(select t.*,rownum row_num from mytable t order by t.id) b
	where b.row_num between 1 and 10

 

结果发现由于该语句会先生成rownum 后执行order by 子句,因而排序结果根本不对,后来在GOOGLE上搜到一篇文章,原来多套一层select 就能很好的解决该问题,特此记录,语句如下:

select * from
	(select a.*,rownum row_num from
		(select * from mytable t order by t.id desc) a
	) b where b.row_num between 1 and 10

 ..