September 17, 2010 at 9:06 am
Most Oracle developers love cursors. I mean, it's so obvious they're in love. If you read an Oracle PL/SQL developer guide, it will be full of sappy love poetry dedicated to the art of writing cursors.
Another thing you will see a lot of in Oracle is old school non-ANSI join syntax. When (not if) you do see something like this, then don't freak out; it's basically the same as a left outer join.
SELECT Table_A.letter, Table_B.letter
FROM Table_A, Table_B
WHERE Table_A.letter(+) = Table_B.letter;
The example below is what's called a NATURAL JOIN. Now this is a piece of work. Because these two tables share a common key column, Oracle will just make an assumption about how they should be joined, I guess because it saves the developer all the additional keystrokes required to explicitly code the join. On the downside, if your employer measures your productivity in terms of how many lines of code you can write in an hour, then this style of coding will work against you. However, I can think of even more practical reasons why natural joins are a bad idea.
SELECT region_id, r.region_name, c.country_id, c.country_name
FROM countries c
NATURAL JOIN regions r;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 19, 2010 at 7:51 am
Eric Russell 13013 (9/17/2010)
Most Oracle developers love cursors. I mean, it's so obvious they're in love. If you read an Oracle PL/SQL developer guide, it will be full of sappy love poetry dedicated to the art of writing cursors.
This is a tricky one.
It might be true that some developers love cursors - probably developers with strong mainframe procedural programming languages background. Cursors usually allow for a direct translation of poorly defined business rules.
On the other hand, cursors force row-based processing which usually performs horrible. Best practice is to design back-end code to take advantage of set-based data processing; avoiding this way the use of cursors.
Last but not least, cursors are a powerfull tool but it has to be used when needed rather than relying on cursors as the preferred development approach.
Just remember Rule #5 which states that DBA are responsible for Database performance then, your job as a DBA is to be sure no unnecesary use of cursors makes it to production environment. In most serious Oracle shops DBA still have the power to validate/improve/reject bad code.
Hope this clarifies a bit.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 13, 2010 at 4:44 am
thanks for above links i also solved my stored procedure code
October 13, 2010 at 4:46 am
what are advantages of oracle over sql
October 13, 2010 at 8:25 am
harrifolfenced (10/13/2010)
what are advantages of oracle over sql
In my opinion, nothing. Both serve a purpose and can do the job.
In the short time I have had with Oracle, however, I definately prefer MS SQL Server. I could be biasedn howevern having worked with SQL Server for 12+ years and Oracle only a couple of weeks.
October 13, 2010 at 2:25 pm
Lynn Pettis (10/13/2010)
harrifolfenced (10/13/2010)
what are advantages of oracle over sqlIn my opinion, nothing. Both serve a purpose and can do the job.
In the short time I have had with Oracle, however, I definately prefer MS SQL Server. I could be biasedn howevern having worked with SQL Server for 12+ years and Oracle only a couple of weeks.
But there must be something that makes you say that... what in particular struck you?
October 13, 2010 at 3:18 pm
harrifolfenced (10/13/2010)
what are advantages of oracle over sql
SQL Server is to Windows what Oracle is to Unix. Oracle has been around since the 70s or early 80s, and it carries a lot of legacy pre-ANSI SQL baggage. There is a much larger body of proprietary knowledge to learn, a lot more switches to tweak, and the impression I get is that there are more Oracle developers but fewer Oracle experts.
On the other hand, SQL Server is newer and will deprecate features that become obsolete (a good thing in my opinion), making it a much cleaner and easier to learn. Go to Barnes & Noble or Borders, browse the books on Oracle 11g and SQL Server 2008, then decide for yourself which speaks to you.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 13, 2010 at 3:38 pm
harrifolfenced (10/13/2010)
what are advantages of oracle over sql
Let me point to just two differences that some people may construct as Oracle advantages.
Oracle runs on multiple O/S
SQL Server runs only on top of WinTel while Oracle would run even in a blender if you can put on it anyone of a wide variety of O/S including but not limited to Windows, Unix, HP-UX, IBM-AIX, Linux, Solaris, etc.
Oracle RAC
Oracle RAC is a functional, reliable high-availability solution. There is nothing like Oracle RAC in SQL Server
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 13, 2010 at 3:51 pm
Eric Russell 13013 (10/13/2010)
On the other hand, SQL Server is newer ...
Always remember SQL Server carries Sybase genes; don't look at SQL Server 4.0 as SQL Server date of birth 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 13, 2010 at 3:55 pm
In my opinion, both Oracle and SQL Server have their place. What I have seen, however, is that the decision to use one over the other is usually dictated by "politics" more than what is the best tool for a particular application or environment. It seems to me that SQL Server can handle many of the environments that Oracle is used and do so at a lower cost. I have seen that Oracle has reduced licensing costs over the past 10 years, but SQL Server still costs less and provides more out of the box.
That said, there will still be places where Oracle may make better sense, such as where RAC is needed.
Bottom line, use the tool that makes sense technically and fiscally, not just politically.
October 13, 2010 at 4:45 pm
Lynn Pettis (10/13/2010)
In my opinion, both Oracle and SQL Server have their place. What I have seen, however, is that the decision to use one over the other is usually dictated by "politics" more than what is the best tool for a particular application or environment. It seems to me that SQL Server can handle many of the environments that Oracle is used and do so at a lower cost.
I couldn't agree more.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 13, 2010 at 7:38 pm
ta.bu.shi.da.yu (9/17/2010)
2. Oracle has implemented more of the SQL standard that Microsoft. Case in point: try doing a lag, lead, or top analytic function in SQL Server... you can't! You can only use rank, denserank, and row_number. If you were able to use lag(column) over () then I'm almost certain we wouldn't need to be using non-supported clustered index approaches to running total aggregates... though we wouldn't have had one of the coolest articles around on this approach, of course 🙂
Heh... thanks for that. As a side bar, there is a super fast way to guarantee either that the running total worked or get a notification that it didn't that Paul White and Tom Thompson came up with. That's a good thing because just before that, Wayne S. was the first and only person I've ever met that broke a previously good running example. I'm thinking I need to update the article (hopefully) one more time and then maybe retire. 😛
Shifting gears, I wouldn't necessarily say that Oracle is ahead of the game for SQL standards. The last time I looked, Oracle had nothing with DATEDIFF, DATEADD, etc, etc and, IIRC, those are part of the SQL standards.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 14, 2010 at 12:33 am
Here are my thoughts after going from many years SQL Server to an extended project in Oracle:
I certainly found a lot of things in Oracle quite 'alien' to me and was initially very resistant and cynical.(particularly having to use a third party tool to develop - TOAD which I think has a user interface like a car accident).
The poster who mentioned that Oracle has loads of baggage is really on the money. MS can now sit back and emulate all the good parts of Oracle without having to take on the baggage!
I'm impressed with Oracle packages as they almost seem like classes to me
-Variables which 'reflect' existing table meta data
-Custom data types
-Multiple 'methods' within the package all returning different types
So you can truly wrap up a large piece of logic in a package which has various methods which can be called.
The other thing that impresses me is the optimiser and the way it can decompose multiple levels of views. You can define many layers of views to contain your business logic and usually be happy that it will still perform. Back in SQL 2000 I tried doing this and it appeared to be effectively materialising the data at each level rather than doing any smart 'rewriting' - maybe things have changed by SQL 2008 though.
In summary, you'd be well served to jump the hurdle and get some experience to make an informed judgement about the two different platforms.
PS at a current client we indeed have what seems to be a political decision to move from Oracle to SQL Server.
October 14, 2010 at 6:00 am
Jeff Moden (10/13/2010)
ta.bu.shi.da.yu (9/17/2010)
2. Oracle has implemented more of the SQL standard that Microsoft. Case in point: try doing a lag, lead, or top analytic function in SQL Server... you can't! You can only use rank, denserank, and row_number. If you were able to use lag(column) over () then I'm almost certain we wouldn't need to be using non-supported clustered index approaches to running total aggregates... though we wouldn't have had one of the coolest articles around on this approach, of course 🙂Heh... thanks for that. As a side bar, there is a super fast way to guarantee either that the running total worked or get a notification that it didn't that Paul White and Tom Thompson came up with. That's a good thing because just before that, Wayne S. was the first and only person I've ever met that broke a previously good running example. I'm thinking I need to update the article (hopefully) one more time and then maybe retire. 😛
Shifting gears, I wouldn't necessarily say that Oracle is ahead of the game for SQL standards. The last time I looked, Oracle had nothing with DATEDIFF, DATEADD, etc, etc and, IIRC, those are part of the SQL standards.
Really? OK, that surprises me... good pickup! As far as I'm aware, Oracle doesn't have a Connect like Microsoft does, so I guess there's no way of providing feedback!
October 14, 2010 at 6:45 am
If you look at these two posts, especially the signature, and that these are the only posts ever from this poster, they feel more like spam for their kitchen cabinets.
harrifolfenced (10/13/2010)
what are advantages of oracle over sql
harrifolfenced (10/13/2010)
thanks for above links i also solved my stored procedure code
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply