Re: Informix 7.3 on Linux -25580 Error

"Obnoxio The Clown" <obnoxio@hotmail.com> writes: [SNIP] > >Sorry: Complex types (Collection and Row) > > > >Espesially the SET type looks nice, but you can't use them (the whole SET > >or just one element) in foreign keys and you can't create indexes on them. > >(which would make 'WHERE foo IN myset' fast enough with large tables) > > Fixed in 9.3. Allegedly. Allrighty, -and when should 9.3 be released? Thomas

From: Thomas Parsli <thomas.parsli@startsiden.no> > >"Obnoxio The Clown" <obnoxio@hotmail.com> writes: > > > From: Thomas Parsli <thomas.parsli@startsiden.no> > > > > > >Paul Brown <paul.NOSPAM.brown@informix.com> writes: > > > > > > > Thomas Parsli wrote: > > > > > > > > > The new datatypes and the possibility to extent those are great, >but > > > > > we still can't create indexes on those -and forget about foreign > > >keys... > > > > > > > > Woah nellie! > > > > > >Nellie? Nellie! > > > > I've been called worse! > >I'm not suprised;) But at least I'm not Norwegian. > > > > You can't index ROW TYPES (yet). But you sure as shoot can create > > >indices on > > > > OPAQUE and DISTINCT types (that's how the R-Tree stuff for spatial > > >works), defined > > > > primary keys using them, and also referential integrity constraints. > > > > > >Yes I know, I was talking about the _new_ datatypes (in 9.2) not opaque >and > > >distinct... > > >(If you don't understand my english we can turn to norwegian instead) > > > > I don't understand your English either -- what new data types in 9.2 are >you > > talking about? > > > > >I find the datatypes cool -and the lack of indexes (on them) uncool;) > > > > Which data types? > >Sorry: Complex types (Collection and Row) > >Espesially the SET type looks nice, but you can't use them (the whole SET >or just one element) in foreign keys and you can't create indexes on them. >(which would make 'WHERE foo IN myset' fast enough with large tables) Fixed in 9.3. Allegedly. ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Tomas sayeth: > > > >Yes I know, I was talking about the _new_ datatypes (in 9.2) not opaque > and distinct... > > > >(If you don't understand my english we can turn to norwegian instead) To which his Clownship wondered: > > > Which data types? At which point Tomas clarified: > >Sorry: Complex types (Collection and Row) > > > >Espesially the SET type looks nice, but you can't use them (the whole SET > >or just one element) in foreign keys and you can't create indexes on them. > >(which would make 'WHERE foo IN myset' fast enough with large tables) > To which the Poster in the Big Shoes responded: > Fixed in 9.3. Allegedly. To which you humble plumber adds a couple of points of clarification: 1. COLLECTIONS (SETS, MULTISETS, LISTS) and ROW TYPES have been part of the 9.X code line since 9.0. Their quality and performance have improved with each release, but neither of them is "new" in IDS.2000 (9.2). 2. The plan is to provide indexing for ROW TYPES in 9.3. 3. Indexing COLLECTIONS is an altogether more complex problem. A brief digression to explain why. 3.1 Consider the case where you want to use a whole COLLECTION in a foreign key/primary key, or create an index on them. How do you tell when one SET is "less than" another (which is necessary to build a B-tree? When it has fewer elements? When the first element in the set is less than the first element in the other (proceed recursively)? What happens when the data types used in the COLLECTION do not have Equal, or LessThan, defined for them (polygons, or random variables, or videos)? What about MULTISETS and LISTS? Does order or repetition of elements count? Regardless of how you answer these questions, someone will find your answer unsatisfactory. Consequently, the only way to deal with it is to let developers decide. 3.2 Consider the case where you want to provide a foreign key from a COLLECTION element to another table. CREATE TABLE Employees ( Id Emp_Number PRIMARY KEY, .... ); CREATE TABLE Departments ( Id Dept_Number PRIMARY KEY, Emps SET( Emp_Number FOREIGN KEY REFERENCING Employees.Id ), . ); This was the approach taken in Illustra. All kinds of problems cropped up. i. To be scalable, you end up having to use a "backing table" that, under the covers, simply looks like a pretty conventional N:M resolution table between Employees and Departments. (Scalable is all about giving the query processor a broad selection of join orders to pursue; E x DE x D, D x DE x E, rather than only ever E x D). This ends up looking like a JOIN INDEX. ii. How do you enforce a rule to the effect that an Employee can have only one Department? To do this, you had to address the backing table somehow. iii. How do you say "for each Department, list the Employees?" This operation requires a transpose operation over the Departments.Emps column. Unfortunately, this is not supported in anyone's SQL. Consequently, you end up querying the backing table directly. - The net lesson was that whenever you want to use the "element within COLLECTION as FK" you end up with a situation where the conventional resolution table approach works just as well in most cases, and much better in a few of them. 3.3 The third case, where you want to index "IN" queries, would be really useful. The technique is called "Inverted indexing". In a conventional index, each row in the table is referred to from at most one entry in the index. In an inverted index (you get the idea from a document index storing word lists) rows in the table can be referred to from several entries in the index. This is actually not that hard to do. Transaction support (locking for isolation level support) becomes a bit of a challenge but nothing too onorous. You could also do "SUBSET" and "SUPERSET", in addition to "ELEMENT OF" predicates. But you can do this with a backing table too. Anyway, ROW TYPE indexing support is on the way, but COLLECTION indexing opens a can of worms. Given that there is resource X to invest in product engineering, the question becomes "Can we get more useful stuff (join indices, distributed query support, better replication) for the same X?" It's not that COLLECTION indexing is hard. It's just that the same engineering investment elsewhere makes new applications possible, and thereby helps get new customers. Hope this enlightens a little. SETs in Illustra wound a couple of customers way 'round the axel. They're really useful in some circumstances, but you've got to be prudent. KR Pb