Oracle Beginnings - Where to Start?

Scalli0n asks: "I'm a programmer with a solid computer science background, but I'd like to know where I should start with the behemoth that is Oracle - my bosses tell me that I need to learn it to store geodatabases (since I work with geospatial intelligence) and I have no clue where to start since nobody can even tell me of a good beginners book - any suggestions?"

Comments

Tried Amazon?

[mrobinso]

Here you go:

Oracle Beginner's Guides

Where to start? Where are you starting?

[Praetorian42]

This is another one of those classic "We need more Info" Ask Slashdot's.

Do you mean that you don't even know SQL, and you need to start from the ground up for everything? Do you know SQL and have a decent background in MySQL? How about Postgres? I'm going to assume that you have some SQL knowledge, it's just you need to know about Oracle itself.

Learning Oracle is no different than any other technology, really. When I started my current job a little over a year ago, I had no clue about Oracle, I just discovered new features as I needed them. I was in a shared office with 2 oracle DBAs, and so there was quite a bit of communal learning there. Anyways, one of our DBAs left last month, and I've since been "promoted" to programmer/DBA. Oracle isn't really so much a behemoth (I learned enough to become a half-DBA in about a year), but it does have quite a few quirks that can leave your head scratching if you're used to some other RDBMS's.

First, if you're going to do anything interesting, you are (unfortunately) going to have to learn PL/SQL. PL/SQL is a big pain in the ass. Dynamic SQL is a pretty powerful tool (Feature?), but the way it is implemented with PL/SQL and Oracle is clumsy at best, and debugging is nearly impossible by any conventional means. Implementation for triggers and stored procedures is a bit more straightforward once you get used to the syntax.

Triggers are pretty important- if you're used to auto_increment fields in MySQL, in order to do it with Oracle (at least 9i.. I don't know much about 10g), you need to create a sequence, and then a trigger which will pull the next value out of the sequence and put it into the field (if it's null). While you have much more flexibility this way, it takes about 5 minutes to do something which would take a half-second in MySQL. That's pretty much the essence of Oracle, really- it's very flexible and powerful, but kind of a pain in the ass to use.

But once you get used to it, it's not all that bad. Oracle has some pretty impressive query/table optimization tools, and if you spend the time on it, you can get most everything running incredibly fast.

Oh, and if you run windows, get TOAD (or TOra for *nix), and it will help you cheat with most of this stuff. For TOAD at least, if you do anything the WYSIWYG way, you can always look at the SQL to see what the hell it is actually doing, which is a pretty decent way to learn.

Re:Where to start? Where are you starting?

[MagicM]

Instead of Toad, I would recommend PL/SQL Developer.

Re:Where to start? Where are you starting?

[abulafia]

That's pretty much the essence of Oracle, really- it's very flexible and powerful, but kind of a pain in the ass to use.

This is the best summary of Oracle ever. Very true.

I've been working with Oracle on and off since v.7.3 (ca. 1996 or so). Which is sort of a worst-case - if you use it quite a bit, you start to feel comfortable, then leave it alone for a while and forget all the dumb little workarounds and tricks.

For whatever it is worth, my advice is:

• Start keeping a directory of files with snippets of plsql/sql/series of steps of how you did something. If you have OCD like me, you can even make these executable scripts and everything. If you're a child of the modern world, you might instead use a wiki or something. The important thing is to keep a log of some sort - you will want to refer back to it.
• Remember to google. Whenever you run in to a problem, I promise you someone else has been there before, and talked about it online. Get used to searching around.
• Become comfortable with the error codes. They suck, but in their own demented way, do actually help. It takes time to learn what they're actually telling you, which is usually different than what they say
• Whether or not you're concerned with optimization (and you will be, at some point), get comfortable with explain and tkprof. Wrapping your head around how the optimizer works gives a fair amount of insight that is generally useful.
• Don't be afraid of plsql. It sometimes feels as if you're building a house in the dark or something, but there's a lot of value in it. I know several folks who draw a hard line here - "well, I can use it like a DB, but programming in it is not what I do". They miss out on a lot of the value of Oracle.
• Make sure you're comfortable with sqlplus. The Quest tools are great, and vastly improve productivity, but the time will come when you need to muck around with the v$ and dba_whatever tables, or have to manually reset a sequence, or something, and making sure you can handle sqlplus (and selecting out the twisted joins required to get anything useful out of said tables) is invaluble. I am not making this up: last time I had a full time job, I got a promotion out of the fact that I was the only one in engineering who was able to use it to diagnose a weird logic problem. (Well, there were other factors, but a high-pressure situation when you can ask and get answers from the DB and nobody else can looks impressive. I have not yet tried this technique with the ladies.)
• And all the general database tips: if you aren't already good at design, learn, learn, learn. Don't ignore the theory: if you don't know the difference between third-normal and Boyce-Codd, go learn it. Don't just read: make test DBs in each form, and build test cases in each one. Make sure you understand at least the basics of set theory. I'm not kidding - folks will tell you it doesn't matter, but the difference between a competent DB developer and a great one is frequently a deep understanding of theory. Being in a line of work that exposes me to designs done by a lot of other people, I can tell a surprising amount about their knowledge and work habits by looking at how they build DBs.

Anyway. That ended up being kind of a random brain dump. Hope something was useful.

Re:Where to start? Where are you starting?

[heinousjay]

Just so no one takes this idea seriously, if there is ever a concurrent update then there will be two inserts that get the same value, and the second one will fail (assuming proper primary key semantics)

Even with max(id)+1 this is wrong. There are reasons databases provide ID generation primitives. One of the biggest is guaranteeing that an id given to a client is unique for that primitive.

Re:Ask Tom

[dshannon]

Tom's website is at http://asktom.oracle.com/

I've found it extremely useful for dealing with specific as well as general problems with Oracle.

But in general, if you know one RDBMS, then until you need specific features it's not too hard - I taught myself with the aid of the manuals and some experimentation. HTH

Dan

for what it's worth

[ackdesha]

About 8 years ago I was in a similar spot. Fresh out of college with a physics degree, I was lucky enough to be hired into a good situation using Oracle everyday to manage a large pharm. corp's marketing DB. I had little programming experience, and no unix or DB experience, and had to get up to speed quickly. I haven't touched Oracle in several years, but my advice is to not only read as much of the Oracle manuals as you can stomach, but also pick a language like perl or python to cozy up to. A lot of the tasks you'll need to do are best handled with gnu unix utilities and scripting. Ask other people to review your schema designs. Avoid application programming at the DB level (PL/SQL). Take advantage of subqueries and hints and find a good system to analyze the query cost and tune your SQL (some used toad, i didn't). Looking at my bookshelf it looks like I wore out "Oracle Performance Tuning and Optimization" by Edward Whalen, and "Oracle8 HOW-TO" by Honour, Dalberth, Kaplan. These are probably really outdated by now, but look for something like a cookbook approach to suppliment the official manual. Hope that helps some. Cheers.

Learn standard SQL first

[sobiloff]

Oracle is, for the most part, a SQL-compliant database at its core. So, you'll want to get a good handle on basic SQL first, then you can sign up for the Oracle Technical Network to get access to the Oracle-specific documentation at <http://www.oracle.com/technology/documentation/da tabase10gr2.html>. There are probably Oracle-specific commands that you will find useful for your specific geospatial data.

When I learned SQL I used the book "The Practical SQL Handbook" by Bowman, Emerson and Darnovsky. It was very readable, used meaningful examples, and presented the topics in an appealing order. I've recommended it to two or three others during my career and they liked the book, too.

RBDMS

[bleaknik]

There are several different options for relational databases, and despite what the slashdot community thinks, MySQL isn't necessarily the right one.

Let's first assume that you're devoted to the Oracle platform. Oracle has some nice advantages compared to some of the other RBDMS. It handles triggers very well, it supports a java based client for end user applications (programmed via PL/SQL), and it's damned fast (when it's setup up very well). Notably, you will need a dedicated Oracle server with pretty robust specs fi you want it to run well.

Now, a brief comparison of other products in case you are not devoted to Oracle.

Access is relatively cheap and easy to use if you're not going to be doing a lot concurrent operations on the DB.

MySQL is one of the most efficient databases I've worked with in terms of speed, assuming you've got a well normalized database. You can also run MySQL from a nondedicated machine for quite some time before you need a dedicated box (your results may vary). MySQL does have some limitations, though.

As best as I can tell, MS SQL Server runs pretty well, but seems like an oversized version of Access. This can be a good or a bad thing, depending on how you look at it. It does offer decent performance, an easy user interface, and it's a Microsoft product, which of course comes with positives and negatives.

There's also IBM's DB2 platform which (last I used it) had a fast db engine, and a horribly slow front end.

Wikipedia has a nice article that distinguishes between the different RDBMS out there. If you haven't yet picked a platform, I would suggest that you start there.

There are several things you can do, once you've picked a RDBMS, however, your best approach is to learn SQL. SQL is pretty standard amongst the DB engines, and if you can use it well, you'll be golden. This has been my favorite reference manual, although I must it admit if you're first learning SQL, the reading is quite dry. I would also recommend that you find a good resource for whichever database engine you end up using, because each of them has specific functions and keywords that you might need to look up from time to time.

Finally, one last question, if you don't mind me asking... If you've got a computer science background, why don't you have a good basic understanding of RDBMS? I mean, when it comes down to it they're all basically the same. Maybe I'm wrong, but it makes me wonder...

Consequentially, if you're boss is willing to hire me, I might just be looking for a job. :)

oreilly has a large catalog of oracle titles

[atomic-penguin]

Here is a link to Learning Oracle. Here is a link to all of their oracle titles.

Free Online Oracle 2 Day DBA Course

[DisasterDoctor]

http://www.oracle.com/technology/obe/2day_dba/inde x.html

Pro Oracle Spatial

[dicey]

For the spatial extension in Oracle I'd recommend http://www.amazon.com/exec/obidos/tg/detail/-/1590 593839/102-1378503-5410527?v=glance

For the normal Oracle stuff, I'm sure there's 101 books out there that you could find useful depending on what angle you want to approach it from. Check out the documentation on the oracle site - also might be worth getting a login for Oracle's OTN and checkout the forums.

Hope this is helpful...

[tchuladdiass]

Most of the Oracle programming books I've found start off either too heavy (assuming you already know a lot of oracle dba stuff), or too basic, where you still don't get the info you need.
I came across one back a while ago that struck a perfect balance, at least for me, in that I was able to go from 0 Oracle experience to setting up tables and writting the app I needed for a work project within about a week. The book is at work, but I think it's "Oracle 9i Programming: A Primer" by Rajshekhar Sunderraman (at least, that's the one I found on Amazon, I think it's the same book I used, I'll double check on Monday at work and get back to you)

OP: Some guidance

[Glonoinha]

Contact the professor that is teaching the Boston University MET* CS_579 class L2 Database Management. His name is Ellis Cohen and he is a hard-core Oracle guy, has a full semester worth of studies in everything that is database and is very Oracle 10g centric. All his stuff is delivered in PowerPoint (yea, yea - but it works) and he has excellent homework exercises that walk you through every aspect of the engine, including all the fun freaky stuff.

The class was one of the more difficult classes I have taken, but I came out of there on par with most of our DBAs (and more fluent in Oracle 10g than a few of them.)

I honestly don't know how he will respond to you contacting him, but if you want to be an Oracle heavy, he can show you the way.

* Metropolitan College

PostgreSQL

[commanderfoxtrot]

I thought PostgreSQL's OpenGIS was far better than MySQL's; this certainly used to be the case - has it changed?

Just to get more back on topic, PostgreSQL has excellent Oracle SQL compliance, so it's probably a good DB to play with to bring you up to speed. I know I always felt a little bit concerned learning DB2 on the actual mainframe...

Caution - O'Reilly's tuning advice has ... issues

[rebill]

The tuning advice given by the O'Reilly books is quite useful if and only if you are going to have a single user accessing the data.

However, if you intend to allow multiple people to access the data at one time (i.e. a web site), then burn those books and start reading about performance tuning on Tom Kyte's website.

The main problem in the O'Reilly books is that they completely ignore the effect of the queries on resources that are shared globally by all users on the database. In a perfect world, there wouldn't be any shared resources, but we aren't in a perfect world.

The idea here is that one person's tasks are processed sequentially simple because of the nature of what they are doing. However, when you have 15 people on the database, you really want their tasks to be running in parallel with each other. You end up sacrificing some of the "best possible" performance that a single user could gat in order to give everyone reasonably good performance.

A good analogy is automotive traffic. It would be really nice for you if every traffic light you hit was always green in your direction ... but everyone else on the road would hate you for it.

www.LazyDBA.com

[Anonymous Coward]

www.LazyDBA.com

This is where everybody starts !

Some links for you

[Captain Numerica]

The Oracle Technology Network
http://otn.oracle.com/ - LOTS of FREE online books and tutorials.

Ask 'Tom'
http://asktom.oracle.com/ - a place to go to ask questions and read FAQs ... but mostly the latter.