On 1/24/06, John Siracusa <[EMAIL PROTECTED]> wrote:
> On 1/24/06, Rob Kinyon <[EMAIL PROTECTED]> wrote:
> > I may be able to help with this. Luckily, if you stick with Oracle
> > 9.2+, it can use the standard JOIN syntax.
>
> Hm, is that reasonable? What is the most popular version of Oracle these
> days?
"Most popular" is a difficult term. DBD::Oracle supports back to
Oracle 7, after a fashion. 9.2 has been out for about 5-6 years and in
my consulting career, I've never encountered anything before 9.2
(except in extreme legacy cases where I once saw an Oracle 6). If you
do the standard JOIN syntax, I would be surprised if Oracle8 didn't
support it for 90%+ of the cases.
10g has been released, but it's completely backwards compatible with 9
which does support JOIN.
> > Oracle doesn't have a LIMIT syntax, though it can be simulated using ROWID.
>
> How about offset?
Same thing. Oracle provides a pseudo-column called ROWID (or something
like that) that indexes into the resultset. So, the first column
returned has ROWID == 1, and so forth. The ROWID is set after GROUPBY
(and i think after SORTBY, but I've have to doublecheck), but before
HAVING because you do things with the ROWID in the HAVIING clause.
So, you'd do things like:
SELECT ...
FROM ...
WHERE ...
HAVING ROWID > 30 AND ROWID <= 30+10
That corresponds to LIMIT 30,10 in MySQL
> > Auto-incrementing is also wonky in Oracle - you have to manually create a
> > sequence and do some random locking stuff in order to do it right. I either
> > have or can easily get all this information.
>
> Well, post it all to the list :)
LOL. I'd have to ask a few DBA friends of mine. I'd like to see the
effort started before I use up a favor. :-)
> Oh, and I almost forgot about auto-init. That area is pretty much
> self-contained. I don't know what DBD::Oracle's introspection
> capabilities are like, but let's hope the Oracle auto-init looks more
> like the Pg auto-init:
Oracle's introspection capabilities are considered to be second-to-none.
The big Oracle-isms are going to be:
1) auto-increment is in a sequence
2) there's no explicit LIMIT clause
3) Self-referential trees are really really easy using CONNECT BY
4) The information schema is going to make you drool
5) PL/SQL support is going to be non-standard (don't worry about
this till someone asks for it)
6) DBI was designed around DBD::Oracle (which isn't surprising
because Tim Bunce wrote both at the same time). So, you will find it a
little easier to work with DBI if you're going against Oracle.
7) It's VARCHAR2, not VARCHAR
8) Everything is in a transaction
9) Don't try to optimize queries
10) Every single server parameter is tunable
11) Connections to the server take about 5x the overhead vs. MySQL
Remember - Oracle is the gold standard against which all other RDBMSes
are judged. If someone has it, then Oracle either has it or has
demonstrated why they won't have it. Oracle has had a major part in
developing most of the SQL "standards" of the past 20 years. So, if
it's standard, 100-1 Oracle does it because they designed the
standard.
Rob
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid3432&bid#0486&dat1642
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
