Jump to content




Recent Status Updates

  • Photo
      15 Nov
    duzamucha

    Hi, I am final year Interior Design Student from University of Huddersfield. I am currently working on my final major project which is going to be linked to coding. I was hoping that you could help me with my research. I have prepared a short survey, it would be a massive help if you could fill it in for me. It takes less than 2 minutes to complete, I promise. Here is the link: https://www.surveymonkey.com/s/73XLJKK Thank you so much in advance!

View All Updates

Developed by TechBiz Xccelerator
Photo
- - - - -

Oracle ROWNUM

Oracle

  • Please log in to reply
6 replies to this topic

#1 debtboy

debtboy

    CC Devotee

  • Just Joined
  • PipPipPipPipPipPip
  • 499 posts

Posted 20 October 2009 - 03:21 PM

I've recently been struggling with a 30 minute
response times on an Oracle database.
3 tables, 1 (rather large) existing view,
2 left outer joins and 1 inner join.

I'm not going to bother posting the db schema and
all the sql because there is quite a lot of it.

I decided to create a manageable view which took
care of 3 tables, but didn't help when I inner joined
it to the large existing view. So I decided to dynamically
create a small view containing a small subset (5K) of the
normal 50K record output, but how do I limit the records.
In SQL server TOP, MySQL LIMIT and Oracle ROWNUM which
brings me back to my buddy phillw and how he uncovered
that LIMIT does the full query anyway and delivers a subset.

Now Oracle assigns a ROWNUM to rows returned so you have to
Select * FROM (Select your actual query) where ROWNUM < something
and this didn't help response time.

I realized that Oracle assigning a ROWNUM could be used, so I created
the full view including ROWNUM (Select ROWNUM etc...) just like
selecting an existing field which worked like an auto-increment field.
Now I joined the 2 views where ROWNUM between X and X and
delivered 5K subsets of the output which each took about 1 minute.
30 minutes became 10 minutes. :P
via a non-standard use of ROWNUM in Oracle.
  • 0

#2 WingedPanther

WingedPanther

    A spammer's worst nightmare

  • Moderator
  • 17,204 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 20 October 2009 - 04:46 PM

Now THAT is cool :)
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 debtboy

debtboy

    CC Devotee

  • Just Joined
  • PipPipPipPipPipPip
  • 499 posts

Posted 20 October 2009 - 05:42 PM

Yes very cool, I tried multiple views, queries w/ sub-queries,
grouping with rank, normal ROWNUM usage, etc...
nothing worked until I misused ROWNUM to create
a unique id column while creating a dynamic view.

Just add ROWNUM to the select portion of the
Create or replace view as... query.
Oracle only!!

Sometimes it's the little things that make a big
difference to me. :)
  • 0

#4 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 20 October 2009 - 06:05 PM

Impressive! :)
  • 0

#5 phillw

phillw

    CC Resident

  • Member
  • PipPipPipPip
  • 70 posts

Posted 21 October 2009 - 06:11 AM

That's one cool bit of lateral thinking !!!

... btw .. it wasn't you searching for the "green cookie monster" on the ft test area, was it ? - lol
No idea who it was - but it did make me chuckle :-)

Phill.
  • 0

#6 debtboy

debtboy

    CC Devotee

  • Just Joined
  • PipPipPipPipPipPip
  • 499 posts

Posted 21 October 2009 - 02:58 PM

:confused:
  • 0

#7 phillw

phillw

    CC Resident

  • Member
  • PipPipPipPip
  • 70 posts

Posted 22 October 2009 - 12:56 AM

The site counter I have installed showed someone searching for that phrase :w00t:

The google bot also found the test site & dutifully indexed it all up - a hit came from someone searching for a part on google - lol

I guess I'd better hurry and get it all finished now !!

Phill.
  • 0





Also tagged with one or more of these keywords: Oracle

Powered by binpress