[20210301]延迟显示输出.txt

来源：这里教程网时间：2026-03-03 16:30:02 作者：

[20210301]延迟显示输出.txt --//一位网友提出一个奇怪要求,就是分段显示输出.我不知道应用是否很好实现. --//可以在select中加入sleep函数,自己测试看看. 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production CREATE OR REPLACE FUNCTION SCOTT.sleep (seconds IN NUMBER) RETURN NUMBER AS BEGIN sys.DBMS_LOCK.sleep (seconds); RETURN seconds; END; / SCOTT@book> set array 3 SCOTT@book> select emp.*,sleep(1) from emp ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SLEEP(1) ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 1 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 1 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 1 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 1 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 1 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 1 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 1 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 1 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 1 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 1 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 1 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 1 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 1 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 1 14 rows selected. Elapsed: 00:00:14.02 --//这样可以实现每次显示3条记录.估计这样不符合对方要求.一般更多地采用通过编程实现. 2.不过我发现这个可以满足一个很独特的测试,就是一致性读取测试,通过例子说明问题: SCOTT@book> create table t as select rownum id1,rownum id2,lpad(rownum||'x',10) vc from dual connect by level<=10; Table created. SCOTT@book> create unique index i_t_id1 on t(id1); Index created. SCOTT@book> create index i_t_id2 on t(id2); Index created. --//分析略. 3.测试: --//session 1: SCOTT@book> select * from t where id1=4 and sleep(12); --//session 2,以下命令最好事先输入,避免12秒内无法完成: SCOTT@book> update t set vc=upper(vc) where id1=4; 1 row updated. SCOTT@book> commit ; Commit complete. --//session 1: SCOTT@book> select * from t where id1=4 and sleep(12)=12; ID1 ID2 VC ---------- ---------- -------------------- 4 4 4X4X4X4X4X --//你可以发现vc输出是大写,而如果按照以前传统的理解,应该输出的是小写. 4.条件换成id2看看. --//session 1: SCOTT@book> select * from t where id2=3 and sleep(12)=12; --//session 2,以下命令最好事先输入,避免12秒内无法完成: SCOTT@book> update t set vc=upper(vc) where id1=3; 1 row updated. SCOTT@book> commit ; Commit complete. --//session 1: SCOTT@book> select * from t where id2=3 and sleep(12)=12; ID1 ID2 VC ---------- ---------- -------------------- 3 3 3x3x3x3x3x --//你可以发现输出是小写. --//Oracle将这种改动称为"RowCR Optimization"，Oracle简单的描述了什么是RowCR Optimization：A brief overview of this --//optimization is that we try to avoid rollbacks while constructing a CR block if the present block has no uncommitted --//changes.这里的avoid rollback，意味着在满足特定的条件时，Oracle就不做一致读了. --//这个提前条件就是索引要唯一. --//RowCR Optimization通过隐含参数"_row_cr"来控制. SYS@book> @ hide _row_cr NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD -------- ------------------------- -------------- ------------- ------------ ----- --------- _row_cr enable row cr for all sql TRUE TRUE TRUE FALSE IMMEDIATE SCOTT@book> alter system set "_row_cr"=false scope=memory; System altered. --//退出session 1再登录重复测试: --//sesion 1: SCOTT@book> select * from t where id1=1 and sleep(12)=12; --//session 2: SCOTT@book> update t set vc=upper(vc) where id1=1; 1 row updated. SCOTT@book> commit ; Commit complete. --//sesion 1: SCOTT@book> select * from t where id1=1 and sleep(12)=12; ID1 ID2 VC ---------- ---------- -------------------- 1 1 1x1x1x1x1x --//现在就输出小写了. 4.收尾: --//重启还原.