GBase8a选择排序列的方法

单节点4实例，一体机环境，查询sql

select ss.ss_sold_date_sk,ss.ss_store_sk,ss.ss_item_sk,sr.sr_returned_date_sk

from store_sales ss

join

store_returns sr

on ss.ss_sold_date_sk=sr.sr_returned_date_sk

and ss.ss_store_sk=sr.sr_store_sk

and ss.ss_item_sk=sr.sr_item_sk

where ss.ss_ticket_number=1179490869;

这条sql需要测试并发，串行的时候用时16s，主要耗时为join，ss表经过where条件过滤后只有15条，也就是小表join大表(2879983663)的情况，需要对另外一个表进行排序，减少dc扫描数。步骤如下：

一、获取在这个过滤条件下关联列的值，

gbase> select ss_sold_date_sk,ss_store_sk,ss_item_sk from store_sales ss where ss.ss_ticket_number=1179490869;

+-----------------+-------------+------------+

| ss_sold_date_sk | ss_store_sk | ss_item_sk |

+-----------------+-------------+------------+

|         2451166 |         853 |     369530 |

|         2451166 |         853 |     111916 |

|         2451166 |         853 |       1867 |

|         2451166 |         853 |      78686 |

|         2451166 |         853 |     119344 |

|         2451166 |         853 |     103471 |

|         2451166 |         853 |     295561 |

|         2451166 |         853 |     311047 |

|         2451166 |         853 |     393940 |

|         2451166 |         853 |     235702 |

|         2451166 |         853 |     181910 |

|         2451166 |         853 |      78901 |

|         2451166 |         853 |     331267 |

|         2451166 |         853 |      87814 |

|         2451166 |         853 |      65062 |

+-----------------+-------------+------------+

15 rows in set (Elapsed: 00:00:00.23)

二、找到数据比较集中的列

发现ss_sold_date_sk和ss_store_sk值比较集中，排序后能保证数值相同的在相同的dc中；而item_sk比较分散，排序后，仍然需要扫描较多dc，所以ss_sold_date_sk和ss_store_sk更适合作为排序键。应用的原理：通过dc的min max（智能索引）过滤大部分dc。挑选这个其实是为了对右表进行排序

三、查看右表对应的关联列distinct值

查看右表对应的关联列，

gbase> select count(distinct sr_store_sk) from store_returns;

+-----------------------------+

| count(distinct sr_store_sk) |

+-----------------------------+

|                         750 |

+-----------------------------+

1 row in set (Elapsed: 00:00:02.83)

gbase> select count(distinct sr_returned_date_sk) from store_returns;

+-------------------------------------+

| count(distinct sr_returned_date_sk) |

+-------------------------------------+

|                                2003 |

+-------------------------------------+

1 row in set (Elapsed: 00:00:02.79)