Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help in Reducing the Number of Inner Queries and Table scans

Posted on 2012-08-20
17
Medium Priority
?
745 Views
Last Modified: 2012-08-20
I have a query that I do a table scan thrice based on my needs .I am wondering if there is a better way to do this .


I have a Student Table with a Status Column . The status Column has two status that we are intrested in .

They are  1.'Warning Letter Sent'
          2."warning Letter Processed'


I want to  

REQUIREMENT  1.Pull out records of students who need to have **'Warning Letter Sent'***  but NOT ""warning Letter Processed'" .
REQUIREMENT  2.I need only the latest set of records when the "Warning Letter has been sent " but not processed



I have written a query that Words but its taking too long.Essentially what I have done is Found the Max Date for the " Warning Letter Sent" and Max Date for ""warning Letter Processed'
(which is two queries) and then checked if the DATE('Warning Letter Sent') > DATE ("warning Letter Processed')

This works great ..but very slow .



SELECT STD_ID
FROM
  STUDENT
WHERE
  STATUS ='Warning Letter Sent'
  AND DATE = ( SELECT MAX( DATE) FROM STUDENT WHERE STATUS ='Warning Letter Sent')
  AND DATE > ( SELECT MAX(DATE) FROM STUDENT  WHERE STATUS IN ( 'warning Letter Processed')


Thanks for your time
0
Comment
Question by:Shaunsmith
  • 7
  • 3
  • 3
  • +2
17 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 38312370
First of all... The column "Status" can only hold "one status at a time", right?

Now, let's check your requirements:

1. Pull out records of students who need to have **'Warning Letter Sent'***  but NOT ""warning Letter Processed'". If your "status" column can only have one value (i.e. Warning Letter Sent), there's no point in evaluating for a second status. A filter like: status='Warning Letter Sent' will guarantee that all the records only have the 'Warning Letter Sent' status.

2. I need only the latest set of records when the "Warning Letter has been sent " but not processed. Is this another query? Or is it in the same one?

I think that by clarifying the first requirement it will be easier to figure out the second one.

Thanks.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 800 total points
ID: 38312375
See if this provides the correct results and is faster:

drop table tab1 purge;
create table tab1(std_id number, status varchar2(25), some_date date);

insert into tab1 values(1,'Warning Letter Sent',to_date('01/01/2001','MM/DD/YYYY'));
insert into tab1 values(1,'Warning Letter Processed',to_date('01/02/2001','MM/DD/YYYY'));
insert into tab1 values(2,'Warning Letter Sent',to_date('01/03/2001','MM/DD/YYYY'));
commit;


select std_id, max_date from
(
select std_id, max(some_date) max_date, sum(letter_sent) letters_sent, sum(letter_processed) letters_processed from
(
	select std_id,
		some_date,
		case when status = 'Warning Letter Sent' then 1 end letter_sent,
		case when status = 'Warning Letter Processed' then 1 end letter_processed
	from tab1
)
group by std_id
)
where  letters_sent=letters_processed
/

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 800 total points
ID: 38312378
taking advantage of the fact that "w" > "W"  in ascii sorting, you can check the min and max values

SELECT *
  FROM (SELECT s.*,
               MIN(status) OVER (PARTITION BY std_id) min_status,
               MAX(status) OVER (PARTITION BY std_id) max_status,
               ROW_NUMBER() OVER (PARTITION BY std_id ORDER BY date DESC) rn
          FROM student s)
 WHERE     min_status = 'Warning Letter Sent'
       AND (max_status IS NULL OR max_status != 'warning Letter Processed')
       AND rn = 1
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38312392
>>advantage of the fact that "w" > "W"  in ascii sorting

My guess is this is a typo.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38312394
just in case the capitalization is supposed to be the same

simply change MAX to MIN on the status if

"warning Letter Processed"

should be

"Warning Letter Processed"


because P < S
0
 

Author Comment

by:Shaunsmith
ID: 38312402
"LIONKING" the issue is the Student Table can have the same student Id Repeated so

Student Table  can have

StdId  Date Status

1   1/10/2010  Warning Letter Sent
1   5 /10/2010  warning Letter Processed
1  5 /10/2011   Warning Letter Sent
2   1/10/2012  Warning Letter Sent

In the above I need to get Id's 1 and 2
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38312488
My post had the letters sent and processed euqle.

Here is an updated one using your test data.

I'm sure sdstubers works as well.  Didn't test which one was 'better'.

drop table tab1 purge;
create table tab1(std_id number, status varchar2(25), some_date date);

insert into tab1 values(1,'Warning Letter Sent',to_date('01/10/2010','MM/DD/YYYY'));
insert into tab1 values(1,'Warning Letter Processed',to_date('05/10/2010','MM/DD/YYYY'));
insert into tab1 values(1,'Warning Letter Sent',to_date('05/10/2011','MM/DD/YYYY'));
insert into tab1 values(2,'Warning Letter Sent',to_date('01/10/2012','MM/DD/YYYY'));
commit;


select std_id, max_date from
(
select std_id, max(some_date) max_date, sum(letter_sent) letters_sent, sum(letter_processed) letters_processed from
(
	select std_id,
		some_date,
		case when status = 'Warning Letter Sent' then 1 end letter_sent,
		case when status = 'Warning Letter Processed' then 1 end letter_processed
	from tab1
)
group by std_id
)
where  letters_sent>letters_processed or letters_processed is null
/

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 400 total points
ID: 38312580
select std_id from
(select std_id, date, status,
 row_number() over (partition by std_id order by date desc, upper(status)) rn
 from student)
where status = 'Warning Letter Sent'
and rn = 1;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38312594
sum(letter_sent)

that seems unreliable to me.

sent
sent
sent
processed

will cause the sum  to return the last sent which is probably wrong.
note, mine doesn't work either based on the last set of data and expected results
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38312595
awking00,  your solution ignores the requirement that the letters haven't been processed
0
 
LVL 32

Expert Comment

by:awking00
ID: 38312686
In what way does it not? Can you show some data where it does not work?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38312709
sure, see the previous post.  The same example that breaks slightwv's code will break yours.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38312719
Shaunsmith ,

try this...
It will look for the last sent and make sure it occurs after the last processed (if it was processed)
This should do what you intended based on the last set of data
ee.txt
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38312734
oops, awking00,  sorry, I misplaced a closing parentheses.

I think your solution should work as long as Processed and Sent don't occur on the same date value.

If they do, then Processed will be considered the last event which may or may not be appropriate
0
 
LVL 32

Expert Comment

by:awking00
ID: 38312911
Currently, my query would not retrieve a std_id where both statuses exist for the same date. We would need some input from Shaunsmith as to how such a condition should be handled if it could exist.
0
 

Author Comment

by:Shaunsmith
ID: 38313732
Thanks for the Help Guys

The Grouping Method(from slightwv)  takes around 6 Minutes while the Partition Method( sdstuber) takes around 11 minutes .

This was better than the 2 Hours it would take for me .

I guess We have to index some columns to get it doing better than this .

Thanks for all your help
0
 

Author Closing Comment

by:Shaunsmith
ID: 38313740
excellent help
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question