分类： Oracle
2010-05-01 11:28:45
如果你有两个数据来源，如平面文件或表数据，并且要将他们合并在一起，你将怎么做?如果他们有一个共同的属性，如客户ID，那么该解决方案应该是很明显：合并相关的属性，在这个例子中，只需合并客户ID就够了。如果没有任何共同之处该怎么办呢?唯一的要求就是，将数据源1中的记录和数据源2中的记录进行匹配 。并且，那个记录去和另一个记录匹配并没有关系，那么问题是，一个数据源中的每一个纪录如何获得从其他数据源记录的标记。
上述问题可以被描述为向一个数据库中加入了不同的或看似无关的数据。在先前的文章的文章中，涉及如何使用ROWNUM在无关的数据之间创造联系。该合并方法的本质是利用甲骨文提供虚拟数据列来建立联系。下面的查询可以用来作为CREATE TABLE AS SELECT声明的一部分或作为基于满足加入条件既定目标表的插入。
SELECT * FROM (SELECT , ROWNUM AS rownum_a FROM TABLE_A ) ALIAS_A, (SELECT , ROWNUM AS rownum_b FROM TABLE_B ) ALIAS_B WHERE ALIAS_A.rownum_a = ALIAS_B.rownum_b; |
假设要合并的记录的数目过大(如数以百万计)，这种方法潜在的缺点是什么?那么，当一行作为一个记录时又如何了?我们没有真正的控制权决定的查询所返回结果行的顺序，直到我们执行查询之前，甲骨文是不知道记录的行号的。换言之， ROWNUM是在这样的事实上创建的。如果你要从两个地方选择数百万行，你将支付甲骨文公司为每个记录分配行号(只针对你的查询，而不是永远)的时间。
让我们监测将两个有100万行的表合并到一起的一个会话。在这第一个例子中，这个数据源已经记录可100万个记录。表A范围从1到1000000及表B范围从1000001至2000000 (即在第一个表中再加入100万行) 。如果加入后能够完美的保持行的顺序，那么有序对将像下面表格这个样子：
当我们查看数据时(通过Toad)发现Oracle数据库并不执行一个完美的排序，并且相差甚远。
该ROWNUM_A和B值一个一个都匹配，因为这是我们匹配/合并的。注意：记录1321 (和1001321 )是如何同ROWNUM 1标记在一起的 。所以我们可以推断是，甲骨文以同样的方式填补表格之间的空白区块。这应该说服你一次甚至永远(如果你至今还不知知道)， ROWNUM虚拟数据列已没有意义或与个表中记录的实际顺序无关。
创建表的声明追踪， 经过TKPROF 解析后，输出结果如下：
CREATE TABLE TABLE_ROWNUM AS SELECT * FROM (SELECT ID, ROWNUM AS rownum_a FROM TABLE_A) ALIAS_A, (SELECT ID AS id_b, ROWNUM AS rownum_b FROM TABLE_B) ALIAS_B WHERE ALIAS_A.rownum_a = ALIAS_B.rownum_b call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 4.41 5.63 1770 12324 5239 1000000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- total 2 4.42 5.64 1770 12324 5239 1000000 |
我们知道一个事实，即每个表都有100万行。在分析了表后， NUM_ROWS值显示为1034591 。当与甲骨文本身将通过连续计数报告的值相比较时要小心依靠通过第三方工具检查出的值(包括从USER_TABLES选择NUM_ROWS )。为什么会有差异呢?是否是因为分析是基于样本或估算的数据，或根据检查到的每个记录?
现在，对于合并数据有一个可供选择的办法。那就是让我们使用一个真正的列替代虚拟数据列，一个自然的选择是创建(在某种意义上)基于序列替代关键字。这个办法是为每个表添加一个命名为SEQ的列，并且在基于序列号对他们进行更新，并且保证每次更新使用相同的起点和相同的增量。对一个表更新操作如下所示。
SQL> create sequence tab_b; Sequence created. Elapsed: 00:00:00.05 SQL> update table_b set seq = tab_b.nextval; 1000000 rows updated. Elapsed: 00:05:00.05 |
有一件事应该可以立即脱颖而出：创造一个合并关键字所花费的时间刚刚超过五分钟，或是ROWNUM采取的方法所花费时间的13倍，这只是对两个表中的一个表所进行操作所花费的时间(第一张表格花费五分钟进行更新) 。增加或创建一个合并关键字是必要的，如有可能，最好在创建表的时候就创建。那么，比通过ROWNUM做同样的事情所多花费的关键点是什么?
根据新的设置，如何进行合并?
CREATE TABLE TABLE_SEQ AS SELECT * FROM (SELECT ID, SEQ AS seq_a FROM TABLE_A) ALIAS_A, (SELECT ID AS id_b, SEQ AS seq_b FROM TABLE_B) ALIAS_B WHERE ALIAS_A.seq_a = ALIAS_B.seq_b call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- Parse 1 0.00 0.06 0 0 0 0 Execute 1 10.64 24.43 12186 12370 5677 1000000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- total 2 10.64 24.49 12186 12370 5677 1000000 |
有趣的是，既然数据并非如此不同，性能也只是略差。那么解释计划展示的是什么?使用ROWNUM原始测试，我们有：
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------ Plan hash value: 1354216904 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------ | 0 | CREATE TABLE STATEMENT | | 10G| 496G| | 15M (2)| 50:40:43 | | 1 | LOAD AS SELECT | TABLE_ROWNUM | | | | | | |* 2 | HASH JOIN | | 10G| 496G| 36M| 186K (97)| 00:37:19 | | 3 | VIEW | | 1009K| 25M| | 1597 (10)| 00:00:20 | | 4 | COUNT | | | | | | | | 5 | TABLE ACCESS FULL | TABLE_B | 1009K| 4930K| | 1381 (11)| 00:00:17 | | 6 | VIEW | | 1016K| 25M| | 1475 (10)| 00:00:18 | | 7 | COUNT | | | | | | | | 8 | TABLE ACCESS FULL | TABLE_A | 1016K| 3969K| | 1285 (12)| 00:00:16 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ALIAS_A"."ROWNUM_A"="ALIAS_B"."ROWNUM_B") 基于序列的合并似乎是一个更好的计划。 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- Plan hash value: 1354216904 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | 10G| 496G| | 15M (2)| 50:40:43 | | 1 | LOAD AS SELECT | TABLE_ROWNUM | | | | | | |* 2 | HASH JOIN | | 10G| 496G| 36M| 186K (97)| 00:37:19 | | 3 | VIEW | | 1009K| 25M| | 1597 (10)| 00:00:20 | | 4 | COUNT | | | | | | | | 5 | TABLE ACCESS FULL | TABLE_B | 1009K| 4930K| | 1381 (11)| 00:00:17 | | 6 | VIEW | | 1016K| 25M| | 1475 (10)| 00:00:18 | | 7 | COUNT | | | | | | | | 8 | TABLE ACCESS FULL | TABLE_A | 1016K| 3969K| | 1285 (12)| 00:00:16 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ALIAS_A"."ROWNUM_A"="ALIAS_B"."ROWNUM_B") |
虽然这是一个相对较小的数据集，你可以明白为什么执行该计划的花费可能会引起误解。如果基于序列的表在同一会话中被删除和重新建立，创建表重新删除的时间到刚刚超过2秒。在表面上看，第二轮创建的表似乎要快得多，但真正要证明的是什么呢?
所要证明的是，数据块已经读入缓存，从缓存中读取数据块的速度将远远超过从磁盘双方读取的速度(这我们已经知道的事实) 。它实际意义是：你创建表需要多少时间?这通常是一次性完成。如果原始表被删除和重创，它的创建时间将大大加快。
通过清除共享池和缓存来恢复性能， 在ROWNUM和基于序的列情况下所花费的时间分别 14秒和10秒的。在这一点上，它可能看起来像是混为一谈。但在运行期间，其性能级别交换了。这也许是事实，但不要忘记设置了序列为基础的表格的费用(按时间)。
总结
从某种意义上说，最为相似的数据集，操作系统和平台依赖性(多少行，内存和I / O等) ，他们可以更快地在不同数据集之间添加一个共同的属性，然后在进行合并操作。对于较小的数据集，也许略高于100万行，我冒昧地说，使用ROWNUM这将永远是比新增一个合并关键字更快，即使使用常见的关键创建表的速度更快。那么，什么时候适当使用ROWNUM ?当在没有共同关键字的情况时，你不关心表之间的特殊关联，即使是正好就存在这样的事实。如果你正在处理相关表，他们基于一个共同的属性，并且这些关联必须排序，你一定不能依赖ROWNUM保持合并表之间的顺序。它事关，在一个表中具体行是否与第二个表中特定行匹配。
