WL#3258: Push down join into NDB kernel
Affects: Benchmarks-3.0 — Status: Un-Assigned — Priority: Medium
Proposal: Have TC send SCAN_FRAG_REQ to DBSPJ instead of DBLQH. Let attr/key info contain description of join. One could also "save" join in DBSPJ then it would in practice be a view. CREATE TABLE PERSON (PERSON_ID); CREATE TABLE ADDRESS (ADDRESS_ID, PERSON_ID, CITY_ID); CREATE TABLE CITY (CITY_ID) Phase 1: [ RANGE ] SCAN + [ PK ] LOOKUP SELECT * FROM ADRESS, PERSON WHERE PERSON.PERSON_ID = ADRESS.ADDRESS_ID [ FILTER ON PERSON ] [ FILTER ON ADDRESS ] [ RANGE ON ADDRESS ] I.e API decides join order. This will be implemented as (range) scan on ADRESS followed by (pk) lookup on PERSON Phase 2: [ RANGE ] SCAN + RANGE SCAN SELECT * FROM PERSON, ADDRESS WHERE PERSON.PERSON_ID = ADRESS.ADDRESS_ID [ FILTER ON PERSON ] [ FILTER ON ADDRESS ] [ RANGE ON PERSON ] This will be impl. as (range) scan on PERSON followed by range scan on ADDRESS(PERSON_ID) Phase 3: More than 2-way join SELECT * FROM PERSON, ADDRESS, CITY WHERE PERSON.PERSON_ID = ADRESS.ADDRESS_ID AND ADRESS.CITY_ID = CITY.CITY_ID [ FILTER ON PERSON ] [ FILTER ON ADDRESS ] [ FILTER ON CITY ] [ RANGE ON PERSON ]
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.