SELECT SINGLE vs. UP TO 1 ROWS

最新推荐文章于 2023-04-07 15:42:55 发布

zhaojas

最新推荐文章于 2023-04-07 15:42:55 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： ABAP 文章标签： up database search less system sql

本文链接：https://blog.youkuaiyun.com/zhaojas/article/details/3872862

ABAP 专栏收录该内容

14 篇文章

订阅专栏

本文探讨了在数据库操作中使用SELECT SINGLE与SELECT UPTO1ROWS的不同之处。前者适用于通过主键读取记录，而后者则用于没有主键时查找最多一条符合条件的记录。文章还讨论了这两种查询方式的具体应用场景及性能差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select single is a construct designed to read database records with primary key. In the absence of the primary key, it might end up doing a sequential search,

whereas the select up to 1 rows may assume that there is no primary key supplied and will try to find most suitable index.

The best way to find out is through sql trace or runtime analysis.

Use "select up to 1 rows" only if you are sure that all the records returned will have the same value for the field(s) you are interested in. If not, you will be reading only the first record which matches the criteria, but may be the second or the third record has the value you are looking for.

The System test result showed that the variant Single * takes less time than Up to 1 rows as there is an additional level for COUNT STOP KEY for SELECT ENDSELECT UP TO 1 ROWS.

The 'SELECT SINGLE' statement selects the first row in the database that it finds that fulfils the 'WHERE' clause If this results in multiple records then only the first one will be returned and therefore may not be unique.

Mainly: to read data from

The 'SELECT .... UP TO 1 ROWS' statement is subtly different. The database selects all of the relevant records that are defined by the WHERE clause, applies any aggregate, ordering or grouping functions to them and then returns the first record of the result set.

Mainly: to check if entries exist.

Conclusion:

If the fields you selected are primary key, use SELECT SINGLE.

If the fields you selected are not primary key, use SELECT ... UP TO 1 ROWS.