Well, ok, but it is not really creating an index drawing data from multiple tables. The table definition part "interleaves" data with the same value for the same column into a "cluster" of pages, and the creation of the index on the cluster is an index of the "common column data".
Creating an index from "multiple databases" would be something like: create table a(a ...); create table b(b ...); create index blah on multisources (a.a, b.b); Since there is no syncronized motion between the two tables a and b, the index is impossible to maintain, except perhaps in a navigational database and then only through a linked to owner set, and only for changes to the right-hand table in the set. You can only create an index drawing data from a single database object. In oracle's case, that "single object" is a cluster of tables where "some one column" contains linkage data between the two tables. That is, it is a hybrid of a relation and a network extended set index. That is one table in the cluster contains a "linked list" of the other table rows having the same value for the cluster column (a LINK TO PRIOR LINK TO OWNER set) and the index is created on the column of the first table (the parent or left-hand table) in the cluster (set). That the various rows of the various tables are stored in relative proximity to each other (in order to reduce I/O) is a nice implementation detail but irrelevant (and it depends on the insertion order of the table rows or requires a re-org to get the rows interleaved properly). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >[email protected]] On Behalf Of Jean-Luc Hainaut >Sent: Thursday, 2 August, 2018 15:04 >To: SQLite mailing list >Subject: Re: [sqlite] Common index for multiple databases > >On 02/08/2018 20:50, Keith Medcalf wrote: >> In no DBMS known can you index data sourced from multiple tables in >the same index -- this applies to "Relational" databases and all >other database models (such as pure hierarchical, network, network >extended, etc.) In all DBMS systems the contents of the index must >be sourced from a single object. > >To the best of my (limited) knowledge, Oracle's CLUSTER is the only >technique allowing an index to reference rows from several tables. >The >rows (from source tables) that share the same value of a column are >collected into a page, if needed complemented by an overflow chain of >pages. Whether the OP is ready to migrate to Oracle is another story! > >J-L Hainaut >_______________________________________________ >sqlite-users mailing list >[email protected] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
