ST_nearestApproachDistance
float8 ST_nearestApproachDistance(trajectory traj,timestamp t1,timestamp t2,geometry g);参数 参数名称 描述 traj 轨迹对象。t1 开始时间。t2 结束时间。range 时间段。g 几何对象。示例 Select ST_nearestApproachDistance(traj,'...
CDN加速静态资源时如何设置服务器端的缓存过期时间
当对象的 Last-Modified 为 20140801 00:00:00，当前时间Curtime为 20140802 00:00:00，则t1=(Curtime-Last_modified)*0.1=8640s，t2=max(10,t1)=8640s，t=min(t2,3600)=3600s，那么缓存时间为3600s。当对象的 Last-Modified 为 20140801 ...
ST_nearestApproachDistance
float8 ST_nearestApproachDistance(trajectory traj,timestamp t1,timestamp t2,geometry g);参数 参数名称 描述 traj 轨迹对象。t1 开始时间。t2 结束时间。range 时间段。g 几何对象。示例 Select ST_nearestApproachDistance(traj,'...
ST_nearestApproachDistance
float8 ST_nearestApproachDistance(trajectory traj,timestamp t1,timestamp t2,geometry g);参数 参数名称 描述 traj 轨迹对象。t1 开始时间。t2 结束时间。range 时间段。g 几何对象。示例 Select ST_nearestApproachDistance(traj,'...
ST_nearestApproachDistance
float8 ST_nearestApproachDistance(trajectory traj,timestamp t1,timestamp t2,geometry g);参数 参数名称 描述 traj 轨迹对象。t1 开始时间。t2 结束时间。range 时间段。g 几何对象。示例 Select ST_nearestApproachDistance(traj,'...
通过Hint干预执行计划
Seq Scan on t2(cost=0.00.15.50 rows=267 width=8)Filter:(val>1)Optimizer:Postgres query optimizer 以下几种方法可以干预JOIN顺序和JOIN类型：在输出执行计划的语句前添加Hint，语句如下：/*+MergeJoin(t1 t2)Leading((t2 t1))*/...
使用Hint
指定t2与t3先进行HashJoin，随后与t1进行NestLoopJoin：/*+Leading(((t2 t3)t1))HashJoin(t2 t3)NestLoop(t2 t3 t1)*/EXPLAIN SELECT*FROM t1,t2,t3 WHERE t1.val=t2.val and t2.val=t3.val;行号纠正提示 以下内容仅适用查询优化器，使用前...
DELETE
DELETE t1,t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.name LIMIT 10001;DELETE FROM t1,t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.name LIMIT 10001;说明 t1、t2和t3的拆分...
HINT
INSERT/*+Leading(target(t1 t2))*/INTO target SELECT t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a-示例2：SELECT后的Hint只支持指定t1、t2为参数。INSERT INTO target SELECT/*+Leading(t2 t1)*/t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;...
JOIN实现类型
SELECT t1.id,t2.id FROM tj_shop AS t1 JOIN tj_item AS t2 ON t1.id>t2.id 说明 数据会膨胀，性能比较差 说明 链接的表类型为KV/KKV/Summary时，该表的查询条件中必须包含PK等值条件，参考 KV、KKV查询 的使用限制。Hash Join：用于包含...
WITH子句
查询和分析语句*|with T1 AS(SELECT host,avg(request_length)length FROM website_log GROUP BY host),T2 AS(SELECT host,avg(request_length)length FROM access_log GROUP BY host)SELECT T1.host,T1.length,T2.length FROM T1 JOIN T2 ...
JOIN操作类型
SELECT t1.id,t2.id FROM tj_shop AS t1 LEFT JOIN tj_item AS t2 ON t1.id=t2.id 说明 如果右表值不唯一，建议不要连续使用过多 left join，以免在JOIN过程中产生数据膨胀 Inner Join：内连接。关键字 inner 可以省略。返回满足ON条件的行...
优化聚合与排序
explain select count(*)from t1 join t2 on t1.id=t2.id group by t1.name,t2.name order by t1.name,t2.name;Project(count(*)="count(*)")MemSort(sort="name ASC,name0 ASC")HashAgg(group="name,name0",count(*)="COUNT()")BKAJoin...
查询改写与下推
返回执行计划信息如下：Project(id="id")HashJoin(condition="id=id AND id=id0",type="inner")Gather(concurrent=true)LogicalView(tables="t2_[0-3],l2_[0-3]",shardCount=4,sql="SELECT `t2`.`id`,`l2`.`id` AS `id0` FROM `t2` AS `t2`...
调整执行计划
prepare s1 as select t.a,t2.avg_b from t join(select avg(b)as avg_b,a from t2 group by a)t2 on t2.a=t.a and t.c<$1 order by t.a;prepare s2 as select t.a,t2.avg_b from t join lateral(select avg(b)as avg_b from t2 where t2.a...
JOIN操作类型
SELECT t1.id,t2.id FROM tj_shop AS t1 LEFT JOIN tj_item AS t2 ON t1.id=t2.id 说明 如果右表值不唯一，建议不要连续使用过多 left join，以免在JOIN过程中产生数据膨胀 Inner Join：内连接。关键字 inner 可以省略。返回满足ON条件的行...
查询改写与下推
Project(id="id")HashJoin(condition="id=id AND id=id0",type="inner")Gather(concurrent=true)LogicalView(tables="t2_[0-3],l2_[0-3]",shardCount=4,sql="SELECT `t2`.`id`,`l2`.`id` AS `id0` FROM `t2` AS `t2` INNER JOIN `l2` AS `l...
Persist plan and Query-Blocker
针对指定SQL Pattern，添加Hints 为相同Pattern的SQL，执行 nested loop join 策略，示例如下：/*+nested_loop_join=true*/PERSIST_PLAN SELECT t1.c1FROM t1 INNER JOIN t2 ON t1.c1=t2.c1 WHERE t1.c2;查询指定SQL是否配置过Hints ...
排序优化和执行
如下示例使用了MemSort算子：explain select t1.name from t1 join t2 on t1.id=t2.id order by t1.name,t2.name;返回信息如下：Project(name="name")MemSort(sort="name ASC,name0 ASC")Project(name="name",name0="name0")BKAJoin...
UPDATE
您可以使用UPDATE语法修改表中符合条件的行。语法 单逻辑表 UPDATE[LOW_PRIORITY][IGNORE][schema_name.]tbl_...UPDATE t1,t2 SET t1.name=t2.name WHERE t1.id=t2.name LIMIT 10001;说明 t1和t2的拆分键为ID。相关文献 MySQL UPDATE 语法。
UNION、INTERSECT和EXCEPT
例如，以下查询中，首先计算 T1 和 T2 的 UNION，然后对 UNION 结果执行 EXCEPT 操作。select*from t1 union select*from t2 except select*from t3 order by c1;在同一查询中，组合使用集合运算符时，INTERSECT 运算符优先于 UNION 和 ...
聚合优化和执行
explain select count(*)from t1 join t2 on t1.id=t2.id group by t1.name,t2.name order by t1.name,t2.name;返回信息如下：Project(count(*)="count(*)")MemSort(sort="name ASC,name0 ASC")HashAgg(group="name,name0",count(*)="COUNT...
UNION、INTERSECT和EXCEPT
例如，在以下查询中，首先计算T1和T2的UNION，然后对UNION结果执行EXCEPT操作：select*from t1 union select*from t2 except select*from t3 order by c1;在同一个查询中使用运算符组合时，INTERSECT运算符优先于UNION和EXCEPT运算符。例如...
表设计最佳实践
Update（将table2表中的记录的值更新到table1表中）INSERT OVERWRITE TABLE table1 SELECT t1.key1,t1.key2,CASE WHEN t2.key1 IS NOT NULL THEN t2.col1 ELSE t1.col1 END AS col1,CASE WHEN t2.key2 IS NOT NULL THEN t2.col2 ELSE t1....
并行查询Hint语法
在t1表上做并行扫表，然后将数据做shuffle分发到下一阶段Workers-在t2表上做并行扫表，然后将数据做shuffle分发到下一阶段Workers-在下阶段Workers上完成co-location join后，结果汇总到Leader SELECT/*+PARALLEL(t1)PARALLEL(t2)PQ_...
Hash Join的并行加速
workers:4)(cost=10.82 rows=4)->Parallel inner hash join(t2.c2=t1.c1)(cost=0.57 rows=1)->Parallel table scan on t2,with parallel partitions:1(cost=0.03 rows=1)->Parallel hash->Parallel table scan on t1,with parallel ...
ODPS-0130252
FAILED:ODPS-0130252:[3,1]Cartesian product is not allowed-cartesian product is not allowed without mapjoin-正确，补充join条件，这里join条件是两个表的字段相等 odps>select t1.*from src t1 join src t2 on t1.key=t2.key;...
CTE（2.0版）
WITH子句同样适用于多子查询：WITH t1 AS(SELECT a,MAX(b)AS b FROM x GROUP BY a),t2 AS(SELECT a,AVG(d)AS d FROM y GROUP BY a)SELECT t1.*,t2.*FROM t1 JOIN t2 ON t1.a=t2.a;WITH子句中定义的关系可以互相连接：WITH x AS(SELECT a ...
子查询
示例如下：SELECT s1 FROM t1 WHERE s1=ANY(SELECT s1 FROM t2);SELECT s1 FROM t1 WHERE s1 IN(SELECT s1 FROM t2);NOT IN：NOT IN在子查询前使用时，等价于<>ALL。示例如下：SELECT s1 FROM t1 WHERE s1<>ALL(SELECT s1 FROM t2);SELECT ...
子查询
示例如下：SELECT s1 FROM t1 WHERE s1=ANY(SELECT s1 FROM t2);SELECT s1 FROM t1 WHERE s1 IN(SELECT s1 FROM t2);NOT IN：NOT IN在子查询前使用时，等价于<>ALL。示例如下：SELECT s1 FROM t1 WHERE s1<>ALL(SELECT s1 FROM t2);SELECT ...
Common Table Expression简介
CTE ON t2.i1=T_CTE.i1_cte AND t2.i2=T_CTE.i2_cte 也可以在JOIN中去使用它：WITH T_CTE(i1_cte,i2_cte)AS(SELECT i1,i2 FROM t1)SELECT*FROM t2 WHERE EXISTS(SELECT*FROM T_CTE WHERE t2.i1=i1_cte AND t2.i2=i2_cte)或者在子查询中去...
获取创建列存索引的DDL语句
执行结果如下：mysql>call dbms_imci.columnar_advise_by_columns('select count(t1.a)from t1 inner join t2 on t1.a=t2.a group by t1.b');DDL_STATEMENT|+-+|ALTER TABLE test.t1 MODIFY COLUMN a int(11)DEFAULT NULL COMMENT '...
Common Table Expresssion（>=3.7.5）简介
CTE ON t2.i1=T_CTE.i1_cte AND t2.i2=T_CTE.i2_cte 你也可以在JOIN中去使用他，WITH T_CTE(i1_cte,i2_cte)AS(SELECT i1,i2 FROM t1)SELECT*FROM t2 WHERE EXISTS(SELECT*FROM T_CTE WHERE t2.i1=i1_cte AND t2.i2=i2_cte)或者在子查询中去...
Common Table Expresssion（>=3.7.5）简介
CTE ON t2.i1=T_CTE.i1_cte AND t2.i2=T_CTE.i2_cte 你也可以在JOIN中去使用他，WITH T_CTE(i1_cte,i2_cte)AS(SELECT i1,i2 FROM t1)SELECT*FROM t2 WHERE EXISTS(SELECT*FROM T_CTE WHERE t2.i1=i1_cte AND t2.i2=i2_cte)或者在子查询中去...
扫描全部/部分分库分表
按条件在指定分表上执行，有JOIN的情况：SELECT/*+TDDL:scan('t1,t2',condition='t1.id=2 and t2.id=2')*/*FROM t1 a JOIN t2 b ON a.id=b.id WHERE b.name="test"执行后会首先计算出逻辑表 t1 t2 满足 condition 条件的所有物理表，然后...
扫描全部/部分分库分表
按条件在指定分表上执行，有JOIN的情况：SELECT/*+TDDL:scan('t1,t2',condition='t1.id=2 and t2.id=2')*/*FROM t1 a JOIN t2 b ON a.id=b.id WHERE b.name="test"执行后会首先计算出逻辑表 t1 t2 满足 condition 条件的所有物理表，然后...
CREATE MATERIALIZED VIEW
create materialized view mv lifecycle 7 partitioned on(ds)clustered by id sorted by value into 1024 buckets as select t1.id,t1.value,t1.ds as ds from t1 join t2 on t1.id=t2.id;示例四：假设有一张页面访问表visit_records，...
执行计划调优
基于以上诊断可以得出，由于没有及时收集表t2的统计信息，导致优化器认为t2为一张小表，从而在Hash Join时将t2广播到各个节点，并且以t1作为内表构建Hash table导致t2下盘，最终导致整个SQL执行耗时长。以上问题的解决方案是重新收集一遍t2...
CREATE STATISTICS
CREATE STATISTICS s2(mcv)ON a,b FROM t2;ANALYZE t2;valid combination(found in MCV)EXPLAIN ANALYZE SELECT*FROM t2 WHERE(a=1)AND(b=1);invalid combination(not found in MCV)EXPLAIN ANALYZE SELECT*FROM t2 WHERE(a=1)AND(b=2);MCV...
增量查询
Transactional Table 2.0支持增量写入和存储，最重要的一个考虑就是支持增量查询以及增量计算优化，为此，专门设计开发了新的...如果begin是t1，end是t2-1，即查询的时间范围为（t1,t2），这个时间段是没有任何增量数据插入的，会返回空行。
- 产品推荐
- 这些文档可能帮助您