![关闭 [x]](http://dw1.s81c.com/i/c.gif){kind=small}
| 免费下载：IBM® Informix® 11.7 试用版（包括 Ultimate Edition、Developer Edition 和 Innovator-C Edition） |
|---|
| 下载更多的 IBM 软件试用版，并加入 IBM 软件下载与技术交流群组，参与在线交流。 |
最近，我遇到了很多与性能相关的问题，因此，我想我应当针对这一问题做一些解释。由于懒于整理自己的基准，而且我认为剖析广泛使用的基准更有意义，因此我们来研究一下 Lester Knutsen 与 Advanced DataTools 所采用的 “Fastest DBA 基准”。大多数读者对此可能并不太熟悉，这是一个由 Advanced DataTools 发起的定期辩论，他们提出了一个缓慢运行的应用程序，让挑战参与者来对其进行调优。我在 参考资料 部分提供了这一讨论相关细节的链接。
我也意识到类没有书面文字有用，因此，您现在所阅读的是对于如何基于 Fastest DBA 基准的剖析，来调优数据库的探索。我们将执行基准、注意性能问题，单独解决每个问题，并使用所有已解决的问题来尝试该基准。注意这是第二个基准；后续的文章将会对第三个（以及当前的）基准进行剖析。
本文不介绍有关基准的安装问题 — README 文件当中提供了全面的讲解。我在我的 MacBook 中提出了两个问题，第一个是关于引擎的 SHMBASE。我必须编写源文件 $ONCONFIG，并进行相应的设置（并针对每个 setup.sh 执行进行重新复制）。第二个问题是，我的 Informix 用户默认属于 “admin” 组，因此，我必须手动将 dbspaces 的所有权改为 Informix:Informix。
基准的执行与交付无法及时完成。基准本身很简单：它加载客户表，然后为这些客户生成账单。最后的操作是对账单表的更新，这需要花费一定时间。该工作并不复杂，但它提供了一个认识调优如何提高性能的机会。
通常，任何性能调优项目的首要问题是问题描述。如何知道何时已经解决了问题？如何衡量性能以及确定它是否已被提高，以及提高了多少？基于本文的目的，我想说，基准的运行时间过长，我想在一个小时内执行完毕。此外，我会捕获每个阶段的数据库指标来确定是否有所改善。
有关数据库调优的第二个问题是确定调优什么。我们将会从纠正中看到最佳结果：
- 数据库模型
- 应用程序
- 磁盘
- 最后是引擎。
由于使用单磁盘的 MacBook Pro，所以我在磁盘调优方面可做的事情不多。那么需要关注 CPU、磁盘、以及内存使用情况。
安装了基准以后，执行 onstat -z 来清除引擎指标，这样就能确保仅看到基准对引擎的作用。
您可能会注意到，基准的创建者以很友好的方式来进行计划解释，因此它被构建到当前目录中。在整个 run.sh 脚本上有一个计时器，但获取每一步的中间时间很有用，因此，我们将会编辑 benchmark_run.sql 并利用如下语句，在每个代码块后面插入时间戳：
select current hour to second from systables where tabid=1; |
下一步，利用 run.sh 来启动基准。在单独窗口中启动 onstat -pr。这将会每隔 5 秒种输出一次引擎指标。接下来可以调出基准，对其进行检测，或者干脆回去休息。
首次对账单表的插入（在笔记本电脑中）耗时 1m16s（第二次耗时 1m17s）而且引擎配置文件内容如下：
清单 1. 引擎性能配置文件
IBM Informix Dynamic Server Version 11.50.FC6DE -- On-Line -- Up 1 days 02:35:27 -- 118812 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 175586 178320 4720830 96.28 113398 237194 1002488 88.69 isamtot open start read write rewrite delete commit rollbk 4130803 157 495827 1321784 330055 24 65 55 0 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 112.78 13.67 2 1 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 449 0 2480376 0 0 1 7 28 ixda-RA idx-RA da-RA RA-pgsused lchwaits 28 0 165256 165232 1520 |
此输出类似于汽车油尺。在一个简短的页面中，它捕获了引擎的状态。
第一行（在 Profile 下面）提供了磁盘与缓存性能的相关信息。在此处，我们对读写缓存命中率感兴趣。读命中率应超过 99%，写缓存命中率最好达到 95% 左右。由于我们没有做很多工作，而且缓存数量还没有调整，所有这就可以了。虽然在第一行中，针对引擎的大小来对内存做了考虑（118,812 KB），但是，我们没有使用很多内存，我们为表保持缓存。
第二行（isamtot）指出实际已经完成了多少工作（例如 isamtot 是全部 isam 操作）。
第三行（gp_*）描述了如何利用 Generic Pages 或者非标准页面来管理引擎。由于我们在这一基准中没有采用这一点，所以所读取的都是 0。我会在后续的 dipstick 读取中移除该行。
第四行以超出锁的次数、用户线程或者缓存数来开始（通常所有的 0 会是这样）。后面的两个是 User CPU 与 System CPU 的数量、Checkpoints 的数量、以及 Flushes 的数量。
第五行是我们所关注的：
表 1. 引擎配置文件的第 5 行
| 参数 | 描述 |
|---|---|
| bufwaits | 进程必须等待缓存的次数，是因为没有足够的缓存，还是因为缓存已被别人使用。 |
| lokwaits | 等待要解开的锁的次数。 |
| lockreqs | 所请求的锁的数量（已锁定的行数或页数）。 |
| deadlks | 检测到死锁的次数。 |
| dltouts | 死锁超时的次数。 |
| ckpwaits | 等待检查点的数量。 |
| compress | 压缩的数量（在删除后清理数据页）。 |
| seqscans | 顺序扫描的数量。 |
此处对缓存等待的边际数、以及顺序扫描的边际数感兴趣，而不是大量的锁请求。
第六行指出 read-ahead 如何工作，以及闭锁的数量。引擎消耗了所有索引与数据 read-aheads。同样，我们看到了大量的闭锁请求。
现在让基准运行时间更长一些，来再次进行检查。
在两天中，我的笔记本电脑休眠了多次，因此并没有真正运行 48 小时（虽然引擎认为它一直在运行）。现在再次访问 dipstick，来查看相关内容。
清单 2. 两天以后的性能情况
IBM Informix Dynamic Server Version 11.50.FC6DE -- On-Line -- Up 3 days 05:42:03 -- 127004 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 19966695480 19989379994 60891586765 67.21 660217 892639 3121469 78.85 isamtot open start read write rewrite delete commit rollbk 15495512 9115 1409626 6189708 759769 955295 1112 6097 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 221553.88 127876.85 1136 604 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 364286149 0 59940823034 0 0 51 1707 102128 ixda-RA idx-RA da-RA RA-pgsused lchwaits 224274 0 19966050547 19966274029 4146547 |
我们所要关注的事情已经膨胀失控。（注意：通常检查站等待数可能会暂停，但是，在本例中，基准正运行单个 SQL 进程，因此该进程正在等待自己，而检查点是向磁盘写入的更有效方式，因此不必对此太过乐观。）
读缓存率为 67%。写缓存率为 78%。而我们执行的逻辑 ISAM 指令的数量是 6 百万次读取，锁请求数是（就是所读取的物理指示器）是 590 亿。顺序扫描数、以及缓冲区等待数和闭锁等待数已经失控。我们正执行大量的 read-ahead 操作，其正在被消耗，它的数量很大，但是为什么要做这么多？
好像我们正在进行顺序扫描（对整个表）来满足 SQL 命令的需求，不管它是什么。我们来仔细看一下相关内容。
在 sysmaster 数据库当中有两个我们所关注的表，sysptprof（Partition Profile）与 syssesprof（Session Profile）。现在看一下它们为我们提供的内容：
select * from sysptprof order by abs(seqscans) desc; |
注意：如果顺序扫描数超过 MAXINT（20 多亿），它将变成一个负数 — 因此，我们需要绝对值。以下是该 select 的结果：
清单 3. 表性能配置文件
dbsname benchmark tabname bills partnum 3145799 lockreqs 136990885 lockwts 0 deadlks 0 lktouts 0 isreads 2419074 iswrites 605280 isrewrites 853404 isdeletes 0 bufreads 1085720339 bufwrites 2071693 seqscans 99566 pagreads -1398716020 pagwrites 534809 dbsname benchmark tabname customer partnum 3145795 lockreqs 727188 lockwts 0 deadlks 0 lktouts 0 isreads 99566 iswrites 0 isrewrites 99562 isdeletes 0 bufreads 1898629 bufwrites 99562 seqscans 4 pagreads 727202 pagwrites 99562 |
要注意 pagreads（页读取）如何变成了负数。无法确定该情况在运行当中出现了多少次。同样，lockreqs（所请求）已经翻滚很多倍。
在 102,000 个顺序扫描当中（见上面的 onstat 输出），有 99,000 个是针对该表的。该表具有 ~600,000 行。我们已经针对它发出了 1 亿 3 千 6 百万个锁请求。（事实上，20 分钟后，该数字是 9 亿 3 千 4 百万，这是个问题）。如果采用 99,000*600,000，那么能得到 590 亿 4 千万，该数字，并非巧合，占去了所请求的大部分。换句话说，我们顺序地扫描了此表，锁定了每个行，操作进行了很多次。由于存在 100,000 个客户，所以必须确保已接近当前步骤的尾声。事实上，它正好已经结束：
Real 3113m27.925s User 0m0.006s Sys 23m45.717s |
不幸的是，我们没有看到 Session Profile 表。这将为我们展示类似的信息，但是在会话级别，因此我们会看到会话做了过多的顺序扫描与锁定请求。
我要单独提出第一个问题，因为这一问题出现的比较频繁。我们的第一个主要问题是，当我们想要一行的内容时，我们会扫描整个账单表，并锁定我们读取的每一行。
这一盲目的顺序扫描/锁定掩饰了真实的性能指标。由于我们一遍又一遍地顺序扫描一个巨大的表，这样我们就无法关注读缓存命中率。因此，在查看更加真实的性能指标之前，我们先关注这一问题。
需要花费两天时间来运行的语句是：
update customer
set balance_due = balance_due + ( select sum ( total_bill )
from bills where bills.customer_number = customer.customer_number )
where customer_number in ( select customer_number from bills );
|
如果我们看一下解释计划（位于 sqexplain.out 中），我们就会开始了解该问题（见粗体的注意部分）：
清单 4. 解释计划
update customer
set balance_due = balance_due + ( select sum ( total_bill )
from bills where bills.customer_number = customer.customer_number )
where customer_number in ( select customer_number from bills )
Estimated Cost: 3 -- The optimizer thought this would be cheap
Estimated # of Rows Returned: 10 With only 10 rows affected
1) informix.customer: INDEX PATH Excellent, an index was used to read
this table
(1) Index Name: informix. 101_2
Index Keys: customer_number (Serial, fragments: ALL)
Lower Index Filter: informix.customer.customer_number = ANY subquery
Subquery:
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) informix.bills: SEQUENTIAL SCAN Here is our problem
Filters: informix.bills.customer_number = informix.customer.customer_number
Subquery:
Estimated Cost: 2 The optimizer thinks this will be cheap. Why?
Estimated # of Rows Returned: 1
1) informix.bills: SEQUENTIAL SCAN
[deletia]
|
账单表出了什么问题，使得优化程序认为需要顺序扫描整个表？我们来看一下该表。
Select * from benchmark:systables where tabname = 'bills'; tabname bills rowsize 1266 nrows 0.00 created 08/23/2010 ustlowts |
我已经略去了很多信息，只展示两条最重要的信息。根据系统目录的内容，账单表是空的。“ustlowts” 是空值，这说明了没有再次对该表进行这一统计。事实上，如果我们看一下基准代码，我们就会发现，该表已被创建、填充、并使用，但是统计信息没有被更新。这是 Database 101 填充内容。优化程序采用系统目录，说明那里有多少行，以及构造了较好的索引来计算所采用的查询路径。由于表是 “空” 的，那么很显然读取空表比采用索引更简单。
第二条关键信息是我们正尝试利用 customer_number 来读取账单表。在账单表中不存在 customer_number 上的索引。即使我们对该表更新了统计信息，优化程序也没有选择，只能对其进行顺序扫描。
在我们对基准的第一个改进中，我们向账单表增加索引，并为表更新统计信息。由于索引增加了写入表的成本，所以要在填充完账单表后立即创建索引。在索引创建后，更新统计信息。（注意，在 V11.x 中统计信息将作为索引创建流程的一部分，在索引创建时生成。我们对其进行测试。）
我们增加如下内容：
create index bills_idx1 on bills(customer_number); |
在填充完账单表后，立即利用以下命令来清除状态计数器：onstat -z 并重启动基准。
现在已增加了索引。我们来查看一下表信息：
tabname bills rowsize 1266 nrows 605280.0000000 ustlowts 2010-08-25 20:52:22.00000 |
情况好了很多。统计信息已被更新，不但填充了 nrows，而且我们还看到统计信息最终被更新了。该运行执行情况如下：
清单 5. 更新完统计信息后
Real 36m5.033s User 0m0.005s Sys 0m1.479s dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1397183 1600342 17971316 92.23 656896 885752 3087149 78.72 isamtot open start read write rewrite delete commit rollbk 14888305 284 1354389 6438035 747981 956259 30 170 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 2980.00 119.77 22 20 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 244886 0 10281980 0 0 10 12 71 ixda-RA idx-RA da-RA RA-pgsused lchwaits 479759 64 901978 1381601 12531 |
现在我们已经拥有合理数量指标，并且已经移除了噪音，但是想要调优引擎，仍然言之过早。
对于调优性能，我们能够做的、最有效的就是解决任何数据模型问题。以下是我们当前的数据模型。
图 1. 用于基准数据库的数据模型
我们看到了一些问题。对于初学者，这一账单表中包含了各种不正常数据垃圾。所有的 Customer、State、以及 Product 信息已被丢弃在这里。
图 2. 突出显示多余数据列
所有粗体的项目都是可以很容易地从源表中检索出的多余垃圾。Sales_Tx 令人吃惊，因为在它可以使用的地方，读出了状态表。Product_Price 不属于这里，但是如果我们看到它是如何使用的，会发现它仅是名字起得很差。这实际上是有多少客户被针对该产品记账到这一账单中。它更应该叫做 Billed_Price。
如果我们研究的更深入一点，我们会发现静态数据与动态数据混在了一起。比如，客户名与地址很可能会变。然而，我们当然希望它的 balance_due 是这样的。此外，账单票据通常不会描述一个客户。它们应当描述客户与其账单之间的关系。此外，账单本身已被损坏。在本基准中，我们为每个客户生成了 6 个账单，而不是只有包含 6 行项目的单个账单。了解这一观点后，我们来再次访问这一数据模型。
这是一个修正的模型。它还有问题：Zipcode 已足够识别国家与城市，因此，这些属性应当被迁移到 City 表（而不是状态表），但是该数据是静态的，因此我们可以不必理会。
图 3. 修正数据模型
如果我们要重组架构，那需要一定的工作量，我们应当想要将其包含在基准时间中。注意，针对以上数据模型执行相同的应用程序花费了 16 秒（在数据被重组以后）。
在这一操作之前，我们应当规划好表的大小。然而由于具有大量的区片，Informix 不再具有性能问题，但每个区片的分配也需要一定的时间。最好是正确地规划表的大小，这样就不必在每次插入 20 行时，还要浪费时间来对其进行扩展。
规划表的大小应当：
表 2. 表大小
| 表 | 大小 |
|---|---|
| Bill_Items | 20,416 |
| Billing_Notes | 202,048 |
| Bills | 14,976 |
| Customer | 14,976 |
| Customer_Balance | 1,728 |
Product 与 state 不会改变，因此不必调整其大小。然而，我们将会为其更新统计数据。（注意：这是因为后面还会涉及不必的和无效的变更）。
下一个提高性能的有效方法就是调优应用程序。这符合 “了解数据，了解查询” 规则。我们在进行该操作时，需要关注一下数据模型，因为这在数据模型中有助于应用程序的使用。
应用程序首先做的是为客户生成三组账单，其中包含以 A 开头的 product_code。可能会基于客户的开户日期（>= 1/1/2000，>=1/1/2005，>=1/1/1985）来生成不同数量的账单。根据产品 1、2、4、7、9、10 而不同。看一下这些数据，所有的产品代码都以 A 开头，最晚的开户日期是 1/1/2007。换句话说，我们应当将这三个插入语句合并到一个插入语句中，并且仅需读取一次源表而不是三次。
接下来，应用程序更新 Bills 表，将一个产品打折为 10，其 start_date 为 <= 1/1/2009，其 balance_due 为 > 50000。WHERE 子句是对该条件的重复并增加红色 herring “product number in (1 through 10)”。由于这包括了所有产品，因此没有指向该指示器。大约有 40,000 个客户的 start_date 为 < 1/1/2009，并且其 start_date 为 > 50000。这意味着对表的顺序扫描比索引读取更快，我们无法遵循指示器标准。
接下来，再次更新账单，将总账单设置为价格减去折扣和销售税，这从 state 表读取。红色 herring 的产品号从 1 到 10（换句话说，是全部）。如果在 Bills 表中只剩下了 sales_tax，我们不必获取它，特别是因为我们现在必须通过 customer 表来导航到此处。因此，我们把它送回去。
最后，我们来更新 customer，依据来自 bills 表的 total_bill 值来递增其 balance_due。我们只需更新具有账单的客户。由于他们都有账单，这没有意义。目前我们必须将 Balance_due 放入其自己的表。如果它处于包含 Bills 的表中，我们就避免了附加的更新操作。可以说，每个客户只有一个账单，它应当在那里。
然后我们读取总值，来验证应用程序已正确运行：Record count、sum(product_price)、sum(product_discount)、sum(total_bill)。现在该信息已在 Bill_Items 表中。如果我们在 bill 表中为其生成摘要列，我们就能提高读取速度。当然，这将产生一个不正确的记录值，我们知道那是 #Customers*6，但这是错误的。
我们从 customers 读取了 Count(*) 与 sum(balance_due。由于我们当前在 bills 表中具有 Balance_Due，我们现在应当直接从此处读取。
我们的数据模型就是这样：
图 4. 修正后的数据模型 2
我们将要执行的操作是：
- 扫描 customer 表（6 秒）
- 扫描 State 表（0 秒）
- 连接 Customer 表与 State 表（6 秒）
- 插入到 Bills（7 秒）
- 创建索引（1 秒）
- 加载 bill_items 表（8 秒）
- 在 bill_items 上创建索引（3 秒）
- 更新 bill_items（58 秒）；利用对 customer 的嵌套循环读取，来顺序扫描 bill_items
- 更新 bills（5 秒）；利用对 bill_items 的循环嵌套读取来顺序扫描
- 验证（4 秒）
清单 6. 修正数据模型的结果
Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 721155 734054 10036861 92.82 19724 60567 1450097 98.64 isamtot open start read write rewrite delete commit rollbk 10295768 447 807589 4558564 706236 706197 65 45 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 75.50 42.50 4 3 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 459393 0 7468727 0 0 2 13 22 ixda-RA idx-RA da-RA RA-pgsused lchwaits 605515 0 105695 711210 212 real 1m38.397s user 0m0.007s sys 0m1.510s |
如果我们看一下对 bill_items 的更新，我们所做的就是基于来自 customer 表的信息来更新 discount，这个我们在首次构建 bill_items 表时已经掌握了。我们可利用这个优势，可在首次创建表时设置 discount。以下 CASE 语句用于解决这个问题：
清单 7. CASE 语句
insert into bill_items
(bill_number,
customer_number,
product_number,
billed_price,
product_discount)
select bill_number,
bills.customer_number,
product_number,
product_price,
case when (customer.start_date<="01/01/2009"
and customer.balance_due>50000) then 10
else 0
end case
from bills, product, customer
where product.product_number in (1,2,4,7,9,10)
and bills.customer_number=customer.customer_number;
|
那么如果再次运行呢？
清单 8. 再次运行 CASE 语句
Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 209038 221593 8178955 97.44 25104 49481 844982 97.03 isamtot open start read write rewrite delete commit rollbk 7271368 376 908477 4242538 706230 100913 65 32 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 45.13 3.65 4 3 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 1617 0 6056020 0 0 2 13 10 ixda-RA idx-RA da-RA RA-pgsused lchwaits 1784 0 202678 204462 81 real 0m34.160s user 0m0.006s sys 0m0.050s loaded bills in 3 sec loaded bill_items in 19 sec updated bills in 6 sec verifications 4 sec |
我们来看一下所有这些锁。由于我们没有操控锁，因此引擎在需要一行（或者页）时会对其进行锁定。我们会以独占模式锁定表。注意，这需要采用事务来环绕锁。我们需要关注逻辑日志，并确保我们所掌握的足狗使用，这样我们就不必将其填入到一个长事务中。
在每次插入更新操字段之前，先对表进行独占锁定：
清单 9. 独占锁
78036 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 9943 217306 7344046 99.88 5757 38980 836059 99.31 isamtot open start read write rewrite delete commit rollbk 7270986 496 908610 4241799 706213 100905 54 28 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 35.80 3.90 1 0 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 3 0 3252 0 0 0 11 11 ixda-RA idx-RA da-RA RA-pgsused lchwaits 6 0 0 6 152 real 0m31.829s user 0m0.006s sys 0m0.048s |
我们还忘记了一些应用程序层问题：Informix 不擅长读取与写入大量数据。但是，通过日志数据库，我们正在记录所执行的每个操作。我们可以通过在加载表时，将其类型设置为 raw，来调优大批量插入，然后再将其设置为标准表，这样就可在之上构建索引。将表 Bills 与Bill_Items 变更为 raw，然后再加载 ：
清单 10. 利用 raw 表来加载
Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 849 208192 6732674 100.00 10031 13202 836054 98.80 isamtot open start read write rewrite delete commit rollbk 7474373 370 807441 4042766 706210 100903 49 20 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 31.98 2.32 6 6 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 0 0 2664 0 0 3 11 7 ixda-RA idx-RA da-RA RA-pgsused lchwaits 0 0 0 0 13 real 0m21.686s user 0m0.005s sys 0m0.019s |
我们利用具体例子对相关问题做了详细的说明，并会在后续的文章中继续对引擎相关问题进行深入探讨。我们在本例中所提及的例子介绍了基准的执行，而没有涉及引擎。我们 “更正” 了数据模型以及应用程序，并更新了统计信息。我们采用工具检测到，在对 Informix 的调优实践中，引擎所做的操作都适用。
以下是初始基准 SQL 脚本，以及我们在本文中所做的修订。增加内容标记为粗体而删除内容标记为斜体。
-- File: benchmark_run.sql
-- Date: 4/20/2009
-- Author: Lester Knutsen
-- Description:
-- There are 4 tables in this process to generate bills
-- customer table with 100100 records,
-- states table with sales tax info
-- product table with 10 products and the
-- bills table where the results get written to.
-- Log: Created 4/20/2009
set explain on;
update statistics high for table product(product_number);
update statistics high for table state(state);
update statistics high for table customer(customer_number);
drop table bill_items;
set isolation dirty read;
-- drop and re-create the bills table
drop table "informix".bills ;
-- Note: Type raw and not carrying useless columns around
-- Also got rid of Primary key since we are now a raw table.
create raw table "informix".bills
(
bill_number serial not null ,
customer_number integer,
last_name char(30),
first_name char(30),
address char(50),
city char(20),
state char(2),
state_name char(20),
zip integer,
start_date date,
bill_date date,
bill_notes char(1000),
product_code char(4),
product_number integer,
product_name char(50),
product_price decimal(16,2),
product_discount decimal(16,2),
sales_tx decimal(16,2),
total_bill decimal(16,2),
balance_due decimal(16,2),
primary key (bill_number )
);
-- Note: Bill_items was not part of original data model
create raw table bill_items
(
bill_number integer not null ,
customer_number integer,
product_number integer,
billed_price decimal(16,2),
product_discount decimal(16,2)
) ;
-- Note: Loading Bills in one single statement.
begin work;
lock table bills in exclusive mode;
insert into bills
(bill_number,
customer_number,
bill_date,
sales_tax,
total_bill,
balance_due)
select 0,
customer_number,
today, -- bill_date
state.sales_tax,
0, -- total bill
balance_due
from customer, state
where customer.state=state.state;
alter table bills type(standard);
-- Note: And putting the index back on, effectively updates statistics
create index bill_idx1 on bills(customer_number);
commit;
-- Note: Replaced the next 4 blocks of code with the single block above
-- Create bills for product numbers 1 and 2
insert into bills
(
customer_number,
last_name,
first_name,
address,
city,
state,
state_name,
zip,
start_date ,
bill_date ,
product_code ,
product_number,
product_name ,
product_price ,
product_discount ,
sales_tx,
total_bill
)
select
customer.customer_number,
customer.last_name,
customer.first_name,
customer.address,
customer.city,
customer.state,
state.state_name,
customer.zip,
customer.start_date ,
today, -- bill_date ,
customer.product_code ,
product.product_number,
product.product_name ,
product.product_price ,
0, -- product_discount ,
state.sales_tax,
0 -- total_bill
from customer, state, product
where customer.state = state.state
and customer.product_code[1] = product.product_code[1]
and customer.start_date >= "01/01/2000"
and product.product_number in ( 1, 2 );
Create bills for product number 4 and 7
insert into bills
(
customer_number,
last_name,
first_name,
address,
city,
state,
state_name,
zip,
start_date,
bill_date,
product_code,
product_number,
product_name,
product_price,
product_discount,
sales_tx,
total_bill
)
select
customer.customer_number,
customer.last_name,
customer.first_name,
customer.address,
customer.city,
customer.state,
state.state_name,
customer.zip,
customer.start_date,
today, -- bill_date,
customer.product_code,
product.product_number,
product.product_name,
product.product_price,
0, -- product_discount,
state.sales_tax,
0 -- total_bill
from customer, state, product
where customer.state = state.state
and customer.product_code[1] = product.product_code[1]
and customer.start_date >= "01/01/2005"
and product.product_number in ( 4, 7 );
-- Create bills for product number 9 and 10
insert into bills
(
customer_number,
last_name,
first_name,
address,
city,
state,
state_name,
zip,
start_date ,
bill_date ,
product_code ,
product_number,
product_name ,
product_price ,
product_discount ,
sales_tx,
total_bill
)
select
customer.customer_number,
customer.last_name,
customer.first_name,
customer.address,
customer.city,
customer.state,
state.state_name,
customer.zip,
customer.start_date ,
today, -- bill_date ,
customer.product_code ,
product.product_number,
product.product_name ,
product.product_price ,
0, -- product_discount ,
state.sales_tax,
0 -- total_bill
from customer, state, product
where customer.state = state.state
and customer.product_code[1] = product.product_code[1]
and customer.start_date >= "01/01/1985"
and product.product_number in ( 9, 10 );
Note: Loading Bill_Items with info that used to be in Bills
begin work;
lock table bill_items in exclusive mode;
insert into bill_items
(bill_number,
customer_number,
product_number,
billed_price,
product_discount)
select bill_number,
bills.customer_number,
product_number,
product_price,
case when (customer.start_date<="01/01/2009" and \
customer.balance_due>50000) then 10
else 0
end case
from bills, product, customer
where product.product_number in (1,2,4,7,9,10)
and bills.customer_number=customer.customer_number;
alter table bill_items type(standard);
create index bill_items_idx2 on bill_items(bill_number);
commit;
Note: We took care of this by loading the bill_items table.
-- Update bills - give the best customers a $10 discount
update bills
set product_discount = ( select 10 from customer
where customer.customer_number = bills.customer_number
and customer.start_date <= "01/01/2009"
and customer.balance_due > 50000 )
where bills.customer_number in ( select customer_number
from customer where customer.start_date <= "01/01/2009"
and customer.balance_due > 50000 )
and product_number in ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 );
-- Update bills - calculate the total with sales tax and minus the discount
-- Note: Similar, but taking advantage of our new data model.
begin work;
lock table bills in exclusive mode;
update bills
set total_bill = (( (select sum(bill_items.billed_price-product_discount)
from bill_items
where bills.bill_number=bill_items.bill_number)) * ( 1 + sales_tax )) ,
balance_due=balance_due +
(( (select sum(bill_items.billed_price-product_discount)
from bill_items
where bills.bill_number=bill_items.bill_number)) * ( 1 + sales_tax )) ;
commit;
update bills
set total_bill = (( product_price - product_discount ) \
* ( 1 + ( select sales_tax from state where bills.state = state.state ))) \
where product_number in ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 );
Note: Not needed since we have balance in the bills table.
-- Update the customer balance for customers who just got a bill
update customer
set balance_due = balance_due + ( select sum ( total_bill ) \
from bills where bills.customer_number = customer.customer_number )\
where customer_number in ( select customer_number from bills );
-- Calculate the totals - This must match the expected results
-- Note: Modified to read values out of new structures.
select count(*) record_count,
sum ( billed_price ) sum_product_price,
sum ( product_discount) sum_product_discount
from bill_items;
select count(*) recourd_count,
sum ( product_price ) sum_product_price,
sum ( product_discount) sum_product_discount,
sum ( total_bill ) sum_total_bill
from bills;
Note: We had to move the total bill down to this section since
that data is now at the bill level.
Note also: Because there are 4 states in the original customer table
which do not exist in the State table, the number of bills will not
match the number of customers (off by 120) and the balance_due as
computed from bills will not include the 6044509.99 balance due from
these customers. This could of course be corrected, but that is
beyond the scope of this effort.
select count(*) customer_count,
sum(total_bill) sum_total_bill,
sum ( bills.balance_due )
from bills;
-- Perform a checkpoint to make sure all data is written to disk -
-- This is required ;)
execute function sysadmin:task ( 'onmode', 'c' );
-- End of SQL script
|
学习
- 深入了解由 Advanced DataTools 提供的 Fastest Informix DBA Contest。
- 在 developerWorks 中国网站 Information Management 专区 深入了解 Information Management。找到技术文档、how-to 文章、教育、下载、产品信息等等。
- 关注
developerWorks 技术事件 与 网络广播。
获得产品和技术
- 直接从 developerWorks 下载的 IBM 产品评估试用版软件 来构建您的下一个开发项目。
讨论
- 参与论坛讨论。
- 查看
developerWorks 博客 并加入 developerWorks 中文社区，developerWorks 社区是一个面向全球 IT 专业人员，可以提供博客、书签、wiki、群组、联系、共享和协作等社区功能的专业社交网络社区。
标签
使用 滑动条 调节标签的数量。
热门标签 显示了特定专区最受欢迎的标签（例如 Java technology，Linux，WebSphere）。
我的标签 显示了特定专区您标记的标签（例如 Java technology，Linux，WebSphere）。