[toc] #
第四十六章 SQL命令 FROM(二) ## %PARALLEL 这个可选关键字在查询的FROM子句中指定。 它建议 IRIS使用多个处理器(如果适用)并行处理查询。 这可以显著提高使用一个或多个`COUNT`、`SUM`、`AVG`、`MAX`或`MIN`聚合函数和/或`GROUP BY`子句的某些查询的性能,以及许多其他类型的查询。 这些通常是处理大量数据并返回小结果集的查询。 例如,`SELECT AVG(SaleAmt) FROM %PARALLEL User.AllSales GROUP BY Region`使用并行处理。 既指定单个字段又指定聚合函数且不包含`GROUP BY`子句的查询不能执行并行处理。 例如,`SELECT Name,AVG(Age) FROM %PARALLEL Sample`。 `Person`不执行并行处理,而是从`SELECT Name,AVG(Age) FROM %PARALLEL Sample.Person GROUP BY Home_State`执行并行处理。 `%PARALLEL`用于`SELECT`查询及其子查询。 `INSERT`命令子查询不能使用`%PARALLEL`。 指定`%PARALLEL`可能会降低某些查询的性能。 在具有多个并发用户的系统上使用`%PARALLEL`运行查询可能会导致整体性能下降。 注意:指定`%PARALLEL`的查询必须在读/写而不是只读的数据库中运行。 否则,可能发生``错误。 不管在`FROM`子句中是否存在`%PARALLEL`关键字,有些查询可能使用线性处理,而不是并行处理:有些查询不支持并行处理; 一些优化后的查询可能无法从并行处理中获益。 可以使用`Show Plan`确定 IRIS是否以及如何对查询进行了并行处理分区。 要确定当前系统上的处理器数量,使用 `%SYSTEM.Util.NumberOfCPUs()`方法。 ## %STARTTABLE 这个可选关键字指定查询优化器应该开始对`FROM`子句中列出的第一个表执行联接。 其余表的连接顺序留给查询优化器。 将此关键字与`%INORDER`进行比较,后者指定了完整的连接顺序。 `%STARTTABLE`不能与交叉连接或右外连接一起使用。 不能使用`%STARTTABLE`(或`%FIRSTTABLE`)从左`OUTER join`(或右`OUTER join`)的左边开始连接顺序。 如果指定的开始表与外部连接的要求不一致,则会生成一个`SQLCODE -34`错误:“优化器未能找到可用的连接顺序。” 为了避免这种情况,当与外部连接一起使用时,建议`%STARTTABLE`只与`ansi`风格的左外部连接或完整外部连接一起使用。 下表显示了在使用`%INORDER`和`%STARTTABLE`优化组合超查询父视图和内联视图时的合并行为: "" | 没有连接优化器的超查询 | 具有%STARTTABLE的超级查询 | 有`%INORDER`的超级查询 ---|---|---|--- 不带连接优化器的视图 | 如果可能,合并视图 |如果视图是超查询`start: don't merge`。否则,如果可能,合并视图。 |合并如果可能的话;视图的底层表是无序的。 使用`%STARTTABLE`查看 | 不合并 | 如果视图是超级查询`start: merge`,如果可能的话。视图的开始表变成了超级查询的开始表。否则,不合并。 | 不合并 使用`%INORDER`查看 | 不合并 | 不合并 | 如果视图不是由`%INORDER`控制的,则不要合并。否则,如果可能,合并视图;视图的顺序被替换为超级查询连接顺序。 `%FIRSTTABLE`提示在功能上与`%STARTTABLE`相同,但是提供了以任意顺序指定连接表序列的灵活性。 # FROM子句中的表值函数 表值函数是一个类查询,它被投影为一个存储过程,并返回单个结果集。 表值函数是任何具有`SqlProc TRUE`的类查询。 用作表值函数的类查询必须在`LOGICAL`或`RUNTIME`模式下编译。 当作为表值函数使用并在`RUNTIME`模式下编译时,表值函数查询将在`LOGICAL`模式下调用。 表值函数遵循与类查询的存储过程名称相同的命名约定。 参数括号是必须的; 括号可以是空的,可以包含一个字面值或一个主机变量,也可以包含一个用逗号分隔的字面值和主机变量列表。 如果不指定参数(空括号或空字符串),表值函数将返回所有数据行。 要使用表值函数发出查询,用户必须对定义表值函数的存储过程拥有`EXECUTE`权限。 用户还必须对表值函数查询访问的表或视图具有`SELECT`权限。 在下面的示例中,类查询`Sample.Person.ByName`被投影为一个存储过程,因此可以用作表值函数: ```sql SELECT Name,DOB FROM Sample.SP_Sample_By_Name('A') ``` 下面的动态SQL示例指定相同的表值函数。它使用`%Execute()`方法将参数值提供给`?`入参: ```java ClassMethod From() { s myquery="SELECT Name,DOB FROM Sample.SP_Sample_By_Name(?)" s tStatement = ##class(%SQL.Statement).%New() s qStatus = tStatement.%Prepare(myquery) if qStatus'=1 {w "%Prepare failed:" d $System.Status.DisplayError(qStatus) q} s rset = tStatement.%Execute("A") d rset.%Display() w !,"End of A data",!! s rset = tStatement.%Execute("B") d rset.%Display() w !,"End of B data" } ``` 表值函数只能在`SELECT`语句或`DECLARE`语句的`FROM`子句中使用。表值函数名可以用模式名限定,也可以用非限定名(没有模式名)限定;非限定名使用默认模式。在`SELECT`语句`FROM`子句中,只要可以使用表名,就可以使用表值函数。它可以在视图或子查询中使用,并且可以使用逗号分隔的列表或显式联接语法与其他表引用项联接。 表值函数不能直接用于`INSERT`、`UPDATE`或`DELETE`语句。但是,可以为这些命令指定子查询,以指定表值函数。 SQL没有为表值函数定义`EXTENTSIZE`,也没有为表值函数列定义`SELECTIVITY`。 # FROM子句中的子查询 可以在`FROM`子句中指定子查询。 这称为流子查询。 子查询被视为与表相同的处理方式,包括它在`JOIN`语法中的使用以及使用`as`关键字可选地分配别名。 `FROM`子句可以以任何组合包含多个表、视图和子查询,但要受`JOIN`语法的限制,如`JOIN`中所述。 ```sql SELECT name,region FROM (SELECT t1.name,t1.state,t2.region FROM Employees AS t1 LEFT OUTER JOIN Regions AS t2 ON t1.state=t2.state) GROUP BY region ``` 子查询可以指定`TOP`子句。 当与`TOP`子句配对时,子查询可以包含`ORDER BY`子句。 子查询可以使用`SELECT *`语法,但有以下限制:因为`FROM`子句的结果是值表达式,所以包含`SELECT *`的子查询只能生成一列。 子查询中的连接不能是`NATURAL`连接或接受`USING`子句。 ## 从子查询和%VID 当调用`FROM`子查询时,它为返回的每个子查询行返回一个`%VID`。 `%VID`是一个整数计数器字段; 它的值是系统分配的、唯一的、非空的、非零的、不可修改的。 `%VID`仅在显式指定时返回。 它以数据类型`INTEGER`返回。 因为`%VID`值是顺序整数,所以如果子查询返回的是顺序数据,则它们更有意义; 子查询只能在与`TOP`子句配对时使用`ORDER BY`子句。 因为`%VID`是一个顺序整数,所以可以用它来确定带有`ORDER BY`子句的子查询中项目的排名。 在下面的示例中,`10`条最新的记录按名称顺序列出,但是使用`%VID`值可以很容易地看到它们的时间戳排名: ``` SELECT Name,%VID,TimeStamp FROM (SELECT TOP 10 * FROM MyTable ORDER BY TimeStamp DESC) ORDER BY Name ``` `%VID`的一个常见用途是`“window”`结果集,将执行划分为符合显示窗口中可用行数的顺序子集。 例如,显示`20`条记录,然后等待用户按`Enter`键,然后显示下`20`条记录。 ```java ClassMethod From1() { s myq=4 s myq(1)="SELECT %VID,* " s myq(2)="FROM (SELECT TOP 60 Name,Age FROM Sample.Person " s myq(3)="WHERE Age > 55 ORDER BY Name) " s myq(4)="WHERE %VID BETWEEN ? AND ?" s tStatement = ##class(%SQL.Statement).%New() s qStatus = tStatement.%Prepare(.myq) if qStatus'=1 {w "%Prepare failed:" d $System.Status.DisplayError(qStatus) q} for i=1:10:60 { s rset = tStatement.%Execute(i, i+9) while rset.%Next() { d rset.%Print() } w !! } w "End of data" } ``` ```java DHC-APP>d ##class(PHA.TEST.SQLCommand).From1() 1 "Ahmed,Elmo X." 78 2 "Alton,Phil T." 68 3 "Anderson,Mario L." 78 4 "Bachman,Susan O." 88 5 "Basile,Filomena X." 87 6 "Browne,Robert X." 83 7 "Bukowski,Mario V." 86 8 "Burroughs,Barbara H." 86 9 "Cerri,Stavros Q." 96 10 "Chadbourne,Barb B." 94 ``` # 可选FROM子句 如果`SELECT`项列表(直接或间接)没有引用表数据,则`FROM`子句是可选的。 这种`SELECT`可以用于从函数、运算符表达式、常量或宿主变量返回数据。 对于不引用表数据的查询: - 如果省略`FROM`子句,则不管`TOP`关键字值如何,最多返回一行数据; `TOP 0`不返回任何数据。 `DISTINCT`子句被忽略。 不需要特权。 - 如果指定了`FROM`子句,则必须指定当前命名空间中的现有表。 必须对该表具有`SELECT`权限,即使该表没有被引用。 除非指定了`TOP`或`DISTINCT`子句,或者用`WHERE`或`HAVING`子句限制它,否则返回的相同数据行数等于指定表中的行数。 指定DISTINCT子句将输出限制为单行数据。 `TOP`关键字将输出限制为`TOP`值指定的行数; `TOP 0`不返回任何数据。 无论是否有`FROM`子句,都可以指定后续子句(如`GROUP BY`、`HAVING`或`ORDER BY`)。 `WHERE`或`HAVING`子句可用于确定是否返回结果,或返回多少相同的结果行。 即使没有指定`FROM`子句,这些子句也可以引用表。 可以指定`GROUP BY`或`ORDER BY`子句,但这些子句没有意义。 下面是不引用表数据的`SELECT`语句示例。 两个示例都返回一行信息。 下面的例子省略了`FROM`子句。 `DISTINCT`关键字不是必需的,但是可以指定。 不允许使用`SELECT`子句。 ```sql SELECT 3+4 AS Arith, {fn NOW} AS NowDateTime, {fn DAYNAME({fn NOW})} AS NowDayName, UPPER('MixEd cASe EXPreSSioN') AS UpCase, {fn PI} AS PiConstant ``` 下面的示例包含一个`FROM`子句。 `DISTINCT`关键字用于返回单行数据。 `FROM`子句表引用必须是一个有效的表。 这里允许使用`ORDER BY`子句,但没有意义。 注意,`ORDER BY`子句必须指定一个有效的选择项别名: ```sql SELECT DISTINCT 3+4 AS Arith, {fn NOW} AS NowDateTime, {fn DAYNAME({fn NOW})} AS NowDayName, UPPER('MixEd cASe EXPreSSioN') AS UpCase, {fn PI} AS PiConstant FROM Sample.Person ORDER BY NowDateTime ``` 下面的例子都使用了`WHERE`子句来决定是否返回结果。 第一个包含`FROM`子句,并使用`DISTINCT`关键字返回单行数据。 第二个省略了`FROM`子句,因此最多返回一行数据。 在这两种情况下,`WHERE`子句表引用必须是具有`SELECT`权限的有效表: ```sql SELECT DISTINCT {fn NOW} AS DataOKDate FROM Sample.Person WHERE FOR SOME (Sample.Person)(Name %STARTSWITH 'A') ``` ```sql SELECT {fn NOW} AS DataOKDate WHERE FOR SOME (Sample.Person)(Name %STARTSWITH 'A') ```