Home » RDBMS Server » Performance Tuning » query taking lot of time (oracle 10g)
query taking lot of time [message #381143] Thu, 15 January 2009 07:35 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

I have a query which is taking lots of time. How can we tune it.

The query is as follows.

t_app has nearly 600 records and v_r has nearly 20lacks of records.

select * from t_app where id not in (select distinct v.id from v_r v where v.date_time between sysdate-30 and sysdate)


Please advice me.

Regards,
Pointers.
Re: query taking lot of time [message #381146 is a reply to message #381143] Thu, 15 January 2009 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your query.

Regards
Michel
Re: query taking lot of time [message #381148 is a reply to message #381143] Thu, 15 January 2009 07:43 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
/* Formatted on 2009/01/15 19:12 (Formatter Plus v4.8.8) */
SELECT *
  FROM t_application
 WHERE app_id NOT IN (SELECT app_id
                        FROM v_raw_data
                       WHERE date_time BETWEEN SYSDATE - 30 AND SYSDATE)
Re: query taking lot of time [message #381150 is a reply to message #381148] Thu, 15 January 2009 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure you gave all the necessary information?

Regards
Michel
Re: query taking lot of time [message #381285 is a reply to message #381150] Fri, 16 January 2009 02:35 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Making the not-in key non-nullable in both outer and sub query will enable an anti-join.
SELECT *
  FROM t_application
 WHERE sys_op_map_nonnull(app_id) NOT IN (SELECT sys_op_map_nonnull(app_id)
                        FROM v_raw_data
                       WHERE date_time BETWEEN SYSDATE - 30 AND SYSDATE)


Ross Leishman
Previous Topic: Qurey taking much cost
Next Topic: Slowness of sql query (merged)
Goto Forum:
  


Current Time: Fri Dec 09 04:19:03 CST 2016

Total time taken to generate the page: 0.08158 seconds