|
博客 | Linux | 论坛 | 人才 | 培训 | 知识库 | 资料 | 读书 | 手册 | 精华 | 下载 | 沙龙 | 搜索 |
 |
| 首页 ‖ 新闻 ‖ ASE ‖ PB/PD ‖ Sybase IQ ‖ iAnywhere ‖ RS ‖ 论坛 ‖ 专家访谈 ‖ 下载 ‖ SDN专区 |
|
了解ASE中的外键验证技术
看来并非所有的用户都了解有关 ASE 中的外键技术的一些问题。本文将通过说明一些鲜为人知的外键限制问题以及用于验证外键的各种查询，尽力填补这一知识空缺。我们还将演示一种用来分析和综合临时表的技术。 外键和 ANSI 外部连接 假定我们有两个名为 FK 和 PK 的表(其意图很明显)。表 FK 可以改动，这样 PK 就成了 FK 的主键表。或者，如果外键已经存在，则用批量拷贝程序 (BCP) 将数据复制到表 FK 即可。在其中任何一种情况下，外键限制都是无效的，尽管这些操作没有任何错误。 我们如何确定是否存在无效外键呢？这可以通过使用 not in, not exists 语句、含有 group by 子句的外部连接或临时表来完成。不过，对 ASE 15.0 版以下的用户而言，最有效的途径是使用 ANSI 外部连接语法。(有关详细信息，请参阅 Rob Verschoor 的《Tips, Tricks & Recipes for Sybase ASE》一书，其中包括所有外键列均不可为空时的性能比较。) 假设表 FK 包含可以为空的列 —A 和 B，并且与此对应的名称相同的列形成了表 PK 的唯一键或主键，则找出 FK 表中的无效外键所需的查询为： select FK.A, FK.B from FK left join PK on coalesce (FK.A, PK.A) = PK.A and coalesce (FK.B, PK.B) = PK.B where PK.A is NULL and PK.B is NULL and FK.A is not NULL and FK.B is not NULL 什么是有效的外键值？ 由于引用约束，您期望现在可以“清除”任何新数据。不过，ASE 仅支持最低外键要求。如果新行的 FK.A 或 FK.B 为 NULL，则(在检查或规则约束的限制范围内)另外一列可以是任意值！该行为符合 ANSI SQL-92 标准。 如果存在可以为空的外键列，笔者当然喜欢有更多限制的行为。ANSI 为外键定义确定了两个附加选项，不过大多数 RDBMS 均不支持这两个选项。(根据 ISUG 的增强功能请求 2296，Sybase 计划将来支持下面的两个 ANSI 选项。)部分匹配 (Match Partial) 意味着对于包含任何 NULL 子键的行，该行的非 NULL 子键必须至少要有一个包含相同值的对应主键行。完全匹配 (Match Full) 则意味着只有在所有子键均为 NULL 时，才允许有“NULL 行”。如果需要其中一个标准的话，您必须在外键表上创建或修改 insert 和 update 触发器。主键表上还需要有 Delete 和 update 触发器。 现在，我们假设所有脏数据已经修改过，并且除外键限制外还创建了触发器。针对上述选项，我们回到“如何确定是否存在无效外键？”这一问题上来。请执行下列符合部分匹配条件的语句： select FK.A, FK.B from FK left join PK on coalesce (FK.A, PK.A) = PK.A and coalesce (FK.B, PK.B) = PK.B where PK.A is NULL and PK.B is NULL and ((FK.A is not NULL) or (FK.B is not NULL)) 请执行下列符合完全匹配条件的语句： select FK.A, FK.B from FK left join PK on FK.A = PK.A and FK.B = PK.B where PK.A is NULL and PK.B is NULL and ((FK.A is not NULL) or (FK.B is not NULL)) 若 FK.A 不可为空，则应用 FK.A 来取代合并(FK.A、PK.A)，并且去除这些查询中(FK.A 不可为 NULL)的条件。这会使性能有很大差别，因为上述 SQL 示例会用到表扫描，但若是对不可为空的列进行了建议的 SQL 更改，则将使用索引。 已经写入的存储过程将对指定引用约束、外键表或整个数据库中的任意一个执行检查。这些检查将适当的在内部生成上述命令，并通过 execute immediate 来执行它们。(代码可在网站 www.sybase.com/developer/codexchange 的 ASE/ Management/Administration 部分中找到。) 权限问题 现在，我们来考虑一个比较深奥的问题。不管是否存在外键限制，表 FK 的所有者可能并不具备对表 PK 的选择权限。表 PK 的所有者只能向表 FK 的所有者提供对列 A 和 B 的引用权限。在这种情况下，表 FK 的所有者无法验证其数据，因为上述查询是动态生成的，在执行时需要有对 PK.A 和 PK.B 的选择权限。对于非系统管理员的用户，最好先检查一下是否具备对 FK 和 PK 列的选择权限。(在这里，笔者假设两个表处于同一数据库中。否则，仅用 SQL 来确定是否具备选择权限是不可行的。)如果提供了外键限制，则可以通过 sysreferences 表来确定主键表和列 ID (colid) 或列名称。随即，通过下列规则在 sysprotects 表中确定表或列的权限(例如，select)： _ 如果用户拥有该表，那么其便拥有权限(除非他撤销了自己的隐式权限)。 _ 此外，如果用户拥有对有效角色的授权行，那么其便拥有权限。 _ 此外，如果用户拥有授权行，那么其便拥有权限。 _ 此外，如果对用户所属的组拥有授权行，并且没有对其撤销行，那么该用户便拥有权限。 使用字符串自变量/参数代替临时表 上面已指出有三个(约束、表或数据库)存储过程，将存储过程(比方说，sp_object_access)进行编码以便仅检查对 FK 或 PK 列的权限时，应很好地遵循模块化规定。这使我们可以将 colid(列名称)列表作为单列临时表传递。从 SQL-BackTrack 逻辑转储/负载来看，正如我们很多人都知道，这会引出其他麻烦。逻辑转储/负载无法重新创建存储过程子例程，该子例程是由其他存储过程创建的临时表决定的。 不过，很容易创建一个字符变量 @clist，它包含用逗号隔开的列 ID(名称)以下是外键表的 SQL 示例。 select @clist = fokey1 || ‘,’ || fokey2 || ‘,’ || . . . fokey15 || ‘,’ || fokey16 || ‘,0’ from sysreferences where constrid = select @clist = left (@clist, charindex (‘,0’, @clist) - 1) 之后，便可以通过含有 @tableid(即外键表的对象 id)以及 @clist 参数的 sp_object_access 命令有效地构建临时表： select @list = @clist -- WANT AN UPDATEABLE COPY OF @clist insert into #oa_colids -- TINYINT COLUMNS colid AND granted select 0, 0 from syscolumns where id = @tableid update #oa_colids set colid = case (charindex (',', @clist)) when 0 then 0 else convert (tinyint, left (@clist, charindex (',', @clist) - 1)) end, @list = substring (@clist, charindex (',', @clist) + 1, 512) delete #oa_colids where colid = 0 在整个表格形成此键的情况下，该 insert 语句将提供足够的行。update 语句将通过表扫描对 #oa_colids 进行更新。行之间的 @list 值和大小写操作值会有所改变，这两个赋值语句的顺序很重要。delete 语句会删去所有不需要的行。 sp_object_access 的剩余部分将通过 @tableid 、proc_role 功能以及若干系统表来更新已授权的列值。更新结束时，将返回存取指示符(若 #oa_colids 中任意获得授权的值为 0，则指示符为 0；否则，为 1)。值 1 的指示符表明，该选择权限已经足够。上述技术还可用于 到目前为止，改变页面大小所造成的影响最大。最小的空间分配单元为：八个数据页大小。因而，2K 的页面所分配的空间为 16K，4K 的页面所分配的空间为 32K，8K 的页面所分配的空间为 64K，而 16K 的页面所分配的空间为 128K。较大的数据集会从较大的页面中受益，但较小的数据集会影响性能。 如果您的表格很小，比方说只有几行，它仍将占用某个页面限度的最小空间。因此，页面大小 16K 与 128K 之间的差异变得更加明显。如果最初该数据全部位于一个 2K 的页面上，将其移至 16K 的页面上就意味着您最终会浪费 126K 的空间。 如果您有很多这样的小表格，则最终结果不言而喻。这也同样适用于索引。索引有它们自己的限度。请相应地计划您修改的容量。 如果您打算换用较大的页面以容纳几个较大的表格，则最好在转至最大的页面之前，先尝试使用一个中间尺寸的页面。另一方面，如果您想容纳较宽的行，则修改数据模型也是一种替代方案。此外，牢记大页面对数据高速缓存的影响。内存始终按照逻辑单位 2K 来分配。使用较大的页面意味着，当您使用同等大小的缓存时，只有少量的缓冲区可供使用。您需要对这些项进行调整，可向缓存增加更多内存以进行补偿。 移植到其他供应商的数据库产品中也会导致意外的容量变化。请查阅相关文档，并向对这些产品拥有丰富经验的 DBA 咨询。举例来说，笔者曾经有一位客户，他拥有一个 2GB 小数据库。三年来，该数据库在使用过程中从未出现过容量问题。1999 年，第三方供应商通知我的客户，他们遇到了 Y2K 问题，而他们的产品升级版本不再在后台支持 Sybase ASE。该供应商建议将数据库移植到 Oracle 中。由于我对此一窍不通，于是请了一位 Oracle DBA。他建议使用 6GB 的 Oracle 数据库。 起初，我对其中的原因感到很困惑，并惊讶地发现在六个月的移植过程中，该数据库大小将增大至 9GB。一年后，我先前的客户好不容易将容量再次修改为 18GB。读过 Brian Ceccarelli 在他的网站(网址为 www.talus software.com)上发表的评论后，我现在知道其中的原因了。看来 Oracle 没能很好地利用本地的数据类型。这个经历清楚地表明，DBA 为什么在移植时始终要将此类容量规划问题牢记在心。 注：有些人在回顾本文前半部分随附的脚本示例时，发现笔者无意中假定存储过程 get_database_summary(在文件 dbsummary.sql 中)的页面大小为 2K。脚本以及 ISUG 网站发布的版本已经对此处进行了更正。 结束语 正如本文的第一部分所提到的，笔者个人使用的是用 Perl 编写的脚本。该进程的自动化程度比我们所介绍的进程要高。不过，基本构造块这里都有，它足以为更加复杂的系统奠定基础。有时您会很被动(tempdb 填充或日志出人意料变满)，大多数情况下您应该尝试后退一步，找出该问题的实际根源，而不是继续处理紧急事件。总体目标是帮助 DBA 更加主动地管理他们的系统。 _ N 列表可传递 N varchar 参数，且 update 语句中含有 2N 赋值子句。笔者还没有尝试将该技术用于多列表或可以为空的数据类型。 在确定索引的选择性时，也有检查列权限的类似需要：如果提供了全部索引键的有效值，则可通过 select 子句返回行的平均数。(有时会将选择性定义为百分比。)唯一索引具有最佳的非寻常选择性值 1。可通过以下命令确定选择性： select selectivity = avg (count ( from
|
|
|
