Oracle 中的隐藏特性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/381231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hidden features in Oracle
提问by Peter Gfader
I enjoyed the answers and questions about hidden features in sql server
我喜欢有关sql server 中隐藏功能的答案和问题
What can you tell us about Oracle?
Hidden tables, inner workings of ..., secret stored procs, package that has good utils...
您能告诉我们有关 Oracle 的哪些信息？
隐藏的表，......的内部工作原理，秘密存储过程，具有良好实用程序的包......
回答by Tony Andrews
Since Apex is now part of every Oracle database, these Apex utility functions are useful even if you aren't using Apex:
由于 Apex 现在是每个 Oracle 数据库的一部分，即使您不使用 Apex，这些 Apex 实用程序功能也很有用：
SQL> declare
2 v_array apex_application_global.vc_arr2;
3 v_string varchar2(2000);
4 begin
5
6 -- Convert delimited string to array
7 v_array := apex_util.string_to_table('alpha,beta,gamma,delta', ',');
8 for i in 1..v_array.count
9 loop
10 dbms_output.put_line(v_array(i));
11 end loop;
12
13 -- Convert array to delimited string
14 v_string := apex_util.table_to_string(v_array,'|');
15 dbms_output.put_line(v_string);
16 end;
17 /
alpha
beta
gamma
delta
alpha|beta|gamma|delta
PL/SQL procedure successfully completed.
回答by David Aldridge
"Full table scans are not always bad. Indexes are not always good."
“全表扫描并不总是坏的。索引并不总是好的。”
An index-based access method is less efficient at reading rows than a full scan when you measure it in terms of rows accessed per unit of work (typically per logical read). However many tools will interpret a full table scan as a sign of inefficiency.
当您根据每个工作单元（通常是每个逻辑读取）访问的行来衡量时，基于索引的访问方法在读取行方面的效率低于完全扫描。然而，许多工具会将全表扫描解释为效率低下的标志。
Take an example where you are reading a few hundred invoices frmo an invoice table and looking up a payment method in a small lookup table. Using an index to probe the lookup table for every invoice probably means three or four logical io's per invoice. However, a full scan of the lookup table in preparation for a hash join from the invoice data would probably require only a couple of logical reads, and the hash join itself would cmoplete in memory at almost no cost at all.
举个例子，你从一张发票表中读取几百张发票，并在一个小的查找表中查找付款方式。使用索引来探测每张发票的查找表可能意味着每张发票有三个或四个逻辑 io。然而，为准备来自发票数据的散列连接而对查找表进行完整扫描可能只需要几次逻辑读取，并且散列连接本身将在内存中完成，几乎没有任何成本。
However many tools would look at this and see "full table scan", and tell you to try to use an index. If you do so then you may have just de-tuned your code.
但是，许多工具会查看此内容并查看“全表扫描”，并告诉您尝试使用索引。如果你这样做了，那么你可能刚刚对你的代码进行了调整。
Incidentally over reliance on indexes, as in the above example, causes the "Buffer Cache Hit Ratio" to rise. This is why the BCHR is mostly nonsense as a predictor of system efficiency.
顺便提一下，如上例所示，过度依赖索引会导致“缓冲区缓存命中率”上升。这就是为什么 BCHR 作为系统效率的预测指标大多是无稽之谈。
回答by David Aldridge
The cardinality hint is mostly undocumented.
基数提示大多没有记录。
explain plan for
select /*+ cardinality(@inner 5000) */ *
from (select /*+ qb_name(inner) */ * from dual)
/
select * from table(dbms_xplan.display)
/
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 10000 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
回答by David Aldridge
The Buffer Cache Hit Ratio is virtually meaningless as a predictor of system efficiency
缓冲区缓存命中率作为系统效率的预测指标几乎毫无意义
回答by David Aldridge
You can view table data as of a previous time using Flashback Query, with certain limitations.
您可以使用闪回查询查看上一次的表数据，但有某些限制。
Select *
from my_table as of timestamp(timestamp '2008-12-01 15:21:13')
11g has a whole new feature set around preserving historical changes more robustly.
11g 有一个全新的功能集，可以更稳健地保留历史更改。
回答by jle
wm_concatworks like the the MySql group_concat but it is undocumented.
wm_concat 的工作方式与 MySql group_concat 类似，但它没有记录。
with data:
有数据：
-car- -maker-
Corvette Chevy
Taurus Ford
Impala Chevy
Aveo Chevy
select wm_concat(car) Cars, maker from cars
group by maker
gives you:
给你：
-Cars- -maker-
Corvette, Impala, Aveo Chevy
Taurus Ford
回答by David Aldridge
Frequent rebuilding of indexes is almost always a waste of time.
频繁地重建索引几乎总是浪费时间。
回答by David Aldridge
The OVERLAPS predicate is undocumented.
OVERLAPS 谓词没有记录。
http://oraclesponge.wordpress.com/2008/06/12/the-overlaps-predicate/
http://oraclesponge.wordpress.com/2008/06/12/the-overlaps-predicate/
回答by David Aldridge
I just found out about the pseudo-column Ora_rowSCN. If you don't set your table up for this, this pcolumn gives you the block SCN. This could be really useful for the emergency, "Oh crap I have no auditing on this table and wonder if someone has changed the data since yesterday."
我刚刚发现了伪列 Ora_rowSCN。如果你不为此设置你的表，这个 pcolumn 给你块 SCN。这对于紧急情况可能非常有用，“哦，废话，我没有对这张表进行审计，想知道是否有人从昨天起更改了数据。”
But even better is if you create the table with Rowdependecies ON. That puts the SCN of the last change on every row. This will help you avoid a "Lost Edit" problem without having to include every column in your query.
但更好的是，如果您在 Rowdependecies ON 的情况下创建表。这会将最后一次更改的 SCN 放在每一行上。这将帮助您避免“丢失编辑”问题，而不必在查询中包含每一列。
IOW, when you app grabs a row for user modification, also select the Ora_rowscn. Then when you post the user's edits, include Ora_rowscn = v_rscn in addition to the unique key in the where clause. If someone has touched the row since you grabbed it, aka lost edit, the update will match zero rows since the ora_rowscn will have changed.
IOW，当您的应用程序抓取一行进行用户修改时，还要选择 Ora_rowscn。然后，当您发布用户的编辑时，除了 where 子句中的唯一键外，还包括 Ora_rowscn = v_rscn。如果有人在您抓取后触摸了该行，即丢失编辑，则更新将匹配零行，因为 ora_rowscn 将发生更改。
So cool.
非常酷。
回答by FerranB
If you get the value of PASSWORDcolumn on DBA_USERSyou can backup/restore passwords without knowing them:
如果您获得PASSWORD列的值，DBA_USERS您可以在 不知道密码的情况下备份/恢复密码：
ALTER USER xxx IDENTIFIED BY VALUES 'xxxx';
