merge tables: how to reveal member tabels?
Hi,
given a merge table, e.g.,
sql>create table t1(a int, b real); operation successful (9.315ms) sql>create table t2(a int, b real); operation successful (8.497ms) sql>create merge table mt(a int, b real); operation successful (8.218ms) sql>alter table mt add table t1; operation successful (7.174ms) sql>alter table mt add table t2; operation successful (6.178ms)
How can I see which tables are members of a merge tables, i.e., in this case, how can I see that t1 & t2 are members of mt?
a plain select (obviously) only shows the content of mt, i.e., the combined content of t1 & t2 ...
sql>select * from mt; +---+---+ | a | b | +===+===+ +---+---+ 0 tuples (4.690ms)
Is there a query to get this directly from mt, or where can I otherwise find that info in the SQL catalog?
Thanks!
Stefan
/* Get the merge tables and their respective partition tables */ SELECT t.id AS mergetable_id ,t.name AS mergetable ,o.nr AS partitiontable_id ,o.name AS partitiontable FROM sys.tables t ,sys.schemas s ,sys.objects o WHERE t.schema_id = s.id AND t.id = o.id AND s.name = 'myschema' ORDER BY t.name ,o.nr ;
Cheers Bart
On 15-03-18 21:59, Stefan Manegold wrote:
Hi,
given a merge table, e.g.,
sql>create table t1(a int, b real); operation successful (9.315ms) sql>create table t2(a int, b real); operation successful (8.497ms) sql>create merge table mt(a int, b real); operation successful (8.218ms) sql>alter table mt add table t1; operation successful (7.174ms) sql>alter table mt add table t2; operation successful (6.178ms)
How can I see which tables are members of a merge tables, i.e., in this case, how can I see that t1 & t2 are members of mt?
a plain select (obviously) only shows the content of mt, i.e., the combined content of t1 & t2 ...
sql>select * from mt; +---+---+ | a | b | +===+===+ +---+---+ 0 tuples (4.690ms)
Is there a query to get this directly from mt, or where can I otherwise find that info in the SQL catalog?
Thanks!
Stefan
Thanks, Bart!
Stefan
----- On Mar 15, 2018, at 10:34 PM, Bart Scheers bartscheers@gmail.com wrote:
/* Get the merge tables and their respective partition tables */ SELECT t.id AS mergetable_id ,t.name AS mergetable ,o.nr AS partitiontable_id ,o.name AS partitiontable FROM sys.tables t ,sys.schemas s ,sys.objects o WHERE t.schema_id = s.id AND t.id = o.id AND s.name = 'myschema' ORDER BY t.name ,o.nr ;
Cheers Bart
On 15-03-18 21:59, Stefan Manegold wrote:
Hi,
given a merge table, e.g.,
sql>create table t1(a int, b real); operation successful (9.315ms) sql>create table t2(a int, b real); operation successful (8.497ms) sql>create merge table mt(a int, b real); operation successful (8.218ms) sql>alter table mt add table t1; operation successful (7.174ms) sql>alter table mt add table t2; operation successful (6.178ms)
How can I see which tables are members of a merge tables, i.e., in this case, how can I see that t1 & t2 are members of mt?
a plain select (obviously) only shows the content of mt, i.e., the combined content of t1 & t2 ...
sql>select * from mt; +---+---+ | a | b | +===+===+ +---+---+ 0 tuples (4.690ms)
Is there a query to get this directly from mt, or where can I otherwise find that info in the SQL catalog?
Thanks!
Stefan
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Bart Scheers -
Stefan Manegold