如何找出已删除的行? [英] How can I find out which rows have been deleted?

问题描述

带有SE引擎的Informix-SQL 7.32:

Informix-SQL 7.32 with SE engine:

我有一个客户从SE表中删除了几行. (我不使用事务日志记录或审计).该表具有一个串行列.我想创建一个Ace报告来打印缺少的序列列.我尝试了以下快速而肮脏的报告，但没有成功！..您能建议一种更好的方法吗?

I have a customer who deleted several rows from an SE table. (I'm not using transaction logging or audit). The table has a serial column. I would like to create an Ace report to print the missing serial columns. I tried the following quick and dirty report, but it didn't work!.. can you suggest a better way?

define
variable next_id integer
end

  select tbl_id 
    from tbl
order by tbl_id {I'm ordering tbl_id because all the rows are periodically re-clustered}
     end        {by an fk_id in order to group all rows belonging to the same customer}

format
on every row
let next_id = tbl_id + 1  

after group of tbl_id
if tbl_id + 1 <> next_id then
print column 1, tbl_id + 1 using "######"

end

或创建一个包含INT列的临时表，该列包含从1到5000的序号，并执行以下select语句:

or maybe create a temporary table with an INT column containing sequential numbers from 1 to 5000 and do a select statement like:

   SELECT tbl_id 
     FROM tbl
    WHERE tbl_id NOT IN
                 (SELECT tmp_int
                    FROM tmp);

或带有HAVING，OUTER等的select语句.

or a select statement with HAVING, OUTER, etc.

推荐答案

由于这是SE，因此我们必须使用老式的表示法，而不是SQL-92 JOIN表示法.

Since this is SE, we have to use the old-fashioned notation, not the SQL-92 JOIN notations.

以下四个查询是两个可能答案的共同基础:

The four queries that follow are a common foundation for the two possible answers:

SELECT t1.tbl_id AS tbl_id, t2.tbl_id AS ind
  FROM tbl AS t1, OUTER tbl AS t2
 WHERE t1.tbl_id + 1 = t2.tbl_id
  INTO TEMP x1;

SELECT t1.tbl_id AS tbl_id, t2.tbl_id AS ind
  FROM tbl AS t1, OUTER tbl AS t2
 WHERE t1.tbl_id - 1 = t2.tbl_id
  INTO TEMP x2;

SELECT tbl_id AS hi_range
  FROM x1
 WHERE ind IS NULL
  INTO TEMP x3;

SELECT tbl_id AS lo_range
  FROM x2
 WHERE ind IS NULL
  INTO TEMP x4;

表x3和x4现在分别包含tbl_id的值，这些值没有立即后继者，也没有立即前任者.每个值都是tbl_id值的连续范围的开始或结束.在IDS而不是SE中，可以使用标准的SQL OUTER JOIN表示法，并在两个查询而不是四个查询中过滤连接的结果.您在SE中没有那么奢侈.

The tables x3 and x4 now contain (respectively) the values for tbl_id that have no immediate successor and no immediate predecessor. Each value is the start or end of a contiguous ranges of tbl_id values. In IDS instead of SE, you could use the standard SQL OUTER JOIN notation and filter the results of the join in two queries instead of four; you do not have that luxury in SE.

现在，您只需要弄清楚如何合并两个表:

Now you just have to work out how to combine the two tables:

SELECT t1.lo_range, t2.hi_range
  FROM x4 AS t1, x3 AS t2
 WHERE t1.lo_range <= t2.hi_range
   AND NOT EXISTS
       (SELECT t3.lo_range, t4.hi_range
          FROM x4 AS t3, x3 AS t4
         WHERE t3.lo_range <= t4.hi_range
           AND t1.lo_range =  t3.lo_range
           AND t2.hi_range >  t4.hi_range
       );

此查询的主要部分出现两次，并生成范围的开始小于或等于范围的末尾的所有成对的行(等于允许范围"本身由一个值组成，并带有两侧的已删除行). NOT EXISTS子句可确保没有其他对具有相同的起始值和较小的终止值.

The main part of this query occurs twice and generates all pairs of rows where the start of the range is less than or equal to the end of the range (equal allows for 'ranges' consisting of one value on its own, with deleted rows on either side). The NOT EXISTS clause ensures that there is no other pair with the same start value and a smaller end value.

如果数据中有很多空白，则对临时表的查询可能不会很快.如果差距很小，那就应该可以了.

The queries on the temp tables may not be very fast if there are many gaps in the data; if there are very few gaps, then they should be OK.

最后一个查询在范围数方面表现出二次行为.当我只有十几个范围时，一切都很好(响应时间不到一秒)；当我有1200个射程时，那还不行-没有在合理的时间内完成.

The last query exhibits quadratic behaviour in terms of the number of ranges. When I had just a dozen ranges, it was fine (sub-second response time); when I had 1,200 ranges, it was not OK - did not complete in a reasonable time.

由于二次行为不好，我们该如何重新表述查询...

Since quadratic behaviour is not good, how can we rephrase the query...

对于范围的每个下限，请找到大于或等于下限的范围的最小上限，或者在SQL中:

For each low end of the range, find the minimum high end of a range that is greater than or equal to the low end, or in SQL:

SELECT t1.lo_range, MIN(t2.hi_range) AS hi_range
  FROM x4 AS t1, x3 AS t2
 WHERE t2.hi_range >= t1.lo_range
 GROUP BY t1.lo_range;

请注意，可以轻松将其合并到ACE报告中.它为您提供了存在的数字范围-而不是缺少的范围.您可以弄清楚如何生成另一个.

Note that this can easily be incorporated into an ACE report. It gives you the ranges of number present - not those which are absent. You can work out how to generate the other.

在具有22100行，包含1200个间隔的数据的表上，该表的性能很好.在基准模式(-B)下使用(my)SQLCMD程序，并将SELECT输出发送到/dev/null，并使用IDS 11.70.FC1在MacOS X 10.6.7(MacBook Pro，3 GHz的Intel Core 2 Duo和4 GB RAM)，结果为:

That performed pretty well on a table with 22100 rows containing 1200 gaps in the data. Using (my) SQLCMD program in its benchmark mode (-B), and sending SELECT output to /dev/null, and using IDS 11.70.FC1 run on MacOS X 10.6.7 (MacBook Pro, Intel Core 2 Duo at 3 GHz and 4 GB RAM), the results were:

$ sqlcmd -d stores -B -f gaps.sql
+ CLOCK START;
2011-03-31 18:44:39
+ BEGIN;
Time: 0.000588
2011-03-31 18:44:39
+ SELECT t1.tbl_id AS tbl_id, t2.tbl_id AS ind
  FROM tbl AS t1, OUTER tbl AS t2
 WHERE t1.tbl_id + 1 = t2.tbl_id
  INTO TEMP x1;
Time: 0.437521
2011-03-31 18:44:39
+ SELECT t1.tbl_id AS tbl_id, t2.tbl_id AS ind
   FROM tbl AS t1, OUTER tbl AS t2
  WHERE t1.tbl_id - 1 = t2.tbl_id
   INTO TEMP x2;
Time: 0.315050
2011-03-31 18:44:39
+ SELECT tbl_id AS hi_range
  FROM x1
 WHERE ind IS NULL
  INTO TEMP x3;
Time: 0.012510
2011-03-31 18:44:39
+ SELECT tbl_id AS lo_range
  FROM x2
 WHERE ind IS NULL
  INTO TEMP x4;
Time: 0.008754
+ output "/dev/null";
2011-03-31 18:44:39
+ SELECT t1.lo_range, MIN(t2.hi_range) AS hi_range
  FROM x4 AS t1, x3 AS t2
 WHERE t2.hi_range >= t1.lo_range
 GROUP BY t1.lo_range;
Time: 0.561935
+ output "/dev/stdout";
2011-03-31 18:44:40
+ SELECT COUNT(*) FROM x1;
22100
Time: 0.001171
2011-03-31 18:44:40
+ SELECT COUNT(*) FROM x2;
22100
Time: 0.000685
2011-03-31 18:44:40
+ SELECT COUNT(*) FROM x3;
1200
Time: 0.000590
2011-03-31 18:44:40
+ SELECT COUNT(*) FROM x4;
1200
Time: 0.000768
2011-03-31 18:44:40
+ SELECT t1.lo_range, MIN(t2.hi_range) AS hi_range
  FROM x4 AS t1, x3 AS t2
 WHERE t2.hi_range >= t1.lo_range
 GROUP BY t1.lo_range
 INTO TEMP x5;
Time: 0.529420
2011-03-31 18:44:40
+ SELECT COUNT(*) FROM x5;
1200
Time: 0.001155
2011-03-31 18:44:40
+ ROLLBACK;
Time: 0.329379
+ CLOCK STOP;
Time: 2.202523
$ 

它将成功；不到几秒钟的处理时间.

It will do; less than a couple of seconds processing time.

这篇关于如何找出已删除的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！