Firebird 2.1发行注记

〇〇

插入性能还不错，和postgre差不多

SQL> select count(*) from t;

       COUNT
============
           0

Current memory = 10787912
Delta memory = -226896
Max memory = 12995928
Elapsed time= 0.00 sec
Buffers = 2048
Reads = 0
Writes 0
Fetches = 4
SQL> insert into dual values('aaa');
Current memory = 10785944
Delta memory = -1968
Max memory = 12995928
Elapsed time= 0.00 sec
Buffers = 2048
Reads = 0
Writes 0
Fetches = 19
SQL> insert into t
CON> WITH t AS (
CON>   SELECT 1 L from dual
CON>   UNION ALL
CON>   SELECT 2 L from dual
CON>   UNION ALL
CON>   SELECT 3 L from dual
CON>   UNION ALL
CON>   SELECT 4 L from dual
CON>   UNION ALL
CON>   SELECT 5 L from dual
CON>   UNION ALL
CON>   SELECT 6 L from dual
CON>   UNION ALL
CON>   SELECT 7 L from dual
CON>   UNION ALL
CON>   SELECT 8 L from dual
CON>   UNION ALL
CON>   SELECT 9 L from dual
CON>   UNION ALL
CON>   SELECT 10 L from dual
CON>   )
CON>   ,t2 as(
CON>   select t.l*9+t1.l L
CON>   from t,t t1
CON>   )
CON>   ,t4 as(
CON>   select t.l*99+t1.l L
CON>   from t2 t,t2 t1
CON>   )
CON>   ,t6 as(
CON>   SELECT t.l*9999+t1.l L
CON>     FROM t2 t,t4 t1
CON>   )
CON> select mod(L,4)c1,mod(L,5)c2,mod(L,67)c3,
CON> mod(case when mod(L,13)>0 then L end ,113)c4, L v from t6;
Current memory = 11212624
Delta memory = 426680
Max memory = 12995928
Elapsed time= 16.83 sec
Buffers = 2048
Reads = 0
Writes 13646
Fetches = 8680889
SQL> select count(*) from t;

       COUNT
============
     1000000

Current memory = 10979416
Delta memory = -233208
Max memory = 12995928
Elapsed time= 1.26 sec
Buffers = 2048
Reads = 15660
Writes 2039
Fetches = 2031299
SQL>

〇〇

这个查询比oracle还快
SQL> select count(distinct c1),count(distinct c2),count(distinct c3),count(distinct c4) from t;

       COUNT        COUNT        COUNT        COUNT
============ ============ ============ ============
           4            5           67          113

Current memory = 10985752
Delta memory = 6336
Max memory = 19513496
Elapsed time= 1.97 sec
Buffers = 2048
Reads = 15650
Writes 0
Fetches = 2031282

〇〇

这个查询也很快
SQL> create table tcube(c1 decimal(2,0),c2 decimal(2,0),c3 decimal(3,0),c4 decimal(3,0),sv decimal(20,0));
Statement failed, SQLSTATE = HY104
Dynamic SQL Error
-SQL error code = -842
-Precision must be from 1 to 18
SQL> create table tcube(c1 decimal(2,0),c2 decimal(2,0),c3 decimal(3,0),c4 decimal(3,0),sv decimal(18,0));
Current memory = 10978032
Delta memory = -7720
Max memory = 19513496
Elapsed time= 0.02 sec
Buffers = 2048
Reads = 53
Writes 26
Fetches = 552
SQL> insert into tcube select c1,c2,c3,c4,sum(v)as sv from t group by c1,c2,c3,c4;
Current memory = 10995392
Delta memory = 16288
Max memory = 75050136
Elapsed time= 10.94 sec
Buffers = 2048
Reads = 15656
Writes 411
Fetches = 2508802

ucgary

你study的东西还真多.

〇〇

不用insert,select也不行
SQL> select count(*),max(v) from
CON> (
CON> WITH RECURSIVE t2(lvl) AS (
CON>   SELECT 1 from t1 --- 先构造第一个
CON>   UNION ALL
CON>   SELECT t2.lvl+1
CON>     FROM t2      ---- 没有任何连接，因为不需要
CON>    WHERE t2.lvl<1E6
CON>   )
CON> select mod(lvl,4)c1,mod(lvl,5)c2,mod(lvl,67)c3,
CON> mod(case when mod(lvl,13)>0 then lvl end ,113)c4, lvl v from t2
CON> );

       COUNT          MAX
============ ============
Statement failed, SQLSTATE = 54001
Too many concurrent executions of the same request
SQL>
SQL> insert into t(c1,c2,c3,c4,v)
CON> select count(*),max(v) from
CON> (
CON> WITH RECURSIVE t2(lvl) AS (
CON>   SELECT 1 from t1 --- 先构造第一个
CON>   UNION ALL
CON>   SELECT t2.lvl+1
CON>     FROM t2      ---- 没有任何连接，因为不需要
CON>    WHERE t2.lvl<1E2
CON>   )
CON> select mod(lvl,4)c1,mod(lvl,5)c2,mod(lvl,67)c3,
CON> mod(case when mod(lvl,13)>0 then lvl end ,113)c4, lvl v from t2
CON> );
Statement failed, SQLSTATE = 21S01
Dynamic SQL Error
-SQL error code = -804
-Count of read-write columns does not equal count of values

ocpdba591

介个版块人好少啊，顶下OO

killkill_shadow

哈哈，貌似不错，Firebird 这个名字让我想起了大学的BBS系统（telnet那种）

〇〇

居然还支持GB18030

ICU character sets
------------------

All non-wide and ascii-based character sets present in ICU can be used by Firebird.
But for small distribution kit, we customize ICU to include only essentials character sets or the ones we had feature request.
If the character set you need is not included in it, you can replace ICU libraries by a complete one found in our site our
installed in your OS.

To use, you first need to register it in intl/fbintl.conf as follow:
        <charset NAME>
                intl_module        fbintl
                collation        NAME [REAL-NAME]
        </charset>

And register it in the databases using sp_register_character_set (found in misc/intl.sql).
You need to know how much bytes a single character can occupy in the encoding.

Example:
        <charset GB>
                intl_module        fbintl
                collation        GB GB18030
        </charset>

        execute procedure sp_register_character_set ('GB', 4);

〇〇

示例都在这里。。。
D:\FB\doc>dir sql.extensions
驱动器 D 中的卷没有标签。
卷的序列号是 353F-71F9

D:\FB\doc\sql.extensions 的目录

2009-09-08  10:57    <DIR>          .
2009-09-08  10:57    <DIR>          ..
2003-07-29  02:55             4,144 README.aggregate_tracking.txt
2007-11-30  15:49            13,359 README.builtin_functions.txt
2005-02-02  01:55             1,837 README.case.txt
2005-02-02  01:55             1,107 README.coalesce.txt
2009-04-03  14:55             5,041 README.common_table_expressions.txt
2007-12-04  10:29             3,513 README.context_variables.txt
2007-03-22  15:07             4,667 README.context_variables2.txt
2005-10-21  11:49               842 README.current_time.txt
2005-01-22  22:26             2,403 README.cursors.txt
2003-07-15  12:32               925 README.data_types.txt
2007-04-09  16:39             1,634 README.data_type_results_of_aggregations.txt
2007-12-04  10:23             1,915 README.db_triggers.txt
2005-12-01  09:12             9,307 README.ddl.txt
2004-01-16  11:32             2,055 README.default_parameters.txt
2005-02-02  01:55             2,310 README.derived_tables.txt
2005-01-22  23:03               840 README.distinct.txt
2007-03-15  11:14               871 README.domains_psql.txt
2007-12-04  10:29             1,523 README.exception_handling.txt
2004-01-16  11:32             1,527 README.execute_block.txt
2006-07-31  09:42             4,487 README.execute_statement.txt
2003-07-31  13:54             6,173 README.explicit_locks.txt
2009-04-03  14:55             1,369 README.expression_indices.txt
2009-04-03  14:55             3,328 README.global_temporary_tables.txt
2005-01-22  23:13               561 README.iif.txt
2006-02-09  09:48             1,816 README.isc_info_xxx.txt
2007-05-03  03:32             1,298 README.joins.txt
2009-01-29  13:53             2,707 README.keywords.txt
2005-01-06  20:32             1,787 README.leave_labels.txt
2005-05-28  00:17               502 README.length.txt
2007-05-09  17:23             1,102 README.list.txt
2006-12-12  00:55             1,457 README.merge.txt
2005-02-02  01:55               638 README.nullif.txt
2005-01-22  23:17                24 README.null_value.txt
2007-07-03  14:10             1,772 README.order_by_expressions_nulls.txt
2005-01-25  08:24             3,639 README.plan.txt
2005-01-28  08:52             2,088 README.PSQL_stack_trace.txt
2007-04-24  17:21             2,391 README.returning.txt
2005-01-28  16:07             1,249 README.rows.txt
2003-07-29  13:33             4,276 README.savepoints.txt
2006-01-10  09:59             2,295 README.select_expressions.txt
2005-01-25  08:24             1,765 README.sequence_generators.txt
2005-12-01  04:34             1,714 README.set_transaction.txt
2005-05-28  00:17             1,035 README.trim.txt
2005-12-03  13:09             2,915 README.universal_triggers.txt
2007-05-13  16:06             1,316 README.update_or_insert.txt
2005-01-22  23:17                24 README.view_updates.txt

〇〇

文档没有提到，连猜带测试地得到了递归CTE的深度最大为1024
SQL> with t as(select 1 a from rdb$database union all select a+1 a from t where a<5)select * from t;
Statement failed, SQLCODE = -104
Dynamic SQL Error
-SQL error code = -104
-CTE 'T' has cyclic dependencies
SQL> with RECURSIVE t as(select 1 a from rdb$database union all select a+1 a from t where a<5)select * from t;

           A
============
           1
           2
           3
           4
           5

SQL> with RECURSIVE t as(select 1 a from rdb$database union all select a+1 a from t where a<5000)select count(*) from t;

       COUNT
============
Statement failed, SQLCODE = -693
Too many concurrent executions of the same request

SQL> with RECURSIVE t as(select 1 a from rdb$database union all select a+1 a from t where a<1024)select count(*) from t;

       COUNT
============
        1024

SQL> with RECURSIVE t as(select 1 a from rdb$database union all select a+1 a from t where a<1025)select count(*) from t;

       COUNT
============
Statement failed, SQLCODE = -693
Too many concurrent executions of the same request