Home » SQL & PL/SQL » SQL & PL/SQL » Help! SQL TUNING!
Help! SQL TUNING! [message #205906] Tue, 28 November 2006 02:12 Go to next message
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 #205909 is a reply to message #205906] Tue, 28 November 2006 02:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yes?
Re: Help! SQL TUNING! [message #205910 is a reply to message #205906] Tue, 28 November 2006 02:20 Go to previous messageGo to next message
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.

Re: Help! SQL TUNING! [message #206060 is a reply to message #205906] Tue, 28 November 2006 08:21 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post EXPLAIN
Re: Help! SQL TUNING! [message #206062 is a reply to message #205906] Tue, 28 November 2006 08:29 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
ERROR at line 3:
ORA-00942: table or view does not exist

[Updated on: Tue, 28 November 2006 08:31]

Report message to a moderator

Re: Help! SQL TUNING! [message #206067 is a reply to message #206062] Tue, 28 November 2006 09:14 Go to previous messageGo to next message
vshari
Messages: 9
Registered: October 2005
Location: India/UK
Junior Member
have you created the plan table.
Re: Help! SQL TUNING! [message #206069 is a reply to message #206062] Tue, 28 November 2006 09:15 Go to previous message
vshari
Messages: 9
Registered: October 2005
Location: India/UK
Junior Member
can you post the explain plan
Previous Topic: problems with script
Next Topic: COUNT funtion help
Goto Forum:
  


Current Time: Fri Dec 09 15:22:57 CST 2016

Total time taken to generate the page: 0.10731 seconds