Oracle基础

Oracle基础

 

sys/oracle1 - 选数据库- SYSDBA

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

 

如：

-- //创建临时表空间

SELECT * FROM DBA_DATA_FILES;CREATE TEMPORARY TABLESPACE AREADEV_TMPTEMPFILE 'O:\ORACLE\PRODUCT\10.2.0\ORADATA\EIPDB102\AREADEV_TMP01.DBF'SIZE 32MAUTOEXTEND ONNEXT 32M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL;

  

-- //创建数据表空间

CREATE TABLESPACE AREADEV_DATA LOGGINGDATAFILE 'O:\ORACLE\PRODUCT\10.2.0\ORADATA\EIPDB102\AREADEV_DATA01.DBF' SIZE 32MAUTOEXTEND ONNEXT 32M MAXSIZE 2048MEXTENT 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) bwhere 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

 ..