Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)?

I know about fragmenting, but this is not quite the same AFAIK. We're using MRG_MYISAM to avoid large amounts of duplicate data across customer specific databases, so MRG_MYISAM is perfect.

That said, I'd like to know if there are equivalent things in other DBs, particularly other open source DBs.

share|improve this question
4  
I'm curious what your use case is. In most (all?) other RDBMS', 'database' and 'schema' are two separate concepts, and you'll probably run into access issues depending on what your usage of 'databases' is. I found an interesting article on SO regarding postgres. Using his example you could set a partitioned table in the shared schema. But I'm about 74.2% sure that MERGE was MySQL's early attempt at partitioning, so won't exist anywhere else. It's one of a kind (and should be put to rest)...imho – Derek Downey Apr 19 at 1:33
1  
The only thing in postgresql I can think of that might be used to solve the same problem is FOREIGN DATA WRAPPERS wiki.postgresql.org/wiki/Foreign_data_wrappers. These allow you to access data from another database which doesn't even have to be postgresql. However this feature isn't very mature yet. As this is part of the SQL standard I would expect some other big vendors to also support this. – Eelke Apr 19 at 5:31

closed as not constructive by RolandoMySQLDBA, Mark Storey-Smith, Max Vernon, Marian, Derek Downey Apr 22 at 14:54

As it currently stands, this question is not a good fit for our Q&A; format. We expect answers to be supported by facts, references, or specific expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, see the FAQ for guidance.

1 Answer

up vote 1 down vote

From the description of the MERGE table type in the MySQL documentation at https://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html it appears this is similar to creating a view that uses a UNION statement to connect multiple tables, either from a single database or multiple databases.

In Microsoft SQL Server (which admittedly is not open-source), you could do something like this:

CREATE VIEW MergedTablesView
AS
    SELECT Field1, Field2, Field3, ...
    FROM Database1.dbo.Table1
    UNION
    SELECT Field1, Field2, Field3, ...
    FROM Database1.dbo.Table2
    UNION
    SELECT Field1, Field2, Field3, ...
    FROM Database1.dbo.Table3

    ...

The UNION statement will remove duplicate rows from the result set, which seems to be your primary requirement.

I'm certain other database engines, including PostgreSQL, also implement the UNION keyword in a very similar manner. Here is the PostgreSQL Manual on the UNION statement.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.