语法格式

查询数据：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    { * | {expression [ [ AS ] output_name ]} [, ...] }
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ [ START WITH condition ] CONNECT BY [NOCYCLE] 
    condition [ ORDER SIBLINGS BY expression ] ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW {window_name AS ( window_definition )} [, ...] ]
    [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
    [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
    [ LIMIT { [offset,] count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT | WAIT n | SKIP LOCKED ]} [...] ];

condition和expression中可以使用targetlist中表达式的别名。

• 只能同一层引用。
• 只能引用targetlist中的别名。
• 只能是后面的表达式引用前面的表达式。
• 不能包含volatile函数。
• 不能包含Window function函数。
• 不支持在JOIN ON条件中引用别名。
• targetlist中有多个要应用的别名则报错。

缓存SELECT语句计划的场景下，WHERE IN候选子集不易过大，建议条件个数不要超过100，防止引发动态内存过高问题：

• WHERE IN候选子集过大时，生成计划的内存占用会增大。
• 当拼接SQL构造的WHERE IN子集不同，缓存计划的SQL模板无法复用。会生成大量不同的计划且计划无法共享 ，占用大量内存。

• 其中子查询with_query为：

  1 2	with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} )

• 其中指定查询源from_item为：

  1 2 3 4 5 6 7 8 9 10 11

  {[ ONLY ] table_name [ * ] [ slice_clasuse ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ] [ TIMECAPSULE {TIMESTAMP | CS N} expression ] |( select ) [ AS ] alias [ ( column_alias [, ...] ) ] |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] |function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ] |function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] ) |xmltable_clause |from_item unpivot_clause |from_item pivot_clause |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}

• 其中GROUP BY子句为：

  1 2 3 4 5 6

  ( ) | expression | ( expression [, ...] ) | ROLLUP ( { expression | ( expression [, ...] ) } [, ...] ) | CUBE ( { expression | ( expression [, ...] ) } [, ...] ) | GROUPING SETS ( grouping_element [, ...] )

• from_item中指定分区slice_clause为：

  1	SLICE BY ( slice_name [, ...] )

  

  指定SLICE只适合RangeBucket表。

• from_item中指定分区partition_clause为：

  1	PARTITION { ( partition_name [, ...] ) | FOR ( partition_value [, ...] ) }

  

  • 指定分区只适合分区表。
  • PARTITION指定多个分区名时，可以存在相同的分区名，最终分区范围取其并集。
• 其中设置排序方式nlssort_expression_clause为：

  1	NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M | generic_m_ci } ' )

  第二个参数可选generic_m_ci，仅支持纯英文不区分大小写排序。

• 简化版查询语法，功能相当于SELECT * FROM table_name。

  1	TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};