从表和导航中选择* [英] select * from table AND navigation

问题描述

这是我的问题。


我需要显示一张表，除了

表名外，我没有任何信息。使用元数据我可以以某种方式显示列名和

记录值。


但我的表有100万行，如果我选择*然后我做获得

100万行。


我希望能像google一样提供页面导航，页面

向前，向后等现在它是一个只读数据库。


如需参考，请在此处查看我的帖子。
http://forum.java.sun.com/thread.jsp...essage=2252212


我会很感激任何想法。我已经被困了一个星期了。


谢谢。


TP

Here is my problem.

I need to display a table about which I have no information except the
table name. Using metadata I can somehow show the column names and
record values.

But my table has 1 million rows and if I do a select * then I do get
1 million rows.

I want to be able to provide page navigation as google does, page
forward, backwards etc. For now it is a read only database.

For reference please see my post here.
http://forum.java.sun.com/thread.jsp...essage=2252212

I would appreciate any ideas. I have been stuck for a week now.

Thanks.

TP

推荐答案

select count（*）会告诉你它有多少行。 DESCRIBE TABLE

table_name将为您提供有关列和数据类型的信息。


TP写道：

select count(*) will tell you how many rows it has. DESCRIBE TABLE
table_name will give you info on columns and data types.

TP wrote:

这是我的问题。

我需要显示一张表，除了
表名之外我没有任何信息。使用元数据我可以以某种方式显示列名和
记录值。

但是我的表有100万行，如果我选择*然后我会得到
100万行。

我希望能够像google一样提供页面导航，页面向前，向后等。现在它是一个只读数据库。

参考请在此处查看我的帖子。
http://forum.java.sun.com/thread.jsp...essage=2252212

我很感激任何想法。我已经被困了一个星期了。

谢谢。

TP

Here is my problem.

I need to display a table about which I have no information except the
table name. Using metadata I can somehow show the column names and
record values.

But my table has 1 million rows and if I do a select * then I do get
1 million rows.

I want to be able to provide page navigation as google does, page
forward, backwards etc. For now it is a read only database.

For reference please see my post here.
http://forum.java.sun.com/thread.jsp...essage=2252212

I would appreciate any ideas. I have been stuck for a week now.

Thanks.

TP

让我们考虑一个非常常见的情况。 GUI应用程序发出一个

查询，然后结果集中的前25行显示在

屏幕上。如果用户按下Page Down，那么接下来的25行将显示

，依此类推。众所周知，对于

这种情况​​，FETCH FIRST 25 ROWS和

OPTIMIZE FOR 25 ROWS子句的SELECT语句非常有用。实际上，使用它们中的任何一种都是一种直接的方式

来告诉优化器你实际需要什么。在

转弯中，优化器可能会选择一个完全不同的访问计划来满足带有这样一个子句的

查询。例如，查询


SELECT * FROM CUSTOMER ORDER BY LAST_NAME


可以通过表空间扫描满足，然后进行排序。作为

的结果，初始响应时间可能非常高。但是，这是检索整个结果集的最快方法。添加OPTIMIZE

FOR以及FETCH FIRST子句可能会导致优化器选择

索引访问计划，以便尽快返回前25行

尽可能。立即响应时间通常需要付出代价：

我们是否需要检索所有行，总体执行时间b / b $ b时间会很高。它可能远远高于表空间扫描的执行时间。


您可能也会遇到使用OLAP函数的建议
ROW_NUMBER（）一次将输出限制为25行。这个建议比b $ b更有争议。让我们更详细地讨论它，并看看它的b $ b潜在的缺点。如果你使用Visual Explain比较这两个语句的执行

计划：

SELECT ROW_NUMBER（）OVER（）AS N，S。* FROM SALES_DETAIL S在哪里N

介于26和50之间;


SELECT *从SALES_DETAIL到第一个50行只有


你会没注意到太大的区别。两个计划都将是表空间扫描。如果你针对一个小表运行这两个查询，你将会发现响应时间没有太大差异。虽然计划

看起来非常相似，但实际执行情况却大不相同：


第一个语句将扫描整个表格，检查每一行

违反标准N BETWEEN 26 AND 50

第二个声明也将开始扫描整个表格，但

扫描将在所需金额后立即停止行是

检索。


不要假设这两个语句对大表的表现同样好。

。如果你使用db2batch实用程序测量真正的b
执行成本，你将能够发现差异：第一个

语句将扫描整个表，而第二个将扫描整个表阅读

只包含前25行的几个数据页。如果生产环境中的表格变得非常大，那么差异就会很大，所以测试你的应用程序通常是一个好主意

来自

开发早期阶段的实际大量数据。


正如我们所见，FETCH FIRST和OPTIMIZE FOR子句通常更多如果结果集可能很大，则适用于限制行数的
。

此外，我已经证明了考虑执行

计划和实际执行的重要性成本。

Let us consider a very common situation. A GUI application issues a
query, then the first 25 rows from the result set are diplayed on
screen. If the user presses Page Down, then the next 25 rows will be
displayed, and so on. It is well known that FETCH FIRST 25 ROWS and
OPTIMIZE FOR 25 ROWS clauses of SELECT statement are very useful in
this situation. In fact, using either of them is a straightforward way
to tell the optimizer what you actually need. The optimizer, in its
turn, might choose an entirely different access plan to satisfy a
query with such a clause. For example, the query

SELECT * FROM CUSTOMER ORDER BY LAST_NAME

may be satisfied by a tablespace scan, followed by a sort. As a
result, the initial response time may be quite high. However, this is
the fastest way to retrieve the whole result set. Adding an OPTIMIZE
FOR, as well as FETCH FIRST, clause may cause the optimizer to choose
an index access plan, so that the first 25 rows are returned as soon
as possible. The immediate response time usually comes at a price:
should we ever need to retrieve all the rows, the overall execution
time would be high. It could be significantly higher than the
execution time of a tablespace scan.

You may also have come across the advice to use an OLAP function
ROW_NUMBER() to limit the output to 25 rows at a time. This advice is
more controversial. Let us discuss it in more detail and see its
potential drawbacks. If you use Visual Explain to compare execution
plans of these two statements:

SELECT ROW_NUMBER() OVER() AS N, S.* FROM SALES_DETAIL S WHERE N
BETWEEN 26 AND 50;

SELECT * FROM SALES_DETAIL FETCH FIRST 50 ROWS ONLY

you will not notice much difference. Both plans will be tablespace
scans. If you run these two queries against a small table, you will
not notice much difference in response time either. Although the plans
look very similar, the actual execution is quite different:

the first statement will scan the whole table, checking every row
against the criteria N BETWEEN 26 AND 50
the second statement will also start scanning the whole table, but the
scanning will stop as soon as the required amount of rows is
retrieved.

Do not assume that these 2 statements will perform equally well
against a big table. If you use db2batch utility to measure real
execution costs, you will be able to detect the difference: the first
statement will scan the whole table, while the second one will read
just several data pages containing the first 25 rows. Should the table
in production environment grow really big, the difference would be
dramatic, so it is usually a good idea to test your application
against realistically big amounts of data from the early stages of
development.

As we have seen, FETCH FIRST and OPTIMIZE FOR clauses are usually more
appropriate for limiting amount of rows if the result set may be big.
Also I have demonstrated the importance of considering both execution
plan and real execution costs.

嘿，


感谢您的回复。


我试过

----------------------------------------- ----------

选择ROW_NUMBER（）OVER（）AS N，S。* FROM来自P390V.FUND s N

BETWEEN 26 AND 50

----------------------------------------- ----------

但是我收到了这个错误

------------------- ---------------------------- ----

DBA2191E SQL执行错误。

com.ibm.db.DataException：发生数据库管理器错误。 ：

[IBM] [CLI驱动程序] [DB2] SQL0104N意外令牌（已找到

跟随"。预期令牌可能包括：" ;，FROM INTO"。

SQLSTATE = 42601

------------------------ ---------------------------

除此之外，当你说


SELECT * FROM SALES_DETAIL FETCH第一行50行


，数据库给我50行。这将如何帮助我导航到接下来的50行
行我。我的意思是怎么说


SELECT * FROM SALES_DETAIL FETCH（下一个）50行（现在）


等等？


感谢您的帮助。


TP。
ak ************ @ yahoo.com （AK）在留言新闻中写道：< 46 ****** ********************@posting.google.com> ...

Hey,

Thanks for your reply.

I tried the
---------------------------------------------------
select ROW_NUMBER() OVER() AS N, S.* FROM from P390V.FUND s WHERE N
BETWEEN 26 AND 50
---------------------------------------------------
but I got this error
---------------------------------------------------
DBA2191E SQL execution error.

com.ibm.db.DataException: A database manager error occurred. :
[IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found
following "". Expected tokens may include: ", FROM INTO ".
SQLSTATE=42601
---------------------------------------------------
That aside, when you say

SELECT * FROM SALES_DETAIL FETCH FIRST 50 ROWS ONLY

and the database gets me 50 rows. how will this help me navigate to
the next 50 rows. I mean how can I say

SELECT * FROM SALES_DETAIL FETCH (the next) 50 ROWS ONLY (now)

and so on?

Thanks for your help.

TP.
ak************@yahoo.com (AK) wrote in message news:<46**************************@posting.google. com>...

让我们考虑一个非常常见的情况.GUI应用程序问题一个
查询，然后是结果的前25行在屏幕上显示t set。如果用户按下Page Down，则显示接下来的25行，依此类推。众所周知，在这种情况下，SELECT语句的FETCH FIRST 25 ROWS和
OPTIMIZE FOR 25 ROWS子句非常有用。实际上，使用它们中的任何一种都是一种直接的方式来告诉优化器您实际需要什么。优化器在其转向时可能会选择完全不同的访问计划来满足使用此类子句的查询。例如，表空间扫描可以满足查询

SELECT * FROM CUSTOMER ORDER BY LAST_NAME

，然后进行排序。作为结果，初始响应时间可能非常高。但是，这是检索整个结果集的最快方法。添加OPTIMIZE
FOR以及FETCH FIRST子句可能会导致优化器选择索引访问计划，以便尽可能快地返回前25行。立即响应时间通常是有代价的：
如果我们需要检索所有行，整体执行时间会很高。它可能远远高于表空间扫描的执行时间。

您可能也会遇到使用OLAP函数的建议
ROW_NUMBER（）来限制输出一次25行。这个建议更具争议性。让我们更详细地讨论它，看看它的潜在缺点。如果您使用Visual Explain来比较这两个语句的执行计划：

SELECT ROW_NUMBER（）OVER（）AS N，S。* FROM SALES_DETAIL S N /> BETWEEN 26和50;

SELECT *从SALES_DETAIL FETCH第一个50行只有

你不会注意到太大的区别。两个计划都将是表空间扫描。如果你对一个小表运行这两个查询，你也不会注意到响应时间的差异。虽然计划看起来非常相似，但实际执行却截然不同：

第一个语句将扫描整个表格，检查每一行
符合标准N BETWEEN 26 AND 50
第二个语句也将开始扫描整个表格，但是一旦检索到所需的行数，
扫描就会停止。

不要假设这两个陈述对大表的表现同样好。如果使用db2batch实用程序来测量实际执行成本，您将能够检测到差异：第一个
语句将扫描整个表，而第二个语句将读取几个数据包含前25行的页面。如果生产环境中的表格变得非常大，那么差异就会很大，所以通常最好根据实际的大量数据来测试你的应用程序。


如我们所见，如果结果集可能很大，FETCH FIRST和OPTIMIZE FOR子句通常更适合于限制行数。
此外，我已经证明了考虑执行计划和实际执行成本的重要性。

Let us consider a very common situation. A GUI application issues a
query, then the first 25 rows from the result set are diplayed on
screen. If the user presses Page Down, then the next 25 rows will be
displayed, and so on. It is well known that FETCH FIRST 25 ROWS and
OPTIMIZE FOR 25 ROWS clauses of SELECT statement are very useful in
this situation. In fact, using either of them is a straightforward way
to tell the optimizer what you actually need. The optimizer, in its
turn, might choose an entirely different access plan to satisfy a
query with such a clause. For example, the query

SELECT * FROM CUSTOMER ORDER BY LAST_NAME

may be satisfied by a tablespace scan, followed by a sort. As a
result, the initial response time may be quite high. However, this is
the fastest way to retrieve the whole result set. Adding an OPTIMIZE
FOR, as well as FETCH FIRST, clause may cause the optimizer to choose
an index access plan, so that the first 25 rows are returned as soon
as possible. The immediate response time usually comes at a price:
should we ever need to retrieve all the rows, the overall execution
time would be high. It could be significantly higher than the
execution time of a tablespace scan.

You may also have come across the advice to use an OLAP function
ROW_NUMBER() to limit the output to 25 rows at a time. This advice is
more controversial. Let us discuss it in more detail and see its
potential drawbacks. If you use Visual Explain to compare execution
plans of these two statements:

SELECT ROW_NUMBER() OVER() AS N, S.* FROM SALES_DETAIL S WHERE N
BETWEEN 26 AND 50;

SELECT * FROM SALES_DETAIL FETCH FIRST 50 ROWS ONLY

you will not notice much difference. Both plans will be tablespace
scans. If you run these two queries against a small table, you will
not notice much difference in response time either. Although the plans
look very similar, the actual execution is quite different:

the first statement will scan the whole table, checking every row
against the criteria N BETWEEN 26 AND 50
the second statement will also start scanning the whole table, but the
scanning will stop as soon as the required amount of rows is
retrieved.

Do not assume that these 2 statements will perform equally well
against a big table. If you use db2batch utility to measure real
execution costs, you will be able to detect the difference: the first
statement will scan the whole table, while the second one will read
just several data pages containing the first 25 rows. Should the table
in production environment grow really big, the difference would be
dramatic, so it is usually a good idea to test your application
against realistically big amounts of data from the early stages of
development.

As we have seen, FETCH FIRST and OPTIMIZE FOR clauses are usually more
appropriate for limiting amount of rows if the result set may be big.
Also I have demonstrated the importance of considering both execution
plan and real execution costs.

这篇关于从表和导航中选择*的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！