Is it really Horricle?



  • I am still a lowly CS student (hides from the barrage of cabbage and assorted spoiled fruits), and I have dared venture into the rabbit hole of Oracle.

     

    So far, there has been ups and downs. Well.. mostly downs, but when I finally get an 'up' that just makes it feel so much better. Solving a particularly troublesome problem is by far much better than solving a trivial problem.

     

    First, I tried the XE, but it lacked the features needed for our data warehouse. Then, I went with the Enterprise. Installing isn't bad - uninstalling is a different issue, but nothing too bad (renaming/moving a few files and deleteing them after a boot).

    So far, I've managed to get the data warehouse running, with a few spatial dimensions. Sure there are a few things that are different from other databases. One thing that didn't dawn on me immediately was the permission management - that each user has it's own namespace etc. But given an instruction of a few sentences, it all makes sense.

    Writing the actual spatial queries is a bit of a mess, I admit, and actually requires *shudder* reading documentation.

     
    I do however fail to see many people grievances - the biggest problem with Oracle IMHO is that it is mindnumbingly complex. But I think it is hard to be anything else with the sheer amount of built-in features.
     

    When reading some of the WTFs on this site, I think it is actually quite a feat to build a DBMS with such a large feature set and still have it work without swallowing your soul. And the cross-platform portability is impressive to say the least.

     Now, installing Enterprise on my old laptop with 512MB RAM was a bit optimistic, and I still wonder what it uses >600MB of RAM for when no data is in any table in the system, but then again - I doubt it was ever intended to be used in the way I am using it atm.

     

    So, what are peoples' problems with oracle?

    - Aside from having a type called VARCHAR2, of course.. 



  • I still think the VARCHAR2 thing is overblown. It makes perfect sense when you realize that it's referring to a 2-byte varchar column. It's not any dumber than SQL Server's NVARCHAR, anyway.



    Disclaimer: I'm actually an SQL Server guy, not an Oracle fanboy/apologist or anything.



    Okay, commence ranting. :)



  • [quote user="Chucara"] 

    First, I tried the
    XE, but it lacked the features needed for our data warehouse.

    [/quote]

    Well, they do mention its limitations in the documentation and license agreement. XE is interesting for "small" applications and as an introduction to PL/SQL and other features. And the installation can be completed in a few minutes on supported distributions.

     
    l.
     



  • Most of the major complaints with Oracle are gone now with 10g - horrible installer, poor management utilities, bloated client install, etc.  There are still some minor grievances / quirks like the row number thing - but what software doesn't have quirks. 

    The problem with Oracle is that it is meant for very complex deployments - people use it in smaller solutions because they want the Oracle name associated with their product.  Is that Oracle's fault?  Not entirely, but they should offer a less feature rich product that isn't so complex for those who want to start simple and grow into the complexities.  I heard Oracle is offering a lite version like MS/IBM, but I haven't worked with it.

    Oracle is a very good product, but like any other solution it isn't right for everything/everyone.  The sooner people realize that, the better.

    Like db2, I'm not an Oracle fanboy.  I primarily develop against MsSql Server, but I do support various Oracle base solutions.  Oracle isn't hard if you have desire to read docs / search the internet.
     



  • The more simple a software is, the more complex the source code is.



  • Can't be as bad as Visual DataFlex (Read my many WTF's)



  • @lpope187 said:

    There are still some minor grievances / quirks like the row number thing - but what software doesn't have quirks.

    Row number thing? 



  • @seraphim said:

    @lpope187 said:

    There are still some minor grievances / quirks like the row number thing - but what software doesn't have quirks.

    Row number thing? 

    select * from foobar where rownum=1 order by foo ; /* doesn't work as expected /

    select * from (select * from foobar order by foo) where rownum=1; / works */ 



  • It works exactly as I expect it :)



  • It does not work as expected as rownum is a pointer to the row in storage which is used internally and is not designed to be used as storage in a query. If you check the oracle documentation it will tell you that.

    Every database stores it's data out of order you need to define what order and what column should be used to sort the output.



  • coming from a SQL Server background that would have confused me.  I would first have been astonished that Oracle didn't support top, then when i found the 'equilivent' and it didn't work, the ranting would have begun.  Eventually I might have read some docs and though, ahh, that makes sense.

    I still think the VARCHAR2 thing is overblown. It makes perfect sense when you realize that it's referring to a 2-byte varchar column. It's not any dumber than SQL Server's NVARCHAR, anyway.

    I didn't think varchar2 was just a unicode varchar type.  I thought it had some other differences.  Mind you, Ingres (yes, we still use Ingres at work, as well as Oracle and SQL Server) has a type called varchar and a type called char.  The difference is that spaces aren't significant in comparisons of the varchar type.  They both take up the declared length all the time.

    My problem with Oracle has always been getting at its data, in particular from .NET.  The Oracle supplied provider was dreadful, the MS one at least worked but still had some very strange behavior.

     



  • @dns_server said:

    It does not work as expected

    That depends on what you expect.  Given that syntax, IMHO it would be a WTF if it [i]didn't[/i] behave the way it does.



  • @ammoQ said:

    @seraphim said:

    @lpope187 said:

    There are still some minor grievances / quirks like the row number thing - but what software doesn't have quirks.

    Row number thing? 

    select * from foobar where rownum=1 order by foo ; /* doesn't work as expected */

    select * from (select * from foobar order by foo) where rownum=1; /* works */ 

    It works exactly as expected, if you understand the rules.  An ORDER BY on a query sorts the results of the query; that is, after the WHERE clause executes.  If it did any differently, I would be shocked.  Be glad you have ROWNUM, and don't have to resort to the mind-numbingly retarded crap that SQL Server puts you through just to have an incrementing number in the result set.



  • @nerdydeeds said:

    It works exactly as expected, if you understand the rules.  An ORDER BY on a query sorts the results of the query; that is, after the WHERE clause executes.  If it did any differently, I would be shocked.  Be glad you have ROWNUM, and don't have to resort to the mind-numbingly retarded crap that SQL Server puts you through just to have an incrementing number in the result set.

    I'll agree SQL was retarded prior to 2005, but now it is easier and more clear to get a row number.  For example

    SELECT   
        Id,
        Name,
        ROW_NUMBER() OVER(ORDER BY Name) as RowNumber
    FROM
        Groups

    Now the "problem" with Oracle is that is isn't clear or intuitive without reading the docs.  Essentially the confusion is that ROWNUM refers to the first row that was selected and not the row number returned.  Really the issue is when does ROWNUM get evaluated.  Does it get evaluated before or after the sort is applied?  Oracle evaluates it before while MsSQL evaluates it after.  In my experience, the vast majority of the time I don't care what the X row selected is but rather what X row returned is.  Therefore I'd like the majority of my SQL to be the easier and less verbose syntax. 

    It's not really a big deal - it just one of those things that tends to catch newbies by surprise the first time it happens. 




  • Just for my reference...  SQL2K5 allows you to do this:

    SELECT  Name, OrderId, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY OrderId) AS RowNumber
    FROM     Clients C
    JOIN       Orders O
      ON       C.Id = O.ClientId

    Which will number each person's orders independently of the others (ie:  Start the numbering over with each new name)

    Can Oracle do this?



  • @RaspenJho said:

    Just for my reference...  SQL2K5 allows you to do this:

    SELECT  Name, OrderId, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY OrderId) AS RowNumber
    FROM     Clients C
    JOIN       Orders O
      ON       C.Id = O.ClientId

    Which will number each person's orders independently of the others (ie:  Start the numbering over with each new name)

    Can Oracle do this?

    Where do you think MS got the idea from (along with snapshot isolation, materialized views, etc)?   It may not have been Oracle, but it's very clear they integrated some of the best ideas from other databases into SQL2005. 

    BTW, this is one of the better Oracle reference sites I've found.  http://psoug.org/reference/library.html

     



  • @lpope187 said:

    @nerdydeeds said:

    It works exactly as expected, if you understand the rules.  An ORDER BY on a query sorts the results of the query; that is, after the WHERE clause executes.  If it did any differently, I would be shocked.  Be glad you have ROWNUM, and don't have to resort to the mind-numbingly retarded crap that SQL Server puts you through just to have an incrementing number in the result set.

    I'll agree SQL was retarded prior to 2005, but now it is easier and more clear to get a row number.  For example

    SELECT   
        Id,
        Name,
        ROW_NUMBER() OVER(ORDER BY Name) as RowNumber
    FROM
        Groups

    Now the "problem" with Oracle is that is isn't clear or intuitive without reading the docs.  Essentially the confusion is that ROWNUM refers to the first row that was selected and not the row number returned.  Really the issue is when does ROWNUM get evaluated.  Does it get evaluated before or after the sort is applied?  Oracle evaluates it before while MsSQL evaluates it after.  In my experience, the vast majority of the time I don't care what the X row selected is but rather what X row returned is.  Therefore I'd like the majority of my SQL to be the easier and less verbose syntax. 

    It's not really a big deal - it just one of those things that tends to catch newbies by surprise the first time it happens. 



    The good news for you is Oracle has both ROW_NUMBER and ROWNUM. So you can go either way, depending on your needs.



  • @RaspenJho said:

    Just for my reference...  SQL2K5 allows you to do this:

    SELECT  Name, OrderId, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY OrderId) AS RowNumber
    FROM     Clients C
    JOIN       Orders O
      ON       C.Id = O.ClientId

    Which will number each person's orders independently of the others (ie:  Start the numbering over with each new name)

    Can Oracle do this?

    Maybe this thread helps to answer this question... (thanks to lofwyr)

    http://forums.oracle.com<wbr>/forums/thread.jspa?threadID<wbr>=529970



  • Thanks.



  • @dns_server said:

    It does not work as expected as rownum is a pointer to the row in storage which is used internally and is not designed to be used as storage in a query. If you check the oracle documentation it will tell you that.

    Every database stores it's data out of order you need to define what order and what column should be used to sort the output.

     

    rowid is the pointer to the row in storage; rownum is the order number assigned as rows are returned to the query. 



  • @Ice^^Heat said:

    Can't be as bad as Visual DataFlex (Read my many WTF's)

     Agreed. I work with Oracle and Dataflex servers everyday. Oracle is decent given the other options available. Doesn't Oracle own 80% of the database market or nearabouts?



  • The core RDBMS is solid enough, and can be quite a good product if you're willing to accept some strangeness and put in the effort required to learn it.  Where it becomes Horricle is when Oracle's other products are brought into the mix, and when they need to interoperate.  There's still a lot of client/server apps out there, and Oracle's approach to this can make grown men weep.  Their Java stuff is also disgusting (and very inadequately tested, IMO).


Log in to reply