| query taking lot of time [message #381143] |
Thu, 15 January 2009 07:35  |
pointers
Messages: 451
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 #381148 is a reply to message #381143] |
Thu, 15 January 2009 07:43  |
pointers
Messages: 451
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 #381285 is a reply to message #381150] |
Fri, 16 January 2009 02:35 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
