Home » SQL & PL/SQL » SQL & PL/SQL » SQL query efficiency (Oracle)
SQL query efficiency [message #361998] Sat, 29 November 2008 07:58 Go to next message
atebbenham
Messages: 2
Registered: November 2008
Junior Member
Hi,

I was wondering if someone could look at my query below and tell me if I could make it any more efficient? I have only just started learning SQL so am unsure if there are better ways for me to do the below. I may well be taking a long route to do this. Your help would be much appreciated.

I am trying to get the name of the agent, who was first to access the piece of information which was the first to be gleaned after 04-MAR-1963. (The query below does return the name).

3 Tables involved:

Agents
------
agent_id
first_name
last_name

Info_access
-----------
agent_id
logon_time (i.e. accessed date)
information_id

Information
--------------
information_id
gleaned_date

Query:

SELECT a.first_name||' '||a.last_name name
FROM   agents a
WHERE  a.agent_id = (SELECT aa.agent_id
                     FROM  (SELECT ia2.agent_id,
                                   ia2.logon_time
                            FROM   info_access ia2
                            WHERE  ia2.information_id = (SELECT earliest_gleaned.info_id2
                                                         FROM  (SELECT gi.info_id info_id2,
                                                                       gi.earliest_date
                                                                FROM  (SELECT i.information_id info_id,
                                                                              i.gleaned_date earliest_date
                                                                       FROM   information i
                                                                       INNER JOIN info_access ia ON i.information_id=ia.information_id
                                                                       ORDER BY i.gleaned_date )gi
                                                                WHERE  gi.earliest_date > TO_DATE('04-03-1963','DD-MM-YYYY')
                                                                ORDER BY gi.earliest_date asc )earliest_gleaned
                                                         WHERE  rownum < 2 )
                            ORDER BY ia2.logon_time )aa
                     WHERE rownum<2 )


[formatted to allow you to appreciate the true horror of this query]

[Updated on: Mon, 01 December 2008 03:44] by Moderator

Report message to a moderator

Re: SQL query efficiency [message #362004 is a reply to message #361998] Sat, 29 November 2008 09:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I was wondering if someone could look at my query below and tell me if I could make it any more efficient?

I will do it as soon as you:
- read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
- post your Oracle version (4 decimals)
- read OraFAQ Forum Guide, Performance Tuning section
- read How to Identify Performance Problem and Bottleneck
- post the usual information for performances question

Regards
Michel
Re: SQL query efficiency [message #362071 is a reply to message #361998] Sun, 30 November 2008 10:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


The SQL query shown is a total mess of ORDER BY and ROWNUM .

You can try with ,

1. Atmost One subquery ( without oderby or Rownum ) Or
2. Analytic function or
3. Wth WHERE EXISTS clause.

Just try these options

Smile
Rajuvan.
Re: SQL query efficiency [message #362161 is a reply to message #362071] Mon, 01 December 2008 04:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As you're a beginner, I'll show you what to do, and what we expect here.
what follows is the sort of test case that we like - it means that we can create the tables and data needed to fix the problem without having to waste our time creating everything from scratch - after all, you're asking for our help - it's only fair that you do some of the work.

DROP TABLE AGENTS;

DROP table info_access;

drop table information;

create table Agents (agent_id number, first_name varchar2(30), last_name varchar2(30));

create table Info_access (agent_id number, logon_time date, information_id number);

create table Information (information_id number,gleaned_date date);

insert into agents values (1,'Agent 1',null);
insert into agents values (2,'Agent 2',null);

insert into information values (100,to_date('04-mar-1963','dd-mon-yyyy'));
insert into information values (101,to_date('03-mar-1963','dd-mon-yyyy'));
insert into information values (102,to_date('05-mar-1963','dd-mon-yyyy'));
insert into information values (103,to_date('06-mar-1963','dd-mon-yyyy'));

insert into info_access values (1,sysdate,100);
insert into info_access values (1,sysdate,101);
insert into info_access values (1,sysdate,102);
insert into info_access values (1,sysdate,103);
insert into info_access values (2,sysdate+1,100);
insert into info_access values (2,sysdate+1,101);
insert into info_access values (2,sysdate+1,102);
insert into info_access values (2,sysdate+1,103);


Here's a simple way of getting the result. Simply select all the data, order it in such a way that the row you want is at the top of the list, and then select just the first row:
select *
from  (select a.agent_id, a.first_name,ia.logon_time,i.information_id,i.gleaned_date
       from   agents a
             ,information i
             ,info_access ia
       where  a.agent_id = ia.agent_id
       and    i.information_id = ia.information_id
       and    i.gleaned_date > to_date('04-mar-1963','dd-mon-yyyy')
       order by  i.gleaned_date,ia.logon_time)
where  rownum = 1;
icon14.gif  Re: SQL query efficiency [message #362296 is a reply to message #362161] Mon, 01 December 2008 13:20 Go to previous message
atebbenham
Messages: 2
Registered: November 2008
Junior Member
Thank you very much!
Previous Topic: URGENT PLEASE HELP!
Next Topic: Items validation
Goto Forum:
  


Current Time: Tue Dec 06 04:22:01 CST 2016

Total time taken to generate the page: 0.13561 seconds