Some Oracle praise?
-
Jörgen Andersson wrote:
it's a little bit like going from VB to C#.
Thank you so much! Now I have to wipe away the stains of tea from my monitor... :-D
Glad to be of service. :)
Wrong is evil and must be defeated. - Jeff Ello
-
Do people here actually like the Oracle database? Especially when compared to SQL Server. It seems everything I did so easily in SQL Server seems to be difficult or even impossible in Oracle. So far I like the for loops and the some_table.some_column%type type declarations. I'm not looking for Oracle hate or a religious war. I really just want to know what's so great about Oracle so I can enjoy it too (so far it's been mostly frustration).
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
It is maybe the order I learned things, but I do not find Oracle that difficult (my first was DB2 on mainframe), but it seems to be less advanced than MSSQL these days, so can't see any reason to bother with in the first place (it even seems to me that most free RDBMS offer more than Oracle for some problems)... So. No good words for Oracle...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
You can think of pipelined functions in the same way as
yield returnin C#. Flashback really is the best thing since sliced bread. It's a bit of a performance drain, but still faster than SQL Server though. Ever wondered why you get the query time in seconds in SSMS and in ms from Oracle? ;PSander Rossel wrote:
Unfortunately, due to awful tooling from Oracle
Well, I certainly give you that one, SSIS is simply awesome despite some quirks.
Sander Rossel wrote:
you're actual DBA's
I'm not, I'm both a backend developer and database developer. No admin in my title to be found.
Sander Rossel wrote:
but that's not very interesting to the average developer?
That's maybe where the problem is. People don't understand what's going on in the databases, and SQL-Server is much better integrated with Visual Studio so it's really quite hidden from people. I once saw someone here comment that the biggest problem with Access is that it makes it to easy for people that don't know what they are doing to create a database. Well that statement really holds a bit of truth for SQL Server as well. That's also why we're seeing these reactionist NOSQL databases which is really bringing us back to the sixties. BTW, one of the big differences between PL-SQL and T-SQL is that there is a lot of implicit stuff going on in T-SQL. In PL-SQL you have to define everything. And that's probably why you thought you can only have one return set from PL-SQL.
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
Ever wondered why you get the query time in seconds in SSMS and in ms from Oracle?
I never got a timeout in Oracle and plenty in SQL Server... :sigh: I SHOULD mention that I haven't worked with Oracle like I have with SQL Server (few months development in Oracle vs. years of development and production in SQL Server).
Jörgen Andersson wrote:
I'm not, I'm both a backend developer and database developer. No admin in my title to be found.
Still, your profile says "Database developer" rather than "Software developer" ;)
Jörgen Andersson wrote:
People don't understand what's going on in the databases
Very true, although my experience is that people don't know what's going on inside C# (or any technology for that matter) as well. Most people are missing theoretical knowledge because that's just not interesting, we're practical and need results :sigh: Just recently a coworker complained that a
SELECT TOP 10 * FROM SomeViewwas very slow so we should move the select statement from the view to an SP and pass in the TOP 10 as parameter to that SP. Yes, because that's certainly going to make it a lot faster... :~ (his idea was actually that the entire view was selected (and the view didn't have a top 10) and that the top 10 was applied after that).Jörgen Andersson wrote:
That's also why we're seeing these reactionist NOSQL databases which is really bringing us back to the sixties.
I have to disagree there! I'm no NoSQL expert, but I really think NoSQL databases solve actual problems and can be successfully leveraged to create highly performant systems where traditional relational databases would fail miserably. Of course you still have to know what you're actually doing.
Jörgen Andersson wrote:
that's probably why you thought you can only have one return set from PL-SQL
Nope, I actually want to see exactly this[^] in SQL Developer, but it's impossible :)
-
Schema is used for example to define an ownership and to separate logically different components into separate areas, in both systems. A separate database in SQL Server is very different from schema.
Exactly. Correct me if I am wrong, but it is usually just "one" database in Oracle with _n_Number of schemas.
-
Slacker007 wrote:
schemas (oracle)
SQL Server has schemas too[^]. They used to be tied to user accounts in SQL 2000, but that was fixed in SQL 2005[^].
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thanks, Richard but schemas in SQL Server are a bit different than in Oracle, as Mika and Sander pointed out.
-
Mika Wendelius wrote:
If these cause you headache
The headache is actually in doing any of that ad-hoc :laugh: Open up a window in SQL Developer and typing:
DECLARE x NUMBER;
x := 5;
SELECT x FROM DUAL;won't work at all :D
Mika Wendelius wrote:
That's actually a pretty good article, will read it :thumbsup:
Mika Wendelius wrote:
we can take the discussion outside, for example to Q&A
I could create a question so that you specifically could answer it :laugh: I don't have access to Oracle right now though, so I can't post anything at the moment :)
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
This works ... :doh:
DECLARE
x NUMBER;
y NUMBER;
BEGIN
x := 5;
SELECT x into y FROM DUAL;
dbms_output.put_line(y);
END;:-D
The environment that nurtures creative programmers kills management and marketing types - and vice versa. - Orson Scott Card
Joe Simes wrote:
dbms_output.put_line(y);
I know, but having many fields and rows (or a few with variable length) requires a gigantic put_line which will become unreadable. I really want my result to be in a table :D
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
I actually have a theory that PL/SQL actually stands for Polish SQL (as in Poland, the country). First, it's like Polish, I don't understand a word of what they say. Second, Polish are to us what Mexicans are to Americans, cheap labor (stealing our jobs)*. Larry just hired some cheap Polish people and let them make a database, hence Oracle and PL/SQL was born :D *Not particularly my opinion, no offence to Polish and Mexicans :D
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Why do the Poles always get a bad rap, no matter what country is speaking about them? I always found that interesting.
-
Jörgen Andersson wrote:
it's a little bit like going from VB to C#
I actually did just that (VB.NET mind you!) and all the glorious stories I heard, about C# being the better and prettier language, were proven not true. It seems idiots write in C# as well :sigh: And indeed, I went from a SQL Server team to an Oracle team and suddenly everyone uses
select ... from table1, table2 where table1.id = table2.table1_idto join tables... :sigh: Not what I was expecting from the grande Oracle database (that, and everything else in this topic) ;pRead my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Teaching old dogs new tricks. Well I rarely use implicit joins, and do you know why, you can't decide the order of the joins (outer) if they're implicit. Better get used to a consistent way of doing things. One reason that the old people use implicit joins is that on Oracle the optimizer was for a long time better at handling implicit joins. But sometimes when I don't get the performance I expect I try implicit joins instead of ANSI joins. It's all about the order of execution. (I'm having an article about that on the way)
Wrong is evil and must be defeated. - Jeff Ello
-
Eddy Vluggen wrote:
You're over 15 years too late to 'enjoy' the Oracle database-server.
Thought as much :sigh:
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
Exactly. Correct me if I am wrong, but it is usually just "one" database in Oracle with _n_Number of schemas.
Usually yes, you can create several databases in one instance, but it's pointless.
Wrong is evil and must be defeated. - Jeff Ello
-
Teaching old dogs new tricks. Well I rarely use implicit joins, and do you know why, you can't decide the order of the joins (outer) if they're implicit. Better get used to a consistent way of doing things. One reason that the old people use implicit joins is that on Oracle the optimizer was for a long time better at handling implicit joins. But sometimes when I don't get the performance I expect I try implicit joins instead of ANSI joins. It's all about the order of execution. (I'm having an article about that on the way)
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
One reason that the old people use implicit joins is that on Oracle the optimizer was for a long time better at handling implicit joins.
I actually read that today :wtf: The reason I don't like it is because it really obscures your where clause and invites bugs because you forgot to join two tables in the where clause (actually happened today, that's why I read about it). It shouldn't matter for your performance though, so I was pretty surprised it DOES matter for performance in Oracle (and not in the way that you want either).
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
Have you used their in-memory database already? Oracle TimesTen Software Downloads[^]
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
Frustration TimesTen? No thanks! :laugh:
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
Brent Jenkins wrote:
Oracle is (IMO) more powerful than SQL Server
I've heard that a lot (it's what every Oracle user says), but why though? I've asked people, but never got a satisfying answer. One person even said "SQL Server can't handle big databases that are larger than 30GB." :wtf: I've heard "Oracle has packages[^]", but I fail to see what's so great about that. I have a header for public access and my package body can have some private stuff. First of all, the entire header is duplicated in the body, which really annoys me (and I know other languages have it too). But why would you want private functions in your database anyway? They're only going to be used by your own software and it's not like you get all those private functions in your intellisense! A package, to me, is really only a set of functions and procedures that can be edited by only one person at a time (or the last one who saves overwrites the other's changes). Besides, if you want to "group" stuff in SQL Server just use a schema (I know, not entirely the same). If anything, packages are "nice" at best. Next to that Oracle doesn't support booleans/bits, it only has one numeric type that fits all, it doesn't have table variables like SQL Server has, you can't write ad-hoc scripts with some variables and return them in a table (I haven't found it anyway). And forget about decent tooling too! The company where I currently work even created their own Toad-like tool for working with Oracle because no tool sufficiently did what they wanted (back in the 80's early 90's). I use Oracle SQL Developer, but that's an amateurish play toy compared to even the Express version of SQL Server Management Studio. Good luck with the documentation too, not nearly as comprehensive as SQL Server. And we're talking about one of the most expensive databases out there (if not THE most expensive)! :~ Here comes the best part, Oracle people now tell me I was "spoiled" by SQL Server and its features and tooling and now I fail to see how awesome Oracle is... WHAT THE...!? :wtf: The only plausible thing I've heard so far is that Oracle is faster because it locks at cell level while SQL Server locks at row level and often escalates to locking an entire page (and and I've heard an Oracle user say S
Sander Rossel wrote:
I've heard "Oracle has packages[^]", but I fail to see what's so great about that.
Think of them as namespaces, that usually helps. And complaining about the existence of access modifiers is just silly, how do you know it's only going to be your own software in the database. Why do you think there are access modifiers in DotNet?
Sander Rossel wrote:
Next to that Oracle doesn't support booleans/bits,
The bit IS a numeric type. :doh:
Sander Rossel wrote:
Good luck with the documentation too, not nearly as comprehensive as SQL Server.
Unless there's some documentation outside of MSDN and Technet that I haven't found, that is simply not true.
Sander Rossel wrote:
The only plausible thing I've heard so far is that Oracle is faster because it locks at cell level while SQL Server locks at row level and often escalates to locking an entire page (and and I've heard an Oracle user say SQL Server always locks entire pages).
There is no such thing as a page lock in Oracle. Oracle has row-level locks and table-level locks.
Sander Rossel wrote:
But honestly, after hearing about how powerful Oracle is supposed to be I'm REALLY VERY disappointed now that I actually have to work with it.
Actually, it depends on what you're doing, OLTP -> Oracle but when doing OLAP I'd say SQL Server has the upper hand. But that would also be to simplified. It really depends.
Sander Rossel wrote:
Oracle doesn't even handle CASING PROPERLY
So it's not case sensitive, but that's something you should be used to from VB.
Wrong is evil and must be defeated. - Jeff Ello
-
Joe Simes wrote:
dbms_output.put_line(y);
I know, but having many fields and rows (or a few with variable length) requires a gigantic put_line which will become unreadable. I really want my result to be in a table :D
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
DECLARE
CURSOR c is SELECT * FROM DUAL;
r dual%ROWTYPE;
d dual.dummy%TYPE;
BEGIN
open c;
fetch c into r;
d := r.dummy;
dbms_output.put_line(d);
END;;P
The environment that nurtures creative programmers kills management and marketing types - and vice versa. - Orson Scott Card
-
Jörgen Andersson wrote:
Ever wondered why you get the query time in seconds in SSMS and in ms from Oracle?
I never got a timeout in Oracle and plenty in SQL Server... :sigh: I SHOULD mention that I haven't worked with Oracle like I have with SQL Server (few months development in Oracle vs. years of development and production in SQL Server).
Jörgen Andersson wrote:
I'm not, I'm both a backend developer and database developer. No admin in my title to be found.
Still, your profile says "Database developer" rather than "Software developer" ;)
Jörgen Andersson wrote:
People don't understand what's going on in the databases
Very true, although my experience is that people don't know what's going on inside C# (or any technology for that matter) as well. Most people are missing theoretical knowledge because that's just not interesting, we're practical and need results :sigh: Just recently a coworker complained that a
SELECT TOP 10 * FROM SomeViewwas very slow so we should move the select statement from the view to an SP and pass in the TOP 10 as parameter to that SP. Yes, because that's certainly going to make it a lot faster... :~ (his idea was actually that the entire view was selected (and the view didn't have a top 10) and that the top 10 was applied after that).Jörgen Andersson wrote:
That's also why we're seeing these reactionist NOSQL databases which is really bringing us back to the sixties.
I have to disagree there! I'm no NoSQL expert, but I really think NoSQL databases solve actual problems and can be successfully leveraged to create highly performant systems where traditional relational databases would fail miserably. Of course you still have to know what you're actually doing.
Jörgen Andersson wrote:
that's probably why you thought you can only have one return set from PL-SQL
Nope, I actually want to see exactly this[^] in SQL Developer, but it's impossible :)
Sander Rossel wrote:
I never got a timeout in Oracle and plenty in SQL Server.
Probably a setting though.
Sander Rossel wrote:
his idea was actually that the entire view was selected (and the view didn't have a top 10) and that the top 10 was applied after that)
He might be right you know, check the plan. That's also why you should avoid views with joins as the plague! I never use views for anything but restricting access or prefiltering tables.
Wrong is evil and must be defeated. - Jeff Ello
-
Sander Rossel wrote:
I've heard "Oracle has packages[^]", but I fail to see what's so great about that.
Think of them as namespaces, that usually helps. And complaining about the existence of access modifiers is just silly, how do you know it's only going to be your own software in the database. Why do you think there are access modifiers in DotNet?
Sander Rossel wrote:
Next to that Oracle doesn't support booleans/bits,
The bit IS a numeric type. :doh:
Sander Rossel wrote:
Good luck with the documentation too, not nearly as comprehensive as SQL Server.
Unless there's some documentation outside of MSDN and Technet that I haven't found, that is simply not true.
Sander Rossel wrote:
The only plausible thing I've heard so far is that Oracle is faster because it locks at cell level while SQL Server locks at row level and often escalates to locking an entire page (and and I've heard an Oracle user say SQL Server always locks entire pages).
There is no such thing as a page lock in Oracle. Oracle has row-level locks and table-level locks.
Sander Rossel wrote:
But honestly, after hearing about how powerful Oracle is supposed to be I'm REALLY VERY disappointed now that I actually have to work with it.
Actually, it depends on what you're doing, OLTP -> Oracle but when doing OLAP I'd say SQL Server has the upper hand. But that would also be to simplified. It really depends.
Sander Rossel wrote:
Oracle doesn't even handle CASING PROPERLY
So it's not case sensitive, but that's something you should be used to from VB.
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
complaining about the existence of access modifiers is just silly
I just don't think they're necessary in the database. And that a header file is the most obnoxious way to support it :laugh: When I first started using Oracle I've searched for a good hour trying to find some weird error, then found out I updated my function declaration in the body, but not the header... :doh:
Jörgen Andersson wrote:
The bit IS a numeric type.
Yes, but one that supports only two values and is correctly converted to bool in C# (and somewhat awkwardly in SSMS).
Jörgen Andersson wrote:
Unless there's some documentation outside of MSDN and Technet that I haven't found, that is simply not true.
Maybe I just don't know where to look :laugh: I keep ending up on oracle.com, but so far I haven't been able to find what I'm looking for (then again, searching for table variables in Oracle isn't going to give you what you're looking for).
Jörgen Andersson wrote:
So it's not case sensitive, but that's something you should be used to from VB.
At least VB doesn't convert EVERYTHING TO UPPER CASE for me :rolleyes: The casing is the least of my problems though. I just wonder why they can't simply support RegularCasing in 2016.
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
Sander Rossel wrote:
I never got a timeout in Oracle and plenty in SQL Server.
Probably a setting though.
Sander Rossel wrote:
his idea was actually that the entire view was selected (and the view didn't have a top 10) and that the top 10 was applied after that)
He might be right you know, check the plan. That's also why you should avoid views with joins as the plague! I never use views for anything but restricting access or prefiltering tables.
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
He might be right you know, check the plan.
HE MIGHT be right that an SP would be faster, but not because the TOP 10 is applied after selecting everything from a view :rolleyes: I did check the plan and it was really pretty hopeless. Basically the view was this: SELECT SomeFields FROM BigTable WHERE ... UNION SELECT SomeOtherFields FROM BigTable WHERE ... Now to get that to work we probably DO need an SP because to get the top 10 the selects on both sides of the union needed to be completely evaluated, then sorted, and then top 10'd. I wouldn't really know how to optimize that in a view. It's probably faster to take both top 10's, union and sort those and take another top 10 :)
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
Jörgen Andersson wrote:
He might be right you know, check the plan.
HE MIGHT be right that an SP would be faster, but not because the TOP 10 is applied after selecting everything from a view :rolleyes: I did check the plan and it was really pretty hopeless. Basically the view was this: SELECT SomeFields FROM BigTable WHERE ... UNION SELECT SomeOtherFields FROM BigTable WHERE ... Now to get that to work we probably DO need an SP because to get the top 10 the selects on both sides of the union needed to be completely evaluated, then sorted, and then top 10'd. I wouldn't really know how to optimize that in a view. It's probably faster to take both top 10's, union and sort those and take another top 10 :)
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Sander Rossel wrote:
, but not because the TOP 10 is applied after selecting everything from a view
Sander Rossel wrote:
because to get the top 10 the selects on both sides of the union needed to be completely evaluated, then sorted, and then top 10'd.
That's a bit contradicting you know. :)
Sander Rossel wrote:
It's probably faster to take both top 10's, union and sort those and take another top 10
That's it. And that's exactly why I avoid views. You're forgetting what's happening underneath.
Wrong is evil and must be defeated. - Jeff Ello
-
Do people here actually like the Oracle database? Especially when compared to SQL Server. It seems everything I did so easily in SQL Server seems to be difficult or even impossible in Oracle. So far I like the for loops and the some_table.some_column%type type declarations. I'm not looking for Oracle hate or a religious war. I really just want to know what's so great about Oracle so I can enjoy it too (so far it's been mostly frustration).
Read my (free) ebook Object-Oriented Programming in C# Succinctly. Visit my blog at Sander's bits - Writing the code you need. Or read my articles here on CodeProject.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
I do see a number of benefits to Oracle. It is really good at crunching really huge volumes. It commands extraordinary rates for consultants It takes a DBA to maintain it I think it requires a larger hardware footprint. It is an absolute fucking pain in the arse to develop against.
Never underestimate the power of human stupidity RAH
