Oracle ROWNUM

[debtboy]

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.
via a non-standard use of ROWNUM in Oracle.

[WingedPanther]

Now THAT is cool

[debtboy]

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.

[Jordan]

Impressive!

[phillw]

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.

[debtboy]

[phillw]

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

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.