永平 的个人资料ERN空间照片日志列表更多  |  工具 帮助 |
|
|
2月1日 整合安装Merriam-Webster的词典
8月9日 书名的翻译啊Expert Oracle Database Architecture出了中文本了，Oracle专家9i和10g编程艺术。书名有趣，把原书的大小标题颠了个儿，编程技巧和解决方案也简化成了编程艺术。看来The Art of Computer Programming遗毒非浅啊。 客观的说，还没有看过书。不过从书名就已经打消了阅读的欲望，“信”都没达到，质量如何就难说了。人邮的质量更让人担心啊。 不过还好，有人知道这本书的质量，好歹花力气翻译了。 顺便说说刚看完的《关于他人的痛苦》，苏珊·桑塔格文集中的一卷，薄薄的一本书。昨天下午开会的时候读了一半，今天过来上班很早，开工前读了另一半。文章内容大致是对战争照片的一些质疑、对媒体倾向、言论自由的一些分析，很有意思，符合作者所谓“美国公众的良知”的身份啊。和这个题目有点关系的是，译者在文末译后记中也谈了对同一本书不同翻译版本、翻译风格的态度，还是挺有意思 ，值得一读。 想想现在技术领域的翻译，唉，都是为了“钱途”啊。还有谁有季羡林翻译印度史诗的精神呢？ 4月14日 Oracle 10g Concepts——Ch4 事务管理（二）事务命名你可以用一个简单、便于记忆的文本串命名一个事务。这个名字是事务有关什么的提示。事务名代替了分布式事务的提交注释（commit comment），有一下好处： Ÿ 监控长时间事务和解决悬疑分布式事务更容易。 Ÿ 你可以在应用中看到事务ID的同时看到事务名。例如，DBA可以在Enterprise Manager 监控系统活动时看到事务名。 Ÿ 当兼容性设置到Oracle9i或更高，事务名会写入事务审计redo记录（auditing redo record）。 Ÿ LogMiner可以用事务名从redo log里事务审计记录中搜索特定事务。 Ÿ 你可以用事务名从数据字典视图，如V$TRANSACTION 中找到特定事务。 在事务开始前用SET TRANSACTION ... NAME语句命名事务。 当你命名事务时，就将事务名和ID关联了。事务名并不必须是唯一的；不同的事务可以在同一时间、同一所有者下拥有相同的名字。你可以使用任何名字来区分事务。 之前的版本中，你可以用提交注释来关联一个注释到事务。但仅当事务正被提交才能关联一个注释。 提交注释由于后向兼容性仍被支持。但Oracle强烈推荐你使用事务名。在命名的事务中，提交注释被忽略。
两步（Two-Phase）提交机制在一个分布式数据库中，Oracle必须跨网络协调事务管理、维护数据一致性，甚至网络或系统发生故障时。 分布式事务是指包括有一条或多条语句组成的事务，它们更新位于分布式数据库两个或更多不同节点的数据。 两步提交机制确保所有参与一个分布式事务的数据库服务器或者全部提交或者全部回退事务中的语句。两步提交机制还保护一致性约束、远程过程调用和触发器等执行的隐式DML操作。 Oracle的两步提交机制对执行分布式事务的用户是完全透明的。事实上，用户甚至不需要知道事务是分布的。一条标志事务结束的COMMIT语句自动触发两步提交机制来提交事务。不需要编写代码或复杂的语句语法在数据库应用内处理分布式事务。 后台恢复进程（RECO）自动解决悬疑分布式事务（in-doubt distributed transactions）—— 提交被任何类型的系统或网络故障干扰的事务。故障被修复、通信重建后，所有相关节点上每个本地Oracle数据库的RECO进程一致自动提交或回滚任何悬疑分布式事务。 在遇到长时间故障时，Oracle允许每个本地管理员手工提交或回退故障导致的任何悬疑分布式事务。这一选项使得本地DBA可以释放任何由于长时间故障被无限期锁定的资源。 如果数据库必须被恢复到过去的某一点，Oracle的恢复工具使得其他节点的DBA也可以将数据库返回到该时间点。这一操作确保了全局数据库保持一致。
自治事务概述自治事务是可从其他事务调用的独立事务。自治事务可以让你脱离调用事务的上下文执行一些SQL操作、提交或回退这些操作然后返回调用事务上下文继续。 一旦被调用，自治事务完全独立于调用它的主事务。它不会看到主事务做的任何未提交修改，不会与主事务共享任何锁或资源。自治事务做的修改在其提交后才对其他事务可见。 一个自治事务可以调用另一个。除了资源限制，调用多少层自治事务没有任何限制。 自治事务和调用它的事务间可能产生死锁。Oracle侦测到死锁后返回一个错误。应用开发者有责任避免死锁情况。 自治事务可用于实现那些需要独立执行而不管调用事务提交或回滚的动作，比如交易日志和重试计数。 自治PL/SQL块你可以从一个PL/SQL块中调用自治事务。使用pragma AUTONOMOUS_TRANSACTION。Pragma是一个编译器指令。你可以声明以下类型的PL/SQL块为自治的： Ÿ 存储过程或函数 Ÿ 本地过程或函数 Ÿ 包 Ÿ 类型方法 Ÿ 顶层（Top-level）自治块 当进入了一个自治PL/SQL块，调用者的事务上下文被暂停。这一操作确保块（或其他它调用的块）里执行的SQL操作对调用者的事务上下文没有任何依赖和效用。 当一个自治块调用另一个自治块或其自身，被调用的块不会与调用块共享任何事务上下文。但当一个自治块调用一个非自治块（即没有声明未自治），被调用的块继承了调用它的自治块的事务上下文。 自治PL/SQL块中的事务控制语句仅作用于当前活动的自治事务。一些例子： SET TRANSACTION COMMIT ROLLBACK SAVEPOINT ROLLBACK TO SAVEPOINT
类似的，主事务中的事务控制语句仅作用于该事务而不能作用于任何它调用的自治事务。例如，回滚主事务至[其中]自治事务开始前的savepoint不会回退自治事务。
Oracle 10g Concepts——Ch4 事务管理（一）第四章 事务管理本章包括如下主题： Ÿ 事务简介 Ÿ 事务管理概述 Ÿ 自治事务概述 事务简介事务是包含一条或多条SQL语句的一个逻辑单位。一个事务是一个原子体（unit）。事务中所有SQL语句的效用或者全部提交（作用到数据库）或者全部回滚（从数据库撤消）。 一个事务以第一条可执行SQL语句开始，执行显式的一条COMMIT或ROLLBACK语句或者隐式的一条DDL语句从而提交或回滚时结束。 考虑一个银行数据库来展示事务的概念。当一个银行客户从一个储蓄账户转帐到一个支票帐户，这个交易可以有三个不同的操作： Ÿ 减少储蓄账户（余额） Ÿ 增加支票帐户（余额） Ÿ 在交易流水中记录本交易 应当允许两种情况。如果执行所有三个SQL语句可以将帐户维持在合理的余额下，事务可以作用到数据库中。但如果发生一些比如资金不足、无效账号或硬件故障等问题使得事务中的一条或两条语句无法完成，整个事务应当被回滚从而保证所有帐户的余额正确。 图 4-1展示了这个银行交易的例子 语句执行和事务控制执行成功的SQL语句和成功提交的事务是不同的。执行成功意味着单条语句： Ÿ 被语义分析（parse）了 Ÿ 是一条有效的SQL指令 Ÿ 作为一个原子体无差错执行。例如一个多行更新的所有行均被修改了。 但在包含此语句的事务被提交前，事务可被回滚，该语句产生的所有修改均被回退。一条语句，而不是一个事务成功执行了。 提交是指用户显式或隐式地申请将事务中的修改永久化。显式申请产生于当用户提请一条COMMIT语句时。隐式申请产生于应用的正常终止后或DDL（数据定义语言）操作完成时。事务中SQL语句产生的变化仅在事务提交后才永久化并可为其它用户所见。事务提交后执行的查询可以看到提交的变化。 在开始事务前你可以用SET TRANSACTION ... NAME 语句来命名事务。这使得监控长时间事务和解决悬疑（in-doubt）的分布式事务更容易。
语句级回滚如果在执行一条SQL语句的任何时间产生了错误，这一语句的效用将全部回滚。回滚的效果就是仿佛这一语句从未执行过。这种操作就是语句级回滚。 SQL语句执行过程中发现的错误导致语句级回滚。这些错误的一个例子是试图向一个主键插入重复值。涉及到死锁（竞争同一数据）的单条SQL语句也可能产生语句级回滚。在SQL语句语义分析过程中发现的错误，比如语法错误，由于语句尚未执行，因此并不引发语句级回滚。 一条失败了的SQL语句仅导致其自身可能执行的任何操作丢失。它不会同一个事务中之前任何操作的丢失。如果是一个DDL语句，那么其之前的隐式提交不会被回退。
可恢复空间分配Oracle提供一种方法可以在空间分配失败时暂停然后恢复大数据库操作的执行。这使管理员可以采取纠正行动而不是让Oracle数据库服务器返回一个错误给用户。错误环境被纠正后，暂停的操作可以自动恢复。 仅当客户端使用ALTER SESSION语句显式激活会话的可恢复语义（semantics）时，语句才会运行在可恢复模式。 当下面情况之一发生时，可恢复空间分配被暂停： Ÿ 空间耗尽 Ÿ 达到最大extents数 Ÿ 超过空间配额（quota） 对于非可恢复空间分配，这些情况会产生错误并回滚语句。 暂停一条语句自动地就暂停了这个事务。那么在语句暂停和恢复期间所有的事务资源被持有。 当错误条件消失（例如，用户干预或可能排序空间被其它查询释放），暂停的语句自动恢复执行。
事务管理概述当出现第一条可执行SQL语句，Oracle开始一个事务。一个可执行SQL语句是产生对一个实例（instance）的调用的SQL语句，包括DML和DDL语句。 事务开始时，Oracle为它分配一个可用的UNDO表空间来记录新事务的回滚项（rollback entry）。 Ÿ 一个用户提请一个没有SAVEPOINT子句的COMMIT或ROLLBACK 语句。 Ÿ 一个用户运行一个DDL语句，例如CREATE、DROP、RENAME或ALTER。如果当前事务包含任何DML语句，Oracle首先提交事务，然后作为一个新的单语句事务运行和提交DDL语句。 Ÿ 一个用户从Oracle断开。当前事务被提交。 Ÿ 一个用户进程异常中断。当前会话被回滚。 一个事务结束后，下一个可执行SQL语句自动开始新事务。
提交事务在修改数据的一个事务提交前，发生下列事件： Ÿ Oracle产生undo信息。undo信息包含事务中SQL语句修改的旧数据值。 Ÿ Oracle在SGA的redo log buffer生成了redo log项（entry）。redo log记录包含了对数据块和回滚块的修改。这些修改也可能在事务提交前就写入了磁盘。 Ÿ 这些变化作用到了SGA中的数据库缓存（database buffers）。这些修改也可能在事务提交前就写入了磁盘。
当事务提交，发生下列事件： 1. 相关undo表空间的内部事务表记录该事务已被提交，事务对应唯一的系统改变号（SCN）被分配并记录入该表。 2. 日志写进程（LGWR）将SGA中redo log buffer里的redo log项写入redo log文件。还将事务的SCN写入redo log文件。这一原子事件决定了事务的提交。
回滚事务回滚是指回退一个未提交事务中SQL语句产生的对数据的修改。Oracle使用UNDO表空间（或回滚段（rollback segment））存储旧值。redo log包含修改的记录。 Oracle允许你回滚整个为提交的事务。或者你也可以回滚未提交事务中在一个称为savepoint的标记之后的部分。 Ÿ 所有类型的回滚使用同样的流程： Ÿ 语句级回滚（由于语句或死锁错误） Ÿ 回滚到一个savepoint Ÿ 由于用户请求回滚事务 Ÿ 由于进程异常中止回滚事务 Ÿ 当实例异常中止，所有事务回滚 Ÿ 恢复时不完整事务回滚 回滚一个完整事务，不需要引用任何savepoint时，发生下列事件： 1. Oracle使用相应的undo表空间回退事务中所有SQL语句产生的所有变化。 2. Oracle释放事务的所有锁。 3. 事务终止。
事务中的Savepoints你可以在事务上下文中声明称为savepoint的中间标记。Savepoint将一个长事务分隔为较小的部分。 使用savepoint，你可以在长事务中任何点任意标记你的操作。然后你可以选择回滚在事务中当前点之前、声明的savepoint之后执行的操作。比如，你可以在一长段复杂的更新中使用savepoint，如果犯了个错，你不需要重新提交所有语句。 Savepoints在应用程序中同样有用。如果一个过程包含几个函数，那可以在每个函数前创建一个savepoint。如果一个函数失败，返回数据到函数开始前的状态并在修改参数或执行一个恢复操作后重新运行函数就非常容易。 在回滚到一个savepoint后，Oracle释放由被回滚的语句持有的锁。其他等待之前被锁资源的事务可以进行了。其他要更新之前被锁行的事务也可以执行。 1. Oracle仅回滚savepoint之后的语句。 2. Oracle保留这一savepoint，但所有建立于此后的savepoints丢失。 3. Oracle释放在该savepoint后获得的所有表、行锁，但保留之前获得的所有锁。 事务保持活动并可继续。 无论何时一个会话在等待事务，到savepoint的回滚不会释放行锁。为了确保事务如果无法获得锁也不会悬挂（hang），在执行UPDATE或DELETE前使用FOR UPDATE ... NOWAIT。（这里指回滚的savepoint之前获得的锁。该savepoint后获得的行锁会被释放，之后执行的语句也会被彻底回滚。） 1月13日 Ask Tom之中文化20060113——Consistent Gets（一）译者说明：这篇至今长达4.3年的帖子相当之冗长，其中有不少详尽的解释。长度决定无法一次性译完，也无法全部翻译。因此下文中有部分内容是译者自己总结了一些问题，合并了一些评论及回复给出的。此后将力争尽快挑选精彩的评论和回复翻译完成。 consistent gets——非常困惑 版本8.1.6 原提交于美国东部时间2001年6月28日16:59，最后更新于2006年1月12日11:49 问： Tom： create table test( a int); begin for i in 1..10000 loop insert into test values (i); end loop; end; set autotrace on select count(0) from test; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'TEST' Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 20 consistent gets 0 physical reads 0 redo size 369 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed select * from test where a=10; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'TEST' Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 21 consistent gets 0 physical reads 0 redo size 360 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed select * from test; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'TEST' Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 686 consistent gets 0 physical reads 0 redo size 185864 bytes sent via SQL*Net to client 74351 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10001 rows processed 问题： 1.当查询count(0)，Oracle究竟在干什么？我们没有索引，Oracle会访问每个块来查询这个数么？如果是这样，为什么select * from test的consistenet gets要小很多呢？它们都是全扫描表哦。 2.为什么select * from test比select * from test where a=10有更高的consistenet gets呢？尽管前者返回更多的行，但都没有索引，Oracle需要访问每一个块来获取值，应当是相同的consistenet gets啊。 此致 答： 问题1 select count(0)就如同 select count(*) from ( select 0 from t ) / 是的，Oracle访问每一块查找行来返回你0。 问题2 这是你arraysize的副作用。你应当是用8K的块，因为我准确的重现了这一切。 我们期望得到大概20次一次性读是吧。SQLPlus的缺省array size是15行/次。10000/15 = 666.66666，666+20 = 686——哇，686就是我们的consistenet gets！ 这就是所发生的事情。当你获取15行，Oracle暂停，把数据返回给你。当你再回去取下15行，它再次获取缓存来继续你的查询。看看不同的array size会发生什么。 ops$tkyte@8i> select * from test; 10000 rows selected. Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 686 consistent gets 0 physical reads 0 redo size 108813 bytes sent via SQL*Net to client 46265 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed ops$tkyte@8i> set arraysize 1000 ops$tkyte@8i> select * from test; 10000 rows selected. Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 30 consistent gets 0 physical reads 0 redo size 86266 bytes sent via SQL*Net to client 942 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed ops$tkyte@8i> set arraysize 5000 ops$tkyte@8i> select * from test; 10000 rows selected. Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 22 consistent gets 0 physical reads 0 redo size 149793 bytes sent via SQL*Net to client 382 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed 另一个需要注意的事情是当array size过大，数据传输量反而增长。在array size上有个边际递减，因此不要走向极端。 -------------------------------------------------------------------------------- 评论： June 29, 2001 评论者：A reader 是否意味着，当我们全表扫描我们希望大一点的array size，而当我们根据rowid查询，我们需要小一点的？ 回复： 将arraysize设置到某个常数，如果它过大对你的单行读取（single row fetches）没什么影响，但如果过小，（读取——译注）多行（不仅仅是全扫描）会影响到你。 Tested it but got different results April 30, 2002 评论者：ZS 来自澳大利亚 我现在有点困惑了。尽管你解释得不错，但我在我的环境下测试得到了不同的结果。我的查询select *返回122行。 Arraysize为15 --> consistenet gets为1051 (122/15=8)即1043 + 8 = 1051（这没问题）。所以我猜想根据上面的例子当我count(*)，consistenet gets应当是1043，是么？但 Arraysize为15下count(*) --> consistenet gets为920。我不知道这是怎么产生的。 我做了更多的测试： Arraysize为122下select * --> consistenet gets = 1043 Arraysize为122下select count(*) --> consistenet gets = 920 Arraysize为5000下select * --> consistenet gets = 1043 Arraysize为5000下select count(*) --> consistenet gets = 920 希望你能解释，谢谢 ZS 回复： count(*)返回多少行？１ array size甚至都没有开始介入。这条语句的运行只需要一次调用。 另外，count(*)还可能“从不碰表”。它可以只使用一个索引。 考虑： ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects; Table created. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' ) Free Blocks.............................0 Total Blocks............................320 Total Bytes.............................2621440 Unused Blocks...........................4 Unused Bytes............................32768 Last Used Ext FileId....................7 Last Used Ext BlockId...................40969 Last Used Block.........................60 PL/SQL procedure successfully completed. 所以表用了大约316块。 ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t; 22905 rows selected. Statistics ---------------------------------------------------------- 0 recursive calls 12 db block gets 1823 consistent gets 46 physical reads 0 redo size 2704019 bytes sent via SQL*Net to client 169811 bytes received via SQL*Net from client 1528 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 22905 rows processed 22905/15 = 1527+316 = 1843这大致就是我们的consistenet gets（我们所期望的）…… ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from t; Statistics ---------------------------------------------------------- 0 recursive calls 12 db block gets 319 consistent gets 46 physical reads 0 redo size 369 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed count(*)，因为它是一次调用完成的，不需要arraysize，只在每个块上做了一次consistenet gets。所以它有319次consistenet gets（也就大致是表的大小）。 现在给表加上一个主键： ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(object_id); Table altered. ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics; Table analyzed. ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from t; Statistics ---------------------------------------------------------- 79 recursive calls 4 db block gets 82 consistent gets 1 physical reads 0 redo size 369 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed 我们可以看到consistenet gets下降了。为什么？因为优化器这次在索引中数行数，而不是表。 -------------------------------------------------------------------------------- May 01, 2002 评论者：A reader 你好Tom， 我们这样计算命中率：(1-(phycial_read/(db_block_get+consistance get)) 但根据你这里的回复，我想consistenet gets或者db_block_gets是物理读（不是逻辑的）。 可以解释一下么？ 回复： db_block_gets + consistent_gets = LOGICAL IO（逻辑IO） physical_reads = PHYSICAL IO（物理IO） 我没看出我可能在这里误导了你——你可以指出上面哪个部分告诉你它们是物理IO么？ -------------------------------------------------------------------------------- May 01, 2002 评论者：A reader Tom 我想我在这里混淆了逻辑IO和物理IO。非常抱歉，不是你的错。 但我确实有些疑惑。 如果我第一次递交一个查询，内存中一无所有，那么db_block_gets和consistant gets都是物理读。（对么？） 如果我再次递交这个查询，很多块已经在内存中了，就是逻辑IO了。对么？ 谢谢。 回复： 你可能是对的，你也可能不对。其他查询和/或操作可能已经缓存了数据。 对于第二点，你可能是对的，也可能不对，这依赖于： o 访问的数据量 o buffer cache的大小 o 系统其他还在运行什么 o 数据库所用的不同特性/功能（如：并行查询） 总体上是对的，你会期望在很多情况下第二次执行大多是逻辑IO。 -------------------------------------------------------------------------------- Reader May 01, 2002 评论：A reader Consistenet gets基于为一致性读重建块。所以它一个仅关于读取的db_blocks的函数。如果你说它可以被arraysize改变，你是否是指由于arraysize，一些块被多次读取，也就是同一个块有大于1次的一致性读？ 谢谢 回复： 不是，你的表述是错误的。 一个consistent get是在读一致性模式下（read consistent mode）——时间点模式——读的一个块。它可能也可能不涉及到重建（回滚）。 DB Block Gets是CURRENT模式读（CURRENT mode gets）——当前的块读取。 有些块是被多次处理的，是的，处理中块会有多于1次一致性读。考虑： ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects; Table created. ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T') Free Blocks.............................0 Total Blocks............................320 Total Bytes.............................2621440 Unused Blocks...........................4 Unused Bytes............................32768 Last Used Ext FileId....................7 Last Used Ext BlockId...................40969 Last Used Block.........................60 PL/SQL procedure successfully completed. 表有316块，22,908行…… ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly statistics; ops$tkyte@ORA817DEV.US.ORACLE.COM> set arraysize 15 ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t; 22908 rows selected. 对于arraysize=15，我们期望： 22908/15 + 316 = 1843 次一致模式读取（consistent mode gets）。db block gets——为FULL SCAN，和我们select的数据本身无关。 Statistics ---------------------------------------------------------- 0 recursive calls 12 db block gets 1824 consistent gets 170 physical reads 0 redo size 2704448 bytes sent via SQL*Net to client 169922 bytes received via SQL*Net from client 1529 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 22908 rows processed ops$tkyte@ORA817DEV.US.ORACLE.COM> set arraysize 100 ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t; 22908 rows selected. Now, with 100 as the arraysize, we expect 22908/100 + 316 = 545 次一致模式读（consistent mode gets）。 Statistics ---------------------------------------------------------- 0 recursive calls 12 db block gets 546 consistent gets 180 physical reads 0 redo size 2557774 bytes sent via SQL*Net to client 25844 bytes received via SQL*Net from client 231 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 22908 rows processed ops$tkyte@ORA817DEV.US.ORACLE.COM> set arraysize 1000 ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t; 22908 rows selected. 现在对于arraysize = 1000我们期望： 22908/1000+316 = 338 次一致模式读（consistent mode gets）。 Statistics ---------------------------------------------------------- 0 recursive calls 12 db block gets 342 consistent gets 222 physical reads 0 redo size 2534383 bytes sent via SQL*Net to client 2867 bytes received via SQL*Net from client 24 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 22908 rows processed 所以，是的，当array读取大小比要读取的行数小时，一致模式（consistent mode）下块被读取不止一次。这是因为我们处理一个块时到一半就获得足够的行返回给客户端，我们就放弃这个块。当他们要之后的N行时，我们回到那个处理了一半的块然后继续。 -------------------------------------------------------------------------------- how does this formula works? May 01, 2002 评论者：A reader “22908/15 + 316 = 1843 次一致模式读取（consistent mode gets）。db block gets——为FULL SCAN，和我们select的数据本身无关。” 你是如何知道22908/15 + 316就是consistent gets总量的？ 回复： 读原来的问题和回答，我在那里提到了。 在这个例子里： 22908 = 行数 15 = arraysize. 22908/15 = 你获取的次数 所以这是你需要读一个块多于一次的次数。 315是表的块数（在一个完美世界里我们要执行315次consistent gets。对于arraysize为15和22908行，我们要加上22908/15次consistent gets）…… -------------------------------------------------------------------------------- I got that srt of idea but I was confused May 01, 2002 评论者：A reader 你好，那么SQL*PLUS中一个低arraysize会歪曲autotrace统计并影响性能？我猜想如果autotrace不那么精确，TKPROF也一样么？ 我们如何设置arraysize来提高类似select * from table_a的性能呢，比如在Developer Reports或Business Objects之类的AD HOC Tools中。 回复： 不——它不会“歪曲”它们。你可以在SQLPlus中使用array size来查看你的应用干了些什么。 Array Size在SQLPlus中缺省是15 JDBC中10 pro*c中2 OCI中1 ODBC中未知（不懂，从来没用过） 这不是歪曲，这是事实！它显示你使用不同arraysize来读取（数据）时真实发生的现象。 TKPROF会显示完全一样的信息。一样——从JDBC到SQLPlus到Pro*C（SQLPlus仅是一个OCI应用程序）。 对于reports，参考： ARRAYSIZE (RWBLD60) 描述ARRAYSIZE是用于Oracle数组化处理（array processing）中的array大小，以K为单位。 语法： [ARRAYSIZE=]n 至于business objects——参考它们的文档。 -------------------------------------------------------------------------------- [以下一个问答由译者归并之后大约4个问答而来，如果不能明白，请参考原文中Tom不厌其烦的解释] 问：开头那个例子里的20是什么？row/arraysize+block_size是怎么来的？ 答：20是表的块数。 block_size是要读的块数，row/arraysize是由于不能一次处理完而需要额外读取的次数。 [总结完毕] -------------------------------------------------------------------------------- [以下开始仅选译部分精彩问答] -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 原文标题：Thanks for the question regarding "consistent gets -- Very puzzling", version 8.1.6 原文地址： [url]http://asktom.oracle.com/pls/ask/f?p=4950:8:12938966801863764274::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:880343948514[/url] -------------------------------------------------------------------------------- 原文版权归属asktom.oracle.com所有，翻译 by ern@CNOUG 2006-01-13 11:40 1月10日 Ask Tom之中文化20060110——仅创建不存在的索引Peter 仅在不存在时创建索引 版本9.0.1 原提交于美国东部时间2006年1月9日15:03，最后更新于2006年1月9日16:03 问： 这很可能是个简单问题，但解决倒还真有点难： 正如所想，索引已经存在时CREATE INDEX语句失败。我如何先测试索引是否存在，从而避免如果索引存在时发生的错误？ 我得到的最好解答时： DECLARE index_exists NUMBER; BEGIN index_exists := 0; SELECT 1 INTO index_exists FROM dual WHERE EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IX_MYINDEX'); IF index_exists = 0 THEN CREATE INDEX IX_MYINDEX ON MYTABLE (MYCOLUMN) NOLOGGING NOPARALLEL; END IF; END; 这产生错误：ORA-00928: missing SELECT keyword（丢失关键字SELECT） 我在做一个数据库升级脚本，将一个产品的一个版本升到另一个版本，但依原始版本不同，索引可能已经存在了。 我作为开发者的广泛数据库经验（大概8年了）都是关于MS SQLServer的。这个任务在SQL Server上不算什么。但找Oracle的解决方案时竟然如此困难，我大吃一惊。我搜索了Oracle文档，Web和你的站点……但一无所获。看起来或 者是根本没有答案，或者就是太简单（当然不是对我）而无人提及了！ 答： 记住，我们知道的就是“简单”，我们不知道的看起来就不可能——直到你“知道”了，然后看起来又简单了。 这是一个方法： ops$tkyte@ORA10GR2> create table t ( x int ); Table created. ops$tkyte@ORA10GR2> declare 2 already_exists exception; 3 pragma exception_init( already_exists, -955 ); 4 begin 5 execute immediate ' 6 create index t_idx on t(x) 7 '; 8 dbms_output.put_line( 'created' ); 9 exception 10 when already_exists then 11 dbms_output.put_line( 'skipped' ); 12 null; 13 end; 14 / created PL/SQL procedure successfully completed. ops$tkyte@ORA10GR2> declare 2 already_exists exception; 3 pragma exception_init( already_exists, -955 ); 4 begin 5 execute immediate ' 6 create index t_idx on t(x) 7 '; 8 dbms_output.put_line( 'created' ); 9 exception 10 when already_exists then 11 dbms_output.put_line( 'skipped' ); 12 null; 13 end; 14 / skipped PL/SQL procedure successfully completed. -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 原文标题：Peter -- Thanks for the question regarding "Only create an index when it doesn't exist", version 9.0.1 原文地址： http://asktom.oracle.com/pls/ask ... ERIA:54643197712655 -------------------------------------------------------------------------------- 原文版权归属asktom.oracle.com所有，翻译 by ern@CNOUG 2006-01-10 10:26 12月30日 Ask Tom之中英对照20051230——如何计算被聚集函数占用的内存？(ORA-01467: sort key too long)Ask Tom之中英对照20051230——如何计算被聚集函数占用的内存？(ORA-01467: sort key too long) Michael How to calculate memory, occupied by agrerate functions ? (ORA-01467: sort key too long) 如何计算被聚集函数占用的内存？(ORA-01467: sort key too long) 版本：Oracle 8.0.5 原提交于美国东部时间2000年8月7日，最新更新于2005年10月27日 问： 我在一个有许多max()函数（大概70个）的查询，遇到了ORA-01467: sort key too long 我知道聚集函数占用的内存上有一个限制：“GROUP BY表达式和所有的非唯一（non-distinct）聚集（即sum、avg）必需能放入一个数据库块”。 我想如果我减小表名和列名，类似表达式Max(decode(ANALYSIS.ANALYSIS_NAME,'CRASH',RESULT.AVG_VALUE)), 占用的空间就能更小点。 但当我重命名了表和列，现在： Before Now ----------------------- ----- ANALYSIS.ANALYSIS_NAME A.N RESULT.AVG_VALUE R.AVG Max(decode(ANALYSIS.ANALYSIS_NAME, 'CRASH',RESULT.AVG_VALUE)) Max(decode(A.N,'CRASH',R.AVG)) 同样数量的max还是会遇到相同的ORA-01467…… 问题：如何计算被聚集函数占用的内存？ 此致， Michael. 另： 这是我的SQL： Select ITEM.ITEM_NAME, Max(decode(ANALYSIS.ANALYSIS_NAME,'CRASH',RESULT.AVG_VALUE)), Max(decode(ANALYSIS.ANALYSIS_NAME,'CRASH',RESULT.MIN_VALUE)), Max(decode(ANALYSIS.ANALYSIS_NAME,'CRASH',RESULT.MAX_VALUE)), Max(decode(ANALYSIS.ANALYSIS_NAME,'SPEED',RESULT.AVG_VALUE)), Max(decode(ANALYSIS.ANALYSIS_NAME,'SPEED',RESULT.MIN_VALUE)), Max(decode(ANALYSIS.ANALYSIS_NAME,'SPEED',RESULT.MAX_VALUE)) , Max(decode(ANALYSIS.ANALYSIS_NAME,'BREAK',RESULT.AVG_VALUE)), Max(decode(ANALYSIS.ANALYSIS_NAME,'BREAK',RESULT.MIN_VALUE)), Max(decode(ANALYSIS.ANALYSIS_NAME,'BREAK',RESULT.MAX_VALUE)) ... Max(...) ... from ITEM, ANALYSIS, RESULT where RESULT.ITEM_ID=ITEM.ITEM_ID and RESULT.ANALYSIS_ID=ANALYSIS.ANALYSIS_ID Group by ITEM.ITEM_NAME 答： 这是基于数据值，而不是它们的名字。考虑： ops$tkyte@8i> create table t ( x varchar2(4000), y varchar2(4000), z varchar2(4000), a int ); Table created. 一个包括所有四个列的排序键（sort key）是非常大的——x,y,z就有12,000字节，数（即a——译注）最大22字节。 ops$tkyte@8i> insert into t values ( 'x', 'y', 'z', 1 ); 1 row created. ops$tkyte@8i> select x, y, z, sum(a) 2 from t 3 group by x, y, z 4 / X Y Z SUM(A) ---------- ---------- ---------- ---------- x y z 1 但这并不是一个问题——除非我们给一个真正的大行： ops$tkyte@8i> insert into t 2 values 3 ( rpad('*',4000,'*'), 4 rpad('*',4000,'*'), 5 rpad('*',4000,'*'), 2 6 ); 1 row created. ops$tkyte@8i> select x, y, z, sum(a) 2 from t 3 group by x, y, z 4 / from t * ERROR at line 2: ORA-01467: sort key too long 所以，你看到这（排序键——译注）是关于真正的数据的函数，而不是表或列的名字或者是它们最大可能的长度。它完全是它们当前真实长度的函数。 评论： OraDoc missleading April 25, 2002 评论者：Marc Blum来自Aachen, Germany Oracle 8.1.7 文档说： ORA-01467 sort key too long 原因：一个DISTINCT, GROUP BY, ORDER BY, 或SET操作需要一个超过Oracle支持的排序键（sort key）。SELECT语句中或者是列过多，或者分组函数过多。 方法：减少操作中涉及的列或分组函数数。 对比你的回复，我想文档有些误导而且没什么帮助。 ORA-01467: sort key too long April 25, 2002 评论者：Kenneth来自HK 尽管我们知道它仅取决于数据大小而不是列名，但如何并没有提到处理。如果我们重新设定数据库块大小，比如从现在的8K增到16K，我们只能重新生成数据库，影响非常大。有什么别的方法避免么？ 回复： 9i中你的数据库有多于一种块大小。所以，一种解决方法是用9i。那样你可以在同一个数据库中有2、4、8、16甚至32K的块。 此外，最大的排序键是块大小的函数。唯一使用更大排序键的方法是用更大的块。 what the term SORT KEY mean June 11, 2002 评论者：ajar bajar 用在这里，排序键是指什么？ （要关心排序键大小——译注）是不是因为当按照多个列分组时，我们也使用了所有这些列的连接（concatanation）来排序？我们为什么在这里要谈到块大小呢？ 回复： 他们自己在问题里说了： …… 我知道聚集函数占用的内存上有一个限制：“GROUP BY表达式和所有的非唯一（non-distinct）聚集（即sum、avg）必需能放入一个数据库块”。 …… 需要能放入一个块（实际上是块的一部分）。这就是这里的排序键。 Reader June 11, 2002 评论者：一个读者 Tom， 在早期的版本中是不是有类似sort_write_buffer_size这样一个可调参数？ 谢谢 回复： 不，没有。 never read about this limitation June 12, 2002 评论者：一个读者 那么分组列+聚集函数应当不超过块大小。 为什么分组列+聚集函数称为排序键？事实上仅分组列才应当是排序键。聚集函数是怎么算进来的？ 你根据分组列的连接，也就是排序键来聚集值，不是么？ 可以给出这个限制问题在文档中的链接么？ 谢谢 回复： [url]http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76999/e900.htm#1656[/url] 搜索1467 ORA-01467 sort key too long 原因：一个DISTINCT, GROUP BY, ORDER BY, 或SET操作需要一个超过Oracle支持的排序键（sort key）。SELECT语句中或者是列过多，或者分组函数过多。 方法：减少操作中涉及的列或分组函数数。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 原文标题： Michael -- Thanks for the question regarding "How to calculate memory, occupied by agrerate functions ? (ORA-01467: sort key too long)", version Oracle 8.0.5 原文地址： [url]http://asktom.oracle.com/pls/ask/f?p=4950:8:1892982080660585514::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:508222056084[/url] -------------------------------------------------------------------------------- 原文版权归属asktom.oracle.com所有，翻译 by ern@CNOUG 2005-12-30 09:54 12月26日 Ask Tom之中英对照20051226——EXECUTE IMMEDIATE中可用的字符串最大长度Francisco EXECUTE IMMEDIATE中可用的字符串最大长度 版本8.1.7.4 原提交于美国东部时间2002年10月26日11:21，最后更新于2004年6月1日15:38 问： 你好Tom。 我想向EXECUTE IMMEDIATE传入一个长2151字符的字符串。例： EXECUTE IMMEDIATE 'string with 2151 characters'; /* All the grants from user */ 我试图从一个被赋予ALL PRIVILEGES的用户那里复制系统权限。 SQL> GRANT ALL PRIVILEGES TO FRANK; 这个用户有大概115个权限了。我如何用PL/SQL来复制这些权限呢？感谢任何建议。 此致 Francisco Mtz. Oracle DBA & Developer OCP DBA 8 & 8i 答： 支持最长32K（PL/SQL变量字符串的最大长度）的字符串。 但，为了在一条单个语句授予多个（权限），语句应当是如下的： begin l_string := 'begin execute immediate ''grant select on t to scott''; execute immediate ''grant update on t to scott''; .... end;'; execute immediate l_string; end; 我猜想你是这样： l_string := 'grant select on t to scott; grant select on x to scott; .....'; 这将是一个SQLPlus脚本，而不是一个可执行的SQL语句。但是，你的问题中并没有给我例子——这只是一个猜测。 评论： It is that I am doing. October 26, 2002 评论者：Francisco Martinez来自Monterrey, N.L., 墨西哥 非常感谢Tom。 我的代码如下： CREATE OR REPLACE PROCEDURE REPLICATE_SQL_STATEMENT (pDatabase IN VARCHAR2, pSQL_statement IN VARCHAR2) IS vSQL_statement VARCHAR2(3000); BEGIN IF pSQL_statement IS NOT NULL THEN vSQL_statement := 'BEGIN ARBOR.Execute_SQL_Statement@'||pDatabase||'('''||pSQL_statement||'''); END;'; EXECUTE IMMEDIATE vSQL_statement; END IF; END; / CREATE OR REPLACE FUNCTION GET_SYS_PRIVS_STMT (pDatabase IN VARCHAR2, pUsername IN VARCHAR2, pEvent IN VARCHAR2, pAdmin IN VARCHAR2) RETURN VARCHAR2 IS vSQL_statement VARCHAR2(3000); vSysprivs_statement VARCHAR2(1000); TYPE EmpCurTyp IS REF CURSOR; cSysprivs EmpCurTyp; vSyspriv VARCHAR2(100); vSyspriv_list VARCHAR2(1000); BEGIN IF (pAdmin = 'ADMIN') OR (pAdmin = 'NOADMIN') THEN IF pEvent = 'GRANT' THEN vSysprivs_statement := 'SELECT A.PRIVILEGE'|| ' FROM DBA_SYS_PRIVS A, DBA_SYS_PRIVS@'||pDatabase||' B'|| ' WHERE B.GRANTEE (+) = A.GRANTEE'|| ' AND B.PRIVILEGE (+) = A.PRIVILEGE'|| ' AND B.GRANTEE IS NULL'|| ' AND B.PRIVILEGE IS NULL'|| ' AND A.GRANTEE = '''||pUsername||''''|| ' AND A.ADMIN_OPTION = DECODE('''||pAdmin||''', ''ADMIN'', ''YES'', ''NO'')'; OPEN cSysprivs FOR vSysprivs_statement; LOOP FETCH cSysprivs INTO vSyspriv; EXIT WHEN cSysprivs%NOTFOUND; SELECT vSyspriv_list||DECODE(vSyspriv_list, NULL, NULL, ', ')||vSyspriv INTO vSyspriv_list FROM DUAL; END LOOP; SELECT DECODE( vSyspriv_list, NULL, NULL, 'GRANT '||vSyspriv_list||' TO '||pUsername||DECODE(pAdmin, 'ADMIN', ' WITH ADMIN OPTION', NULL)) INTO vSQL_statement FROM DUAL; ELSIF pEvent = 'REVOKE' THEN vSysprivs_statement := 'SELECT B.PRIVILEGE'|| ' FROM DBA_SYS_PRIVS A, DBA_SYS_PRIVS@'||pDatabase||' B'|| ' WHERE B.GRANTEE = A.GRANTEE (+)'|| ' AND B.PRIVILEGE = A.PRIVILEGE (+)'|| ' AND B.GRANTEE = '''||pUsername||''''|| ' AND B.ADMIN_OPTION = DECODE('''||pAdmin||''', ''ADMIN'', ''YES'', ''NO'')'|| ' AND A.GRANTEE IS NULL'|| ' AND A.PRIVILEGE IS NULL'|| ' UNION '|| 'SELECT A.PRIVILEGE'|| ' FROM DBA_SYS_PRIVS A, DBA_SYS_PRIVS@'||pDatabase||' B'|| ' WHERE B.GRANTEE = A.GRANTEE'|| ' AND B.PRIVILEGE = A.PRIVILEGE'|| ' AND B.ADMIN_OPTION <> A.ADMIN_OPTION'|| ' AND A.GRANTEE = '''||pUsername||''''; OPEN cSysprivs FOR vSysprivs_statement; LOOP FETCH cSysprivs INTO vSyspriv; EXIT WHEN cSysprivs%NOTFOUND; SELECT vSyspriv_list||DECODE(vSyspriv_list, NULL, NULL, ', ')||vSyspriv INTO vSyspriv_list FROM DUAL; END LOOP; SELECT DECODE(vSyspriv_list, NULL, NULL, 'REVOKE '||vSyspriv_list||' FROM '||pUsername) INTO vSQL_statement FROM DUAL; ELSE RAISE_APPLICATION_ERROR(-20101, 'ERROR: pEvent parameter value is invalid in procedure Get_Role_Privs_Stmt.'); END IF; ELSE RAISE_APPLICATION_ERROR(-20101, 'ERROR: pAdmin parameter value is invalid in procedure Get_Role_Privs_Stmt.'); END IF; RETURN (vSQL_statement); END; / 我必须排除一些类似"ADMINISTER RESOURCE MANAGER"、"DEQUEUE ANY QUEUE"、"ENQUEUE ANY QUEUE"和"MANAGE ANY QUEUE"的权限，它们需要用包来授权。 注意：我没有为这种情况修改我的函数（GET_SYS_PRIVS_STMT）。 这个REPLICATE_SQL_STATEMENT是被其他主过程调用的。 函数GET_SYS_PRIVS的返回值传递给REPLICATE_SQL_STATEMENT，后者在远程服务器执行。 我从我的函数获得如下的一个字符串： GRANT DROP ANY TABLE, EXECUTE ANY PROCEDURE, ... 直到115个权限 我刚测试过这个函数，它能运作，准确的返回我所有的115个权限。但问题出现在这个字符串送到远程服务器的EXECUTE IMMEDIATE语句时。 感谢任何建议。 真正的感谢您的帮助。 此致 回复： 嗯，不允许通过数据库链接（dblink）执行DDL——这和那有点关系。 你可以用远程站点的dbms_job来安排一个存储过程在你提交后执行——这样DDL变成了本地事务。 并且，你总是想包括错误信息和代码！ 另外，请使用绑定变量——在EXECUTE IMMEDIATE中使用绑定比不使用还容易！！ execute immediate 'begin procedure( :x ); end;' using some_string; Max string length in execute immediate March 05, 2003 评论者Ray White来自渥太华，安大略 抱歉，我要换个讨论方向，但是…… 你提到，execute immediate的最大字符串长度是32K。 我们在用execute immediate来创建包，并且我们现在通过execute immediate v_myvc1 || my_vc2，传递超过35000字符给它。vc1和vc2均是32K的varchar2变量，合计长度超过35000。 都是在8.1.7上。 我的问题是execute immediate的最大长度是多少，因为我担心是32K并且我们已经超过了它，我都不知道我什么时候会撞上这堵墙。 回复： 有意思——从来没考虑过这么做。 看起来可以——它会撞到墙么？不清楚，我不会超过32K的。 看起来它可以扩展到很大。 ops$tkyte@ORA817DEV> declare 2 l_str1 long := 'select /* ' || rpad( '*', 20000, '*' ) || ' */ * '; 3 l_str2 long := 'from /* ' || rpad( '*', 15000, '*' ) || ' */ dual'; 4 l_str3 long := '/* ' || rpad( '*', 32000, '*' ) || ' */ '; 5 l_result dual.dummy%type; 6 begin 7 execute immediate l_str1 || l_str2 || l_str3 || l_str3 || l_str3 || ' d' into l_result; 8 dbms_output.put_line( l_result ); 9 end; 10 / X PL/SQL procedure successfully completed. For no particular reason.... March 06, 2003 评论者：Mike Jones来自英格兰 Oracle8i Enterprise Edition Release 8.1.7.3.0——HP-UX，看起来可以接受一个143,799字符的SQL语句。 当运行手提电脑上的Personal Oracle9i Release 9.2.0.1.0——Win2K，只能到65,535字符。 我猜想这或者是一个依赖于平台的，或者9i中收缩到了一个合理的值（或者是一个个人版和企业版Oracle的区别，或者是UNIX/Win问题，或者……） 为什么有人要执行143K的SQL语句呢，这已经超过我的理解了…… 答： 我建议： o 将execute immediate保持在32K以下 o 用DBMS_SQL来解析任何超过32K的东西，文档里说它支持“通过一个数组，一次一行传给我一个任何长度的SQL语句，我来搞定”。 我会认为用execute immediate来执行超过32K不可靠。 Great November 19, 2003 评论者：Kalyan A Samaddar来自Chennai,印度 我以前只认为用EXECUTE IMMEDIATE和DBMS_SQL的区别只在于（字符串）大小。现在看来字符串长度不是EXECUTE IMMEDIATE的限制了。 那EXECUTE IMMEDIATE和DBMS_SQL之间有什么不同呢？ 回复： dbms_sql可以 a) 解析一次 b) 执行执行再执行 execute immediate不能这样，它每次解析/执行。 "passing a sql statement within an Array June 01, 2004 评论者：Keith Jamieson来自爱尔兰都柏林 你好Tom， 我有个如下使用dbms_sql的过程例子。我的insert语句很长，早超过了32K限制。 请给一个用数组传递SQL语句的例子。 CREATE OR REPLACE PROCEDURE DBMS_SQL_TEST AS l_cursor integer; l_status integer; l_statement varchar2(4000); l_min_emp_id integer := 0; l_increment integer := 5000; BEGIN l_statement := 'insert into emp(emp_id)' || 'select policy_id from emp ' || 'WHERE emp_id >= :BV1 ' || 'and emp_id < :BV2'; l_cursor := dbms_sql.open_cursor; dbms_sql.parse (l_cursor, l_statement, dbms_sql.native); while l_min_emp_id < 100000 + l_increment loop dbms_sql.bind_variable(l_cursor, ':bv1',l_min_emp_id); dbms_sql.bind_variable(l_cursor, ':bv2', l_min_emp_id + l_increment); l_status := dbms_sql.execute(l_cursor); l_min_emp_id := l_min_emp_id + l_increment; end loop; commit; END DBMS_SQL_TEST; 回复： 你的insert超过32K？你怎么做的？ 无论如何，填充一个数组，然后解析它（但问问自己，为什么……看起来不是很大么？） declare l_stmt dbms_sql.varchar2s; l_cursor integer default dbms_sql.open_cursor; l_rows number default 0; begin l_stmt(1) := 'create'; l_stmt(2) := 'table'; l_stmt(3) := 'foo ( x int primary key )'; dbms_sql.parse( c => l_cursor, statement => l_stmt, lb => l_stmt.first, ub => l_stmt.last, lfflg => TRUE, language_flag => dbms_sql.native ); l_rows := dbms_sql.execute(l_cursor); dbms_sql.close_cursor( l_cursor ); exception when others then if dbms_sql.is_open(l_cursor) then dbms_sql.close_cursor(l_cursor); end if; raise; end; / works in 8i not in 9i June 01, 2004 评论者：Ashiq Shamsudeen A来自Trichy, Tamil Nadu,印度 mars@MARS> sho rel release 801070000 mars@MARS> declare 2 l_str1 long := 'select /* ' || rpad( '*', 20000, '*' ) || ' */ * '; 3 l_str2 long := 'from /* ' || rpad( '*', 15000, '*' ) || ' */ dual'; 4 l_str3 long := '/* ' || rpad( '*', 32000, '*' ) || ' */ '; 5 l_result dual.dummy%type; 6 begin 7 execute immediate l_str1 || l_str2 || l_str3 || l_str3 || l_str3 || ' d' into l_result; 8 dbms_output.put_line( l_result ); 9 end; 10 / X PL/SQL procedure successfully completed. mars@MARS> 但在9i中不行。 scott@SHAM> sho rel release 902000100 scott@SHAM> declare 2 l_str1 long := 'select /* ' || rpad( '*', 20000, '*' ) || ' */ * '; 3 l_str2 long := 'from /* ' || rpad( '*', 15000, '*' ) || ' */ dual'; 4 l_str3 long := '/* ' || rpad( '*', 32000, '*' ) || ' */ '; 5 l_result dual.dummy%type; 6 begin 7 execute immediate l_str1 || l_str2 || l_str3 || l_str3 || l_str3 || ' d' into l_result; 8 dbms_output.put_line( l_result ); 9 end; 10 / declare * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 7 scott@SHAM> 什么问题？ 回复： 它本来就不应该运行，只是个偶然。PL/SQL中的字符串是32K。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 原文标题： Francisco -- Thanks for the question regarding "Max string allowed in EXECUTE IMMEDIATE.", version 8.1.7.4 原文地址： [url]http://asktom.oracle.com/pls/ask/f?p=4950:8:14414319588212740508::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6161200355268[/url] -------------------------------------------------------------------------------- 原文版权归属asktom.oracle.com所有，翻译 by ern•CNOUG 2005-12-26 11:37 12月19日 Ask Tom之中英对照20051219——未分析的分区表上CBO估计的基数Jack 未分析的分区表上CBO估计的基数 版本：9.2.0.7 原提交于美国东部时间2005年12月18日18:05，最后更新于2005年12月18日19:33 问： Tom， 我做了如下操作。 这里的“create table”来自JL的CBO书（应该是指Cost-Based Oracle Fundamentals——译注）。 create table t1 ( part_col not null, id not null, small_vc, padding ) partition by range(part_col) ( partition p0200 values less than ( 200), partition p0400 values less than ( 400), partition p0600 values less than ( 600), partition p0800 values less than ( 800), partition p1000 values less than (1000) ) nologging as with generator as ( select --+ materialize rownum id from all_objects where rownum <= 5000 ) select trunc(sqrt(rownum-1)), rownum-1, lpad(rownum-1,10), rpad('x',50) from generator v1, generator v2 where rownum <= 1000000 / create index t1_idx1 on t1(id) local / 注意：我在运行下面语句前没有收集任何统计信息。 set autotrace trace exp select * from t1 where id=160010 / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=8598 Bytes=515880) 1 0 PARTITION RANGE (ALL) 2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T1' (Cost=7 Card=8598 Bytes=515880) 3 2 INDEX (RANGE SCAN) OF 'T1_IDX1' (NON-UNIQUE) (Cost=6 Card=3439) 我知道Oracle会为分区表使用CBO，但我不明白Oracle在这里是怎么得到基数（cardinality）的。 optimizer related init setting: ================================ optimizer_dynamic_sampling integer 1 optimizer_features_enable string 9.2.0 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_max_permutations integer 2000 optimizer_mode string CHOOSE 答： 块数*（减去头开销的块大小）/平均行长度 平均行长度=100 （Number of blocks * (block-size-minus-overhead)/average_row_length default avg_row_length = 100） Now, we know how many blocks are used by the table (segment information used by full scan would reveal that - it has to know how many blocks to scan...) 现在，我们知道表用了多少块（全扫描使用的段——segment——信息将显示这一点，它必须知道要扫描多少块……） 所以，如果我们只是“全扫描”表： Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1014 Card=859848 Bytes=51590880) 1 0 PARTITION RANGE (ALL) 2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=1014 Card=859848 Bytes=51590880) 那么，它认为有859,848行——如果认为每个块是大约8168字节…… 然后取1%（一个索引/表中不同的键值数——即有多少个不同的列取值 译注——缺省为100……） http://download-west.oracle.com/ ... 533/stats.htm#25056 这就是估计的基数了。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 原文标题： Jack -- Thanks for the question regarding "Cardinality estimated by CBO on partition table without statistics", version 9.2.0.7 原文地址： http://asktom.oracle.com/pls/ask ... ERIA:53495695721103 -------------------------------------------------------------------------------- 原文版权归属asktom.oracle.com所有，翻译 by ern•CNOUG 2005-12-19 14:17 12月15日 Ask Tom 之中英对照20051215——Oracle是如何判断锁的Venkat Oracle是如何判断锁的？ 版本9.2.0.5 原提交于美国东部时间2005年12月12日11:19，最后更新于2005年12月12日16:22 问： Tom, 在你的书和帖子里，你写到Oracle和其他数据库最大的区别之一就是锁机制。如果Oracle不维护一个锁的列表（如同大多数其他数据库一样），Oracle是如何知道什么当前被锁定了呢。V$LOCK是从哪里得到信息的？Oracle是查看每一个数据块来查到当时在数据库中的锁么？ 谢谢 答： v$lock显示某事务在指定表的零行（即没有行锁——译注）、一行或更多行上有锁。 如果我锁了那个表上的1,000行或1行或10,000,000，v$lock并不为每一行显示一条记录，它里面只有一行，说明我持有“某些东西”，但没有说是什么…… 要找到我锁定的那些行，那得去那些行——锁是数据本身的属性。 （在我的书和其他贴子里我已经进行了详细的讲解……一下引自Expert One on One Oracle和Expert Oracle: Database Architecture） <quote> TX (Transaction) Locks TX（事务）锁 当一个事务实例化它的第一个修改，就获得了一个TX锁，并保持到执行COMMIT或ROLLBACK。这是一个排队机制，其他会话将等待该事务完成。你在事务中修改的每一行或SELECT FOR UPDATE都将“指向”该事务相关的一个TX锁。要理解为什么，你需要概念上理解锁“存在”在哪里，是如何管理它们的。Oracle中锁是作为数据的属性保存的（阅读第10章Oracle]块格式概述）。Oracle中没有一个传统的锁管理器来维护系统中锁住的每一行信息的长列表。许多其他数据库这样做，是因为对它们而言锁是一个稀有资源，需要监控其使用。用的锁越多，系统需要管理的也越多。所以在这些系统里是否用了“过多”的锁是一个需要考虑的问题。 在一个使用传统基于内存的锁管理器的数据库中，锁定行的流程类似如下： 1. 查找你需要锁定行地址 2. 获取对锁管理器的访问（因为它是一个普通内存结构，这是需要串行化的） 3. 锁定列表 4. 搜索列表查看是否已有其他人锁定该行 5. 在列表中创建新记录以确定你已经锁定该行 6. 解锁列表 现在你锁住了这行，可以修改了。之后，当你提交你的修改，需要继续如下的流程： 7. 再次访问锁管理器 8. 锁住锁的列表 9. 搜索列表，释放你所有的锁 10.解锁列表 正如你看到的，获得的锁越多，修改数据之前和之后的操作所需时间越多。Oracle不这样做。Oracle的流程类似如下： 1. 查找你需要锁定行地址 2. 访问行 3. 锁定行（如果已经被锁，则等待锁定它的事务结束，除非你使用了NOWAIT选项） 完成了。由于锁作为数据属性存放，Oracle不需要一个传统的锁管理器。事务只是简单的访问数据并锁定它（如果它还没被锁定）。有趣的是，当你访问时，数据即便没有被锁，可能看起来也像被锁了。当你在Oracle中锁住了多行数据，该行指向一个与含有该数据的块一起存放的事务ID，当锁被释放，事务ID还被保留着。这个事务ID对你的事务是w唯一的，代表了回滚段号（rollback segment number）、槽（slot）和序号（sequence number）。你将（事务ID——译注）留在含有那些行的块中来告知其他回话，你“拥有”这些数据（不是块中所有数据，只是你修改了的行）。当出现另一个会话，它可以迅速查看拥有锁的会话是否仍然活动。如果锁不再活动（active），这个会话获准访问数据。如果锁仍然活动，在锁被释放后将立即告知这个会话。这样你得到了一个排队机制：申请锁的会话将排队等待（持有锁的——译注）那个会话完成，然后获得数据。 </quote> -------------------------------------------------------------------------------- 评论： Followup to How does Oracle determine locks December 12, 2005 评论者：Venkat Thanks for the answer, Tom. I do understand that locks are stored along with the rows. What I still don't get is HOW does Oracle know what locks there are, if a list is not maintained. Are you saying that Oracle maintains "something" that tells it there are 1 or more locks which it can find by going to the rows? So there may be 10,000 rows locked but it is indicated by a single entry somewhere? 谢谢你的回答，Tom。我知道锁是和行存放在一起的。我仍然不明白的是，如果不维护一张列表，Oracle怎么知道有哪些锁。你是说Oracle维护“某些东西”，（这些东西——译注）可以通过访问这些行来告诉你有一个或更多的锁？所以可能锁了10,000行，但只用一个记录来表示。 回复： Oracle不需要知道有什么锁。为什么要（知道——译注）呢？ 它所知道的是，“事务A修改了表T，所以在事务A提交/回滚前，在V$LOCK中有一个TX锁说明这个事实”。 它不知道那些行被锁了，它不关心。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 原文标题： Venkat -- Thanks for the question regarding "How does Oracle determine locks", version 9.2.0.5 原文地址： http://asktom.oracle.com/pls/ask/f?p=4950:8:15574295566872171581::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:53140949357315 -------------------------------------------------------------------------------- 原文版权归属asktom.oracle.com所有，翻译 by ern·CNOUG 2005-12-15 11:24:00 12月13日 Ask Tom 之中英对照20051213——使用rowid定位行是否可靠Alexander 使用rowid定位行是否可靠 版本9.2.0 原提交于美国东部时间2005年12月12日11:15，最新更新于2005年12月12日16:13 问： 你好Tom， 我在看一个客户端应用，它需要取包括ROWID在内的若干列，然后用ROWID来定位需要更新的行。 update some_table t set col1=value1 where t.rowid = :selected_rowid 这样做安全么？如果表被修改过，一行的ROWID会改变么？ 我做了一些研究： create table a(i number, c varchar2(250)); insert into a select some_numeric_column, 'a' from table_500K_rows; commit; select rowid r,i,c from a where rownum<100; 然后我修改几行： update a set c=';asdfha sdfhaj sdhf asdhlf asldfh ajlskdfh jklasdh fjasdhf jlaksh dfjlashdfjashdfa sdjklfhalsdfhajlsdhflashdflasdhflasd' where i<200; commit; 然后重复如下的select： select rowid r,i,c from a where rownum<100; 我得到了不同的行，所以我猜想我将c更新为一个大值时分割了页，但rowid保留未变： select rowid r,i,c from a where i<100 返回了相同的行和相同的rowid。ROWID会改变么？如果是，我如何简单的再现？ 谢谢你。 答： 一个rowid在插入时分配给一行，除非该行被删除并重新插入（就是说它是另一行，不再是原来那行了），rowid会保持不变。 但是如果DBA或表的所有者设置了表的"enable row movement"属性，行可能被多个命令“透明的”删除+插入。 就是说， 如果你更新分区关键字会导致行从一个分区转移到另一个分区，分区表中的行的rowid会改变。 如果你使用了alter table t shrink space compact，rowid会改变 如果你使用了alter table t move，rowid会改变 如果你使用了flashback table t to ....，rowid会改变 但是如果你将rowid和主键一起使用，那在任何情况下都是十分安全的： update t set... where rowid = :x and primary_key = :pk; 我们可以使用rowid来找到行（速度快），认为它还在那里并更新它。注意，你还是需要考虑结合对更新丢失的保护！！！你不能只是用rowid和主键来修改——除非你在读出行时锁住它，否则你将覆盖其他用户的更新。 -------------------------------------------------------------------------------- 评论 Single update statement? December 12, 2005 评论者：Greg来自多伦多 Tom，对于单一一条update语句这也是个问题么？ 例如： Update some_table set value = something where rowid in ( select rowid from some_table where some_critieria); 仅一条语句？Rollback/Undo将为我们保留记录，是么？这里rowid是“安全”的吧。 回复： in this case rowid is "safe" but perhaps not "efficient" 这里rowid是“安全”的，但可能不是“高效”的。 update t set value = something where some_criteria; 除非子查询有分析（analytics）或类似的东西…… What about page splits? December 12, 2005 评论者：Alexander来自芝加哥 Tom， 谢谢你的解释。 当我更新窄行，将c设为更宽的值时，发生了哪些（操作）？有页分割么？如果页被分割了，ROWID没有改变，那ROWID内在含义究竟是什么？ 回复： 行迁移。 在本站搜索"row migration"，或者看看Expert one on one Oracle或Oracle: Database Architecture，我详细的进行了讲解。 即便行迁移了，ROWID是不变的。 Different row, same rowid December 12, 2005 评论者：一个读者来自加州圣弗朗西斯科 ROWID在如下情况中也是不可靠的。 会话1: 获取一行和rowid X 会话2：删除rowid X的行，提交 /* rowid X 此时可以自由重用 */ 会话3: 插入rowid为X的新行，提交 会话1: update .... where rowid = X 会话1的更新不是更新早前取出的同一行了。 回复： 这就是为什么我上面提到需要主键。 .... 但是如果你将rowid和主键一起使用，那在任何情况下都是十分安全的 ..... -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 原文标题： Alexander -- Thanks for the question regarding "Is it safe to use ROWID to locate a row?", version 9.2.0 原文地址： http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:53140678334596 -------------------------------------------------------------------------------- 原文版权归属asktom.oracle.com所有，翻译 by ern·CNOUG 2005-12-13 09:49:00 后记：自即日起为保证文章不要过长，将不再同时附上原全文，将附注原文标题与地址，可以自行察看原文。 Enjoy your Reading 12月12日 Ask Tom 之中英对照20051211——ORA-00604什么含义ORA-00604什么含义 版本9.0.1.0 原提交于美国东部时间2002年5月24日8:09，最新更新于2005年12月10日5:24 问: 你好， 我有如下问题： SQL> select * from t; select * from t * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04092: cannot COMMIT in a trigger ORA-06512: at line 12 ORA-00942: table or view does not exist 并且表t不存在。但是无论我用什么用户、干什么，都会产生这个错误（可以认为产生这个错误是个常规）。 我在找这个可能执行提交的触发器，但是我们数据库里任何一个方案里都没有。 这个ORA-00604是什么意思？和递归SQL查询有关系么？这是一个内部问题么？我如何修复？ 先谢谢了， Piotr 答： 有人创建了"after servererror"触发器，并在其中提交。考虑： ops$tkyte@ORA9I.WORLD> drop user a cascade; User dropped. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> grant create session to a identified by a; Grant succeeded. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> @connect a/a ops$tkyte@ORA9I.WORLD> set termout off a@ORA9I.WORLD> REM GET afiedt.buf NOLIST a@ORA9I.WORLD> set termout on a@ORA9I.WORLD> select * from t; select * from t * ERROR at line 1: ORA-00942: table or view does not exist a@ORA9I.WORLD> a@ORA9I.WORLD> @connect / a@ORA9I.WORLD> set termout off ops$tkyte@ORA9I.WORLD> REM GET afiedt.buf NOLIST ops$tkyte@ORA9I.WORLD> set termout on ops$tkyte@ORA9I.WORLD> drop table log; Table dropped. ops$tkyte@ORA9I.WORLD> create table log( msg varchar2(255) ); Table created. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> create or replace trigger error_trigger 2 after servererror on database 3 begin 4 insert into log values ( 'hi' ); 5 commit; 6 end; 7 / Trigger created. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> @connect a/a ops$tkyte@ORA9I.WORLD> set termout off a@ORA9I.WORLD> REM GET afiedt.buf NOLIST a@ORA9I.WORLD> set termout on a@ORA9I.WORLD> select * from t; select * from t * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04092: cannot COMMIT in a trigger ORA-06512: at line 3 ORA-00942: table or view does not exist a@ORA9I.WORLD> a@ORA9I.WORLD> @connect / a@ORA9I.WORLD> set termout off ops$tkyte@ORA9I.WORLD> REM GET afiedt.buf NOLIST ops$tkyte@ORA9I.WORLD> set termout on ops$tkyte@ORA9I.WORLD> select owner, trigger_name 2 from dba_triggers 3 where trigger_type = 'AFTER EVENT'; OWNER TRIGGER_NAME ------------------------------ ------------------------------ SYS AURORA$SERVER$STARTUP OPS$TKYTE ERROR_TRIGGER ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> drop trigger error_trigger; Trigger dropped. 使用最后这个查询来找那个出错的触发器，然后或者将它放到一个自治事务中以便提交或者删掉那个提交。 评论 It just takes to know right things May 24, 2002 评论者：Piotr Jarmuz来自德国Braunschweig 现在真相大白了。我知道了控制点的流程（？）。事实上我知道系统级触发器，但不太懂“after servererror”，所以我无法将它和这个（出错）信息联系起来。我用了一个自治事务，现在工作正常了。我也追踪到了那个建立这个触发器的“嫌犯”。 再次表示感谢。 can commit cause problem in Logoff trigger July 09, 2002 评论者：Dilip来自纽约州 在“before logoff”触发器，我这样更新一个标记： ……登出数据库前…… If <condition> THEN Update table set col = releaseflag ; ==> COMMIT ; End if ; …… 这个触发器里的提交会导致数据库关闭么？ 谢谢！ DilipM 回复： 应该不会。 What about Forms 6i January 03, 2003 评论者：Luis Soto来自多米尼加共和国 在Form 6i每次我关闭并重启Win98第二版上的Oracle 8.1.5个人版，Forms会在我试图提交修改时报这个错误（ORA-00604——译注）。我在SQL*Plus里测试就正常。如果我重新编译这个form又恢复正常。让我受不了的是只有这个form有问题，而其他都正常。 任何帮助都令人感激 Luis Soto superb answer for after servererror May 07, 2003 评论者：jignesh来自印度 你只是gr8 ORA-00604 while compiling a procedure January 22, 2004 评论者：Mariano来自阿根廷Córdoba 你好Tom， 在试图编译一个存储过程时报如下错误： ORA-00604: error occurred al recursive sql level 1 ORA-01653: unable to extend table SYS.IDL_UB2$ by 25 in tablespace SYSTEM. 我不能查询这个表/视图。 我如何知道我的代码里哪里出发了这个递归（我指一个方向性的指导，我明白不看看代码是不可能确定的），或者还是得问问我的DBA，让他检查一下表空间来确定是否分区（extent，原文为extend，疑为笔误——译注）问题？ 顺便提一下：我没有在存储过程内部调用它自身代码，我调用了另一个存储过程却得到这个错误。 还是先谢谢你 回复： 联系你的DBA。 系统满了。idb_ub2$是一个"byte code"表，DBA需要增加空间。 ORA-00604 while dropping table June 06, 2004 评论者：一个读者 Tom，我碰到如下错误： drop table t_emp_varray; drop table t_emp_varray * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04063: table "USER1.T_EMP_VARRAY" has errors 有什么主意？有什么办法强制删掉这张表么？ 谢谢！ 回复： 给我一点信息——你如何碰到这个情况的？ thanx Tom! June 07, 2004 评论者：一个读者 我在执行一个脚本——我应该是做了什么，然后就开始出现这个情况了。 这是10gR1。我预感它和删除类型的“force”选项有关系。 这是脚本： scott@ORA10G> /* scott@ORA10G> drop objects. scott@ORA10G> */ scott@ORA10G> drop table t_emp_varray; drop table t_emp_varray * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04063: table "BENCHMARK.T_EMP_VARRAY" has errors scott@ORA10G> drop type emp_type force; Type dropped. scott@ORA10G> drop type emp_type_varray force; Type dropped. scott@ORA10G> scott@ORA10G> /* create an object type - emp_type */ scott@ORA10G> create or replace type emp_type as object 2 ( 3 name varchar2(30), 4 hire_date date 5 ); 6 / 回复： 整个例子是怎么样的？我想不出办法来重现（这个错误）。 July 16, 2004 评论者：Annie来自印度 我们遇到一个类似问题。当数据通过Forms 6i数据并保存，错误"FRM-40508-ORACLE error unable to insert record"就出现了。察看错误，错误中的SQL语句类似： insert into cust_rect values(:1,:2) Error: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist. 但表cust_rect是存在的。 请帮忙。 回复： 但应该对当时登录用户来说不存在。 Error Dropping User August 24, 2005 评论者：Thiyagu来自印度Chennai Tom： 我使用这个指令删除用户： drop user xyz cascade; 然后提示一个错误： ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist 我用了你之前评论中的查询( select owner, trigger_name from dba_triggers where trigger_type = 'AFTER EVENT';) 没有返回任何结果。我已经删除了所有的after event触发器。错误仍然存在。请你评论一下。 回复： 打开sql_trace=true，然后看看跟踪文件里哪个语句失败。 Please ignore my earlier comment August 24, 2005 评论者：Thiyagu来自印度Chennai 我从9.2.0.1升级到9.2.0.5，没有完成安装后的升级任务。我执行升级后任务后一切正常了。 December 09, 2005 评论者Raghav来自印度 你好Tom 第一个案例：我试图用一个用户的用户名和密码登录数据库时得到如下错误： ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 78 ORA-06512: at "CPMS.APP_SECURITY_CONTEXT", line 20 ORA-06512: at line 2 我已经授予用户create session, connect, resource权限。但还是不能连接。 第二个案例：一个用户试图刷新立方体（cubes）时（共有10个立方体），一个立方体报下面的错误。这个用户可以登陆并刷新其他九个立方体。 ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error ORA-06512: at "CPMS.APP_SECURITY_CONTEXT", line 17 ORA-06512: at line 2 请你给点线索吧。 谢谢 Raghav 回复： 案例1：和用户没有关系 这是说你在存储过程app_security_context的第20行试图设置的上下文（context）没有正常的建立起来以便app_security_context设置它。每个运行app_security_context的用户均会收到同样的错误。 案例2：所给信息不足以作出分析评论。 Debugging Cancellation Error December 09, 2005 评论者：Smita来自印度 你好Tom， 当我在TOAD中取消一个调试会话（存储过程、包等等）时遇到如下错误： ORA-00604 ORA-01422 ORA-06512 ORA-O6543 请帮助。 回复： 可能我会给TOAD打个电话然后问他们“那么，出什么事了？？？”。没法多说什么——我自己没有TOAD，也从来没用过它。 numeric or value error December 10, 2005 评论者Raghav来自印度 你好TOM 我们有一个称为CPMS的系统。它将数据以立方体的形式提取到EXCEL中来进行分析。当用户用自己的编号（数据库）和密码刷新报表时，数据/立方体在他的电脑上刷新。这些用户（数据库）有cpms_reports角色，使得他们可以通过用户编号和密码来登陆CPMS访问这些报表。所有其他用户均可刷新所有立方体，这个特定用户和这个特定立方体就会产生这个错误。他可以刷新其他九个立方体。 ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error ORA-06512: at "CPMS.APP_SECURITY_CONTEXT", line 17 ORA-06512: at line 2 希望这些信息可以让你明白并分析这个问题，给我一个解决方案。 谢谢。此致 Raghav 回复： 我估计你用字符串来存储了不能转换为数值的数据，并且只有这个用户使用这个数据，其他用户不使用这个出了问题的数据。 还是没有足够的信息来分析。就像我进了一个汽车修理店，然后说： 我有部车。我每天开着它以65英里每小时在101高速公路上跑两个小时。我转右它转左。我停下来了，现在这车启动不了。为什么？ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 原文： Piotr -- Thanks for the question regarding "ORA-00604 - what does it mean", version 9.0.1.0.0 originally submitted on 24-May-2002 8:09 Eastern US time, last updated 10-Dec-2005 5:24 You Asked (Jump to Tom's latest followup) Hi, I have the following problem: SQL> select * from t; select * from t * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04092: cannot COMMIT in a trigger ORA-06512: at line 12 ORA-00942: table or view does not exist And table `t' does not exist. But this happens no matter what user I am, no matter what I do but only if there are errors (assuming that it would normally give only the last error). I was looking for the trigger that might do commit but there is no single one in any schemas in our database. What does this ORA-00604 mean? Does it have something to do with recursive SQL queries? Is this some kind of internal problem? How can I get rid of it? Thanx in advance, Piotr and we said... someone created an "after servererror" trigger and is committing in it. consider: ops$tkyte@ORA9I.WORLD> drop user a cascade; User dropped. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> grant create session to a identified by a; Grant succeeded. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> @connect a/a ops$tkyte@ORA9I.WORLD> set termout off a@ORA9I.WORLD> REM GET afiedt.buf NOLIST a@ORA9I.WORLD> set termout on a@ORA9I.WORLD> select * from t; select * from t * ERROR at line 1: ORA-00942: table or view does not exist a@ORA9I.WORLD> a@ORA9I.WORLD> @connect / a@ORA9I.WORLD> set termout off ops$tkyte@ORA9I.WORLD> REM GET afiedt.buf NOLIST ops$tkyte@ORA9I.WORLD> set termout on ops$tkyte@ORA9I.WORLD> drop table log; Table dropped. ops$tkyte@ORA9I.WORLD> create table log( msg varchar2(255) ); Table created. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> create or replace trigger error_trigger 2 after servererror on database 3 begin 4 insert into log values ( 'hi' ); 5 commit; 6 end; 7 / Trigger created. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> @connect a/a ops$tkyte@ORA9I.WORLD> set termout off a@ORA9I.WORLD> REM GET afiedt.buf NOLIST a@ORA9I.WORLD> set termout on a@ORA9I.WORLD> select * from t; select * from t * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04092: cannot COMMIT in a trigger ORA-06512: at line 3 ORA-00942: table or view does not exist a@ORA9I.WORLD> a@ORA9I.WORLD> @connect / a@ORA9I.WORLD> set termout off ops$tkyte@ORA9I.WORLD> REM GET afiedt.buf NOLIST ops$tkyte@ORA9I.WORLD> set termout on ops$tkyte@ORA9I.WORLD> select owner, trigger_name 2 from dba_triggers 3 where trigger_type = 'AFTER EVENT'; OWNER TRIGGER_NAME ------------------------------ ------------------------------ SYS AURORA$SERVER$STARTUP OPS$TKYTE ERROR_TRIGGER ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> drop trigger error_trigger; Trigger dropped. Use that last query to find the offending trigger and either make it an autonomous transaction so it can commit or remove the commit. Reviews It just takes to know right things May 24, 2002 Reviewer: Piotr Jarmuz from Braunschweig, Germany Now all is clear. I understand the flow of locus of control. Actually I knew about system level triggers but not about `after servererror' so actually I could not associate it with this message. I used an autonomous transaction and now it all works well. And I also traced down the culprit who or rather what set this trigger up. Thank you again. can commit cause problem in Logoff trigger July 09, 2002 Reviewer: Dilip from NY In before logoff trigger, I am updating flag like this: ..before logoff on database.. If <condition> THEN Update table set col = releaseflag ; ==> COMMIT ; End if ; .. Can Commit in this trigger cause the database to shutdown? Thanks! DilipM Followup: it should not. What about Forms 6i January 03, 2003 Reviewer: Luis Soto from Dominican Republic In forms 6i every time I shutdown and restart Personal Oracle 8.1.5 in Win98Se Forms give me this error when I try to commit my chages. I tried it in SQL*Plus and it Works well. If I compile the form again it runs well. What drive me crazy is that it happen just with one form the others works well. Any Help Will be really apreciate Luis Soto superb answer for after servererror May 07, 2003 Reviewer: jignesh from india u r simply gr8 ORA-00604 while compiling a procedure January 22, 2004 Reviewer: Mariano from Córdoba, Argentina Hi Tom, while trying to compile a procedure it's raising this error: ORA-00604: error occurred al recursive sql level 1 ORA-01653: unable to extend table SYS.IDL_UB2$ by 25 in tablespace SYSTEM. I cannot query this table/view. how can i now if something in my code is causing the recursion (I mean a guide in this matter as i'm aware that without taking a look in the code it's imposible to know for sure) or it's necesary to ask my DBA to check tablespace system in order verify extends? Just to mention: i'm not calling the procedure inside its own code but i'm calling another procedure with the same error. As usual, thanks in advance. Followup: contact your dba. system is full, idb_ub2$ is a "byte code" table. dba needs to add space ORA-00604 while dropping table June 06, 2004 Reviewer: A reader Tom I am getting the following error.. drop table t_emp_varray; drop table t_emp_varray * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04063: table "USER1.T_EMP_VARRAY" has errors Any ideas? Is there a way to "force" the dropping of the table? thanx! Followup: give me a little "info" -- how did you get into this condition? thanx Tom! June 07, 2004 Reviewer: A reader I had been executing a script - I must have done something and this condition started showing up this is 10gr1. My hunch is it has to do with the "force" option in dropping the type. Following is the script.. scott@ORA10G> /* scott@ORA10G> drop objects. scott@ORA10G> */ scott@ORA10G> drop table t_emp_varray; drop table t_emp_varray * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04063: table "BENCHMARK.T_EMP_VARRAY" has errors scott@ORA10G> drop type emp_type force; Type dropped. scott@ORA10G> drop type emp_type_varray force; Type dropped. scott@ORA10G> scott@ORA10G> /* create an object type - emp_type */ scott@ORA10G> create or replace type emp_type as object 2 ( 3 name varchar2(30), 4 hire_date date 5 ); 6 / Followup: hows about the entire example? I cannot think of anyway to reproduce. July 16, 2004 Reviewer: Annie from India We're facing a similar problem. When data is entered through Forms 6i and saved, "FRM-40508-ORACLE error unable to insert record" occurs. When the error is viewed, The SQL statement in error is something like insert into cust_rect values(:1,:2) Error: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist. But the table cust_rect exists. Please help. Followup: must not exist for the currently logged in user then. Error Dropping User August 24, 2005 Reviewer: Thiyagu from Chennai, India Tom: I am trying to drop a user using the command: drop user xyz cascade; and this gives me an error ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist I used the query you had supplied in the earlier comment ( select owner, trigger_name from dba_triggers where trigger_type = 'AFTER EVENT';) which does not return me any results. I had deleted all the after event triggers. Still the error seem to exist. Can you please comment. Followup: turn on sql_trace=true and see what statement is failing in the trace file Please ignore my earlier comment August 24, 2005 Reviewer: Thiyagu from Chennai, India I have upgraded the database from 9.2.0.1 to 9.2.0.5 and did not complete the upgrade with the Post Installation tasks. After I execute the post installation task all worked fine. December 09, 2005 Reviewer: Raghav from India Hi Tom First case: For one user when I am trying to connect to the database through his username and password, I am getting the following error. ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 78 ORA-06512: at "CPMS.APP_SECURITY_CONTEXT", line 20 ORA-06512: at line 2 I have granted that user with create session, connect, resource. But still not able to connect. Second Case: For a particular user when he is trying to refresh the cubes (there are 10 cubes) at one cube this particular error was coming. whereas he is able to connect to the database through his login and as well he is able to refresh rest 9 cubes. ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error ORA-06512: at "CPMS.APP_SECURITY_CONTEXT", line 17 ORA-06512: at line 2 Request you to please give some light on these issues. Thanks and Regards Raghav Followup: case 1: has NOTHING to do with the user this means that the context you are trying to set on line 20 of the app_security_context procedure was not created in a way that allows app_security_context to set it. EVERY user that runs app_security_context will recieve this same error. case 2: insufficient data to even begin to comment. Debugging Cancellation Error December 09, 2005 Reviewer: Smita from India Hi Tom, I get the following errors when I cancel a debugging session(procedures, packages, etc) in toad. ORA-00604 ORA-01422 ORA-06512 ORA-O6543 Please help. Followup: guess I'd give toad a call and ask them "so, whats up with that?"??? not sure what else to say - given I don't have toad myself and have never used it. numeric or value error December 10, 2005 Reviewer: Raghav from India Hi Tom We have a system called cpms in which the data is retrieved into excel in the form of cubes for analysis purpose. The data/cubes are refreshed in ones pc when he refreshed the reports with his userid (database) and password. These users (database) are given the cpms_reports 'role' permission, whcih allows the them to connect to cpms through their userid and password to access these reports. While all the other users are able to refresh all the cubes, one particular user particularly in one cube is getting this error. He was able to refresh the other 9 cubes. ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error ORA-06512: at "CPMS.APP_SECURITY_CONTEXT", line 17 ORA-06512: at line 2 Hope this information will give you some idea to understand and to analyse the problem and give me a solution. Thanks and regards Raghav Followup: I guess you have data in strings that cannot be converted to a number and only this user it hitting that data, others are not using that particular bad piece of data. still entirely insufficient data to diagnose anything. Very similar to me going into a car repair shop and saying: I have a car that I drive on highway 101 at 65mph for 2 hours every day. I turn right then I turn left. I park the car. Now, the car won't start. why not? -------------------------------------------------------------------------------- 原文版权归属asktom.oracle.com所有，翻译 by ern·CNOUG 2005-12-11 23:59:00 12月8日 Ask Tom 之中英对照20051208——几条SQL的优化parag j patankar SQL查询优化——版本9.2 原提交于美国东部时间2005年10月26日3:22，最新更新于2005年12月8日6:58 问： 你好Tom， 假设我有如下的SQL查询 UPDATE TB38O SET G000KE = (SELECT TB39G.A6000D FROM TB39G WHERE TB39G.A0020C = TB38O.A020KF AND TB39G.A0090C = TB38O.A090KF AND TB39G.A1010Y = TB38O.B010KF AND TB39G.A6000Y = TB38O.G000KF AND TB39G.A0230Z = TB38O.A230KF AND TB39G.A9490C = TB38O.J490KE AND rownum < 2 ) WHERE G000KE='999' AND EXISTS (SELECT '1' FROM TB39G WHERE TB39G.A0020C = TB38O.A020KF AND TB39G.A0090C = TB38O.A090KF AND TB39G.A1010Y = TB38O.B010KF AND TB39G.A6000Y = TB38O.G000KF AND TB39G.A0230Z = TB38O.A230KF AND TB39G.A9490C = TB38O.J490KE) ; 在这个查询中TB39G和TB380无法建立主键。 在这类查询中能否避免对set和where子句中的表三次FTS（一次TB380和两次TB39G）而是对每张表仅一次FTS呢。 此致感谢 pjp 答： 这是个非常非常非常可怕的内容。 "rownum < 2"，那么基本上你是用TB39G中关联上的任意一条记录来更新G000KE——如果有两条或更多记录，那么你不能控制使用哪条。 这称为“不确定性”，在两个不同的数据库里使用相同的数据，你将得到不同的最终结果。 对我来说这非常非常可怕。 不能建立键，因为你不能更新连接，优化会比较困难。你很可能得到多于三次的全表扫描（可能对于TB380中要更新的每条记录都要全扫描一遍TB39G）。 你可以这样来避免部分工作： UPDATE TB38O SET G000KE = nvl( (SELECT TB39G.A6000D FROM TB39G WHERE TB39G.A0020C = TB38O.A020KF AND TB39G.A0090C = TB38O.A090KF AND TB39G.A1010Y = TB38O.B010KF AND TB39G.A6000Y = TB38O.G000KF AND TB39G.A0230Z = TB38O.A230KF AND TB39G.A9490C = TB38O.J490KE AND rownum < 2 ), '999' ) WHERE G000KE='999' / 但那个rownum < 2——真的非常非常可怕。 如果列事实上是唯一的，并且你建立适当的唯一/主键约束（从而根本不需要使用rownum），无论怎样你都可以得到最高效的方法来更新连接。 评论： 评论者: Parag J Patankar来自印度 你好Tom， Thanks for your excellent answer as usual. Will you pl explain in more your quote of "That is called "non-deterministic", using the same data in two different databases - you'll get two different end results." if possible with example ? 谢谢你精彩的回答。如果可以，能否请你用这个例子进一步解释一下“这称为‘不确定性’，在两个不同的数据库里使用相同的数据，你将得到不同的最终结果”？ 我想这里的不同数据库都是指Oracle。 此致感谢 pjp 答： ops$tkyte@ORA10GR2> create table t1 ( x int, y int ); ops$tkyte@ORA10GR2> create table t2 ( x int, y int ); ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> insert into t1 values ( 1, 1 ); ops$tkyte@ORA10GR2> insert into t1 values ( 1, 2 ); ops$tkyte@ORA10GR2> insert into t2 values ( 1, 2 ); ops$tkyte@ORA10GR2> insert into t2 values ( 1, 1 ); ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> select * from t1 minus select * from t2; ops$tkyte@ORA10GR2> select * from t2 minus select * from t1; ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> select * from t1 where x = 1 and rownum < 2; X Y ---------- ---------- 1 1 ops$tkyte@ORA10GR2> select * from t2 where x = 1 and rownum < 2; X Y ---------- ---------- 1 2 t1和t2——一样的数据 where x = 1 and rownum < 2——不同的结果. 这不是确定的，相同的数据，不同的行顺序，不同的答案。 让我脊柱发凉…… 评论者：Anil Pant 你好， 我们有一个维护客户详细信息的应用。一个屏幕有一个搜索选项，用户可以搜索任意或所有的10-15列（客户ID、客户名、地址行1、地址行2、城市、邮编等等）。如果用户想搜索客户名或地址行1或地址行2或邮编，需要很长的时间。 我们使用基于代价的优化器，并且更新了统计信息。我不得不给他们解决方案来最优化这个查询。 客户ID是主键，有些列上也有索引。 给我的一个建议方案是如果用户要搜索任何一个无索引列，如地址行1或地址行2或城市，试图命中那个主键列或主键表。我不知道如何写这样的查询。 我知道这是一个非常模糊的问题，但还是期待您的意见。 我们使用Oracle 9i。 谢谢 Anil Pant 答： 这太模糊了。 我不明白你说的“给我的一个建议方案是如果用户要搜索任何一个无索引列，如地址行1或地址行2或城市，试图命中那个主键列或主键表”是什么意思。 看起来好像涉及到“多张表”——我都不知道这个方案（schema）可能是什么样子，所以回答这样的问题有点“难”。 评论者：Parag J Patankar来自印度 你好Tom， 我有如下的一个用来计算两个工作日的查询： select tomdate, spotdate from ( select dt tomdate, dt+1 spotdate from ( select sysdate+rownum dt from all_objects where rownum <= 25 ) where to_char(dt, 'DY') not in ('SAT', 'SUN') and to_char(dt+1, 'DY') not in ('SAT', 'SUN') and to_char(dt, 'RRRRMMDD') not in ( select trunc(dt) from holiday ) and to_char(dt+1, 'RRRRMMDD') not in ( select trunc(dt) from holiday ) where rownum = 1 / 我只是想避免对“holiday”表的两次扫描。可以告诉我最好的方法么？ 此致感谢 pjp 答： 为什么要select trunc(dt)？？？为什么不能就用holiday里的日期呢？（将会使holiday的索引无法使用——除非万不得已永远不要对数据库列使用函数）。 还有为什么to_char用RRRRMMDD？？？？？？为什么要做一个to_char()将日期转换成字符串再和日期类型比较？？？？？？？？我一点都不明白，为什么用这么多转换？ 优化应当从去掉这些to_char和rrrrmmdd以及trunc开始。 建议： holiday is an index organized table. holiday already HAS the trunc of the date. check to see if the date is in fri, sat, sun (don't deal with dt+1). use not exists to see if dt or dt+1 exists, instead of not in in this case.. holiday是一张索引化表。 holiday已经有了日期的trunc。 检查日期是否fri、sat、sun，不要用dt+1。 使用not exists检查是否存在dt或dt+1，而不是这里的not in…… ops$tkyte@ORA10GR2> drop table holiday; ops$tkyte@ORA10GR2> create table holiday ( dt date primary key ) organization index; ops$tkyte@ORA10GR2> insert into holiday ops$tkyte@ORA10GR2> select trunc(sysdate)+rownum+50 from all_objects where rownum <= 500; ops$tkyte@ORA10GR2> commit; ops$tkyte@ORA10GR2> alter session set nls_date_format = 'rrrrmmdd'; ops$tkyte@ORA10GR2> */ ops$tkyte@ORA10GR2> set autotrace on statistics ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> with dates 2 as 3 (select trunc(sysdate)+level dt 4 from dual 5 where to_char(trunc(sysdate)+level,'DY') not in ( 'FRI', 'SAT', 'SUN' ) 6 connect by level <= 25) 7 select dt, dt+1 8 from dates 9 where not exists ( select null 10 from holiday h 11 where h.dt = dates.dt 12 or h.dt = (dates.dt+1)) 13 and rownum = 1 14 / DT DT+1 -------- -------- 20051107 20051108 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 472 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> select tomdate, spotdate 2 from ( 3 select dt tomdate, dt+1 spotdate 4 from ( select sysdate+rownum dt 5 from all_objects 6 where rownum <= 25 7 ) 8 where to_char(dt, 'DY') not in ('SAT', 'SUN') 9 and to_char(dt+1, 'DY') not in ('SAT', 'SUN') 10 and to_char(dt, 'RRRRMMDD') not in ( 11 select trunc(dt) 12 from holiday 13 ) 14 and to_char(dt+1, 'RRRRMMDD') not in ( 15 select trunc(dt) 16 from holiday 17 ) 18 ) 19 where rownum = 1 20 / TOMDATE SPOTDATE -------- -------- 20051107 20051108 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 27 consistent gets 0 physical reads 0 redo size 481 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 评论者：Parag J Patankar来自印度 你好Tom， 精彩解释。还有一个关于SQL的问题，有如下的查询： 17:51:38 SQL> l 1 select count(*) 2 from 3 ( select to_date(v09040, 'RRRRMM') v09040 4 from tw937 5 where trim(v09040) <> '013' and 6 trim(v09040) <> '024' ) tw937 7 where 8* (sysdate - v09040 -1) > 800 如果我运行它会得到如下错误信息 17:51:37 SQL> @toto ( select to_date(v09040, 'RRRRMM') v09040 * ERROR at line 3: ORA-01843: not a valid month 但我如果运行:( 上面SQL的内层查询 1 select distinct to_date(v09040, 'RRRRMM') 2 from tw937 3 where trim(v09040) <> '013' and 4* trim(v09040) <> '024' 17:57:32 SQL> / TO_DATE(V --------- 01-JUN-03 01-JUL-03 01-AUG-03 01-SEP-03 01-OCT-03 01-NOV-03 01-DEC-03 01-JAN-04 01-FEB-04 01-MAR-04 01-APR-04 01-MAY-04 01-JUN-04 01-JUL-04 01-AUG-04 01-SEP-04 01-OCT-04 01-NOV-04 01-DEC-04 01-JAN-05 01-FEB-05 01-MAR-05 01-APR-05 01-MAY-05 01-JUN-05 01-JUL-05 01-AUG-05 01-SEP-05 28 rows selected. 查询成功执行。能请你告诉我哪出问题了么？如果我单独内层查询为什么就成功了呢？ 此致感谢 pjp 注：因为表有好几千条记录索引我用了distinct。 答： 1 select count(*) 2 from 3 ( select to_date(v09040, 'RRRRMM') v09040 4 from tw937 5 where trim(v09040) <> '013' and 6 trim(v09040) <> '024' ) tw937 7 where 8* (sysdate - v09040 -1) > 800 因为有谓语推入（predicate pushing）和视图合并（view merging），和下面这个没什么区别： select count(*) from tw937 where trim(v09040) <> '013' and trim(v09040) <> '024' ) tw937 and (sysdate - to_date(v09040,'rrrrmm') -1) > 800 欢迎来到“当你不使用合适的数据类型来存放日期、数值和字符串，这就是会发生的——得到大堆错误”的世界。 （你真的需要那个trim么？如果是的，为什么？？？呃……）无论怎样： select count(*) from tw937 where trim(v09040) <> '013' and trim(v09040) <> '024' and (sysdate - case when trim(v09040) <> '013' and trim(v09040) <> '024' ) then to_date(v09040,'rrrrmm') end -1) > 800 实际上： select count(*) from tw937 where (sysdate - case when trim(v09040) <> '013' and trim(v09040) <> '024' ) then to_date(v09040,'rrrrmm') end -1) > 800 就足够了（因为如果没有被to_date，sysdate-……将会是NULL） 如果你能100%保证列存放了日期，不是013或024，那总是“安全”的。（你当然不能保证，总有一天有人会放进去一些“坏”东西——墨菲说的……） -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 原文： parag j patankar -- Thanks for the question regarding "SQL query optimization", version 9.2 originally submitted on 26-Oct-2005 3:22 Eastern US time, last updated 8-Dec-2005 6:58 Tom's latest followup | GOTO a Bookmarkable Page | Bottom You Asked (Jump to Tom's latest followup) Hi Tom, Suppose I am having following SQL query UPDATE TB38O SET G000KE = (SELECT TB39G.A6000D FROM TB39G WHERE TB39G.A0020C = TB38O.A020KF AND TB39G.A0090C = TB38O.A090KF AND TB39G.A1010Y = TB38O.B010KF AND TB39G.A6000Y = TB38O.G000KF AND TB39G.A0230Z = TB38O.A230KF AND TB39G.A9490C = TB38O.J490KE AND rownum < 2 ) WHERE G000KE='999' AND EXISTS (SELECT '1' FROM TB39G WHERE TB39G.A0020C = TB38O.A020KF AND TB39G.A0090C = TB38O.A090KF AND TB39G.A1010Y = TB38O.B010KF AND TB39G.A6000Y = TB38O.G000KF AND TB39G.A0230Z = TB38O.A230KF AND TB39G.A9490C = TB38O.J490KE) ; In this query TB39G and TB380 can not have primary keys. In this type of queries is there any way to avoid 3 FTS ( 1 FTS of TB380 and 2 FTS of TB39G ) of the tables in set ... and where clause and having only 1 FTS of each table ? regards & thanks pjp and we said... that is scary scary scary stuff. "rownum < 2" - so basically, you are updating G000KE to a random record from TB39G where that key matches -- if there are 2 or more records, you have NO CONTROL over which is used. That is called "non-deterministic", using the same data in two different databases - you'll get two different end results. Very very scary to me. Without keys, optimization will be hard since you cannot update the join. You'll have lots more than 3 full table scans likely (you might have a full scan of TB39G for each row in TB38O to be updated) You can avoid part of the work by UPDATE TB38O SET G000KE = nvl( (SELECT TB39G.A6000D FROM TB39G WHERE TB39G.A0020C = TB38O.A020KF AND TB39G.A0090C = TB38O.A090KF AND TB39G.A1010Y = TB38O.B010KF AND TB39G.A6000Y = TB38O.G000KF AND TB39G.A0230Z = TB38O.A230KF AND TB39G.A9490C = TB38O.J490KE AND rownum < 2 ), '999' ) WHERE G000KE='999' / but that rownum < 2 - that is very very scary. if the columns are in fact UNIUQE - and if you put on the proper unique/primary key constraint (obviating the need to use rownum at all), you can update the join which would be the most efficient method in all likelyhood. Reviews GOTO a page to Bookmark Review | Bottom | Top SQL Optimization October 27, 2005 Reviewer: Parag J Patankar from India Hi Tom, Thanks for your excellent answer as usual. Will you pl explain in more your quote of "That is called "non-deterministic", using the same data in two different databases - you'll get two different end results." if possible with example ? I assume that different databases means different databases in Oracle. regards & thanks pjp Followup: ops$tkyte@ORA10GR2> create table t1 ( x int, y int ); ops$tkyte@ORA10GR2> create table t2 ( x int, y int ); ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> insert into t1 values ( 1, 1 ); ops$tkyte@ORA10GR2> insert into t1 values ( 1, 2 ); ops$tkyte@ORA10GR2> insert into t2 values ( 1, 2 ); ops$tkyte@ORA10GR2> insert into t2 values ( 1, 1 ); ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> select * from t1 minus select * from t2; ops$tkyte@ORA10GR2> select * from t2 minus select * from t1; ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> select * from t1 where x = 1 and rownum < 2; X Y ---------- ---------- 1 1 ops$tkyte@ORA10GR2> select * from t2 where x = 1 and rownum < 2; X Y ---------- ---------- 1 2 t1 and t2 - same data. where x = 1 and rownum < 2 - different answers. It is not deterministic, same data, different order of rows, different answers. sends chills down my spine.... GOTO a page to Bookmark Review | Bottom | Top Help required October 28, 2005 Reviewer: Anil Pant Hello, We have application which maintains the customer details. One of the screen has a search option and user can search on any or all of the 10-15 fields (customer id, customer name, address line1, address line2, city, zip etc). If the user tries to search on Customer name or address line 1 or address 2 or zip code it takes lot of time. We are using Cost based and the statistics are updated. I've to give them a solution how best the query can be optimized. Customer Id is the key column. There are index on few of the columns. One proposed solution given to me is if the user tries to search on any one of the non-key columns like address line 1 or address line 2 or city, try to hit the key columns or try to hit the key tables. I did not understand how to write such a query. I know it very vague question but your inputs will be appreciated. We are working on Oracle 9i. Thanks Anil Pant Followup: it is way too vague. I've no idea what you mean by "One proposed solution given to me is if the user tries to search on any one of the non-key columns like address line 1 or address line 2 or city, try to hit the key columns or try to hit the key tables." that sort of implies "multiple tables" - hence, answering this is sort of "hard" since I've no clue what the schema might be. GOTO a page to Bookmark Review | Bottom | Top SQL Query optimization November 02, 2005 Reviewer: Parag J Patankar from India Hi Tom, I am having following query select tomdate, spotdate from ( select dt tomdate, dt+1 spotdate from ( select sysdate+rownum dt from all_objects where rownum <= 25 ) where to_char(dt, 'DY') not in ('SAT', 'SUN') and to_char(dt+1, 'DY') not in ('SAT', 'SUN') and to_char(dt, 'RRRRMMDD') not in ( select trunc(dt) from holiday ) and to_char(dt+1, 'RRRRMMDD') not in ( select trunc(dt) from holiday ) where rownum = 1 / Which is calculating 2 working days. I just want to avoid two scans of "holiday" tbale. Will you pl tell me what is the best way to do this ? regards & thanks pjp Followup: why select trunc(dt)??? why wouldn't holiday just have the days in there (would preclude index access to holiday - never never apply functions to database columns unless and until you have to) and why RRRRMMDD with a to_char?????? why are you doing a to_char() to turn a date into a string, to compare to a DATE???????? I'm not getting this at all - why all of the conversions? The optimizations should start with getting rid of the to_chars with rrrrmmdd and the truncs. suggestions: holiday is an index organized table. holiday already HAS the trunc of the date. check to see if the date is in fri, sat, sun (don't deal with dt+1). use not exists to see if dt or dt+1 exists, instead of not in in this case.. ops$tkyte@ORA10GR2> drop table holiday; ops$tkyte@ORA10GR2> create table holiday ( dt date primary key ) organization index; ops$tkyte@ORA10GR2> insert into holiday ops$tkyte@ORA10GR2> select trunc(sysdate)+rownum+50 from all_objects where rownum <= 500; ops$tkyte@ORA10GR2> commit; ops$tkyte@ORA10GR2> alter session set nls_date_format = 'rrrrmmdd'; ops$tkyte@ORA10GR2> */ ops$tkyte@ORA10GR2> set autotrace on statistics ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> with dates 2 as 3 (select trunc(sysdate)+level dt 4 from dual 5 where to_char(trunc(sysdate)+level,'DY') not in ( 'FRI', 'SAT', 'SUN' ) 6 connect by level <= 25) 7 select dt, dt+1 8 from dates 9 where not exists ( select null 10 from holiday h 11 where h.dt = dates.dt 12 or h.dt = (dates.dt+1)) 13 and rownum = 1 14 / DT DT+1 -------- -------- 20051107 20051108 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 472 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> select tomdate, spotdate 2 from ( 3 select dt tomdate, dt+1 spotdate 4 from ( select sysdate+rownum dt 5 from all_objects 6 where rownum <= 25 7 ) 8 where to_char(dt, 'DY') not in ('SAT', 'SUN') 9 and to_char(dt+1, 'DY') not in ('SAT', 'SUN') 10 and to_char(dt, 'RRRRMMDD') not in ( 11 select trunc(dt) 12 from holiday 13 ) 14 and to_char(dt+1, 'RRRRMMDD') not in ( 15 select trunc(dt) 16 from holiday 17 ) 18 ) 19 where rownum = 1 20 / TOMDATE SPOTDATE -------- -------- 20051107 20051108 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 27 consistent gets 0 physical reads 0 redo size 481 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed GOTO a page to Bookmark Review | Bottom | Top SQL query November 07, 2005 Reviewer: Parag J Patankar from India Hi Tom, Brilliant explanation to my question. One more question regarding SQL I have following query 17:51:38 SQL> l 1 select count(*) 2 from 3 ( select to_date(v09040, 'RRRRMM') v09040 4 from tw937 5 where trim(v09040) <> '013' and 6 trim(v09040) <> '024' ) tw937 7 where 8* (sysdate - v09040 -1) > 800 If I run this query I am getting following error message 17:51:37 SQL> @toto ( select to_date(v09040, 'RRRRMM') v09040 * ERROR at line 3: ORA-01843: not a valid month But If I run : ( inner query of above SQL ) 1 select distinct to_date(v09040, 'RRRRMM') 2 from tw937 3 where trim(v09040) <> '013' and 4* trim(v09040) <> '024' 17:57:32 SQL> / TO_DATE(V --------- 01-JUN-03 01-JUL-03 01-AUG-03 01-SEP-03 01-OCT-03 01-NOV-03 01-DEC-03 01-JAN-04 01-FEB-04 01-MAR-04 01-APR-04 01-MAY-04 01-JUN-04 01-JUL-04 01-AUG-04 01-SEP-04 01-OCT-04 01-NOV-04 01-DEC-04 01-JAN-05 01-FEB-05 01-MAR-05 01-APR-05 01-MAY-05 01-JUN-05 01-JUL-05 01-AUG-05 01-SEP-05 28 rows selected. my query is executing successfully. Will you pl tell me what is the problem ? If I run only inner query seperately from SQL then why it is successful ? regards & thanks pjp Note : I have run distinct as this table is having few thousand records Followup: 1 select count(*) 2 from 3 ( select to_date(v09040, 'RRRRMM') v09040 4 from tw937 5 where trim(v09040) <> '013' and 6 trim(v09040) <> '024' ) tw937 7 where 8* (sysdate - v09040 -1) > 800 with predicate pushing and view merging is not any different to us than: select count(*) from tw937 where trim(v09040) <> '013' and trim(v09040) <> '024' ) tw937 and (sysdate - to_date(v09040,'rrrrmm') -1) > 800 Welcome to the world of "this is what happens when you don't use appropriate datatypes to hold DATES, NUMBERS and STRINGS - you get tons of errors" (do you really need that trim, if so WHY??? ugh).... Anyway: select count(*) from tw937 where trim(v09040) <> '013' and trim(v09040) <> '024' and (sysdate - case when trim(v09040) <> '013' and trim(v09040) <> '024' ) then to_date(v09040,'rrrrmm') end -1) > 800 actually: select count(*) from tw937 where (sysdate - case when trim(v09040) <> '013' and trim(v09040) <> '024' ) then to_date(v09040,'rrrrmm') end -1) > 800 is sufficient (since the sysdate-.... will be NULL if it is not to_date'ed is always "safe" IF you can be 100% sure that when that column is not 013 or 024 - it contains a date (which you cannot be of course, some day someone will put something "bad" in there - Murphy says so....) GOTO a page to Bookmark Review | Bottom | Top A possible solution for Anil Patel's query November 07, 2005 Reviewer: Bipul from London,Uk Hi. You can use Oracle text search for efficient searching. Use a pl/sql procedure as user_datasource to build XML from the various columns of your table and then create text index on that XML. You will then need to dynamically build the query using CONTAINS and "within" clause to do text search. For example, add a VARCHAR2(1) column called text_xml to the table. Create a pl/sql user_datastore procedure which will generate an XML from different columns of the table your XML will look like <City>London</City> <AddressLine1>Middlesex House</AddressLine1> ... Create text index on this text_xml column using the user_datastore. Generate the query which will look like Select <blah> from <table_name > WHERE CONTAINS (text_xml, 'london within CITY') > 0; HTH -bipul GOTO a page to Bookmark Review | Bottom | Top SQL Query November 07, 2005 Reviewer: Parag J Patankar from India Hi Tom, Thanks for your answer. Tom, you said ---------------------------------------------------------- 1 select count(*) 2 from 3 ( select to_date(v09040, 'RRRRMM') v09040 4 from tw937 5 where trim(v09040) <> '013' and 6 trim(v09040) <> '024' ) tw937 7 where 8* (sysdate - v09040 -1) > 800 with predicate pushing and view merging is not any different to us than: select count(*) from tw937 where trim(v09040) <> '013' and trim(v09040) <> '024' ) tw937 and (sysdate - to_date(v09040,'rrrrmm') -1) > 800 ------------------------------------------------------------ Will you pl tell me what is "predicate pushing and view merging " in simple terms ? Secondly, if I run first query 1 select count(*) 2 from 3 ( select to_date(v09040, 'RRRRMM') v09040 4 from tw937 5 where v09040 <> '013' and 6 v09040 <> '024' ) tw937 7 where 8* (sysdate - v09040 -1) > 800 10:07:26 SQL> / ( select to_date(v09040, 'RRRRMM') v09040 * ERROR at line 3: ORA-01843: not a valid month and if I run query ( I think oracle will run convert above query as below ) 1 select count(*) 2 from tw937 3 where v09040 <> '013' 4 and v09040 <> '024' 5* and (sysdate - to_date(v09040,'rrrrmm') -1) > 800 10:08:31 SQL> / COUNT(*) ---------- 44270 1 row selected. Will pl tell me why second query is running not he first one ? ( Sorry I have not understood your explanation given above in this thread for same question ) best regards & thanks pjp Followup: see http://www.oracle.com/technology/oramag/oracle/05-mar/o25asktom.html and look for Views and Merging/Pushing the order of evaulation of the predicate - what gets done first, is up to the optimizer. In one case you got lucky, in the other not lucky. You should do it as I said - always check and convert using case. You really do not want to rely on the order of predicate evaluation to make your app safe - we can and will change the order of evaluation. storing dates in strings, you do not even want to know what I think about that horrible practice. GOTO a page to Bookmark Review | Bottom | Top December 08, 2005 Reviewer: parag jayant patankar from India Hi Tom, As per your suggestion of SQL optimization, I have modified the query as below UPDATE TB38O SET G000KE = nvl( (SELECT TB39G.A6000D FROM TB39G WHERE TB39G.A0020C = TB38O.A020KF AND TB39G.A0090C = TB38O.A090KF AND TB39G.A1010Y = TB38O.B010KF AND TB39G.A6000Y = TB38O.G000KF AND TB39G.A0230Z = TB38O.A230KF AND TB39G.A9490C = TB38O.J490KE AND rownum < 2 ), '999' ) WHERE G000KE='999' / According to me execution time of this query is long. It is taking 22 mins for updating 127015 records. 127015 rows updated. Elapsed: 00:22:01.60 1* select count(*) from tb38o 12:24:04 SQL> / COUNT(*) ---------- 144811 1 row selected. 12:24:05 SQL> select count(*) from tb39g; COUNT(*) ---------- 53729 Both tables are not having any index. Explain plan shows following 12:36:53 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 6 | 198 | 240 (41)| | 1 | UPDATE | TB38O | | | | |* 2 | TABLE ACCESS FULL | TB38O | 6 | 198 | 240 (41)| |* 3 | COUNT STOPKEY | | | | | |* 4 | TABLE ACCESS FULL | TB39G | 1 | 27 | 91 (44)| ------------------------------------------------------------------------- PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------- -------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("SYS_ALIAS_6"."G000KE"=999) 3 - filter(ROWNUM<2) 4 - filter("TB39G"."A9490C"=:B1 AND "TB39G"."A0230Z"=:B2 AND TO_NUMBER("TB39G"."A0090C")=:B3 AND TO_NUMBER("TB39G"."A1010Y")=:B4 AND TO_NUMBER("TB39G"."A6000Y")=:B5 AND TO_NUMBER("TB39G"."A0020C")=:B6) 19 rows selected. Kindly guide me. regards & thanks pjp Followup: well, an index would not hurt, don't you think? you do see the full scan of a table for each row in the outer table right? If you are unwilling to index (and hey, if you put a UNIQUE constraint..... we could update the join and boy how good would this go).... -------------------------------------------------------------------------------- 原文版权归属asktom.oracle.com所有，翻译 by ern·CNOUG 2005-12-08 23:03:00 12月7日 Ask Tom 之中英对照——访问外部表优化Optimize External Table Access 访问外部表优化 -------------------------------------------------------------------------------- William -- Thanks for the question regarding "Optimize External Table Access", version 9.2.0.7 originally submitted on 5-Dec-2005 2:37 Eastern US time, last updated 7-Dec-2005 1:51 You Asked (Jump to Tom's latest followup) 问： It is possible to reduce the access to external table down to 1 time? 是否可以将下面对外部表的访问降低到一次？ CREATE OR REPLACE DIRECTORY "BDUMP_DIR" AS 'c:\oracle\admin\dev92\bdump\' / CREATE TABLE "DBA_SUPPORT"."ALERT_LOG" ( "LINE1" VARCHAR2(4000) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "BDUMP_DIR" ACCESS PARAMETERS ( records delimited by newline fields missing field values are null ) LOCATION ( 'alert_dev92.log' ) ) / with a as (select rownum line#, a.* from alert_log a ), s as (select * from a where a.line1 like 'ORA-%') select distinct a.* from a, s where a.line# between s.line# - 5 and s.line# order by a.line# / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (UNIQUE) 2 1 NESTED LOOPS 3 2 VIEW 4 3 COUNT 5 4 EXTERNAL TABLE ACCESS (FULL) OF 'ALERT_LOG' 6 2 VIEW 7 6 COUNT 8 7 EXTERNAL TABLE ACCESS (FULL) OF 'ALERT_LOG' Statistics ---------------------------------------------------------- 31 recursive calls 0 db block gets 535 consistent gets 0 physical reads 0 redo size 28334 bytes sent via SQL*Net to client 873 bytes received via SQL*Net from client 36 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 512 rows processed Best regards, William -------------------------------------------------------------------------------- and we said... 答： two approaches: 两种方式： ops$tkyte@ORA9IR2> set autotrace traceonly explain ops$tkyte@ORA9IR2> with 2 a as (select /*+ MATERIALIZE */ rownum line#, a.* from alert_log a ), 3 s as (select * from a where a.line1 like 'ORA-%') 4 select distinct a.* 5 from a, s 6 where a.line# between s.line# - 5 and s.line# 7 order by a.line# 8 / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 2 RECURSIVE EXECUTION OF 'SYS_LE_2_0' 2 0 TEMP TABLE TRANSFORMATION 3 2 SORT (UNIQUE) 4 3 NESTED LOOPS 5 4 VIEW 6 5 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D660B_127C20' 7 4 VIEW 8 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D660B_127C20' ops$tkyte@ORA9IR2> select r, line1 2 from (select r, line1, 3 lead(line1,1) over (order by r) next1, 4 lead(line1,2) over (order by r) next2, 5 lead(line1,3) over (order by r) next3, 6 lead(line1,4) over (order by r) next4, 7 lead(line1,5) over (order by r) next5 8 from (select line1, rownum r from alert_log) 9 ) 10 where line1 like 'ORA-%' 11 or next1 like 'ORA-%' 12 or next2 like 'ORA-%' 13 or next3 like 'ORA-%' 14 or next4 like 'ORA-%' 15 or next5 like 'ORA-%' 16 / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 VIEW 2 1 WINDOW (SORT) 3 2 VIEW 4 3 COUNT 5 4 EXTERNAL TABLE ACCESS (FULL) OF 'ALERT_LOG' ops$tkyte@ORA9IR2> set autotrace off -------------------------------------------------------------------------------- 评论： /*+ MATERIALIZE */ December 06, 2005 Reviewer: Kim Berg Hansen from Middelfart, Denmark Hi, Tom 你好，Tom That hint (/*+ MATERIALIZE */) is new to me. Is it documented? 我还刚知道提示/*+ MATERIALIZE */，它是官方公布的么？ I can't find it in the list of hints in the 10g docs - but maybe it's just because I'm nearsighted :-) 我无法在10g的文档的提示列表里找到它——也许只是我近视？:-) Is it an alternative method of using "where rownum > 0" or something similar to force materializing an inline view? 这是"where rownum > 0"的另一种实现还是类似强制将一个内联视图物化？ Thanks for your help as always... 和往常一样，谢谢你的帮助…… Followup: 答： it is not. (Jonathan Lewis is a proponent of materialize over the rownum > 0 'trick' I use sometimes) 它不是（官方公布的）。（Jonathan Lewis提倡用rownum > 0来物化，我有时也用） it is like "where rownum > 0" 这就和"where rownum > 0"类似 -------------------------------------------------------------------------------- semi-documented hints December 06, 2005 Reviewer: Padders from UK While on the subject of hints (see how I did that), can you clarify the purpose and mechanism of the /*+ NESTED_TABLE_FAST_INSERT */ hint which is referred to in the 10g documentation for DBMS_FREQUENT_ITEMSET? 既然主题都是关于提示，你能否澄清一下提示/*+ NESTED_TABLE_FAST_INSERT */（它在10g文档的DBMS_FREQUENT_ITEMSET提到了）的目的和机制？ "If you want to use an insert statement to load frequent itemsets into a nested table, it is better to use the NESTED_TABLE_FAST_INSERT hint for performance: “如果你要用一个插入语句来将一个itemsets频繁插入嵌套表，最好使用NESTED_TABLE_FAST_INSERT来提高性能： CREATE TABLE fq_nt (coll FI_VARCHAR_NT) NESTED TABLE coll STORE AS coll_nest; INSERT /*+ NESTED_TABLE_FAST_INSERT */ INTO fq_nt SELECT cast(itemset as FI_VARCHAR_NT) FROM table(DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL( cursor(SELECT tid, iid FROM tranx_table_in), 0.6, 2, 5, NULL, NULL));" ” Followup: 答： it appears it performs an array insert of the the nested table instead of slow by slow processing. 看起来是对这个嵌套表执行一个数组（array）插入而不是慢慢执行。 -------------------------------------------------------------------------------- Very useful December 06, 2005 Reviewer: William Lai from Penang, Malaysia I think I will use the your second method. 我想我会用你的第二个方法。 my original method runtime: ~60sec your method 1 runtime: ~31sec your method 2 runtime: ~27sec 我原来的运行时间：60秒 你的第一个方法：31秒 你的第二个方法：27秒 BTW, where can I find the documentation about the MATERIALIZE hint? 另外，哪里能找到提示MATERIALIZE的文档？ Thanks 谢谢 Followup: 答： it is not currently documented. 目前还没有公布。 -------------------------------------------------------------------------------- 原文版权归属asktom.oracle.com所有，翻译 by ern·CNOUG 2005-12-07 22:33:00 |
|
|
