How Let's Party! changes SQL statements
This section describes the default behaviour of Let's Party!.
I think there should be a way to override this default implementation, maybe
using an interface. Maybe on next version a class could be specified in the
config file to allow for such customization.
In case a statement only uses a normal (non-partitioned) table, it is simply passed to the underlying driver and there will be no limitations.
The WHERE clause
To decide which partitions have to be accessed Let's Party! uses the WHERE clause.
Example: suppose you have a partitioned table (MY_PART_TAB) that is partitioned on the long column PART_COL.
The partition MY_PART_TAB_1 has values on the range 1-10, MY_PART_TAB_2 has values on the range 11-20.
A statement like:
select * from MY_PART_TAB where PART_COL=9
will be tranformed into:
select * from MY_PART_TAB_1 where PART_COL=9
More in general, Let's Party! uses the operators
- =
- >
- <
- >=
- <=
- <>
- IS (NOT) NULL
- (NOT) BETWEEN
- (NOT) IN
- AND
- OR
Example: using the same table/column of the previous example, a statement like:
select * from MY_PART_TAB where PART_COL>20 OR ANOTHER_COL=444
will be transformed into:
select * from MY_PART_2 where PART_COL>20 OR ANOTHER_COL=444
Other examples:
Original | Modified |
select * from MY_PART_TAB where PART_COL IN (2,4,8) |
select * from MY_PART_TAB_1 where PART_COL IN (2,4,8) |
select * from MY_PART_TAB where (PART_COL = 2) OR (PART_COL = 5) |
select * from MY_PART_TAB_1 where (PART_COL = 2) OR (PART_COL = 5) |
select * from MY_NORMAL_TAB, MY_PART_TAB where MY_NORMAL_TAB.ID=MY_PART_TAB.PART_COL
AND MY_NORMAL_TAB.ID=5 |
select * from MY_NORMAL_TAB, MY_PART_TAB_1 as MY_PART_TAB where
MY_NORMAL_TAB.ID=MY_PART_TAB.PART_COL
AND MY_NORMAL_TAB.ID=5 since MY_NORMAL_TAB.ID=MY_PART_TAB.PART_COL , MY_NORMAL_TAB.ID=5
equals
MY_PART_TAB.PART_COL=5 (that is, Let's Party! knows that those two columns are
"synonyms") |
select * from MY_PART_TAB where (PART_COL = 2) OR (PART_COL = 15) |
(select * from MY_PART_TAB_1 where (PART_COL = 2) OR (PART_COL = 15)) UNION
(select * from MY_PART_TAB_2 where (PART_COL = 2) OR (PART_COL = 15)) there is no way to know to which of the two tables to issue the query, so Let's Party! uses a UNION. More on this on the select section |
SELECT
In case there are no joins in the statement, it simply works like in the examples in
the where clause, that is: for every partition that could have data for
the query a select is build. Each select is combined into one using one o more UNION operator.
The same applies in case of an inner join.
Original | Modified |
select * from NORMAL_TAB, MY_PART_TAB where MY_PART_TAB.PART_COL=3 |
select * from NORMAL_TAB, MY_PART_TAB_1 where MY_PART_TAB_1.PART_COL=3 |
select * from NORMAL_TAB, MY_PART_TAB where NORMAL_TAB.ID=MY_PART_TAB.PART_COL
AND NORMAL_TAB.ID=3 |
select * from NORMAL_TAB, MY_PART_TAB_1 where NORMAL_TAB.ID=MY_PART_TAB_1.PART_COL
AND NORMAL_TAB.ID=3 |
select * from NORMAL_TAB, MY_PART_TAB |
(select * from NORMAL_TAB, MY_PART_TAB_1) UNION
(select * from NORMAL_TAB, MY_PART_TAB_2) |
In case a table is left-outer-joined with a partitioned table (TABLE_A), the partitioned table is transformed into a subselect having a UNION with all the partitions of TABLE_A.
Some examples should do more than pages of explanations: MY_PART_TAB is the partitioned table of the examples above.
Original | Modified |
select * from NORMAL_TAB LEFT OUTER JOIN MY_PART_TAB
where MY_PART_TAB.PART_COL=3 |
select * from NORMAL_TAB LEFT OUTER JOIN MY_PART_TAB_1 where
MY_PART_TAB_1.PART_COL=3 |
select * from NORMAL_TAB LEFT OUTER JOIN MY_PART_TAB |
select * from NORMAL_TAB LEFT OUTER JOIN (SELECT * FROM MY_PART_TAB_1 UNION
select * FROM MY_PART_TAB_2) AS MY_PART_TAB |
Current Limitations:
- Aggregate functions
In case an aggregate function (for example, "MAX") is used in the select clause and more than one partition match the where clause, the query will not work:Original Modified select MAX(MY_FIELD) from MY_PART_TAB
(select MAX(MY_FIELD) from MY_PART_TAB_1) UNION (select MAX(MY_FIELD) from MY_PART_TAB_1)
These are not the same!
A solution would be:
select MAX(MY_FIELD) from ( (select * from MY_PART_TAB_1) UNION (select * from MY_PART_TAB_1) )
- Unions: not handled yet
- Subselects: not handled yet
- USING in JOIN: not handled yet
- RIGHT outer join: not handled (and I think never will be)
INSERT
The table to which insert data is chosen based on the value(s) passed to the statement. Example:
Original | Modified |
insert into MY_PART_TAB (PART_COL, A_COL) values (4, 'sometext') |
insert into MY_PART_TAB_1 (PART_COL, A_COL) values (4, 'sometext') |
Current Limitations:
- Column names must be specified; an insert like
insert into MY_PART_TAB values (4, 'sometext')
will not work - Values for the partitioned columns must be specified
- The "INSERT INTO ... SELECT FROM" syntax is not currently supported
UPDATE
Case 1: there is no partitioned column in the SET clause, or the updated values of th partitioned columns still
belongs to the same partition
The update is executed on a list of partitions of the table (example: MY_PART_1, MY_PART_2 etc)
based on the WHERE clause (in case there is no WHERE, the statement is executed on every
partition of the table).
Examples:
Original | Modified |
update MY_PART_TAB SET A_COL=3 where PART_COL=15 |
update MY_PART_TAB_2 SET A_COL=3 where PART_COL=15 |
update MY_PART_TAB SET A_COL=3 |
update MY_PART_TAB_1 SET A_COL=3;
|
update MY_PART_TAB SET PART_COL=13 where PART_COL=15 |
update MY_PART_TAB_2 SET PART_COL=13 where PART_COL=15 |
Case 2: there is at least one partitioned column in the SET clause, and it is set to a such a value that its partition has to change
For each partition that has to be updated (given by the WHERE clause, if any, otherwise all the partitions) an
INSERT INTO partition_to SELECT <the value of the partitioned column>,<all others values> from
partition_from
As usual, some examples:
Original | Modified |
update MY_PART_TAB SET PART_COL=3 where PART_COL=15 |
insert into MY_PART_TAB_1 SELECT 3,<all others cols> from MY_PART_TAB_2
where PART_COL=15; delete from MY_PART_TAB_2 where PART_COL=15 |
Current Limitations: None known so far.
DELETE
A list of partitions that will be the object of the delete is build using the WHERE clause if any, otherwise
the delete is done on every partition.
Examples:
Original | Modified |
delete MY_PART_TAB where PART_COL=15 |
delete MY_PART_TAB_2 where PART_COL=15 |
delete MY_PART_TAB where NORMAL_COL=3 |
delete MY_PART_TAB_1 where NORMAL_COL=3;
|
Current Limitations: None known so far.
NOT SUPPORTED (yet)
- The DROP and TRUNCATE statement should be issued to every partition (it will be done shortly).
- REPLACE (MySQL) is not supported yet