趣味题

〇〇 · 发表于 2010-6-11 07:44

SQL> create table t AS (SELECT REPLACE(SYS_CONNECT_BY_PATH(rn,','),',') x
  2             FROM (SELECT to_char(ROWNUM-1,'fmx') rn FROM DUAL CONNECT BY ROWNUM<=16)
  3             WHERE LEVEL=4
  4             CONNECT BY NOCYCLE rn<> PRIOR rn AND LEVEL<=4
  5          );

表已创建。

已用时间:  00: 00: 00.57
SQL> select count(*)from t;

  COUNT(*)
----------
   43680

已用时间:  00: 00: 00.01
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('LT','T');

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.12
SQL> explain plan for select count(*) from(
  2  select a.x||'+'||b.x||'+'||c.x||'='||to_char(to_number(a.x,'fmxxxx')+to_number(b.x,'fmxxxx')+to_number(c.x,'fmxxxx'),'fmxxxx')s
  3  from t a,t b,t c
  4  where to_number(a.x,'fmxxxx')<to_number('8000','fmxxxx')
  5       and to_number(a.x,'fmxxxx')<to_number(b.x,'fmxxxx')
  6       and to_number(b.x,'fmxxxx')<to_number(c.x,'fmxxxx')
  7       AND to_number(a.x,'fmxxxx')+to_number(b.x,'fmxxxx')+to_number(c.x,'fmxxxx') <=to_number('fedc','fmxxxx')
  8       and translate('0123456789abcdef','$'||a.x||b.x||to_char(to_number(a.x,'fmxxxx')+to_number(b.x,'fmxxxx')+to_number(c.x,'fmxx
xx'),'fmxxxx'),'$') is null
  9       );

已解释。

已用时间:  00: 00: 00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4206735519

-------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    1 | 15 | 31960 (100)| 00:06:24 |
| 1 |  SORT AGGREGATE       |    |    1 | 15 |          |       |
| 2 | MERGE JOIN          |    | 26M| 372M| 31960 (100)| 00:06:24 |
| 3 | SORT JOIN          |    |  4769K| 45M| 63  (31)| 00:00:01 |
| 4 |    MERGE JOIN       |    |  4769K| 45M| 63  (31)| 00:00:01 |
| 5 |    SORT JOIN       |    | 43680 | 213K| 24 (9)| 00:00:01 |
| 6 |    TABLE ACCESS FULL| T | 43680 | 213K| 22 (0)| 00:00:01 |
|*  7 |    SORT JOIN       |    |  2184 | 10920 | 24 (9)| 00:00:01 |
|*  8 |    TABLE ACCESS FULL| T |  2184 | 10920 | 23 (5)| 00:00:01 |
|*  9 | FILTER             |    |    |    |          |       |
|* 10 |    SORT JOIN       |    | 43680 | 213K| 24 (9)| 00:00:01 |
|  11 |    TABLE ACCESS FULL | T | 43680 | 213K| 22 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access(TO_NUMBER("A"."X",'fmxxxx')<TO_NUMBER("B"."X",'fmxxxx'))
   filter(TO_NUMBER("A"."X",'fmxxxx')<TO_NUMBER("B"."X",'fmxxxx'))
8 - filter(TO_NUMBER("A"."X",'fmxxxx')<TO_NUMBER('8000','fmxxxx'))
9 - filter(TO_NUMBER("A"."X",'fmxxxx')+TO_NUMBER("B"."X",'fmxxxx')+TO
            _NUMBER("C"."X",'fmxxxx')<=TO_NUMBER('fedc','fmxxxx') AND
            TRANSLATE('0123456789abcdef','$'||"A"."X"||"B"."X"||TO_CHAR(TO_NUMBER("A
            "."X",'fmxxxx')+TO_NUMBER("B"."X",'fmxxxx')+TO_NUMBER("C"."X",'fmxxxx'),
            'fmxxxx'),'$') IS NULL)
  10 - access(TO_NUMBER("B"."X",'fmxxxx')<TO_NUMBER("C"."X",'fmxxxx'))
   filter(TO_NUMBER("B"."X",'fmxxxx')<TO_NUMBER("C"."X",'fmxxxx'))

已选择32行。

已用时间:  00: 00: 00.04

〇〇 · 发表于 2010-6-11 08:48

为什么connect_by_path算出来的t记录要多3000多？

SQL> set timi on
SQL> set lines 132 pages 50000
SQL> with t as(select x from (select to_char(level-1,'fmxxxx') x from dual connect by level<=65536)
  2  where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1) and  substr(x,1,1)<>substr(x,4,1)
  3  and substr(x,2,1)<>substr(x,3,1) and substr(x,2,1)<>substr(x,4,1) and substr(x,3,1)<>substr(x,4,1)
  4  )
  5  select count(*) from t;

  COUNT(*)
----------
   40950

已用时间:  00: 00: 00.21
SQL> with t as(select x from (select to_char(level-1,'fmxxxx') x from dual connect by level<=65536)
  2  where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1) and  substr(x,1,1)<>substr(x,4,1)
  3  and substr(x,2,1)<>substr(x,3,1) and substr(x,2,1)<>substr(x,4,1) and substr(x,3,1)<>substr(x,4,1)
  4  )
  5  select sum(length(x))/4 from t;

SUM(LENGTH(X))/4
----------------
         40950

已用时间:  00: 00: 00.21

---

SQL> create table t AS (SELECT REPLACE(SYS_CONNECT_BY_PATH(rn,','),',') x
  2             FROM (SELECT to_char(ROWNUM-1,'fmx') rn FROM DUAL CONNECT BY ROWNUM<=16)
  3             WHERE LEVEL=4
  4             CONNECT BY NOCYCLE rn<> PRIOR rn AND LEVEL<=4
  5          );

表已创建。

已用时间:  00: 00: 00.57
SQL> select count(*)from t;

  COUNT(*)
----------
   43680

是自己写错了,速度还是不用connect_by_path快

SQL> with t as(select x from (select to_char(level-1,'fm0xxx') x from dual connect by level<=65536)
  2  where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1) and  substr(x,1,1)<>substr(x,4,1)
  3  and substr(x,2,1)<>substr(x,3,1) and substr(x,2,1)<>substr(x,4,1) and substr(x,3,1)<>substr(x,4,1)
  4  )
  5  select count(*) from t;

  COUNT(*)
----------
   43680

已用时间:  00: 00: 00.21
SQL> with t AS (SELECT REPLACE(SYS_CONNECT_BY_PATH(rn,','),',') x
  2    FROM (SELECT to_char(ROWNUM-1,'fmx') rn FROM DUAL CONNECT BY ROWNUM<=16)
  3 WHERE LEVEL=4
  4 CONNECT BY NOCYCLE rn<> PRIOR rn AND LEVEL<=4
  5  )
  6  select count(*) from t;

  COUNT(*)
----------
   43680

已用时间:  00: 00: 00.42
SQL>
SQL> /

  COUNT(*)
----------
   43680

已用时间:  00: 00: 00.40
SQL>

[ 本帖最后由〇〇于 2010-6-11 08:54 编辑 ]

〇〇 · 发表于 2010-6-11 10:01

修改后问题规模太大了
改成2个16进制数相加等于第3个也出不来
SQL> select count(*) from(
  2  select a.x||'+'||b.x||'='||to_char(to_number(a.x,'fm0xxx')+to_number(b.x,'fm0xxx'),'fm0xxx')s
  3  from t a,t b --,t c
  4  where to_number(a.x,'fm0xxx')<to_number('8000','fm0xxx')
  5       and to_number(a.x,'fm0xxx')<to_number(b.x,'fm0xxx')
  6       AND to_number(a.x,'fm0xxx')+to_number(b.x,'fm0xxx') <=to_number('fedc','fm0xxx')
  7       and translate('0123456789abcdef','$'||a.x||b.x||to_char(to_number(a.x,'fm0xxx')+to_number(b.x,'fm0xxx')),'$') is null
  8       );
^C

dingjun123 · 发表于 2010-6-11 10:19

鼓励研究和分享精神，希望大家多研究多总结，这样才能相互提高！

〇〇 · 发表于 2010-6-11 11:04

对not exists来说a+b-c=0的写法影响不大

SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122)) where substr(x,1,1)<>substr(x,2,1) and
  2  substr(x,1,1)<>substr(x,3,1)
  3  and  substr(x,2,1)<>substr(x,3,1) and instr(x,0)=0),
  4  a as (select rownum rn,x from t where x<494)
  5  select count(*) from (
  6  select a.x||'+'||b.x||'-'||c.x||'==0' from a a,t b,t c
  7  where a.x+b.x=c.x and a.x<494 and a.x<b.x --and b.x<c.x
  8  and not exists(select 1 from (select rownum l from dual connect by level<=9)where instr(a.x||b.x||c.x ,l,1,2)>0))
  9  ;

  COUNT(*)
----------
   168

已用时间:  00: 00: 07.00
SQL>
SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122)) where substr(x,1,1)<>substr(x,2,1) and
  2  substr(x,1,1)<>substr(x,3,1)
  3  and  substr(x,2,1)<>substr(x,3,1) and instr(x,0)=0),
  4  a as (select rownum rn,x from t where x<494)
  5  select count(*) from (
  6  select a.x||'+'||b.x||'-'||c.x||'==0' from a a,t b,t c
  7  where a.x+b.x=c.x and a.x<494 and a.x<b.x and b.x<c.x
  8  and not exists(select 1 from (select rownum l from dual connect by level<=9)where instr(a.x||b.x||c.x ,l,1,2)>0))
  9  ;

  COUNT(*)
----------
   168

已用时间:  00: 00: 07.03
SQL>
SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122)) where substr(x,1,1)<>substr(x,2,1) and
  2  substr(x,1,1)<>substr(x,3,1)
  3  and  substr(x,2,1)<>substr(x,3,1) and instr(x,0)=0),
  4  a as (select rownum rn,x from t where x<494)
  5  select count(*) from (
  6  select a.x||'+'||b.x||'-'||c.x||'==0' from a a,t b,t c
  7  where a.x+b.x-c.x=0 and a.x<494 and a.x<b.x and b.x<c.x
  8  and not exists(select 1 from (select rownum l from dual connect by level<=9)where instr(a.x||b.x||c.x ,l,1,2)>0))
  9  ;

  COUNT(*)
----------
   168

已用时间:  00: 00: 07.89

regonly1 · 发表于 2010-6-11 11:52

老O还在研究啊，学习！

〇〇 · 发表于 2010-6-11 18:52

SQL> create index txidx on t(x);

索引已创建。

已用时间:  00: 00: 00.09
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('LT','T');

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.25
SQL> explain plan for select count(*) from(
  2  select a.x||'+'||b.x||'='||to_char(to_number(a.x,'fmxxxx')+to_number(b.x,'fmxxxx'),'fmxxxx')s
  3  from t a,t b --,t c
  4  where to_number(a.x,'fmxxxx')<to_number('8000','fmxxxx')
  5       and to_number(a.x,'fmxxxx')<to_number(b.x,'fmxxxx')
  6       AND to_number(a.x,'fmxxxx')+to_number(b.x,'fmxxxx') <=to_number('fedc','fmxxxx')
  7       and translate('0123456789abcdef','$'||a.x||b.x||to_char(to_number(a.x,'fmxxxx')+to_number(b.x,'fmxxxx')),'$') is null
  8       );

已解释。

已用时间:  00: 00: 00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4282957982

------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    1 | 10 | 63  (31)| 00:00:01 |
| 1 |  SORT AGGREGATE    |    |    1 | 10 |          |       |
| 2 | MERGE JOIN       |    | 11925 | 116K| 63  (31)| 00:00:01 |
| 3 | SORT JOIN       |    |  2184 | 10920 | 24 (9)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL | T |  2184 | 10920 | 23 (5)| 00:00:01 |
|*  5 | FILTER          |    |    |    |          |       |
|*  6 |    SORT JOIN       |    | 43680 | 213K| 24 (9)| 00:00:01 |
| 7 |    TABLE ACCESS FULL| T | 43680 | 213K| 22 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter(TO_NUMBER("A"."X",'fmxxxx')<TO_NUMBER('8000','fmxxxx'))
5 - filter(TO_NUMBER("A"."X",'fmxxxx')+TO_NUMBER("B"."X",'fmxxxx')<=T
            O_NUMBER('fedc','fmxxxx') AND TRANSLATE('0123456789abcdef','$'||"A"."X"|
            |"B"."X"||TO_CHAR(TO_NUMBER("A"."X",'fmxxxx')+TO_NUMBER("B"."X",'fmxxxx'
            )),'$') IS NULL)
6 - access(TO_NUMBER("A"."X",'fmxxxx')<TO_NUMBER("B"."X",'fmxxxx'))
   filter(TO_NUMBER("A"."X",'fmxxxx')<TO_NUMBER("B"."X",'fmxxxx'))

已选择25行。

已用时间:  00: 00: 00.03
SQL> select count(*) from(
  2  select a.x||'+'||b.x||'='||to_char(to_number(a.x,'fmxxxx')+to_number(b.x,'fmxxxx'),'fmxxxx')s
  3  from t a,t b --,t c
  4  where to_number(a.x,'fmxxxx')<to_number('8000','fmxxxx')
  5       and to_number(a.x,'fmxxxx')<to_number(b.x,'fmxxxx')
  6       AND to_number(a.x,'fmxxxx')+to_number(b.x,'fmxxxx') <=to_number('fedc','fmxxxx')
  7       and translate('0123456789abcdef','$'||a.x||b.x||to_char(to_number(a.x,'fmxxxx')+to_number(b.x,'fmxxxx')),'$') is null
  8       );

  COUNT(*)
----------
      0

已用时间:  00: 43: 21.15
SQL>

newkid · 发表于 2010-6-11 23:44

你建的临时表T为什么用LEVEL=4的？不是应该有五位吗？
你还必须把TO_NUMBER另外建一个列，后面省很多计算。
我的WITH写法跑了一个多小时出不来，被我停掉了。

szusunny · 发表于 2010-6-11 23:46

无解的原因是条件translate('0123456789abcdef','$'||a.x||b.x||to_char(to_number(a.x,'fmxxxx')+to_number(b.x,'fmxxxx')),'$') is null 的问题.

OO的研究精神真令人敬佩! :-) 加油!!

〇〇 · 发表于 2010-6-12 07:49

select count(*) from(
select a.x||'+'||b.x||'='||to_char(to_number(a.x,'fm0xxx')+to_number(b.x,'fm0xxx'),'fm0xxx')s
from t a,t b --,t c
where to_number(a.x,'fm0xxx')<to_number('8000','fm0xxx')
   and to_number(a.x,'fm0xxx')<to_number(b.x,'fm0xxx')
   AND to_number(a.x,'fm0xxx')+to_number(b.x,'fm0xxx') <=to_number('fedc','fm0xxx')
   and length(translate('0123456789abcdef','$'||a.x||b.x||to_char(to_number(a.x,'fm0xxx')+to_number(b.x,'fm0xxx')),'$'))=4
   );

[转载] 趣味题

拆成2步

建立索引，执行计划不变，终于运行出来了，居然无解