Re: Question on Structuring Product Attributes

Again, the CHECK() constraint in Standard SQL does allow ANY valid search conditon. No product I know has implemented it and the only product with a CREATE ASSERTION statement was RDB, a product form DEC that was bought by OrACLE.

You asked for a Standard SQL and that was one solution.

Actually, the regular expression should have been the SIMILAR TO predicate. Again, this is ANSI/ISO Standard and based on POSIX from the Federal Standards. Have you ever downloaded a copy of the Standards?

The VIN is a special case because we can validate that the vehicle is an SUV from the VIN. Other identifiers might not have this property. If you want to see the power of regualr expressions, look up the pattern

>> When existence checking, one should *never* use "SELECT *", as that
drags the whole row into cache, and returns it to the thread, no matter what columns are used to qualify the query<<

LOL! Why does anyone still beleive that myth? There was an early version of Oracle that did that. It was correct the next release. The options were "SELECT * ", "SELECT <constant>", "SELECT <column>", and "SELECT <expression>" and they are all treated as *. Here we get into that "effectively" thing in Standard-speak. Since only the table cardinaltiy matters, the optimizer throws out the SELECT list in every SQL product I have used or consulted on.

The SELECT * is not required, but it is the preferred style because <column name> or <expression> can change, so the subquery will be invalid, <constant> tells you nothing about the nature of the query while SELECT * tells you that this is a table level construct, not dependent on the rows and columns. To stress this consider that EXISTS (SELECT * FROM .. WHERE..)
is equivalent to
(SELECT COUNT(*) FROM .. WHERE..) > 0 And that is how SQL products actually inplement it under the covers. The mechanism for counting rows can use the indexes and stop when it gets the first row back. Received on Sun Oct 16 2011 - 19:16:28 CEST