Home » SQL & PL/SQL » SQL & PL/SQL » Oracle select query
Oracle select query [message #195937] Tue, 03 October 2006 03:52 Go to next message
Ramnath
Messages: 7
Registered: July 2006
Junior Member
Is there a better way to make this query more efficient?
(It took 9 mins to retrieve the records from the table which has a total of 17000 records. No of records displayed as output is 5000)


Usage: Retrieve records for previous week where a particular PHONE_NO records are present 2 or more times in a particular day.





select * from TABLE1 F1 where

(IN_DATE between (SELECT TRUNC(SYSDATE, 'DAY')-7 FROM DUAL) AND (SELECT TRUNC(SYSDATE, 'DAY')-1 FROM DUAL))

and

(

select count(F2.PHONE_NO) from TABLE1 F2

where F2.PHONE_NO=F1.PHONE_NO

and F2.IN_DATE=F1.IN_DATE

)>=2





Table1



PHONE_NO
IN_DATE
NAME

24356788
12-Sep-06
Ram

24356788
12-Sep-06
Siva

24356788
12-Sep-06
Raj

24356788
13-Sep-06
Ram




Output: (Last record is omitted since for that 13 Sep only 1 record is found with that phone_no.



PHONE_NO
IN_DATE
NAME

24356788
12-Sep-06
Ram

24356788
12-Sep-06
Siva

24356788
12-Sep-06
Raj

Re: Oracle select query [message #195939 is a reply to message #195937] Tue, 03 October 2006 04:02 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
For starters, you don't need to select SYSDATE from dual. You can just do:

select * from TABLE1 F1 where 
(IN_DATE between TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)-1) 
and
(
select count(F2.PHONE_NO) from TABLE1 F2 
where F2.PHONE_NO=F1.PHONE_NO 
and F2.IN_DATE=F1.IN_DATE
)>=2

A critical thing to speeding it up would be to have an index on the phone_no and in_date columns on table1. But you could also probably just re-code it as:

select *
from
(select f1.*, 
count(1) over (partition by in_date, phone_no) as phone_count
where (IN_DATE between TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)-1))
where phone_count >=2

[Updated on: Tue, 03 October 2006 04:07]

Report message to a moderator

Re: Oracle select query [message #196420 is a reply to message #195939] Thu, 05 October 2006 05:17 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
I agree that indexes would probably help, but we can't tell from the information you provided (for all I know, there can be 3 records with in-dates before previous week, an index on in_date would do more harm then good then).

Having said that, partition by tends to be not the best performing feature of SQL, so, an alternative is:

SELECT in_date
      ,phone_no
      ,COUNT(1)
FROM   table1
WHERE  in_date >= SYSDATE - 7
GROUP  BY in_date
         ,phone_no
HAVING COUNT(1) > 1


(removed the between, assuming that there are no indate in de future).

Regards,
Sabine
Re: Oracle select query [message #196421 is a reply to message #195937] Thu, 05 October 2006 05:24 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The problem with that is that you are limited to returning the in_date and phone_no fields. You can't get any other columns from the table, whereas Raj's original query implied he wanted all columns from table1.
Re: Oracle select query [message #196438 is a reply to message #196421] Thu, 05 October 2006 06:15 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Good point, then the partition by probably is the best method.

Regards,
Sabine
Previous Topic: Crystal report with sql
Next Topic: will this query work in oracle10g
Goto Forum:
  


Current Time: Tue Dec 06 04:42:44 CST 2016

Total time taken to generate the page: 0.23702 seconds