继续-在查询完成前查看第一行

我已经确定了我的用户通常使用的查询结构。对于我来说,创建复合索引来支持这些构造并提供第一行功能是否有意义?

如果我从SE迁移到IDS,我将失去使用C-ISAM调用编写低级函数的能力,但会获得第一行以及其他优点,如:索引扫描的设置读取(onconfig use_[ko]batchedread)、优化器指令、并行查询等。

评论中的信息

当铺生产表的查询依据:customer.name char(30),使用通配符(las) 苏尔 f*要查找姓氏、名字)或通过Pawns.Ticket_number int查询。客户和Pawns的连接方式为:customer.name=Pawns.name,not customer.serial=Pawns.fk。trx日期大于1年的当铺将移动到另一个硬盘上的另一个数据库中的历史表(>500k nrows)。历史索引按trx_日期降序排列。这就是即席复合查询构造发挥作用的地方。

一旦找到客户的典当交易,当客户进行Intrest或赎回Pymt时,该行将更新。如果客户在90天内不制作Pymt,用户将手动更新将要没收的典当。当铺。当客户赎回当铺或因缺少Pymt而没收当铺时,状态变为不活动。当他们的trx日期超过1年时,不活动的人会从当铺表移到历史表中,因此在这个应用程序中不会发生大规模更新。当铺每天早上开业前都会运行这个程序。

{ISQL 2.10.06E (SE-DOS16M protected mode) pawns table optimization - 
 once-daily, before start of business, procedure}

 unload to "U:\UNL\ACTIVES.UNL"
    select * from pawns where pawns.status = "A"
  order by pawns.cust_name, pawns.trx_date;

 unload to "U:\UNL\INACTIVE.UNL"
    select * from pawns
     where pawns.status <> "A"
       and pawns.trx_date >= (today - 365)
  order by pawns.cust_name, pawns.trx_date desc;

 unload to "U:\UNL\HISTORIC.UNL"
    select * from pawns
     where pawns.status <> "A"
       and pawns.trx_date < (today - 365)
  order by pawns.trx_date desc;

 drop table pawns;

 create table pawns
 (
     trx_num serial,
     cust_name char(30),
     status char(1),
     trx_date date,
 . . . ) in "S:\PAWNSHOP.DBS\PAWNS";

 load from "U:\UNL\ACTIVES.UNL" insert into pawns;         {500:600 nrows avg.}
 load from "U:\UNL\INACTIVE.UNL" insert into pawns;        {6500:7000 nrows avg.}
 load from "U:\UNL\HISTORIC.UNL" insert into dss:historic; {>500K nrows}

 create cluster index pa_cust_idx on pawns (cust_name);

 {this groups each customers pawns together, actives in
  oldest trx_date order first, then inactive pawns within the last year in most 
  recent trx_date order. inactives older than 1 year are loaded into historic 
  table in a separate database, on a separate hard disk. historic table 
  optimization is done on a weekly basis for DSS queries.}

 create unique index pa_trx_num_idx on pawns (trx_num);
 create index pa_trx_date_idx on pawns (trx_date);
 create index pa_status_idx on pawns (status);

 {grant statements...}

 update statistics;