都2周了，还是找不到好的办法，集思广益，有思路就给分

[leborety]

我有张大表，记录为5百万条，这表对id和create_time做了4个hash分区。（表可以加索引，但其他都不允许改）


需要使用的字段有(asc_code,vin,bl_time,phone)
asc_code有400个，bl_time的时间范围在2年。字段上都有索引。
条件为，
1.对于同一个asc_code，vin和phone都不可以重复
2.祛除重复值后，对asc_code进行采样（随机取标本数，例如，随机对每个asc_code只取7条记录）


我现在的情况描述
1。我用一条sql语句，可以完成上述步骤，都用了分析函数，因为表较大，其排序会花费很长时间。我的时间取一个月，asc_code为全部，花费时间为5分钟左右。3个月的，我都没完成就点了cancel，但需要查询范围可以为一年。

2。前台为jsp，在5分钟内没响应就会超时。

3。前台需要把查询出的结果写文件。

4。如果查询时间为一年，我的cost为22万，代价就在上面的2个排序上。


请大家给点建议，什么都行，分区啊，索引啊，物化视图都行，但最好给出详细可行的。

暂时不贴sql语句，看大家需要再贴

谢各位看帖

[leborety]

还是给出sql和plan

/* Formatted on 2005/12/01 10:57 (Formatter Plus v4.8.6) */
SELECT *
FROM (SELECT g.*,
ROW_NUMBER () OVER (PARTITION BY g.asc_code, g.series ORDER BY SYS_GUID
()) rd
FROM (SELECT t.*,
ROW_NUMBER () OVER (PARTITION BY asc_code, vin ORDER BY asc_code)
rn_vin,
ROW_NUMBER () OVER (PARTITION BY asc_code, NVL2
(deliverer_phone,
deliverer_ddd_code
|| deliverer_phone,
deliverer_mobile
) ORDER BY asc_code)
rn_phone
FROM (SELECT a.asc_code, c.asc_name, a.deliverer,
a.deliverer_ddd_code, a.deliverer_phone,
a.deliverer_mobile, a.owner_name, a.model,
a.license, a.vin, c.company_address, a.series
FROM tt_sgm_asc_repairorder a,
tm_sgm_asc_ascbasicinfo c,
tt_sgm_sys_deparasc da
WHERE a.asc_code = c.asc_code
AND a.asc_code = da.asc_code
-- and a.asc_code='2100121'
AND c.asc_status = 1
AND a.balance_time >=
TO_DATE ('20040130', 'yyyymmdd')
AND a.balance_time <
TO_DATE ('20051201', 'yyyymmdd')
AND deliverer IS NOT NULL
AND ( LENGTH (deliverer_phone) >= 7
OR deliverer_mobile IS NOT NULL
)
AND series IS NOT NULL
AND a.BRAND = 'BUICK'
AND da.DEPARTMENT_ID IN (
SELECT D.DEPARTMENT_ID
FROM TS_SGM_SYS_DEPARTMENT D
START WITH D.DEPARTMENT_ID IN (
SELECT UD.DEPARTMENT_ID
FROM TT_SGM_SYS_USERDEPARTMENT UD
WHERE UD.USER_ID IN (47))
CONNECT BY PRIOR D.DEPARTMENT_ID =
D.PARENT_DEPARTMENT)) t) g
WHERE g.rn_vin = 1 AND g.rn_phone = 1)
WHERE rd <= 7

[leborety]

SELECT STATEMENT, GOAL = CHOOSE Cost=218070 Cardinality=759435 Bytes=283269255 IO cost=218070 Optimizer=CHOOSE
VIEW Object owner=OWSGM Cost=218070 Cardinality=759435 Bytes=283269255
WINDOW SORT PUSHED RANK Cost=218070 Cardinality=759435 Bytes=273396600 IO cost=218070
VIEW Object owner=OWSGM Cost=108597 Cardinality=759435 Bytes=273396600
WINDOW SORT PUSHED RANK Cost=108597 Cardinality=759435 Bytes=162519090 IO cost=108597
WINDOW SORT Cost=108597 Cardinality=759435 Bytes=162519090 IO cost=108597
HASH JOIN SEMI Cost=3380 Cardinality=759435 Bytes=162519090 IO cost=3380
HASH JOIN Cost=919 Cardinality=759435 Bytes=152646435 IO cost=919
TABLE ACCESS FULL Object owner=OWSGM Object name=TT_SGM_SYS_DEPARASC Cost=2 Cardinality=413 Bytes=6195 IO cost=2 Optimizer=ANALYZED
HASH JOIN Cost=880 Cardinality=759435 Bytes=141254910 IO cost=880
TABLE ACCESS FULL Object owner=OWSGM Object name=TM_SGM_ASC_ASCBASICINFO Cost=4 Cardinality=138 Bytes=9660 IO cost=4 Optimizer=ANALYZED
TABLE ACCESS BY INDEX ROWID Object owner=OWSGM Object name=TT_SGM_ASC_REPAIRORDER Cost=826 Cardinality=1583228 Bytes=183654448 IO cost=826 Optimizer=ANALYZED
INDEX RANGE SCAN Object owner=OWSGM Object name=I_SGM_ASC_REPAIRORDER_BALTIME Cost=26 Cardinality=5118558 IO cost=26
VIEW Object owner=SYS Object name=VW_NSO_1 Cost=2 Cardinality=219 Bytes=2847
FILTER
CONNECT BY WITH FILTERING
NESTED LOOPS
NESTED LOOPS Cost=1 Cardinality=7 Bytes=84 IO cost=1
INDEX FULL SCAN Object owner=OWSGM Object name=PK_TS_SGM_SYS_DEPARTMENT Cost=1 Cardinality=219 Bytes=876 IO cost=1 Optimizer=ANALYZED
INDEX UNIQUE SCAN Object owner=OWSGM Object name=AK_PRIMARY_GROUP_TT_SGM_S Cardinality=1 Bytes=8 Optimizer=ANALYZED
TABLE ACCESS BY USER ROWID Object owner=OWSGM Object name=TS_SGM_SYS_DEPARTMENT Optimizer=ANALYZED
HASH JOIN
CONNECT BY PUMP
TABLE ACCESS FULL Object owner=OWSGM Object name=TS_SGM_SYS_DEPARTMENT Cost=2 Cardinality=219 Bytes=1752 IO cost=2 Optimizer=ANALYZED
INDEX UNIQUE SCAN Object owner=OWSGM Object name=AK_PRIMARY_GROUP_TT_SGM_S Cost=1 Cardinality=1 Bytes=8 IO cost=1 Optimizer=ANALYZED

[子陌红尘]

关注。

[waterfirer]

下面这段能解释一下吗？为什么要这么做
da.department_id IN (
SELECT d.department_id
FROM ts_sgm_sys_department d
START WITH d.department_id IN (
SELECT ud.department_id
FROM tt_sgm_sys_userdepartment ud
WHERE ud.user_id IN (47))
CONNECT BY PRIOR d.department_id =
d.parent_department)

[bzszp]

FROM tt_sgm_asc_repairorder a,
tm_sgm_asc_ascbasicinfo c,
tt_sgm_sys_deparasc da
改成
FROM tt_sgm_asc_repairorder a,
tm_sgm_asc_ascbasicinfo c,
(
SELECT d.department_id,asc_code
FROM ts_sgm_sys_department d
START WITH d.department_id IN (
SELECT ud.department_id
FROM tt_sgm_sys_userdepartment ud
WHERE ud.user_id IN (47))
CONNECT BY PRIOR d.department_id =
d.parent_department
) da
...

后面的 AND da.DEPARTMENT_ID IN (.. 这部分去掉

试试

[leborety]

谢楼上的兄弟

你们说的可以改善部分性能，那些我知道。现在的问题，主要在分析函数那里，里面的内容，cost都很小的，全表扫也一样。

在sql方面，我个人觉得改善的可能性不大了

想征求其他解决方案

[hlp912]

关注一下。
在select 前面加上表的索引会快些的

[taler]

在数据库中分成多个表,在应用层处理

[leborety]

楼上的兄弟，我用的字段是有索引的

还是强调下，时间花在排序上，不是select上

[zzwind5]

我这有一个表 数据量 10853498 随机取10条数据
sample(xxx)
xxx是百分比，5代表5%
每次都是随机的，不过连个数都随机

SQL> select count(so_nbr) from so;

COUNT(SO_NBR)
-------------
10853498

SQL> set timing on;
SQL> select so_nbr from so sample(0.0001) where rownum<=10;

SO_NBR
--------------------------
2002080008674
2003010383585
2003020053155
2004040243584
2005010324758
2005040002732
2005040002859
2005050333656
2005090444850
9200205874370

10 rows selected.

Elapsed: 00:00:01.05

一千万的数据如果全表排序我也不知道用多少时间反正是没响应了
是不是可以这样
对400个asc_code，和每个asc_code 的sample()参数做一个配置表，用动态SQL 循环的方式把
400个asc_code的SQL语句拼出来

[andzen]

ORACLE的内存排序参数设置了多少，
你的ｏｒａｃｌｅ版本是什么，
表的并行参数多少
是否CACHE
．．．
．．．
最好把你的服务器的硬件写一下，要不怎么估算瓶颈在哪里
不能一味把目标放在SQL上面，优化是个系统工程

[liuyi8903]

既然是花在排序上. 你可以试试修改表连接方式看效果会如何.
还有.你对表进行分析了没?


如果能把执行计划帖上来就好.

[常遇道]

分配一下表的空间,还有你的db_cache_size大小

[leborety]

oracle 9i
无并行

在测试环境的一台pc机上，并且生产环境不可测。我也拿不到具体配置信息。

sql优化我已经认为没大作为了。所以在寻求其他方案

[leborety]

我还是觉得，把这种数据量和这种查询放在jsp本身就不应该。定的方案还是写procedure在后台慢慢跑吧。

谢各位，给分了。

[软侠]

關注中！！！！！
幫頂!!

[starry11]

mark

我的一个表也是千万数据级，在里面查找符合某条件的数据要用上10秒钟，也想不出怎么优化

[adaizi1980]

关注中……
最头疼的就是排序时间

[xiewenlin2004]

数据太大能否使用下优化策略
我上个项目数据也是百万以上的没有使用优化前需要十五分钟，使用后大约在2秒多
我得办法是：尽量避免对大数据量的标进行全表扫描，让Oracle分析器按照索引进行分析查找：
在第一个Select后加上/*+Rule*/
希望能帮到你