CREATE TABLE TEST
(
  F_Code           INTEGER,             //主键
  F_DATETIME           TIMESTAMP(6),        //时间
  F_FLAG        CHAR(1),        //标记
  C_0001              FLOAT(126),        //数据1
  C_0002              FLOAT(126),        //数据2
  C_0003              FLOAT(126)        //数据3
)
业务上数据列（C_0001等）很多，在此进行了简化
时间列（F_DATETIME）记录了绝对时间 唯一
标记列（F_FLAG）只有两个值 0或1
在时间和标记列上都建立索引
数据量很大， 600万左右

其中标记列里大概有100个 值为1的数据，其他的都为0

需求：
按时间排序，
取出标记列为1的记录及距离此记录上下都为n的记录（即共2n+1条记录，n可变化，在1000以内)
在取出的记录上对数据C_0001，C_0002，C_0003等进行平均

个人思路：
1对表进行时间排序
2生成按时间排序后的rownum
3取出标记为为1的记录的rownum
4对rownum 进行+-n的计算
5取出rownum-n 到rownum+n 之间的所有记录 加平均函数

问题是 第1，2步中执行了全表扫描，数据量这么大的情况下，耗时十几分钟或更长


求优化思路，谢！~

用分析函数

Using Oracle's built-in Lead and Lag functions, it is easy to create reports
which display next / previous values even when the primary key is not consecutive
(i.e. dates, week days).







set pagesize 110 ;

create table regional_sales  (
   sales_id         integer,
   sales_month_dt   date,
   total_sales      number
);


insert into regional_sales values (101, '1-jan-04', 1000) ;
insert into regional_sales values (102, '1-feb-04', 2000) ;
insert into regional_sales values (103, '1-mar-04', 3000) ;
insert into regional_sales values (104, '1-apr-04', 4000) ;
insert into regional_sales values (105, '1-may-04', 5000) ;
insert into regional_sales values (106, '1-jun-04', 6000) ;
insert into regional_sales values (107, '1-jul-04', 7000) ;
insert into regional_sales values (108, '1-aug-04', 8000) ;
insert into regional_sales values (110, '1-sep-04', 9000) ;
insert into regional_sales values (112, '1-oct-04', 10000) ;
insert into regional_sales values (114, '1-nov-04', 11000) ;
insert into regional_sales values (119, '1-dec-04', 12000) ;

commit ;

select sales_id,  lead (total_sales)  over (order by sales_id)  as lead_val
from regional_sales  ;


   SALES_ID   LEAD_VAL
---------- ----------
        101       2000
        102       3000
        103       4000
        104       5000
        105       6000
        106       7000
        107       8000
        108       9000
        110      10000
        112      11000
        114      12000
        119

分析函数只能对全表？

我这里加了一个F_FLAG =‘1’ 条件，
然后在用分析函数的话，就找不到临近记录了，

还是俺不太会用？

是俺不太会用

版主的办法应该是没错，ORACLE的分析函数感觉效率肯定比自己写的要高一些，
对于你的特殊需求，稍微灵活一下不就可以了吗？先把符合条件的记录找到，用子查询，然后再用分析函数，

QUOTE:

---

原帖由 hotiice 于 2008-6-24 13:36 发表
是俺不太会用

---

right~貌似是窗口函数~

时间列（F_DATETIME）记录了绝对时间 唯一
标记列（F_FLAG）只有两个值 0或1
在时间和标记列上都建立索引

复合索引还是分别建索引？

另外，分析函数中可以使用
over（order by xx range between 10 preceding and 10 following）
每行对应的数据窗口是之前行幅度值不超过10，之后行幅度值不超过10
over（order by xx rows between 10 preceding and 10 following）
每行对应的数据窗口是之前10行，之后10行

看看对你是否有帮助

谢谢大家的回复，

To 6楼 现在的主要问题是 找到符合条件的记录时有效率问题

To 8楼 分别建立了索引 不是复合索引
虽然也觉得在F_FLAG列上建立索引有些 不妥 但数据量太大了，为了快速查询，只考虑到了这么解决（如有问题请指教）

另：对于分析函数 是不是 如果含有where 字句，则只对通过where 查询后的结果 应用分析？

我的需求是需要有where 字句来确定要分析的点，然后确定此点周围的记录，在进行分析....


仍在思考中,再次感谢大家！

f_flag列上建立索引，可以参考tom的expert one by one上的一段话

Previously, I said that you can use to your advantage the fact that totally null entries are
not stored in a B*Tree index—here is how. Say you have a table with a column that takes
exactly two values. The values are very skewed; say, 90 percent or more of the rows take
on one
value and 10 percent or less take on the other value. You can index this column efficiently to
gain quick access to the minority rows. This comes in handy when you would like to use an
index to get to the minority rows, but you want to full scan to get to the majority rows, and you
want to conserve space. The solution is to use a null for majority rows and whatever value
you want for minority rows or, as demonstrated earlier, use a function-based index to index
only the non-null return values from a function.

另：对于分析函数 是不是 如果含有where 字句，则只对通过where 查询后的结果 应用分析？
恐怕得通过嵌套去处理了