Oracle 伪例
一: ROWNUM
1>ROWNUM 伪例是在存储数据的同时自动生成的. 它是按照数据查询的顺序进行排序的.
2>查询
(1)使用rownum=n (n为大于1的自然数时)逻辑会判断为FALSE
(2)使用rownum>n 逻辑会判断为FALSE , 这种情况下若要查出ROWNUM大于n的数据,则需要使用子查询
3>实际应用,用ROWNUM 查询TOP-N的情况
4> 实例:
以下转自: http://hi.baidu.com/t8500071/blog/item/9830ff7e1c79243c0dd7da45.html
首先我们建立一个表,并插入数据。
create table student (ID char(6), name VARCHAR2(100));
insert into student values('200001',‘张一’);
Insert into student values('200002',‘王二’);
insert into student values('200003',‘李三’);
insert into student values('200004',‘赵四’);
commit;
(1)关于rownum的排序
Rownum的顺序是在查询中自动按顺序生成的,如下列语句
Select rownum , ID , name from student order by name;
执行结果如下
ROWNUM ID NAME
------- ------ -------
3 200003 李三
2 200002 王二
1 200001 张一
4 200004 赵四
可以看到rownum的顺序不在是顺序排列的了,要让rownum顺序排列,我们可以用子查询实现。如:
select rownum , ID , name
from (select rownum , ID , name from student order by name)
执行结果如下
ROWNUM ID NAME
------- ------ --------
1 200003 李三
2 200002 王二
3 200001 张一
4 200004 赵四
(2) 关于rownum的查询
在条件中以Rownum作为条件进行查询有一些限制,首先我们对rownum等于某值的情况,执行下面的语句
select rownum , ID , name from student where rownum =1
显示如下:
ROWNUM ID NAME
------- ------ --------
1 200001 张一
如果执行
select rownum , ID , name from student where rownum =2
显示则为未选定行。
出现这种结果的原因是rownum=n (n为大于1的自然数时)逻辑会判断为FALSE。
所以查不到数据。
对于rownum大于某值的情况,执行下面的语句
select rownum , ID , name from student where rownum >2
执行的结果是未选定行。
如果要查询出rownum大于2的行,则需要用到子查询
select * from(select rownum no ,id,name from student) where no >=2;
查询结果为:
NO ID NAME
----- ------ ----------
2 200002 王二
3 200003 李三
4 200004 赵四
注意子查询中的rownum必须要有别名,否则还是不会查出记录来,这是因为rownum不是某个表的列,如果不起别名的话,无法知道rownum是子查询的列还是主查询的列。
对于rownum小于某值的查询条件
如果想找到第三条记录以前的记录,当使用rownum<3是能得到两条记录的。显然rownum对于rownum1的自然数)的条件认为是成立的,所以可以找到记录。
SQL> select rownum,id,name from student where rownum <3;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200001 张一
2 200002 王二
可能有时候需要查询rownum在某区间的数据,从上可以看出rownum对小于某值的查询条件是人为true的,rownum对于大于某值的查询条件直接认为是false的,但是可以间接的让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据,包括第二行和第三行数据,那么我们只能写以下语句,先让它返回小于等于三的记录行,然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。
select * from (select rownum no,id,name from student where rownum<=3 )
where no >=2;
NO ID NAME
---------- ------ ---------------------------------------------------
2 200002 王二
3 200003 李三
还可以用集合的方法
select rownum,id,name from student where rownum<=3
minus
select rownum,month,sell from sale where rownum<2
二: ROWID
以下内容转自:http://chorpin.**.com/blog/155681
1> 简介
rowid就是唯一标志记录物理位置的一个id,在oracle 8版本以前,rowid由file#+block#+row#组成,占用6个bytes的空间,10 bit 的 file# ,22bit 的 block# ,16 bit 的 row#。
从oracle 8开始rowid变成了extend rowid,由data_object_id#+rfile#+block#+row#组成,占用10个bytes的空间, 32bit的 data_object_id#,10 bit 的 rfile#,22bit 的 block#,16 bit 的 row#.由于rowid的组成从file#变成了rfile#,所以数据文件数的限制也从整个库不能超过1023个变成了每个表空间不能超过1023个 数据文件。
说了rowid的组成,那么我们再来看看rowid在索引里面占用的字节数又是什么样子的。在oracle 8以前索引中存储的rowid占用字节数也是6bytes,在oracle8之后,虽然oracle使用了extend rowid,但是在普通索引里面依然存储了bytes的rowid,只有在global index中存储的是10bytes的extend rowid,而extend rowid也是global index出现的一个必要条件,下面我们会解释原因。
为什么golbal index需要把data_object_id#也包含在index rowid entry中呢?如果不包含会这么样?首先我们需要知道index的rowid entry的存在是为了能根据它找到表的这条记录存在哪个具体的物理位置,我们需要知道它在哪个数据文件,在哪个block,在那一行,普通的索引 oracle根据rfile#,block#,row#就可以知道了,但是partition table可以分布在多个表空间,也就是可以分布在多个数据文件,当我们建立local index时,index rowid entry并不包含data_object_id#,因为oracle可以知道这个index对应的是哪一个table分区,并可以得到table分区的 ts#(tablespace号),那么oracle根据ts#和rfile#就可以找到具体的数据文件。但是如果换成是golbal index,如果不包含data_object_id#,那么我们并不能知道这个索引对应着哪个表分区,也自然不能知道它的rfile#和file#的转 换关系,所以它将找不到所对应的记录。包含data_object_id#后,oracle可以根据data_object_id#实现rfile#和 file#的转换然后找到记录对应的物理位置。需要注意的是要理解以上概念我们还是需要了解file#和rfile#的区别。
二.比较file#和rfile#
oracle数据文件为什么存在file#和rfile#?
归根结底的原因是因为 ROWID 的存储格式造成的,因为 rowid 中文件编号标志只有10bit,最大数据容量1024,由于不存在0编号文件,所以实际上只允许1023个文件编号。在oracle8 之前的版本的数据库中,rowid是受限的,只包括 file# /block# /row# ,则数据库最多只允许1023个文件。
而oracle8开始rowid 包括 data_object_id# / Rfile# /block# /rowid# 。data object id 的引入,同时支持了表分区的概念,一个表可以拥有多个分区(segment),而一个分区可以在不同的表空间中(由Rfile# 表示在segment对应的表空间中对应的 相对文件编号)。这样表的容量也增大了。 扩展的rowid使得oracle不再局限于数据文件只能有1023个的限制,而一个表可以分区,也使得表的容量不再局限于单个表空间中(1023个文件 的限制)。
当然,你或许要问,为什么oracle不调整rowid中表示 file# 的 bit数量,这个应该是由于兼容性的引起的,在 oracle7 的索引中存储的rowid就是 file# + block# + row# ,,因为这样处理后关于索引的存储,oracle8和oracle7没有发生变化(在oracle8中一个索引(可能分区)segment肯定对应了一个 表(可能分区)的segment,这个可以由数据字典关系得到,从而确立了 索引中的rowid 对应哪个 表空间中的数据文件),在升级的时候就不用关心 索引的问题,而直接升级oracle软件以及运行相关的包,否则将会大动干戈解决索引的问题。这就是oracle实现物理文件升级的基础。
当 然,真正升级的时候,一些数据文件头的 rfile# 需要发生变化,这也是有文件的一些存储的特性决定的,为了不和oracle8的格式发生冲突,才需要修改。这个修改代价非常的小,所以oracle选择了 这个方案。详细的信息,大家可以去参考metalink相关内容,有详细的 存储(byte 中字节位)的变化关系。
三.rowid举例
1.创建一临时表
create table test_rowid (id number, row_id rowid);
2.插入一行记录
insert into test_rowid values(1,null);
3.修改刚插入的记录
update test_rowid set row_id = rowid where id = 1;
4.查看rowid
select rowid,row_id from test_rowid;
返回结果为:
rowid row_id
AAAO0DAAJAAAAMYAAA AAAO0DAAJAAAAMYAAA
Oracle的物理扩展ROWID有18位,每位采用64位编码,分别用A~Z、a~z、0~9、+、/共64个字符表示。A表示0,B表示1,……Z表示25,a表示26,……z表示51,0表示52,……,9表示61,+表示62,/表示63。
ROWID具体划分可以分为4部分。
(1).OOOOOO:前6位表示DATA OBJECT NUMBER,将起转化位数字后匹配DBA_OBJECTS中的DATA_OBJECT_ID,可以确定表信息。
如上面例子中的DATA OBJECT NUMBER是AAAO0D,转化位数字是14×64×64 +52×64 + 3。
输入以下查询:
select owner, object_name from dba_objects where data_object_id = 14*64*64 + 52*64 + 3;
返回:
OWNER OBJECT_NAME
WG TEST_ROWID
(2)FFF:第7到9位表示相对表空间的数据文件号。
上面的例子中是AAJ,表示数据文件9。
输入以下查询:
(3).BBBBBB:第10到15位表示这条记录在数据文件中的第几个BLOCK中。
上面的例子是AAAAMY,转化位数字是12×64+24,表示这条记录在数据文件中的第792个BLOCK。
(4).RRR:最后3位表示这条记录是BLOCK中的第几条记录。
上面的例子是AAA,表示第0条记录(总是从0开始计数)。
二 实例:
以下内容转自: http://database.ccidnet.com/art/1105/20070111/999167_1.html
表CZ的结构如下:
SQL> desc cz
Name Null? Type
-----------------------------------------
C1 NUMBER(10)
C10 NUMBER(5)
C20 VARCHAR2(3)
删除重复记录的方法原理:
(1).在Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。
(2).在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中那些具有最大rowid的就可以了,其余全部删除。
重复记录判断的标准是:
C1,C10和C20这三列的值都相同才算是重复记录。
经查看表CZ总共有16条记录:
SQL>set pagesize 100
SQL>select * from cz;
C1 C10 C20
---------- ---------- ---
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
2 3 che
2 3 che
2 3 che
3 4 dff
3 4 dff
3 4 dff
4 5 err
5 3 dar
6 1 wee
7 2 zxc
20 rows selected.
1.查找重复记录的几种方法:
(1).SQL>select * from cz group by c1,c10,c20 having count(*) >1;
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff
(2).SQL>select distinct * from cz;
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff
(3).SQL>select * from cz a where rowid=(select max(rowid)
from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff
2.删除重复记录的几种方法:
(1).适用于有大量重复记录的情况(在C1,C10和C20列上建有索引的时候,用以下语句效率会很高):
SQL>delete cz where (c1,c10,c20) in (select c1,c10,
c20 from cz group by c1,c10,c20 having count(*)>1) and rowid not in
(select min(rowid) from cz group by c1,c10,c20 having count(*)>1);
SQL>delete cz where rowid not in
(select min(rowid) from cz group by c1,c10,c20);
(2).适用于有少量重复记录的情况(注意,对于有大量重复记录的情况,用以下语句效率会很低):
SQL>delete from cz a where a.rowid!=(select max(rowid)
from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);
SQL>delete from cz a where a.rowidfrom cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);
SQL>delete from cz a where rowid from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);
(3).适用于有少量重复记录的情况(临时表法):
SQL>create table test as select distinct * from cz;
(建一个临时表test用来存放重复的记录)
SQL>truncate table cz; (清空cz表的数据,但保留cz表的结构)
SQL>insert into cz select * from test;
(再将临时表test里的内容反插回来)
(4).适用于有大量重复记录的情况(Exception into 子句法):
采用alter table 命令中的 Exception into 子句也可以确定出库表中重复的记录。这种方法稍微麻烦一些,为了使用“excepeion into ”子句,必须首先创建 EXCEPTIONS 表。创建该表的 SQL 脚本文件为 utlexcpt.sql 。对于win2000系统和 UNIX 系统, Oracle 存放该文件的位置稍有不同,在win2000系统下,该脚本文件存放在$ORACLE_HOMEOra90rdbmsadmin 目录下;而对于 UNIX 系统,该脚本文件存放在$ORACLE_HOME/rdbms/admin 目录下。
具体步骤如下:
SQL>@?/rdbms/admin/utlexcpt.sql
Table created.
SQL>desc exceptions
Name Null? Type
-----------------------------------------
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)
SQL>alter table cz add constraint
cz_unique unique(c1,c10,c20) exceptions into exceptions;
*
ERROR at line 1:
ORA-02299: cannot validate (TEST.CZ_UNIQUE) - duplicate keys found
SQL>create table dups as select
* from cz where rowid in (select row_id from exceptions);
Table created.
SQL>select * from dups;
C1 C10 C20
---------- ---------- ---
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
2 3 che
2 3 che
2 3 che
3 4 dff
3 4 dff
3 4 dff
16 rows selected.
SQL>select row_id from exceptions;
ROW_ID
------------------
AAAHD/AAIAAAADSAAA
AAAHD/AAIAAAADSAAB
AAAHD/AAIAAAADSAAC
AAAHD/AAIAAAADSAAF
AAAHD/AAIAAAADSAAH
AAAHD/AAIAAAADSAAI
AAAHD/AAIAAAADSAAG
AAAHD/AAIAAAADSAAD
AAAHD/AAIAAAADSAAE
AAAHD/AAIAAAADSAAJ
AAAHD/AAIAAAADSAAK
AAAHD/AAIAAAADSAAL
AAAHD/AAIAAAADSAAM
AAAHD/AAIAAAADSAAN
AAAHD/AAIAAAADSAAO
AAAHD/AAIAAAADSAAP
16 rows selected.
SQL>delete from cz where rowid in
( select row_id from exceptions);
16 rows deleted.
SQL>insert into cz select distinct * from dups;
3 rows created.
SQL>select *from cz;
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff
4 5 err
5 3 dar
6 1 wee
7 2 zxc
7 rows selected.
从结果里可以看到重复记录已经删除
三 Level
以下转自http://mwei.**.com/blog/550858
定义如下id 和 名称描述
1— 省长 2— 市长 3— 县长 4— 镇长 5— 村长 其他(6-15)— 平民(没有管辖任何人)
只有省长、市长和县长能处理问题,那么每个人出了问题应该首先找谁来解决?
省长权利最大,自己出了问题自己解决,别人也没法知道; 可以认为在关系表里省长的父节点是自己。
create table person(id int primary key, description varchar2(50));
create table relationship(child int, parent int, primary key(child,parent));
insert into person values(1,‘省长’);
insert into person values(2,‘市长’);
insert into person values(3,‘县长’);
insert into person values(4,‘镇长’);
insert into person values(5,‘村长’);
insert into person values(6,‘平民’);
...//省略的都是平民
insert into person values(15,‘平民’);
insert into relationship values(1,1); //关系如下
insert into relationship values(2,1);
insert into relationship values(3,1);
insert into relationship values(5,2);
insert into relationship values(6,2);
insert into relationship values(4,3);
insert into relationship values(7,3);
insert into relationship values(8,3);
insert into relationship values(9,3);
insert into relationship values(10,4);
insert into relationship values(11,4);
insert into relationship values(12,4);
insert into relationship values(13,4);
insert into relationship values(14,5);
insert into relationship values(15,5);
查看一下关系树:
select rpad('---',(level-1)*3,'---')||child relation_tree
from relationship
start with child=parent
connect by nocycle prior child=parent; --结果如下
RELATION_TREE
------------------
1
---2
------5
---------14
---------15
------6
---3
------4
---------10
---------11
---------12
---------13
------7
------8
------9
已选择15行。
查看父子关系情况:
select child,parent
from relationship
start with child=parent
connect by nocycle prior child=parent
order by parent; --结果如下
CHILD PARENT
---------- ----------
1 1
2 1
3 1
5 2
6 2
4 3
8 3
7 3
9 3
11 4
12 4
10 4
13 4
14 5
15 5
已选择15行。
下面要看一看:每个人有事时,首先找到谁来处理?不能每个人有事都找省长吧。
下面的sql使用了oracle家的两个变态函数:first_value & connect_by_root
select distinct child ,first_value(parent)over(partition by child order by lv) parent
from(
select connect_by_root(r.child) child, p.description descr, level lv, r.parent
from person p ,relationship r
where p.id= r.parent
connect by nocycle prior r.parent=r.child
)
where descr in('省长', '市长', '县长')
order by parent,child; --结果如下
CHILD PARENT
---------- ----------
1 1
2 1
3 1
5 2
6 2
14 2
15 2
4 3
7 3
8 3
9 3
10 3
11 3
12 3
13 3
已选择15行。
这个sql到底行不行,再加条数据看看
insert into person values(333,‘县长’);
insert into person values(555,‘村长’);
insert into person values(666,‘平民’);
insert into person values(777,‘平民’);
insert into relationship values(333,1);
insert into relationship values(555,333);
insert into relationship values(666, 555);
insert into relationship values(777,666);
--666这个平民有¥,777愿意跟着他(这条记录比较特殊)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17087603/viewspace-675718/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17087603/viewspace-675718/