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

Question

还是给出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

Answer 1

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

Answer 2

下面这段能解释一下吗？为什么要这么做
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)

Answer 3

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 (.. 这部分去掉试试

Answer 4

谢楼上的兄弟你们说的可以改善部分性能，那些我知道。现在的问题，主要在分析函数那里，里面的内容，cost都很小的，全表扫也一样。在sql方面，我个人觉得改善的可能性不大了想征求其他解决方案

Answer 5

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

Answer 6

楼上的兄弟，我用的字段是有索引的还是强调下，时间花在排序上，不是select上

Answer 7

我这有一个表 数据量 10853498 随机取10条数据
sample(xxx)
xxx是百分比，5代表5%
每次都是随机的，不过连个数都随机SQL> select count(so_nbr) from so;COUNT(SO_NBR)
-------------
     10853498SQL> 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
920020587437010 rows selected.Elapsed: 00:00:01.05一千万的数据如果全表排序我也不知道用多少时间反正是没响应了
是不是可以这样
对400个asc_code，和每个asc_code 的sample()参数做一个配置表，用动态SQL 循环的方式把
400个asc_code的SQL语句拼出来

Answer 8

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

Answer 9

oracle 9i
无并行在测试环境的一台pc机上，并且生产环境不可测。我也拿不到具体配置信息。sql优化我已经认为没大作为了。所以在寻求其他方案

Answer 10

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

Answer 11

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

Answer 12

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