彩神大发快三_神彩大发快三官方

[20180607]函数与标量子查询8.txt

时间:2020-01-11 09:02:35 出处:彩神大发快三_神彩大发快三官方

--//输出太长,一共2640个值,略,你这俩 结果而是在1-3152之间,总出 hash冲突的值.

   3 - filter("ID2"<=3152)

   4 - filter(("ID2"="ID2" AND "R"-"RP">=2 AND "ID2"<=3152))

35 rows selected.

select  rowid,t.*,(select sleep(id2) from dual) s from t where id2<=3152

and id2 not in

(

SELECT /*+ NL_AJ */ id2

  FROM (  SELECT b / 2 id2, a r, LAG (a) OVER (ORDER BY b) rp

            FROM t1

        ORDER BY b/2)

WHERE r - rp >= 2 and id2<=3152

);

--//昨天我测试11.2.0.4 for linux下,哈希表不止25十个 Buckets.

--//今天测试看看10g下到底有多少个Buckets.不可能 我感觉10g不可能 哈希表的buckets不可能 不大.

SCOTT@book> create table t1 ( b number ,a number);

Table created.

--//注b在前,表示查询记录数量,a表示执行fast dual次数,也而是递归次数.

--//修改b4.txt ,改写成inert插入表t1.执行如下:

:%s/^/insert into t1 values(/g

:%s/$/);/g

1.环境:

SYS@test> @ &r/ver1

prompt

prompt argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive

prompt

通俗来将,当使用标量子查询的刚刚,ORACLE会将子查询结果缓指在哈希表中, 不可能 后续的记录总出 同样的值,优化器通过缓指在哈希

表中的值,判断重复值不必重复调用函数,直接使用上次计算结果即可。从而减少调用函数次数,从而达到优化性能的效果。另外在

ORACLE 10和11中, 哈希表只所含了25十个 Buckets,也而是说它能存储25十个 不同值,不可能 超过你这俩 范围,就会总出 散列冲突,你这俩 总出

散列冲突的值就会重复调用函数,即便这么,依然能达到大幅改善性能的效果。

--//dpc脚本如下:

set verify off

select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2'));

--//删除冲突的记录看看.

delete from t where id2 in (SELECT id2

  FROM (  SELECT b / 2 id2, a r, LAG (a) OVER (ORDER BY b) rp

            FROM t1

        ORDER BY b/2)

WHERE r - rp >= 2 and id2<=3152 );

commit;

--//多执行多少,除理其它递归影响.

spool cz.txt

@ ay.txt

spool off

SELECT id2, r, rp

  FROM (  SELECT b / 2 id2, a r, LAG (a) OVER (ORDER BY b) rp

            FROM t1

        ORDER BY b/2)

WHERE r - rp >= 2 and id2<=3152 ;

   1 - SEL$2        / DUAL@SEL$2

   2 - SEL$8771BF6C

   3 - SEL$8771BF6C / T@SEL$1

   4 - SEL$4        / from$_subquery$_003@SEL$3

   5 - SEL$4

   7 - SEL$4        / T1@SEL$4

--//而是不可能

select rowid,t.*,(select sleep(id2) from dual) s from t where (id2<=3152 and id2<>1693) or id2=:x;

--//:x 取舍 3153-60 0 任何另另十个 ,fast dual 的starts还会 514,也而是指在冲突.很多人可不可不可不可否 自行验证.

SCOTT@test> @ &r/dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  9q5bnk36nnq68, child number 0

-------------------------------------

select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=3152

Predicate Information (identified by operation id):

---------------------------------------------------

CREATE OR REPLACE FUNCTION sleep1 (seconds IN NUMBER)

RETURN NUMBER

is

d_date date;

BEGIN

  select sysdate into d_date from dual;

  sys.dbms_lock.sleep(seconds/10);

  RETURN seconds;

END;

/

CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)

RETURN NUMBER

is

d_date date;

BEGIN

  select sysdate into d_date from dual;

--//sys.dbms_lock.sleep(0.01);

  RETURN seconds;

END;

/

--//3152值还会进入backupset,上端的数字带入还会 总出 hash 冲突的情况.

--//前面看http://www.cnblogs.com/kerrycode/p/909960 7.html链接,上端提到:

[20160 60 7]函数与标量子查询8.txt

--//建立脚本ay.txt:

alter session set statistics_level=all;

set feed on

variable x number;

exec :x := 1;

select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=:x;

@ http://192.168.60 .40/sqllaji//dpc '' ''

...

exec :x := 60 0;

select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=:x;

@ http://192.168.60 .40/sqllaji//dpc '' ''

create table t as select rownum id1,mod(rownum-1,60 0)+1 id2 from dual connect by level<=60 00;

--//ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;

--//注意插入数据的顺序,我刚刚的插入有1点大什么的问题,原因分析id2显示不按照1-60 0,1-60 0显示(执行select * from t).

--//原因分析测试总出 很多奇怪情况.

2.测试:

SELECT    'exec :x := '

           || LEVEL

           || ';'

           || CHR (10)

           || 'select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=:x;'

           || CHR (10)

           || '@ &r/dpc '''' '''''

      FROM DUAL

CONNECT BY LEVEL <= 60 0;

--//取出数字

$ egrep 'FAST DUAL|rows selected' cz.txt | sed '/^29 rows selected./d' > c1.txt

$ grep  "rows selected." c1.txt  | cut -f1 -d' '> c2.txt

$ grep "FAST DUAL"  c1.txt | cut -f5 -d"|" > c3.txt

$ paste c2.txt c3.txt -d"," > c4.txt

select max(id2) from (

SELECT id2, r, rp

  FROM (  SELECT b / 2 id2, a r, LAG (a) OVER (ORDER BY b) rp

            FROM t1

        ORDER BY b/2)

WHERE r - rp = 1 order by id2);

  MAX(ID2)

----------

      3152

--//正好512,说明10.2.0.4,哈希表只所含了51另另十个 Buckets,也而是说它能存储51另另十个 不同值,

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

Plan hash value: 1032660 217

grant execute on sys.dbms_lock to scott;

select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=3152;

SCOTT@test> @ &r/dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  7r8dyxjmdwucp, child number 0

-------------------------------------

select  rowid,t.*,(select sleep(id2) from dual) s from t where id2<=3152 and id2 not in ( SELECT /*+ NL_AJ */ id2   FROM (  SELECT b / 2

id2, a r, LAG (a) OVER (ORDER BY b) rp             FROM t1         ORDER BY b/2)  WHERE r - rp >= 2 and id2<=3152 )

Plan hash value: 4160 365942

---------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation             | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

---------------------------------------------------------------------------------------------------------------------------------------------------

|   1 |  FAST DUAL            |      |    512 |      1 |       |     2   (0)| 00:00:01 |    512 |00:00:00.01 |       0 |       |       |          |

|   2 |  NESTED LOOPS ANTI    |      |      1 |   660 4 |   283K| 46284  (32)| 00:09:16 |   1024 |00:00:07.65 |      40 |       |       |          |

|*  3 |   TABLE ACCESS FULL   | T    |      1 |   660 6 | 44142 |     6   (0)| 00:00:01 |   660 4 |00:00:00.01 |      24 |       |       |          |

|*  4 |   VIEW                |      |   660 4 |      1 |    39 |     7  (29)| 00:00:01 |   5260 |00:00:07.65 |      16 |       |       |          |

|   5 |    SORT ORDER BY      |      |   660 4 |   60 0 | 260 00 |     7  (29)| 00:00:01 |   4096K|00:00:06.14 |      16 |   160 K|   160 K|  142K (0)|

|   6 |     WINDOW SORT       |      |   1024 |   60 0 | 260 00 |     7  (29)| 00:00:01 |   4096K|00:00:04.10 |      16 |   196K|   196K|  174K (0)|

|   7 |      TABLE ACCESS FULL| T1   |      1 |   60 0 | 260 00 |     5   (0)| 00:00:01 |   60 0 |00:00:00.01 |      16 |       |       |          |

---------------------------------------------------------------------------------------------------------------------------------------------------

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- ----------------------------------------------------------------

x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

--------------------------------------------------------------------------------------------------------------------

| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------------------

|   1 |  FAST DUAL        |      |    512 |      1 |       |     2   (0)| 00:00:01 |    512 |00:00:00.01 |       0 |

|*  2 |  TABLE ACCESS FULL| T    |      1 |   660 6 | 44142 |     6   (0)| 00:00:01 |   1024 |00:00:00.01 |      24 |

--------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$2 / DUAL@SEL$2

   2 - SEL$1 / T@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("ID2"<=3152)

24 rows selected.

热门

热门标签