| Help! SQL TUNING! [message #205906] |
Tue, 28 November 2006 02:12  |
Tom_webex
Messages: 13
Registered: July 2006
Location: China
|
Junior Member |
|
|
SELECT a.id_no,a.detail_type,a.detail_code,to_char(a.begin_time,'YYYYMMDDHH24MISS'),
to_char(a.end_time,'YYYYMMDDHH24MISS'),a.fav_order,a.mode_code,b.phone_no,a.mode_flag
FROM dBillCustDetail0 a,dCustMsg b
WHERE a.id_no = b.id_no AND ( detail_type in('2','3','4','1') )
AND a.begin_time<=add_months(to_date('200610'||'01000000','YYYYMMDDHH24MISS'),2)
AND a.end_time>to_date('200610'||'01000000','YYYYMMDDHH24MISS')
AND substr(b.phone_no,1,7) between '1000000' and '2020000'
AND substr(b.run_code,2,1)<'a'/** and substr(b.run_code,2,1)<>'I' **/
and exists (select * from acct_host_route c where c.acct_home='/account2' and c.route_seg=substr(b.phone_no,1,7) )
-- AND acct_home='/account2'
-- AND substr(b.phone_no,1,7)=route_seg
and rownum<100
ORDER BY b.phone_no asc,a.mode_flag asc
|
|
|
|
|
|
| Re: Help! SQL TUNING! [message #205910 is a reply to message #205906] |
Tue, 28 November 2006 02:20  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Yup, it's a query.
I'm not convinced you've got the end_time and begin_time clauses right.
A function based index on b.id,substr(b.phone_no,1,7) might help it run faster.
Other than that, seeing as we don't know the table structures, the indexes, the data distribution, or even what the problem is, that's about all you're getting for the moment.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
