Comments - Moving from Oracle to MariaDB

8 years, 4 months ago Sergei Petrunia

Your observations are correct.

MariaDB is not able to push down conditions into non-mergeable derived table subqueries (and neither is MySQL).

Moreover, pushdown would put "id1=1" into HAVING. One also need to be able to push queries down from HAVING into WHERE for this to be efficient. Again, neither MySQL nor MariaDB support this at the moment.

MariaDB has development tasks for this: MDEV-9197, MDEV-7486 and these are considered for the next MariaDB version (10.2).

 
8 years, 3 months ago Vignesh Sambasivamurthy

Thanks a lot for the response. The Jira MDEV-7486 is classified as priority RED, but MDEV-9197 is GREEN. Does it mean that only 7486 will be part of 10.2. These 2 fixes should be considered as one fix, else we may not be able to achieve the desired result.

 
8 years, 3 months ago Vignesh Sambasivamurthy

As part of the fix MDEV-7486 and MDEV-9197, to what level the values will be pushed down. In the below example, will the value 1234 get pushed to table B2. Please clarify.

Select …… From tab1 A Inner join ( Select …… From tab2 B1 Inner join ( Select ….. From tab3 Group by ref_no ) B2 On b1.ref_no = b2.ref_no ) B On A.ref_no = B.ref_no Where A.ref_no = ‘1234’

 
8 years, 3 months ago Sergei Petrunia

Re-formatting:

select …… From tab1 A Inner join ( Select …… From tab2 B1 Inner join ( Select ….. From tab3 Group by ref_no ) B2 On b1.ref_no = b2.ref_no ) B On A.ref_no = B.ref_no Where A.ref_no = ‘1234’

I think, doing pushdown several levels deep is possible. However, this example requires something beyond that.

In the example: - the condition is on A.ref_no - "(SELECT ...from tabl2 b1 ) B" will be merged into the top-level subquery this means that B.ref_no will become either b1.ref_no or b2.ref_no (select list is '...' so one can't tell). - Pushdown is on b1.ref_no or b2.ref_no.

In order for pushdown to happen, the optimizer will have to apply Equality Propagation:

A.ref_no=B.ref_no AND A.ref_no='1234' -> B.ref_no=12345

and then possibly also use b1.ref_no=b2.ref_no (from the ON expression).

So, the pushdown in a subquery should work as follows: - Analyze the GROUP BY clause and move parts of HAVING into WHERE - remove nested joins (simplify_joins) - call optimize_cond()/ build_equal_items() - Attempt to move parts of WHERE down into derived-table subqueries. The subquery is not yet optimized at this point, so its optimizer will have a chance to repeat the same steps recursively.

Looks feasible for me, but I'd like to discuss this with other members of the optimizer team.

 
8 years, 3 months ago Sergei Petrunia

Ok, discussed. Technically, the solution is feasible. Both MDEVs are targeted for 10.2, however, they are likely to be "rolling" features. (For these features, best effort will be made to put them into 10.2, however, the release won't wait for them, and there is no guarantee).

 
8 years, 3 months ago Vignesh Sambasivamurthy

Thank you. Fingers crossed.

 
8 years, 3 months ago Vignesh Sambasivamurthy

Can I please know what is tentative date for 10.2 release.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.