4 ms·
> That the order isn't guaranteed is a feature, not a bug. It frees the implementation to collect records in the most efficient fashion and from having to maint
by beagle3 13y ago
> That the order isn't guaranteed is a feature, not a bug. It frees the implementation to collect records in the most efficient fashion and from having to maintain ordering.
Yes, I understand the theoretical argument. I'm looking for real world examples where that actually helps anything, or theoretical examples where it is superior (in the sense that "there is no way to assign order which would not move the problem to a higher complexity class").
> The cost is that you need to keep an index updated. A trite observation, but there it is. Sometimes that cost is worth paying, sometimes it isn't. SQL doesn't forcefully extract that cost, it lets the database programmer or administrator decide, based on local conditions, what is best.
My point is that ksql does the same -- except that in most cases, the cost is 0, so you get the order "for free"; when the cost is not zero, ksql doesn't make guarantees, and you'll need to ask for ordering, just like in sql. Every sql engine already assigns order in one way or another (ROWID, etc.), except it is unpredictable, non-standardized, and doesn't provide most of the benefits even though it is there.
And the "no order" default often does have a cost; e.g., if you are trying to store e.g. tax brackets in your database and find out which tax bracket a salary falls into:
Either you store both lower and upper bound of bracket in a record, which means you can't normalize (and it is hard to express the integrity requirement of "no gaps" as a constraint) - or you do normalize, and then every query is a self join or correlated query that many optimizers won't handle properly. Whereas in ksql, you store it normalized (just start-of-bracket), order by start-of-bracket, and then get the bin that corresponds to your question.
- jacques_chester 13y agoI'm wondering if we're talking about different things here. You're talking about vectors, which to me suggests an ordered collection of primitives. I'm talking about sets of relations. The main difference is that a relation is not a primitive. It is a tuple of primitives. The point being that sometimes you care about those relations being ordered by date, sometimes by amount, sometimes by ID and so on and so forth. The database imposes no order on you and so you can impose one at will. The database equivalent of maintaining order for every single-column selection is to have per-column indices. Try that in production on a busy OLTP system and let me know how you go. Where array/vector languages and SQL see eye-to-eye is in basically abolishing the loop and having a logical model where operations are applied simultaneously to whole collections. From talking to you the difference is when order is imposed. In cases of time series analysis, where there are single numbers and a natural order, I see the value of array/vector languages (or one of the various proprietary temporal-SQL databases). But most problems of interest aren't vectors with a single natural order. Incidentally, I don't see why you think the tax-bracket thing is such a killer case. Oh noes, a small join!
- beagle3 13y ago> The database equivalent of maintaining order for every single-column selection is to have per-column indices. I guess that's where the misunderstanding comes. In K/APL, every column has an implicit "natural index" - the first item has index 0, the second item has index 1, etc. The thing that is always guaranteed is that, in a result set, the natural indices of the different columns of the same tuple correspond. Some operations guarantee a particular natural index order, but many do not. An additional index on the values that allows you to find an exact value (e.g. hash) or range of values (e.g. a b-tree) may or may not exist, just like in a relational database. This "natural implicit index" does not take memory or anything to maintain, but it is useful. > Try that in production on a busy OLTP system and let me know how you go. ksql runs my queries approximately 100 times faster than optimized MySQL (using MyISAM - faster, but no acid guarantees; ksql is acid). > Incidentally, I don't see why you think the tax-bracket thing is such a killer case. Oh noes, a small join! That was just for known terminology, not knowing where you come from. I have tables with thousands of such brackets. And while I haven't tried Oracle or MS-SQL for them since 2006 or so, it was incredibly easy to confuse their optimizer into doing a complete cross product on those tables and filter it - which makes it takes a second per query instead of milliseconds when the query is optimized properly (or microseconds using K). This is known as an "as-of" join in time-series queries, and any database without explicit support for it usually takes thousands of times more resources than needed for such a join.