Home » SQL & PL/SQL » SQL & PL/SQL » order by
order by [message #207777] Thu, 07 December 2006 00:22 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
select ss.*,sp.filecaststreamprofileid, st.filecastaccessid
from servicesession ss, sessionprofile sp, servicesessiontarget st
where ss.isdeleted = 0
and sp.isdeleted = 0
and ss.servicecode = 'esg'
and ss.contentprovidercode='dco'
and ss.operationaldate = '27-dec-2006'
and 10800 <= (castingtime + castingduration)
and 86400 >= (castingtime)
and ss.sessionprofileid = sp.sessionprofileid
and st.sessionnumber=ss.sessionnumber
and ss.servicecode=st.servicecode
and st.operationaldate=ss.operationaldate
and sp.filecaststreamprofileid is not null
order by st.filecastaccessid








I dunno no of rows in each table,but i want it 2 excute faster
while ordered by
.














Re: order by [message #207790 is a reply to message #207777] Thu, 07 December 2006 01:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Scenario: Me at a carpet store talking to the salesman...

Me: ./fa/1605/0/ I want 2 to buy some carpet (note here the clever saving of a single byte - Frank N. will be impressed ./fa/1578/0/ that I am saving space in the OraFaq database)

Carpet Guy: ./fa/1598/0/ Of course, Sir. How big is the area you wish to carpet?
M: ./fa/1600/0/ Dunno.

CG: (unfazed) ./fa/1587/0/ No worries, I'll estimate it. How many rooms are there?
M: ./fa/1580/0/ Dunno.

CG: (getting a little fazed now, but still professional) ./fa/450/0/ That's OK, I'll just sell you plenty, and you can bring back the unused portion. How much money do you have?
M: ./fa/1637/0/ Dunno.

CG: ./fa/1582/0/ ./fa/1604/0/ Next!

Ross Leishman

P.S. Make an effort. Give us table volumes, expected rows returned, current Explain Plan, available indexes.


(modified by Littlefoot) I couldn't resist, so I added a little bit of scenery

[Updated on: Thu, 07 December 2006 02:59] by Moderator

Report message to a moderator

Re: order by [message #207822 is a reply to message #207777] Thu, 07 December 2006 02:48 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
each table has 1 million rows
Re: order by [message #207842 is a reply to message #207822] Thu, 07 December 2006 03:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, that just leaves us waiting for the expected rows returned, the Explain Plan from the current query,and a list of the available indexes.
Re: order by [message #207937 is a reply to message #207842] Thu, 07 December 2006 08:05 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And once again, comparing a DATE column to a character string. How sad.
Re: order by [message #207948 is a reply to message #207937] Thu, 07 December 2006 08:27 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
joy_division wrote on Thu, 07 December 2006 08:05
And once again, comparing a DATE column to a character string. How sad.



Once of my biggest gripes. You will get into trouble doing this, it's just a matter of time.
Re: order by [message #207953 is a reply to message #207948] Thu, 07 December 2006 08:41 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Be fair - he may be storing a date in a Varchar2 instead. Just as bad, but different. Cool
Previous Topic: Format a number to double type
Next Topic: Euro Sign
Goto Forum:
  


Current Time: Fri Dec 02 16:34:26 CST 2016

Total time taken to generate the page: 0.25548 seconds