POP/FED: Progressive Query Optimization for Federated Queries in DB2

1. View Matching VMV [lb..ub] CHECK CHECK CHECK CHECK CHECK CHECK CHECK CHECK TAOB TAOB TAOB TAOB TAOB TAOB TAOB TAOB TAOB TAOB TAOB TAOB CHECK CHECK Ret. GrpBy Scan Sort MGJN NLJN Filter Scan Scan NLJN Sort Temp HSJN Scan Scan SHIP Temp Sort ORA. Nation SHIP SHIP SHIP SHIP ORA. Customer ORA. Nation Region DB2. Lineitem Orders ORA. Supplier POP/FED: Progressive Query Optimization for Federated Queries in DB2 Query Runtime Query Compiler Section Closure Parser 9) Match Previus Results 8) Save Intermediate Results Semantics Interm.Res. VMV Rewrite VMV Cardinality ??? Optimizer Section Execution 7) Execute Checks 1) Compute Validity Ranges RDS Monitor Counters Cheaper [lb1..ub1] Plans withoptimalitycriterion Reoptimize ! [lb3..ub3] a++ N Intermediate Results / Additional knowledge b++ lb < actual card < ub ? [lb2..ub2] c++ MoreExpensive d++ Err e++ 2) Place dams for eager materialization Interm.Result f++ Plans withjudiciousmaterialization Temp Interm.Result OK Y Interm.Result 3) Place CHECKs Checked Plan Check [lb..ub] Query Result [lb..ub] [lb..ub] Code Generator Relational Data Services 4) Generate CHECK Code Compiler to Runtime Transition Thread:28F: CHK (0010) jf=2A0:0 op=13 lb=a ub=b2A0: TA (001F) jf=2C0:0 jb=0:0 evlo=0:0 taob=158:02C0: JNF (0008) jf=2D8:0 jump=308:0 Code withCHECK 6) Drop Redundant Interm. results 5) Add Matching Information MatchableTAOBs Checked & Rematchable Code Subsumes Example Query Legend Plan with POP Without POP TAOB Table Object Section Executable Code and Objects [lb..ub] Validity Range [lower .. upper bound] Virtual Materialized View Remote Statement Object Oracle Nickname Data / Knowledge Control Flow SELECT YEAR AS YEAR, SUM(CASE WHEN NATION = 'BRAZIL' THEN VOLUME ELSE 0 END) / SUM(VOLUME) AS MKT_SHARE FROM (SELECT YEAR(O_ORDERDATE) AS YEAR, L_EXTENDEDPRICE*(1-L_DISCOUNT) AS VOLUME, N2.N_NAME AS NATION FROM ORA.SUPPLIER, ORA.CUSTOMER, ORA.NATION N1, ORA.NATION N2, ORA.REGION, DB2.LINEITEM, DB2.ORDERS, WHERE S_SUPPKEY = L_SUPPKEY AND L_ORDERKEY = O_ORDERKEY AND O_CUSTKEY = C_CUSTKEY AND C_NATIONKEY = N1.N_NATIONKEY AND N1.N_REGIONKEY = R_REGIONKEY AND R_NAME = 'AMERICA' AND S_NATIONKEY = N2.N_NATIONKEY AND O_ORDERDATE BETWEEN DATE('1995-01-01') AND DATE('1996-12-31') ) AS ALL_NATIONS GROUP BY YEAR ORDER BY YEAR; Ret. Ret. Total execution time:57 sec. GrpBy GrpBy Scan Scan Sort Sort MGJN HSJN VMV Reoptimize 3x NLJN Filter SHIP Est: 182K VR: [47K, 10M] Act: 17.9K NLJN SHIP Scan ORA Est: 120M VR: [10M, INF] Act: 1.2M Scan NLJN SHIP MGJN ORA. Nation Check Temp ORA. Supplier Sort Scan Scan VMV Filter SHIP HSJN Check Temp VMV ORA. Nation Sort SHIP POP/FED Monitor SHIP Scan SHIP Est: 50 VR: [25, 1M] Act: 5 ORA. Supplier Check ORA. Customer DB2. Lineitem Orders Temp CHECK: Operator 3 RANGE [47287, 1000000]: CARDINALITY: 17916 OUT OF BOUNDS! Reoptimizing! SHIP ORA. Nation Region Total execution time:107 sec. Goal: Add Robustness to Complex Federated Queries Environment: Complex federated SQL queries, e.g. in decision support applications Solution: Monitor federated plan during runtime and trigger reoptimization, reusing intermediate results. Results: Queries much more robust. Improvement in execution time by orders of magnitude. Problem: Sub-optimal query performance due to erroneous cardinality estimates used during federated query compilation/optimization Wook-Shin Han, Volker Markl, Stephan Ewen Vijayshankar Raman, Holger Kache