趣味题

〇〇 · 发表于 2010-6-13 07:34

iot很好,复合主键还是要回表

SQL> set autot off
SQL> drop table t purge;
表已删除。
已用时间: 00: 00: 00.06
SQL> create table t(x char(4),n number(5,0)PRIMARY KEY)organization index ;
表已创建。
已用时间: 00: 00: 00.03
SQL> insert into t select x,to_number(x,'fm0xxx')n from(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 );
已创建43680行。
已用时间: 00: 00: 00.65
SQL> create unique index idxtx on t(x);
索引已创建。
已用时间: 00: 00: 00.10
SQL> create unique index idxtn on t(n);
create unique index idxtn on t(n)
*
第 1 行出现错误:
ORA-01408: 此列列表已索引
已用时间: 00: 00: 00.01
SQL> create unique index idxtnx on t(n,x);
索引已创建。
已用时间: 00: 00: 00.10
SQL> drop index idxtx;
索引已删除。
已用时间: 00: 00: 00.02
SQL> drop index idxtnx purge;
drop index idxtnx purge
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束
已用时间: 00: 00: 00.00
SQL> drop index idxtnx;
索引已删除。
已用时间: 00: 00: 00.02
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('LT','T');
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.14
SQL> set autot on
SQL> show arraysize
arraysize 15
SQL> select a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
2 from t a,t b --,t c
3 where a.n<to_number('8000','fm0xxx')
4 and a.n<b.n
5 AND a.n+b.n <=to_number('fedc','fm0xxx')
6 and length(translate('0123456789abcdef','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$'))=4
7 and rownum<=10;
S
---------------
0c63+17fb=245e
0c93+17fb=248e
0ca3+17fb=249e
0d93+17fb=258e
0da3+17fb=259e
0d67+1834=259b
0d6a+1834=259e
0d6b+1834=259f
0d7b+1834=25af
0d96+1834=25ca
已选择10行。
已用时间: 00: 00: 14.86
执行计划
----------------------------------------------------------
Plan hash value: 3738967521
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 180 | 21 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 10 | 180 | 21 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| SYS_IOT_TOP_89130 | 19 | 171 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SYS_IOT_TOP_89130 | 1 | 9 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
4 - access("A"."N"<TO_NUMBER('8000','fm0xxx'))
filter("A"."N"<"B"."N" AND "A"."N"+"B"."N"<=TO_NUMBER('fedc','fm0xxx') AND
LENGTH(TRANSLATE('0123456789abcdef','$'||"A"."X"||"B"."X"||TO_CHAR("A"."N"+"B"."N",'
fm0xxx'),'$'))=4)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
91469 consistent gets
0 physical reads
0 redo size
755 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select /*use_merge(a b)*/a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
2 from t a,t b --,t c
3 where a.n<to_number('8000','fm0xxx')
4 and a.n<b.n
5 AND a.n+b.n <=to_number('fedc','fm0xxx')
6 and length(translate('0123456789abcdef','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$'))=4
7 and rownum<=10;
S
---------------
0c63+17fb=245e
0c93+17fb=248e
0ca3+17fb=249e
0d93+17fb=258e
0da3+17fb=259e
0d67+1834=259b
0d6a+1834=259e
0d6b+1834=259f
0d7b+1834=25af
0d96+1834=25ca
已选择10行。
已用时间: 00: 00: 14.77
执行计划
----------------------------------------------------------
Plan hash value: 3738967521
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 180 | 21 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 10 | 180 | 21 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| SYS_IOT_TOP_89130 | 19 | 171 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SYS_IOT_TOP_89130 | 1 | 9 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
4 - access("A"."N"<TO_NUMBER('8000','fm0xxx'))
filter("A"."N"<"B"."N" AND "A"."N"+"B"."N"<=TO_NUMBER('fedc','fm0xxx') AND
LENGTH(TRANSLATE('0123456789abcdef','$'||"A"."X"||"B"."X"||TO_CHAR("A"."N"+"B"."N",'
fm0xxx'),'$'))=4)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
91469 consistent gets
0 physical reads
0 redo size
755 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> set autot off
SQL> drop table t purge;
表已删除。
已用时间: 00: 00: 00.02
SQL> create table t(x char(4),n number(5,0));
表已创建。
已用时间: 00: 00: 00.01
SQL> insert into t select x,to_number(x,'fm0xxx')n from(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 );
已创建43680行。
已用时间: 00: 00: 00.56
SQL> create unique index idxtnx on t(n,x);
索引已创建。
已用时间: 00: 00: 00.08
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('LT','T');
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.17
SQL> set autot on
SQL> select a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
2 from t a,t b --,t c
3 where a.n<to_number('8000','fm0xxx')
4 and a.n<b.n
5 AND a.n+b.n <=to_number('fedc','fm0xxx')
6 and length(translate('0123456789abcdef','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$'))=4
7 and rownum<=10;
S
---------------
0234+5f78=61ac
0234+5f79=61ad
0234+5f89=61bd
0234+5f8a=61be
0234+5f97=61cb
0234+5f9a=61ce
0234+5fa7=61db
0234+5fa8=61dc
0234+5fb8=61ec
0234+5fb9=61ed
已选择10行。
已用时间: 00: 00: 37.28
执行计划
----------------------------------------------------------
Plan hash value: 2436370502
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 180 | 27 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 10 | 180 | 27 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDXTNX | 2105 | 18945 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T | 10 | 90 | 25 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - access("A"."N"<TO_NUMBER('8000','fm0xxx'))
4 - filter("A"."N"<"B"."N" AND "A"."N"+"B"."N"<=TO_NUMBER('fedc','fm0
xxx') AND LENGTH(TRANSLATE('0123456789abcdef','$'||"A"."X"||"B"."X"||TO_
CHAR("A"."N"+"B"."N",'fm0xxx'),'$'))=4)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
19443 consistent gets
0 physical reads
0 redo size
755 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select /*use_merge(a b)*/a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
2 from t a,t b --,t c
3 where a.n<to_number('8000','fm0xxx')
4 and a.n<b.n
5 AND a.n+b.n <=to_number('fedc','fm0xxx')
6 and length(translate('0123456789abcdef','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$'))=4
7 and rownum<=10;
S
---------------
0234+5f78=61ac
0234+5f79=61ad
0234+5f89=61bd
0234+5f8a=61be
0234+5f97=61cb
0234+5f9a=61ce
0234+5fa7=61db
0234+5fa8=61dc
0234+5fb8=61ec
0234+5fb9=61ed
已选择10行。
已用时间: 00: 00: 37.35
执行计划
----------------------------------------------------------
Plan hash value: 2436370502
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 180 | 27 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 10 | 180 | 27 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDXTNX | 2105 | 18945 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T | 10 | 90 | 25 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - access("A"."N"<TO_NUMBER('8000','fm0xxx'))
4 - filter("A"."N"<"B"."N" AND "A"."N"+"B"."N"<=TO_NUMBER('fedc','fm0
xxx') AND LENGTH(TRANSLATE('0123456789abcdef','$'||"A"."X"||"B"."X"||TO_
CHAR("A"."N"+"B"."N",'fm0xxx'),'$'))=4)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
19443 consistent gets
0 physical reads
0 redo size
755 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL>

复制代码

〇〇 · 发表于 2010-6-13 09:58

貌似iot对全表没有什么贡献

SQL> set autot on
SQL> select count(*) from(
2 select a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
3 from t a,t b --,t c
4 where a.n<to_number('8000','fm0xxx')
5 and a.n<b.n
6 AND a.n+b.n <=to_number('fedc','fm0xxx')
7 and length(translate('0123456789abcdef','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$'))=4
8 );
COUNT(*)
----------
3026800
已用时间: 00: 37: 54.65
执行计划
----------------------------------------------------------
Plan hash value: 1585527285
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 177 (84)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| 2 | MERGE JOIN | | 23849 | 419K| 177 (84)| 00:00:03 |
|* 3 | INDEX RANGE SCAN | SYS_IOT_TOP_89136 | 2184 | 19656 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | SORT JOIN | | 43680 | 383K| 29 (7)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| SYS_IOT_TOP_89136 | 43680 | 383K| 27 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."N"<TO_NUMBER('8000','fm0xxx'))
4 - filter("A"."N"+"B"."N"<=TO_NUMBER('fedc','fm0xxx') AND
LENGTH(TRANSLATE('0123456789abcdef','$'||"A"."X"||"B"."X"||TO_CHAR("A"."N"+"B"."N",'fm
0xxx'),'$'))=4)
5 - access("A"."N"<"B"."N")
filter("A"."N"<"B"."N")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
148 consistent gets
0 physical reads
0 redo size
525 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

复制代码

〇〇 · 发表于 2010-6-13 12:29

把问题规模变成只包含1-9abc的16进制数，时间变得有意义了

drop table t purge;
create table t(x char(4),n number(5,0)PRIMARY KEY)organization index ;
insert into t select x,to_number(x,'fm0xxx')n from(SELECT REPLACE(SYS_CONNECT_BY_PATH(rn,','),',') x
FROM (SELECT to_char(ROWNUM,'fmx') rn FROM DUAL CONNECT BY ROWNUM<=12)
WHERE LEVEL=4
CONNECT BY NOCYCLE rn<> PRIOR rn AND LEVEL<=4
);
--create unique index idxtx on t(x);
--create unique index idxtn on t(n);
--create unique index idxtnx on t(n,x);
exec DBMS_STATS.GATHER_TABLE_STATS('LT','T');
select count(*) from(
select a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
from t a,t b --,t c
where a.n<to_number('6000','fm0xxx')
and a.n<b.n
AND a.n+b.n <=to_number('cba9','fm0xxx')
and translate('123456789abc','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$')is null
);
--2 rownum
select a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
from t a,t b --,t c
where a.n<to_number('6000','fm0xxx')
and a.n<b.n
AND a.n+b.n <=to_number('cba9','fm0xxx')
and translate('123456789abc','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$')is null
and rownum<=10;
SQL> set autot on
SQL> select count(*) from(
2 select a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
3 from t a,t b --,t c
4 where a.n<to_number('6000','fm0xxx')
5 and a.n<b.n
6 AND a.n+b.n <=to_number('cba9','fm0xxx')
7 and translate('123456789abc','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$')is null
8 );
COUNT(*)
----------
3408
已用时间: 00: 01: 45.01
执行计划
----------------------------------------------------------
Plan hash value: 3235280849
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 23 (53)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| 2 | MERGE JOIN | | 8820 | 155K| 23 (53)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_IOT_TOP_89142 | 594 | 5346 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | SORT JOIN | | 11880 | 104K| 10 (10)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN| SYS_IOT_TOP_89142 | 11880 | 104K| 9 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."N"<TO_NUMBER('6000','fm0xxx'))
4 - filter("A"."N"+"B"."N"<=TO_NUMBER('cba9','fm0xxx') AND
TRANSLATE('123456789abc','$'||"A"."X"||"B"."X"||TO_CHAR("A"."N"+"B"."N",'fm0xxx'),'$')
IS NULL)
5 - access("A"."N"<"B"."N")
filter("A"."N"<"B"."N")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
524 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
2 from t a,t b --,t c
3 where a.n<to_number('6000','fm0xxx')
4 and a.n<b.n
5 AND a.n+b.n <=to_number('cba9','fm0xxx')
6 and translate('123456789abc','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$')is null
7 and rownum<=10;
S
---------------
1234+5897=6acb
1234+5978=6bac
1234+6a85=7cb9
1234+a568=b79c
1234+b675=c8a9
1234+b756=c98a
1235+8a46=9c7b
1235+9487=a6bc
1235+6a84=7cb9
1235+b674=c8a9
已选择10行。
已用时间: 00: 00: 00.10
执行计划
----------------------------------------------------------
Plan hash value: 1988574745
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 180 | 9 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 11 | 198 | 9 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_IOT_TOP_89142 | 587 | 5283 | 2 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| SYS_IOT_TOP_89142 | 11 | 99 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - access("A"."N"<TO_NUMBER('6000','fm0xxx'))
4 - filter("A"."N"<"B"."N" AND "A"."N"+"B"."N"<=TO_NUMBER('cba9','fm0xxx') AND
TRANSLATE('123456789abc','$'||"A"."X"||"B"."X"||TO_CHAR("A"."N"+"B"."N",'fm0xxx'),'$
') IS NULL)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
67 consistent gets
0 physical reads
0 redo size
755 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL>

复制代码

〇〇 · 发表于 2010-6-13 13:07

with 居然快一些。

SQL> with t as
2 (
3 select x,to_number(x,'fm0xxx')n from(SELECT REPLACE(SYS_CONNECT_BY_PATH(rn,','),',') x
4 FROM (SELECT to_char(ROWNUM,'fmx') rn FROM DUAL CONNECT BY ROWNUM<=12)
5 WHERE LEVEL=4
6 CONNECT BY NOCYCLE rn<> PRIOR rn AND LEVEL<=4
7 )
8 )
9 select count(*) from(
10 select a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
11 from t a,t b --,t c
12 where a.n<to_number('6000','fm0xxx')
13 and a.n<b.n
14 AND a.n+b.n <=to_number('cba9','fm0xxx')
15 and translate('123456789abc','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$')is null
16 );
COUNT(*)
----------
3408
已用时间: 00: 01: 39.47
执行计划
----------------------------------------------------------
Plan hash value: 2529199631
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4030 | 6 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | VIEW | | 1 | 2002 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | CONNECT BY WITHOUT FILTERING | | | | | |
| 6 | VIEW | | 1 | 3 | 2 (0)| 00:00:01 |
| 7 | COUNT | | | | | |
|* 8 | CONNECT BY WITHOUT FILTERING| | | | | |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 4030 | | |
| 11 | NESTED LOOPS | | 1 | 4030 | 4 (0)| 00:00:01 |
|* 12 | VIEW | | 1 | 2015 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6652_16B6486 | 1 | 2002 | 2 (0)| 00:00:01 |
|* 14 | VIEW | | 1 | 2015 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6652_16B6486 | 1 | 2002 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(LEVEL=4)
5 - filter("RN"<>PRIOR "RN" AND LEVEL<=4)
8 - filter(ROWNUM<=12)
12 - filter("A"."N"<TO_NUMBER('6000','fm0xxx'))
14 - filter("A"."N"<"B"."N" AND "A"."N"+"B"."N"<=TO_NUMBER('cba9','fm0xxx') AND
TRANSLATE('123456789abc','$'||"A"."X"||"B"."X"||TO_CHAR("A"."N"+"B"."N",'fm0xxx'),'$') IS NULL)
统计信息
----------------------------------------------------------
136 recursive calls
34 db block gets
133698 consistent gets
25 physical reads
1456 redo size
524 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

复制代码

〇〇 · 发表于 2010-6-13 15:40

把问题缩小以后,sql vs plsql
plsql完败

SQL> with t as
2 (
3 select x,to_number(x,'fm0xxx')n from(SELECT REPLACE(SYS_CONNECT_BY_PATH(rn,','),',') x
4 FROM (SELECT to_char(ROWNUM,'fmx') rn FROM DUAL CONNECT BY ROWNUM<=12)
5 WHERE LEVEL=4
6 CONNECT BY NOCYCLE rn<> PRIOR rn AND LEVEL<=4
7 )
8 )
9 select count(*) from(
10 select a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
11 from t a,t b --,t c
12 where a.n<to_number('cba9','fm0xxx')/2
13 and a.n<b.n
14 AND a.n+b.n <=to_number('cba9','fm0xxx')
15 and translate('123456789abc','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$')is null
16 );
COUNT(*)
----------
3408
已用时间: 00: 01: 41.53

复制代码

SQL> create or replace procedure pc3
2 as
3 time_before BINARY_INTEGER;
4 time_after BINARY_INTEGER;
5 TYPE t_num IS TABLE OF VARCHAR(5) INDEX BY PLS_INTEGER ;
6 x t_num;
7 a t_num;
8 b t_num;
9 c PLS_INTEGER;
10 c1 PLS_INTEGER;
11 r PLS_INTEGER;
12 l varchar(4);
13 begin
14 c:=0;
15 time_before := DBMS_UTILITY.GET_TIME;
16 for i in to_number('1234','fm0xxx')..to_number('cba9','fm0xxx') loop
17 l:=to_char(i,'fm0xxx');
18 if substr(l,1,1)<>substr(l,2,1)and
19 substr(l,2,1)<>substr(l,3,1)and
20 substr(l,3,1)<>substr(l,4,1)and
21 substr(l,1,1)<>substr(l,3,1)and
22 substr(l,2,1)<>substr(l,4,1)and
23 substr(l,1,1)<>substr(l,4,1)and
24 instr(l,0)=0 then
25 c:=c+1;
26 if i<to_number('cba9','fm0xxx')/2 then
27 a(c):=l;--to_char(i,'fm0xxx');
28 c1:=c;
29 end if;
30 b(c):=l;--to_char(i,'fm0xxx');
31 x(i):=c;
32 else
33 x(i):=c;
34 end if;
35 end loop;
36 r:=0;
37 dbms_output.put_line('c='||c||'c1='||c1||a(1)||b(1));
38 FOR I IN 1 .. c1 LOOP
39 FOR J IN x( to_number(a(i),'fm0xxx'))+1 .. x(to_number('cba9','fm0xxx')-to_number(a(i),'fm0xxx')) LOOP
40 if translate('123456789abc','$'||a(i)||b(j)||
41 to_char(to_number(a(i),'fm0xxx')+to_number(b(j),'fm0xxx'),'fm0xxx'),'$')is null then
42 --dbms_output.put_line(a(i)||b(j)||to_char(to_number(a(i),'fm0xxx')+to_number(b(j),'fm0xxx'),'fm0xxx'));
43 --NULL;
44 r:=r+1;
45 end if;
46 end loop;
47 end loop;
48
49 time_after := DBMS_UTILITY.GET_TIME;
50 DBMS_OUTPUT.PUT_LINE ('output:'||(time_after - time_before)||'rows:'||r);
51 end;
52 /
过程已创建。
已用时间: 00: 00: 00.04
SQL> exec pc3;
c=25701c1=1166812341234
output:137213rows:3408
PL/SQL 过程已成功完成。
已用时间: 00: 22: 52.07
SQL>

复制代码

dingjun123 · 发表于 2010-6-13 15:43

很深奥，赞！

〇〇 · 发表于 2010-6-13 16:27

深奥的是sql的内部算法，如何实现的,75#同样是2个全表扫描和nest loop,算法和我的plsql一样

plsql用整数代替varchar,省了2分钟

SQL> create or replace procedure pc4
2 as
3 time_before BINARY_INTEGER;
4 time_after BINARY_INTEGER;
5 TYPE t_num IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER ;
6 x t_num;
7 a t_num;
8 b t_num;
9 c PLS_INTEGER;
10 c1 PLS_INTEGER;
11 r PLS_INTEGER;
12 l varchar(4);
13 begin
14 c:=0;
15 time_before := DBMS_UTILITY.GET_TIME;
16 for i in to_number('1234','fm0xxx')..to_number('cba9','fm0xxx') loop
17 l:=to_char(i,'fm0xxx');
18 if substr(l,1,1)<>substr(l,2,1)and
19 substr(l,2,1)<>substr(l,3,1)and
20 substr(l,3,1)<>substr(l,4,1)and
21 substr(l,1,1)<>substr(l,3,1)and
22 substr(l,2,1)<>substr(l,4,1)and
23 substr(l,1,1)<>substr(l,4,1)and
24 instr(l,0)=0 then
25 c:=c+1;
26 if i<to_number('cba9','fm0xxx')/2 then
27 a(c):=i;
28 c1:=c;
29 end if;
30 b(c):=i;
31 x(i):=c;
32 else
33 x(i):=c;
34 end if;
35 end loop;
36 r:=0;
37 dbms_output.put_line('c='||c||'c1='||c1||a(1)||b(1));
38 FOR I IN 1 .. c1 LOOP
39 FOR J IN x(a(i))+1 .. x(to_number('cba9','fm0xxx')-a(i)) LOOP
40 if translate('123456789abc','$'||to_char(a(i),'fm0xxx')||to_char(b(j),'fm0xxx')||
41 to_char(a(i)+b(j),'fm0xxx'),'$')is null then
42 --dbms_output.put_line(to_char(a(i),'fm0xxx')||to_char(b(j),'fm0xxx')||to_char(a(i)+b(j),'fm0xxx'));
43 --NULL;
44 r:=r+1;
45 end if;
46 end loop;
47 --if r>=2 then
48 -- exit;
49 --end if;
50 end loop;
51
52 time_after := DBMS_UTILITY.GET_TIME;
53 DBMS_OUTPUT.PUT_LINE ('output:'||(time_after - time_before)||'rows:'||r);
54 end;
55 /
过程已创建。
已用时间: 00: 00: 00.06
SQL> exec pc4;
c=25701c1=1166846604660
output:126172rows:3408
PL/SQL 过程已成功完成。
已用时间: 00: 21: 01.65
SQL>

复制代码

〇〇 · 发表于 2010-6-13 16:34

不算count(*),求实际记录也是一样的时间

SQL> create table re as
2 with t as
3 (
4 select x,to_number(x,'fm0xxx')n from(SELECT REPLACE(SYS_CONNECT_BY_PATH(rn,','),',') x
5 FROM (SELECT to_char(ROWNUM,'fmx') rn FROM DUAL CONNECT BY ROWNUM<=12)
6 WHERE LEVEL=4
7 CONNECT BY NOCYCLE rn<> PRIOR rn AND LEVEL<=4
8 )
9 )
10 select cast(a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx') as varchar(14))s
11 from t a,t b --,t c
12 where a.n<to_number('cba9','fm0xxx')/2
13 and a.n<b.n
14 AND a.n+b.n <=to_number('cba9','fm0xxx')
15 and translate('123456789abc','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$')is null
16 ;
表已创建。
已用时间: 00: 01: 41.22

复制代码

〇〇 · 发表于 2010-6-13 19:39

自己写错了，导致没用的def也混进了穷举范围，本来b:11800 结果 25701，时间翻了6倍
现在和SQL持平

SQL> create or replace procedure pc5
2 as
3 time_before BINARY_INTEGER;
4 time_after BINARY_INTEGER;
5 TYPE t_num IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER ;
6 x t_num;
7 a t_num;
8 b t_num;
9 c PLS_INTEGER;
10 c1 PLS_INTEGER;
11 r PLS_INTEGER;
12 l varchar(4);
13 begin
14 c:=0;
15 time_before := DBMS_UTILITY.GET_TIME;
16 for i in to_number('1234','fm0xxx')..to_number('cba9','fm0xxx') loop
17 l:=to_char(i,'fm0xxx');
18 if substr(l,1,1)<>substr(l,2,1)and
19 substr(l,2,1)<>substr(l,3,1)and
20 substr(l,3,1)<>substr(l,4,1)and
21 substr(l,1,1)<>substr(l,3,1)and
22 substr(l,2,1)<>substr(l,4,1)and
23 substr(l,1,1)<>substr(l,4,1)and
24 instr(l,0)=0 and instr(l,'d')=0 and instr(l,'e')=0 and instr(l,'f')=0 then
25 c:=c+1;
26 if i<to_number('cba9','fm0xxx')/2 then
27 a(c):=i;
28 c1:=c;
29 end if;
30 b(c):=i;
31 x(i):=c;
32 else
33 x(i):=c;
34 end if;
35 end loop;
36 r:=0;
37 --dbms_output.put_line('c='||c||'c1='||c1||a(1)||b(1));
38 FOR I IN 1 .. c1 LOOP
39 FOR J IN x(a(i))+1 .. x(to_number('cba9','fm0xxx')-a(i)) LOOP
40 --if translate('123456789abc','$'||to_char(a(i),'fm0xxx')||to_char(b(j),'fm0xxx')||to_char(a(i)+b(j),'fm0xxx'),'$')is null then
41 --dbms_output.put_line(to_char(a(i),'fm0xxx')||to_char(b(j),'fm0xxx')||to_char(a(i)+b(j),'fm0xxx'));
42 --NULL;
43 l:=to_char(a(i)+b(j),'fm0xxx');
44 if substr(l,1,1)<>substr(l,2,1)and
45 substr(l,2,1)<>substr(l,3,1)and
46 substr(l,3,1)<>substr(l,4,1)and
47 substr(l,1,1)<>substr(l,3,1)and
48 substr(l,2,1)<>substr(l,4,1)and
49 substr(l,1,1)<>substr(l,4,1)and
50 instr(l,0)=0 and instr(l,'d')=0 and instr(l,'e')=0 and instr(l,'f')=0
51 and length(translate(to_char(a(i),'fm0xxx'),'$'||l,'$'))=4
52 and length(translate(to_char(b(j),'fm0xxx'),'$'||l,'$'))=4
53 and length(translate(to_char(a(i),'fm0xxx'),'$'||to_char(b(j),'fm0xxx'),'$'))=4 then
54 r:=r+1;
55 --dbms_output.put_line(r||':'||to_char(a(i),'fm0xxx')||to_char(b(j),'fm0xxx')||to_char(a(i)+b(j),'fm0xxx'));
56 end if;
57 end loop;
58 --if r>=1000 then
59 -- exit;
60 --end if;
61 end loop;
62
63 time_after := DBMS_UTILITY.GET_TIME;
64 DBMS_OUTPUT.PUT_LINE ('output:'||(time_after - time_before)||'rows:'||r);
65 end;
66 /
过程已创建。
已用时间: 00: 00: 00.05
SQL> exec pc5;
output:10228rows:3408
PL/SQL 过程已成功完成。
已用时间: 00: 01: 42.27
SQL>

复制代码

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

怎么才能利用加数交换个十百位减少穷举次数呢

[转载] 趣味题

浏览过的版块