A Few Oracle Tips
August 28th, 2005
I miss MySQL. I have had the pleasure of working with PHP and MySQL for all of my web programming life. Note the had. With a new job, came not only a new programming language (Coldfusion) but also a new database (Oracle). I quickly adapted to Coldfusion but my switch to Oracle has been slower. With MySQL I always used phpMyAdmin as a GUI to manage the creation and modification of whichever database I was working with. phpMyAdmin is great and very powerful, but it doesn not have an Oracle counterpart. I have been using Tora as an Oracle client to create my tables and alter records, but I have found it to be clunky at best. This means I have to hand code my queries which I am not fond of. On top of that, I have found the Oracle website to be crap when it comes to figuring out how to do something. I say all this to explain why I am posting these seemingly trivial tips. Below are a few things that are easy in MySQL but took me a bit to figure out in Oracle.
MySQL Limit Functionality
Paging result sets is a required functionality of every administration area and often times is also a requirement on the front end. No one wants to see 100 records on a page. eBay and Google have trained users to click next and previous while navigating through pages of search and auction results. In MySQL this is very simple. On the end of any query to be paged you simply pass an offset and a limit. For example, let pretend we are showing a list of news ordered by the date descending. It’s a simple query:
SELECT * FROM news ORDER BY date_created DESC
That was easy. The problem is if we have 50 news stories stored in the news table, we get a huge list. Ideally we want to page the results in sets of 10 or 15. Using the example above, we would simply tack on the limits like so:
SELECT * FROM news ORDER BY date_created DESC LIMIT 1,10
The previous query would select all the news ordered by date_created descending from the first to the tenth record. If we wanted to show the next page we would change our query to:
SELECT * FROM news ORDER BY date_created DESC LIMIT 11, 10
This would show records eleven through twenty. Oracle, however, does not have LIMIT functionality. For a while I was hacking my way around this using Coldfusion’s cfoutput tag parameters startrow and maxrows. If I wanted to show records one through ten I would:
<cfquery name="news">
SELECT * FROM news ORDER BY date_created DESC
</cfquery>
<cfoutput query="news" startrow="1" maxrows="10">
<!--- output code --->
</cfoutput>
Oracle Limit Functionality
This didn’t strike me as efficient (because I am not sure exactly how cfquery and cfoutput work together) so the other day I went on an Oracle hunt. I discovered that you can do the following to limit your results:
SELECT *
FROM (
SELECT n.*, ROWNUM as numero
FROM news n
ORDER BY date_created DESC
)
WHERE
numero BETWEEN 1 AND 10
For those that don’t know, n is an alias for news. Selecting n.* means get all the columns from news (equivalent to news.*). Also, numero is an alias for ROWNUM (because of the ‘as’). If you just want the most recent five news articles you could do the following query:
SELECT *
FROM (
SELECT n.*, ROWNUM as numero
FROM news n
ORDER BY date_created DESC
)
WHERE
numero <= 5
MySQL Random Queries
Another common front end functionality is randomness. Often times you show only one news article but show a random one from the most recent five. In mysql you can simple order by rand() like so:
SELECT * FROM news ORDER BY rand() LIMIT 5
Oracle Random Queries
Oracle does not have a rand() function so I usually created programming logic to display something randomly. That was until I discovered an odd order by statement last Friday. To perform the MySQL query above in Oracle I discovered I can do this:
SELECT *
FROM (
SELECT *
FROM news
ORDER BY date_created DESC
)
WHERE
ROWNUM <= 5
ORDER BY
mod(DBMS_RANDOM.RANDOM, 50) + 50
If you would like a lengthy explanation of what that order by statement does I am not the man for the job. I found it in this forum. However, I am of the belief that as long as it works, I don’t have to understand it. Although the query looks nasty, it is actually pretty simple. I know these queries can be improved upon even more, but I just wanted give others new to Oracle a few solutions to problems I know they will run into.
If you enjoyed this post, get free updates by email or RSS.
Subscribe
From PHP/MySQL to CF/Oracle
You don’t see many people traveling in this directly lately. John Nunemaker talks a bit about his new gig as a ColdFusion/Oracle guy.
…
You think Oracle’s bad, you oughta use MSSql server….it doesn’t have rownum so you have to do a limited select with 4 subqueries! I wish I could have Oracle again. Also, at least CF can deal with this kind of limitation with Oracle and MSSql Server. Try using RoR for pagination when you don’t have the LIMIT keyword :)
BTW, cool preview feature!
@Marlon - I will remember next time I get frustrated with Oracle that it could always be worse. I could be using something by Microsoft. ;)
From the joelonsoftware.com forums here:
http://discuss.joelonsoftware.com/default.asp?joel.3.193493.34
Please don’t try to compare apples (mysql, this thread) and oranges (oracle). They are used for entirely different purposes, each excellent in its own realm.
Abstract Typist is right (in concept): the above oracle queries are way more complicated than they should be.
select * from (select ename from emp order by ename desc) where rownum <= 10
@Scot - The reason your initial comment got cut off was because you didn’t encode the <. I fixed it and deleted your second comment since it was identical to the first. Hope that is ok.
This post was not about comparing apples and oranges. It was about learning how to do something in Oracle that I already knew how to do in MySQL. I didn’t find it easy to discover the solution to my problem, so I thought I would post about it to make it easier for others.
Also, if you would have read the entire post, you would have noticed in the last paragraph where I stated:
I don’t claim to be an Oracle pro and I am always up for improvements. Thanks for your additional input.
having used MS SQL Server and then moved more into MySQL, I’ve found MySQL extremely limiting. Btw: to select a subset of records in MS SQL, use TOP.
SELECT TOP 10 * FROM tablename
Random Records:
SELECT * FROM mytable ORDER BY NEWID();
Top 10 Random Records:
SELECT 10 * FROM mytable ORDER BY NEWID();
To handle paging, you’d have to use a stored procedure. Definitely more of a pain. Most people probably handle it via ADO which has functions to handle that but it’s likely not as fast.
@Jonathan - That is interesting. Seems pretty simple. Now that I am beginning to grasp Oracle, I too feel limited by MySQL. I think MySQL is easier to learn, but obviously it lacks the power of Oracle and such.
very interesting. I’m actually in a similar boat where i’ve been developing with PHP/MySQL for a few years and now it looks as though I’m forced to do some very quick learning of Oracle - specifically their iLearning product which uses the 9i database, 10g application server and 10g development suite.
Do you have any good references (books/sites) for getting up to speed on Oracle, especially coming from a MySQL background? It would be GREATLY appreciated.
Another option would be if anyone knows of an experienced Oracle developer that has some free time and familiarity with iLearning. We definitely could use some help ASAP.
Thanks again…
i guess it would help if i specified a way of contacting me ;)
brian.tully at gmail.com
thanks again.
@Brian - I don’t know of any good books, but I have a few resources I have found helpful bookmarked at Delicious.
thanks John! very helpful indeed :)
I know I’m a bit late in responding, but I just found this article using google.
Your LIMIT example is incorrect. The ROWNUM field is added to the query BEFORE it’s sorted. So if a not ordered select will give you the newest 5 articles it will work, but that is not a guarenteed situation.
You example should have been:
SELECT *
FROM (
SELECT n.*
FROM news n
ORDER BY date_created DESC
)
WHERE
ROWNUM
@DelGurth - Interesting. If that is indeed the case then it wouldn’t work but I haven’t had any problems as of yet.
@John
I’ve just created a table news on an oracle 8.1.7 install I’ve access to. I’ve inserted 10 test records:
SQL> select * from news;
ID DATE_CREA NAME
— ——— ——————–
1 19-MAY-06 test 1 - 100
2 29-MAY-06 test 2 - 90
3 08-JUN-06 test 3 - 80
4 18-JUN-06 test 4 - 70
5 28-JUN-06 test 5 - 60
6 08-JUL-06 test 6 - 50
7 18-JUL-06 test 7 - 40
8 28-JUL-06 test 8 - 30
9 07-AUG-06 test 9 - 20
10 17-AUG-06 test 10 - 10
Now the result of your query:
ID DATE_CREA NAME NUMERO
— ——— ——————– ——
5 28-JUN-06 test 5 - 60 5
4 18-JUN-06 test 4 - 70 4
3 08-JUN-06 test 3 - 80 3
2 29-MAY-06 test 2 - 90 2
1 19-MAY-06 test 1 - 100 1
That does not seem to be correct, does it? I would expect id 10 to 6 to show up, not 5 to 1.
I also verified this with a clean oracle 10 XE install, and it gives me the same results.
There is however an alternave for the solution I gave. You could also use:
SELECT *
FROM (
SELECT n.*
, row_number() over (order by date_created desc)
as numero
FROM news n
)
WHERE
numero
thanks very helpful indeed
Thank you for those tips. I’am also comming from a MySQL background and I’m having a hart time getting used to Oracle …
Hi, liked the article. regards, カジノ5484.
Hi, liked the article. regards, カジノ5215.
Hi all,
I agree the oracle site isn’t the easiest one but…
I just wanted to say you can always ask tom about oracle.
http://asktom.oracle.com
Pim
Hi, liked the article. regards, bingo3020.
minnesolta health care plan…
bourgeois passing Markham …