Thanks all for the replies. I will illustrate using a simplified schema and
query. Please note I am not using any Phoenix indexes as there are further
complexities that are not illustrated by this simplified example that preclude
using that Phoenix capability. I have created two tables and two sample queries
of the join I will paste below. Perhaps I am missing something, but I am
unclear as to why a full table scan is needed on the clicks table given the key
fields in the query for both tables. Any further insight is very appreciated in
advance. CREATE TABLE IF NOT EXISTS clicks ( sha_key VARCHAR(64) NOT NULL
PRIMARY KEY, user_id VARCHAR(40), product_id VARCHAR(40), zip_code
VARCHAR(40))COMPRESSION=SNAPPY, DISABLE_WAL=true, IMMUTABLE_ROWS=true; CREATE
TABLE IF NOT EXISTS products ( product_id VARCHAR(40) NOT NULL, sha_key
VARCHAR(64) NOT NULL, zip_code VARCHAR(64) NOT NULL CONSTRAINT pk PRIMARY
KEY (product_id, sha_key, zip_code) )COMPRESSION=SNAPPY, DISABLE_WAL=true,
IMMUTABLE_ROWS=true, SALT_BUCKETS=256; explainselect clicks.*from clicks,
productswhere clicks.sha_key = products.sha_key and products.product_id in
('w8kfc1','wxdfe8','fmlwl6') and products.zip_code in
('90210','55511','81811')
+--------------------------------------------------------------------------------------------------------------------------+|
PLAN
|+--------------------------------------------------------------------------------------------------------------------------+|
CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER CLICKS
|| PARALLEL INNER-JOIN TABLE 0
(SKIP MERGE)
|| CLIENT 256-CHUNK PARALLEL 256-WAY ROUND ROBIN SKIP SCAN ON
768 KEYS OVER PRODUCTS [0,'fmlwl6'] - [255,'wxdfe8'] || SERVER
FILTER BY FIRST KEY ONLY AND ZIP_CODE IN ('55511','81811','90210')
|| DYNAMIC SERVER FILTER BY CLICKS.SHA_KEY IN
(PRODUCTS.SHA_KEY)
|+--------------------------------------------------------------------------------------------------------------------------+
explainselect clicks.*from clickswhere clicks.sha_key in ( select
products.sha_key from products where
products.product_id in ('w8kfc1','wxdfe8','fmlwl6') and
products.zip_code in ('90210','55511','81811'))
+--------------------------------------------------------------------------------------------------------------+|
PLAN
|+--------------------------------------------------------------------------------------------------------------+|
CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER CLICKS
|| SKIP-SCAN-JOIN TABLE 0
|| CLIENT
256-CHUNK PARALLEL 256-WAY SKIP SCAN ON 768 KEYS OVER PRODUCTS [0,'fmlwl6'] -
[255,'wxdfe8'] || SERVER FILTER BY FIRST KEY ONLY AND ZIP_CODE IN
('55511','81811','90210') || SERVER
AGGREGATE INTO DISTINCT ROWS BY [SHA_KEY]
|| CLIENT MERGE SORT
|| DYNAMIC SERVER FILTER BY
CLICKS.SHA_KEY IN ($5.$7)
|+--------------------------------------------------------------------------------------------------------------+
____________________________________________________________
Get Rid Of Unwanted Moles & Skin Tags [At Home]
Smart Life Now
http://thirdpartyoffers.netzero.net/TGL3231/597a7d934ae377d9379b6st02vuc
