oracle一个性能优化的sql...

Question

有一个sql语句想优化下，看过很多文章说不用in，可是不知道该如何修改：
  select work.Id from table01 as work where (work.Id = 01 
             or work.workId in (select p.workId from table02 as p
             where p.PId=01)) and work.isDel<>1  and work.workDate between ?  and  ?
             order by work.workDate,work.beginTime;
我创建了一个索引 字段是 workDate。其他的方式该怎么搞？能不能用视图，怎么用啊？....

Answer 1

(work.Id = 01 
or work.workId in (select p.workId from table02 as p
where p.PId=01))
把上面的换成下面的两种方法试试：
work.workId in (select p.workId as workId from table02 as p where p.PId = 01 union all select 01 as workId from dual)exists 
( select 1
   from (select p.workId as workId from table02 as p where p.PId = 01 union all select 01 as workId from dual) T
  where T.workId = work.workId)
 

Answer 2

select work.Id 
from table01  T1 
where 
T1.workDate between ? and ? and
T1.isDel<>1  
and
(T1.Id = 01  or 
exists (
select 1 
from table02 T2
where PId=01 and T1.workid = T2.workId 
)
 
)
order by T1.workDate,T1.beginTime;
没有测试过！

Answer 3

什么是主要是并发解决问题,,模糊啊~~
是并发用户很多?..........还是要用ORACLE的并发来提高性能啊...........汗~

Answer 4

select work.Id,work.workDate,work.beginTime
 from table01 as work 
where work.Id = 01 
  and work.isDel<>1 
  and work.workDate between ? and ?
union all
select work.Id,work.workDate,work.beginTime
 from table01 as work,table02 as p 
where p.PId=01 and work.workId = p.workId
  and work.isDel<>1 and work.workDate between ? and ?
order by workDate,beginTime;

Answer 5

用户过多时，同时访问数据库导致性能下降。
我想了两个方案：
1：想用视图解决下，把所有数据放到一个视图里面，一次性查询，不知道这种方案可行不。
2：优化sql

Answer 6

Oracle数据库索引创建要做到三个适当

Answer 7

SELECT  user_id, psa.vendor_id supplier_id,popedom_type --DISTINCT
          FROM po_vendor_sites_all psa,
               (SELECT area_id,
                       area_code,
                       area_correspond_code,
                       area_chinese_name,
                       area_type,
                       user_id
                       ,popedom_type
                  FROM (SELECT c.area_id,
                               area_code,
                               area_correspond_code,
                               area_chinese_name,
                               area_type,
                               c.popedom_user user_id
                               ,c.popedom_type
                          FROM crm_popedom   c,
                               (SELECT area_id,
                                       area_code,
                                       area_correspond_code,
                                       area_chinese_name,
                                       area_type,
                                       area_parent_id,
                                       (rtrim(substr(ltrim(sys_connect_by_path(area_parent_id, ','), ','),
                                                    0,
                                                    instr(ltrim(sys_connect_by_path(area_parent_id, ','), ','),
                                                          ',',
                                                          1)),
                                             ','))
                                              AS root_area_id
                                  FROM crm_area_country ca
                                 START WITH ca.area_parent_id IN (SELECT area_id FROM crm_popedom)
                                CONNECT BY PRIOR ca.area_id = ca.area_parent_id) a
                         WHERE a.root_area_id = c.area_id or a.area_parent_id=c.area_id )
                 WHERE area_type = 'Country') ca               --,po_vendors_all pa
         WHERE psa.country = ca.area_correspond_code
         union all
  select cac.popedom_user user_id, supplier_id, popedom_type
    from (select supplier_id,
                 (select area_id
                    from crm_area_country
                   WHERE area_type = 'office'
                   start with area_id = c.supplier_country_id
                  connect by prior area_parent_id = area_id) a,
                 (select area_id
                    from crm_area_country
                   WHERE area_type = 'Regional'
                   start with area_id = c.supplier_country_id
                  connect by prior area_parent_id = area_id) b
            from crm_supplier_baseinfo c
           where supplier_cooperate_id = 'LATENCY') tmp,
         crm_popedom cac
   where (tmp.a = cac.area_id or tmp.b = cac.area_id);