Sorry if this sounds whiney, but it's something I find myself explaining to nearly every new Oracle developer, and I've never heard a really good reason for why not. Space: if I'm using a fixed width multi-byte character set, doesn't a char(1) boolean take up more space than a universal boolean would? Speed: doesn't that check constraint take a tiny amount more time than validating against conformance to a particular datatypes? PL/SQL compatibility: It would be nice to be able to declare booleans using "var_name%TYPE".Īnd just to clarify on performance issues: Someone who spoke another language might choose two different characters. When I needed my first one, I used T and F. It would be nice to not have to make that conversion every time.Ĭonsistency: Y/N values with a check constraint are one way to represent a boolean. Sure, if it's a char(1) that's a good indication, but not a sure-fire one.Ĭompatibility: I know you're suspicious of products developed for multiple platforms, but there are a lot of them out there. Understanding NULL values and their quirks and dealing with it is a "best practice".Clarity: if the value I'm trying to store really is a boolean, then when I'm glancing at my model, it's clear that that's what the column is. But those quirks are usually overcome with appropriately defined indexes and carefully written queries. We do note that there are some quirks with some database implementations, not making use of an index to satisfy a col IS NULL predicate. But in my book, "avoiding dealing with NULLs" is not a "best practice". And it's true that having a column defined as NOT NULL may somewhat ease their burden. Some set of application developers may not like (or understand) how to deal with the nuances of NULL values. The NULL and tri-valued boolean logic has been around forever (since E.F.Codd first coined "relational" in 1970, the advent of System/R and Oracle in 1977, and DB2 in 1983. It doesn't go against any "best practices" I'm aware of. Q: Is checking whether a column is NULL go against best practices? adding the (redundant) approved column flies in the face of third normal form, and the familiar mantra "Every attribute is dependent on the key, the whole key, and nothing but the key. (And we're setting aside here a discussion of redundant data and update anomalies. We can't exclude some exceptional corner case where the addition of that column would be of benefit, but in general, given the information provided, no, there's no "speed benefit" to adding that column. That would require additional blocks (space), and would add overhead for maintenance of the index entries. an index on that column would not be negligible. While the additional byte for the approved column is negligible (assuming that's defined as TINYINT, that extra byte will not really impact the number of rows that "fit" in a block). In this case, it's very unlikely that there would be any query would obtain a "speed benefit" from using the approved column vs approved_dttm IS NULL. Q: Is there any speed benefit to using a boolean field?
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |