This discussion is archived
1 3 4 5 6 7 26 Previous Next 385 Replies Latest reply: Aug 20, 2007 5:26 AM by Alessandro Rossi Go to original post RSS
  • 60. Re: Treatment of zero-length strings as NULLs?
    245482 Newbie
    Currently Being Moderated
    Albert,

    I understand exactly why you say

    "I wish Oracle had something similar to switch between its "empty-string-nulls" to "ANSI" nulls, something like

    SET ANSINULLS ON"

    It makes a ton of sense, but in my opinion only initially. One of the strengths of Oracle in my mind is that I can drop my code on any instance with the appropriate features (i.e. new enough and/or license level) and my code will work IN EXACTLY THE SAME MANNER. Having spend a good many years on the road as a consultant this is a huge win. On SQL Server the number of factors (e.g. collation, ansi nulls) makes this very painful.

    While I understand your genuine wish that Oracle's NULL was implemented a bit differently, there is no way that I want to have to remember to check how NULL is behaving before I drop my code on a given box.

    Cheers,
    Scott
  • 61. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    > But with Oracle? Still no temporary tables
    At the risk of wandering away from Albert's interesting question of why Oracle doesn't handle empty strings the way he thinks it should: huh? We have global temporary tables, we just don't have explicit local temporary tables. Even then, we have the MATERIALIZE hint for "WITH" subqueries, and various similar techniques for inline views etc which allow us to influence Oracle's internal temp table handling. I don't really see what Oracle's supposed to be missing here, or what it is you think you have to "clean up".

    > And how about an identity column in Oracle? I tired of having to write a dumb trigger every time to implement a surrogate key.
    Well, there's SYS_GUID I guess though I'm not sure how much to recommend it.
  • 62. Re: Treatment of zero-length strings as NULLs?
    245482 Newbie
    Currently Being Moderated
    Dennis,

    Interestingly you've hit on two issues that are very closely related here. The fact that SQL Server now has multi-versioned data (aka row-level versioning) means that developers who have that feature turned on now almost never need temporary tables. The whole point of developing code that is centered around temporary tables is that you need to nab your data quickly and get the hell out -- at least in a READ COMMITED isolation level. If your query runs for any longer than necessary it is blocking everyone else.

    Now, with multi-versioned data you can just query what you want, insert/update/delete what you need to. You can lengthen out your transaction scope to match your business definition of a transaction. Now temporary tables are only needed for a very few uncommon cases.

    From that perspective consider Oracle. Because things have been like this for longer than I remember no one really feels the need for temporary tables very often at all. And on those occasions a permanent "global temporary table" whose definition lives in the data dictionary serves just fine.

    Cheers,
    Scott
  • 63. Re: Treatment of zero-length strings as NULLs?
    60660 Journeyer
    Currently Being Moderated
    No, now readers block writers forever. It was Wall
    Street's feature request. Accept this information as
    my New Year's Gift to you.
    Describing inferior technology is a gift? But if you insist.

    C.

    Message was edited by:
    cd
  • 64. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    I don't really see your point.
    I believe you.
    You can google for NULL? Great.
    Yes, I can. Thanks.
    I'm not denying that the SQL standard includes NULL
    (*), I'm just raising out the well established
    argument that inclusion of NULL at all is
    widely regarded as a major failing of the SQL
    "implementation" of the relational model.
    The concept of NULL (you do like concepts, don't you?) is one of the fundamentals in set theory and relational algebra. Saying that "a + b = a + b + 1" is specific arithmetic implementation and that I have to deal with it subtracting 1 from each and every one result of that operation, is completely beyond me and my mental circuit. The example perhaps is not equivalent, but the analogy is the same.
    In bickering over which failed vendor interpretation
    of a flawed concept is least ambiguous, the point is
    missed.
    Right. All what I wanted to know was why Oracle treated empty-strings as nulls. Now, I know the answer and, from my point of view, this discussion is finished.
    (*) We should all praise our lords that the SQL3
    standard initial draft didn't go ahead allowing
    multiple user defined NULL types.
    User defined NULL types? You really understand nothing. User defined NULL types would be the negation of NULL, ~NULL or (NOT)NULL that is again NULL.

    Good night and sleep sweetly.

    Albert
  • 65. Re: Treatment of zero-length strings as NULLs?
    60660 Journeyer
    Currently Being Moderated
    >>
    > So they've finally introduced MVCC or the like?
    Writers don't block readers anymore?
    With MS SQL Server I thinks it's only been since 2005
    that writers don't have to block readers. So in that
    sense they are catching up.
    You mean that snapshot view? That's not really MVCC, is it?
    But with Oracle? Still no temporary tables.
    You mean local temporary tables, because GTT are available.
    I counted
    today 500 of our 1200 tables are temp. tables (ie.
    permanent 'temporary' tables). I didn't make them
    myself - but it looks like I'll have to clean them
    up. Sure, in Oracle you can have temporary data but
    the table is still permanent.
    I'd say that's a discussion about taste. Some people prefer table creation on the fly, others a fixed data model. There are advantages/disadvantages for both approaches.
    And how about an identity column in Oracle? I tired
    of having to write a dumb trigger every time to
    implement a surrogate key.
    I wouldn't mind the implementation from postgres. Nobody said there wasn't room for improvement in Oracle, but PL/SQL still rules ... ;-)

    C.
  • 66. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    (*) We should all praise our lords that the SQL3
    standard initial draft didn't go ahead allowing
    multiple user defined NULL types.
    User defined NULL types? You really understand nothing. User defined NULL types would
    be the negation of NULL, ~NULL or (NOT)NULL that is again NULL.
    I'm so sorry. I've read your double-quoted text wrong. Too tired. I do apologize.

    Regards

    Albert
  • 67. Re: Treatment of zero-length strings as NULLs?
    455796 Newbie
    Currently Being Moderated
    Dennis,

    Interestingly you've hit on two issues that are very
    closely related here. The fact that SQL Server now
    has multi-versioned data (aka row-level versioning)
    means that developers who have that feature turned on
    now almost never need temporary tables. The whole
    point of developing code that is centered around
    temporary tables is that you need to nab your data
    quickly and get the hell out -- at least in a READ
    COMMITED isolation level.
    Temporary Tables are required when something is not possible to do in one query.

    Such as:
    create T1 as ...
    create T2 as ...
    insert into T1 select ... from ...
    loop
    if ... then insert into T1 select ... from ...
    else insert into T1 select ... from ...
    end loop

    insert into T2
    select ...
    from T1, T3, T4
    where ...

    return T2



    With the 'With' statement this is less often required, but can still be required.
  • 68. Re: Treatment of zero-length strings as NULLs?
    27876 Newbie
    Currently Being Moderated
    I know what isolation levels mean in Sybase. In IBM DB2 too. It was unnecessarily to quote Trancast-SQL User's Guide. I've read it multiple times.
    It was NOT unnecessary to quote that link since it pertains to discussing the dirty reads.

    I'm not discussing isolation levels. All I was doing there is replying to your below statement.

    <quote>
    Dirty reads were never an option in Sybase. Where have you read this?
    </quote>

    with the link to the document where this concept is discussed.
  • 69. Re: Treatment of zero-length strings as NULLs?
    29240 Newbie
    Currently Being Moderated
    Hi All,

    I have been following this thread and it has been very interesting.

    I would have prefered to let sleeping dogs lie but just have to add this point.

    Who says SQL is a standard?. If it is, there would not have been any need for all this argument. Why MS Varchar and Oracle Varchar2 in the first place if they are not going to mean the same. The issue about not null saying different things by different vendors should have been taken up by the standards commission before rollout. Looks like RDBMSes vendors are uncontrollable by the ANSI SQL standards body as it may be believed to be taking them one step backward when you should have been two ahead, by this I mean making better an existing standard. It is fine to make better an existing standard, but if it now impacts on existing organisational data structure, then, this is where the control comes in even before going as far as designing it talkless of implementing it.

    Market competiton is one thing, making standards for competitors comformance another.

    Has the ANSI SQL standards commission no control over all these powerful vendors? Now RDBMSes vendors are now using these laxities as a competitve edge to sell their products by making it unique and different and more or less tie you in when you no longer can look back.

    Having said all this,

    It is a competitive RDBMS market and there are so many other issues or differences here and there. If you don't like this, then, go for that. And despite all these differences which negates SQL being a standard, it is still being regarded as a standard even though we still have to make amendments in an MS-SQL statement for it to work in an Oracle database environment and vice versa, customised codes to conform to disparate DBMSes and so and so.

    Lets just forget about standards, it does not exist. Even with the present XML standards, I hope it will not eventually become like another SQL issue. Lets just wait and see. You will say I said so.

    case closed


    Thanks

    Cube60
    Please let sleeping dogs
  • 70. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    It was NOT unnecessary to quote that link since it pertains to discussing the dirty reads.
    I'm not discussing isolation levels. All I was doing there is replying to your below statement.
    <quote>
    Dirty reads were never an option in Sybase. Where have you read this?
    No, no, you were, in fact, discussing the isolation level 0. The link you gave "Dirty reads" is a subtitle of "Choosing an isolation level" parent title. Furthermore, there is no sense discussing the dirty reads outside the concept of isolation levels. Namely, dirty reads are exclusively related to the isolation level 0, but opposite is not true. The isolation level 0 is much more extensive term where neither "dirty reads" nor "reads at all" are most relevant terms. Without the isolation level 0 dirty reads don't exist. Without dirty reads, the isolation level 0 still exists. In fact, isolation level 0 means "extremely fast", where dirty reads are just one of "nus-products" due to the absence of consistency checking, just the trade-off between speed and consistency. So, I will repeat:

    DIRTY READS WERE NEVER AN OPTION IN SYBASE. THE ISOLATION LEVEL 0 WERE.

    but dirty reads and the isolation level 0 are not synonyms.

    Cheers

    Albert
  • 71. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    The concept of NULL (you do like concepts, don't
    you?) is one of the fundamentals in set theory and
    relational algebra.
    Someone here has already pointed out that Empty Set has nothing to do with SQL NULL.

    For fact: The SQL NULL is an an ad hoc deviation from the relational model, it involves 3VL and is not represented in either relational algebra or relational calculus.

    As to SQL. To begin to address the incompleteness of the SQL NULL concept, it has already been widely shown that you'd need something like 23 value logic due to varying context of NULL. You've mentioned just 2, and infact then only in relation to character strings.

    SQL NULLs also violate the Information Principle, but this is nothing to do with the algebra or calculus. But this should be indicative that they not represent good idea.
    You really understand nothing.
    Thankyou, I think this was my point..! ;-)
  • 72. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Sorry William for my beeing late. I've just noticed your post as the post addressed to me. This thread's gone too far away, and here in Germany is 03:20 AM.
    Of course, you didn't, for God's Sake!
    Then why quote it? Sorry but I don't see the point of the quote if it doesn't support your quite
    interesting argument that Oracle is in violation of some alleged standard.
    William, what am I to do with you? Am I here to explain every word I wrote? I am pretty sure you are the only member of this thread who intentionally doesn't understand anything. If my problem is English, what do you think about Chinese? Now, you want me to explain even the quotes around the attributes TOUCHED and NON-TOUCHED. Ok, baby, I'll do even that.

    I've quoted them because I've intentionally put them in front of the word COLUMN that was not their natural place. How to touch a column in a table? With fingers? Their natural place was the word FIELD (app form field), without quotes, because a form field can be touched by a key on the keyboard. Since the use of the word FIELD was out of the question in the context of Oracle's empty-string-nulls, I respectfully quoted them and put them in front of the word COLUMN, that is much closer word to Oracle's world.
    Yes I used to work with Oracle Forms, which has most of that stuff. This touching business
    never came up though. I suppose being an Oracle tool it wouldn't do.
    Maybe you're right. That touching business means sensibility. Try Clarion 6.3. It has a driver for Oracle, though very expensive. In five minutes (quick start mode) you get real database application - truly binary executable, directly run by processor (unlike Java's bytecode). See:

    http://www.softvelocity.com

    Cheers

    Albert
  • 73. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Now, I must go sleeping and cannot discuss.

    I've already done this. Once again:
    (*) We should all praise our lords that the SQL3
    standard initial draft didn't go ahead allowing
    multiple user defined NULL types.
    User defined NULL types? You really understand nothing. User defined NULL types would
    be the negation of NULL, ~NULL or (NOT)NULL that is again NULL.
    I'm so sorry. I've read your double-quoted text wrong. Too tired. I do apologize.

    Regards

    Albert
  • 74. Re: Treatment of zero-length strings as NULLs?
    245482 Newbie
    Currently Being Moderated
    cd,

    I believe that Dennis is referring to "row level versioning" in SQL Server 2005. It is MVCC, but you enable it table-by-table. This makes for some mean applications to debug, as varying conditions hold/fail across a single application. Consider table A that has row level versioning enabled and table B that does not. Now consider joins between them under conditions where external dml is hitting A. Oh the joy.

    Unfortunately, there is scads of t-sql out there that expects readers to block writers -- so MS can't exactly turn around and make multi-versioned data the default. Perhaps in another few versions that will be the case, but it really doesn't seem to me that MS is pushing row level versioning as hard as they should so who knows?

    Scott
1 3 4 5 6 7 26 Previous Next