Let's Party!

Home Very important things Configuration How Let's Party! changes SQL statements Multiple columns Writing custom handlers Let's Party! and MySql Help forum Download JavaDocs Sourceforge

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
• select
• insert
• update
• delete
• not supported (yet)

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

To decide which partition(s) to use.
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:

1. 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) )
2. Unions: not handled yet
3. Subselects: not handled yet
4. USING in JOIN: not handled yet
5. 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:

1. Column names must be specified; an insert like insert into MY_PART_TAB values (4, 'sometext') will not work
2. Values for the partitioned columns must be specified
3. 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_2 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; delete MY_PART_TAB_2 where NORMAL_COL=3

Current Limitations: None known so far.

NOT SUPPORTED (yet)

1. The DROP and TRUNCATE statement should be issued to every partition (it will be done shortly).
2. REPLACE (MySQL) is not supported yet