If you absolutely need to be able to join across all three tables this may be a case where you really do have a many-to-many relationship. I would think that suppliers might be a small enough table that you could make it replicated rather than partitioned. If not, then you are going to have to avoid joining across all three tables in a single SQL statement, and instead perform the query on the supplier table separately from the query on the customer table and join "manually".
co locating a single table with two other table
Is it possible to co locate a single table with two different tables.For example order table needs to be co located with customer and supplier table.I partition the customer table with customer id and supplier table with supplier id.customer table and supplier table is not related.order table has foreign key customer id and supplier id which refers to customer and supplier table.
Please sign in to leave a comment.
Thanks for your response..can you elaborate on how to join manually with some query example
I don't quite understand why you need a join across customer, order and supplier. If you will please explain what you are trying to do I'll try to walk you through it.
I need to know what are the orders made by a customer and who are the supplier for those orders and I can not replicate those tables because of its huge size
So you want to select customerID and supplierID from orders? I don't see need for a join there. If you need detail about the suppliers you will need to select that detail from suppliers in a separate query since supplier table will not be co-located with customer table.
You will have to do two separate queries. select * from customer c,order o where c.customerid=o.customerid and c.customerid='customer1'; Then separately iterate over all of the returned records to select * from supplier s where s.supplierid in the_supplier_ids_returned_from_the_first_query. It's not as easy as the join but it is the only way on GemXD. If most of your queries tend to be like this you might consider using GPDB or HAWQ rather than GemXD, as they are all licensed as a single Big Data SKU and you can choose which bits to use for what. GemXD is really targeted at eXtreme Transaction Processing (XTP). GPDB and HAWQ are targeted at batch ingest and analyzing data after it is captured. GemXD can be used to capture the transactions and micro-batch them into either GPDB or HAWQ without too much work.