Oracle伪列之rowmun和rowid

rownum和rowid的共同点是什么？
都是伪劣

rowid是什么？
给一行，除非该行被删除并重新插入（就是说它是另一行，不再是原来那行了），rowid会保持不变。
G
Rowid在什么情况下会发生变化
       从Rowid定义可知,只有数据行的物理位置改变才会导致rowid改变,所以我们只需要关心那些操作会产生数据的物理位置的改变即可。
1）exp&imp（导出原数据，删除，重新导入）
2）alter table xx move[tablespace xxx]
3）alter table shrink space（此操作如果表中数据正好是物理存储上是连续的，则rowid不会发生变化）
move的效率和ctas差不多
4) 分区表中更新分区关键字导致记录从一个分区到另外一个分区，则rowid会发生改变

Rowid的应用：
1、提高查询速度
 查询实际上就是从数据块中读取记录，而Rowid标记了这个位置，故通过rowid定位记录是最快的方式。
 正因为此，故索引结构中最低级的块存储了索引键跟rowid来实现快速获取记录的。
通过索引查询的过程：扫描基于索引列的索引，通过索引键最终定位到Rowid，然后通过rowid访问此表，执行特定的数据块的读取。
2、RowID范围的应用
 RowID不同的范围则对应不同的数据块，故通过不同范围的RowID来对数据进行操作，可以保证每个操作对应的数据在固定的物理位置上，
这样可以避免竞争相同的共享磁盘资源
3、分区切片
一般按照区进行分区切片

应用示例：

删除

 1）批量更新数据的时候利用rowid来减少在线表的记录锁定时间。
实现方式：大数据量（或者是更新效率很低的语句）更新语句的转换---》将需要更新的记录的rowid先查询出来存储在临时表中，然后通过rowid来更新原表。
 这样看起来操作多了，但实际上对生产库的影响变小了很多，因为将定位需要更新的记录且需要锁定在线表的记录的时间转换成查询了。
结合batch collect批量更新数据 （此方法比使用唯一索引效率还高(请想一下?) ）
         将需要更新数据的条件定义成查询语句，用游标获取需要更新数据的rowid，然后通过rowid来更新记录。

Rowid的改变可能带来的影响：
1)    由于有些操作是可能带来rowid的改变的，故硬编码用另外一张表来存储rowid的定位记录是有风险的，不建议产品环境使用，
只建议在临时处理数据的时候使用，比如维护或者割接数据。
2)  由于索引是通过存储索引键跟rowid来快速定位记录的。故会引起rowid改变的操作必然会带来以下的情况：
  a)   索引失效，例如move表操作，需要rebuild索引，维护的时候切记此种高风险
  b)   带来维护索引的代价，例如shrink表操作，此操作会同时维护索引，故做此类操作的时候需要评估维护索引可能带来的风险

rownum

ROWNUM是什么？
           ROWNUM是一个伪列(不是真正的列，在表中并不真实存在），是oracle数据库从数据文件或缓冲区中读取数据的顺序。
请切勿理解成记录的行号（这是很多人一直这样认为的），比如说你想查询第二行记录按下面的方法是查询不到的。
         select * from dba_objects where rownum=2
由于表中的行没有行号，因此不能够查找表的第几行（大于1)记录
以下结构的查询中语句执行顺序解释
         Select …,rownum
              From t
              Where <where clause>
              Group by <column>
         Having <having clause>
              Order by <column>
实际执行顺序如下:
1)首先执行From和Where 子句(即查询部份)
2)从From/Where 中,Rownum分配给每个输出行并增1
3)使用Select
4)使用Group by
5)使用Having
6)数据被排序
    这就是为什么以下查询几乎肯定出错的原因:
select * from emp where rownum<=5 order by sal desc
           开发人员的意图应该是想得到薪水排行前5位的人,但实际得到的结果是5个按薪金排倒序的随机记录。

Rownum的应用
1、分页查询
        基本模式：
         Select *
             From(Select a.*,Rownum rnum
                           From (your_query_goes_here) a
                          Where Rownum <=:MAX_ROW_TO_FETCH)
           Where rnum>= :MIN:ROW_TO_FETCH;
          请避免使用下面的看起来等价的模式(?)：
          Select *
              From (Select a.*,Rownum rnum
                             From (your_query_goes_here) a)
          Where rnum between : MIN:ROW_TO_FETCH and <=:MAX_ROW_TO_FETCH
2、Top-N查询
Rownum有Top-N优化功能，使用它可以避免大数据集合的负担沉重的排序操作。例如下面的语句(假定salary为未索引列)
排序操作。例如下面的语句(假定salary为未索引列)
           Select * from (select * fro big_emp order by salary)  where ROWNUM<=N;
Oracle 的处理方式如下：
1. 全扫描 big_emp表 (不可避免)
2. 使用一个数组：有N个元素 (大概此时是在内存中), 仅排序N行
3、稳定执行计划

优化查询—实例化内嵌视图

select fileid, usernumber
  from disk_file_detail t
 where t.directoryid = (select d.directoryid
                          from disk_directory_info d
                         where d.directorylevel = 1
                           and d.directoryname = 'xxx'
                           and t.usernumber = d.usernumber
                           )  and   not exists (select 1
          from photo_photo_detail p
         where p.usernumber = t.usernumber
           and p.fileid = t.fileid)      
和
select fileid, usernumber
  from disk_file_detail t
 where t.directoryid = (select d.directoryid
                          from disk_directory_info d
                         where d.directorylevel = 1
                           and d.directoryname = 'xxx'
                           and t.usernumber = d.usernumber
                           and rownum=1)  and   not exists (select 1
          from photo_photo_detail p
         where p.usernumber = t.usernumber
           and p.fileid = t.fileid)      

两个表查询的结果集,然后再 rownum

这根rownum的机制有关系  rownum在哪个子查询里面  就先要把那个子查询的rownum给算出来 ...   

使用Rownum注意事项
1) 使用rownum来更新记录的设计的程序不可以并行
     Update os_sms_message_detail set status=1
         where createtime>trunc(sysdate) and status=0 and     rownum<=100
            因为按以上的更新条件在多进程并发进行的时候可能取到相同的记录来更新,就会出现堵塞了,特别是如果更新列还不是条件的话,
则还同时会出现重复更新。