Bad performance of the SQL

lone_zz

if you know why ,please tell me ,thanks

alantany

try rewrite your sql like this:
[php]
select ......  
FROM
(SELECT ........  FROM Test_log, temp_table  
                          WHERE
                          Test_log.CASE_PHONE2CASE = temp_table.OBJID
                           AND temp_table.CODE_PAGE = 'GB2312'
                           AND
                           (Test_log.CREATION_TIME BETWEEN to_date('03/14/2008 05:14:53', 'mm/dd/yyyy hh24:mi:ss')
                           AND to_date('03/16/2008 09:00:17', 'mm/dd/yyyy hh24:mi:ss')
                           OR Test_log.STOP_TIME BETWEEN to_date('03/14/2008 05:14:53', 'mm/dd/yyyy hh24:mi:ss')
                           AND to_date('03/16/2008 09:00:17', 'mm/dd/yyyy hh24:mi:ss'))) a,
                           ,( select OBJID FROM TEMP_WFMIM_TABLE_SUBCASE) b
                        where a.SUBC_PHONE2SUBCASE = b.OBJID(+)

[/php]
now table Test_log will be only scan one time.

[ 本帖最后由 alantany 于 2008-3-18 17:53 编辑 ]

kkngisn

原帖由 alantany 于 2008-3-18 17:52 发表
try rewrite your sql like this:
[php]
select ......  
FROM
(SELECT ........  FROM Test_log, temp_table  
                          WHERE
                          Test_log.CASE_PHONE2CASE = temp_table.OBJID
                           AND temp_table.CODE_PAGE = 'GB2312'
                           AND
                           (Test_log.CREATION_TIME BETWEEN to_date('03/14/2008 05:14:53', 'mm/dd/yyyy hh24:mi:ss')
                           AND to_date('03/16/2008 09:00:17', 'mm/dd/yyyy hh24:mi:ss')
                           OR Test_log.STOP_TIME BETWEEN to_date('03/14/2008 05:14:53', 'mm/dd/yyyy hh24:mi:ss')
                           AND to_date('03/16/2008 09:00:17', 'mm/dd/yyyy hh24:mi:ss'))) a,
                           ,( select OBJID FROM TEMP_WFMIM_TABLE_SUBCASE) b
                        where a.SUBC_PHONE2SUBCASE = b.OBJID(+)

[/php]
now table Test_log will be only scan one time.

  说的是什么意思？