Oracle 10g:MIN/MAX列值估计 [英] Oracle 10g: MIN/MAX column value estimation

问题描述

是否可以检索有关Oracle 10g中数字列的最小值或最大值的统计信息?我发现表USER_TAB_COL_STATISTICS具有LOW_VALUE和HIGH_VALUE列，但是我不确定这些值是否是我要寻找的值.

is it possible to retrieve statistics about the minimal or maximal value of a numeric column in Oracle 10g? I have found the table USER_TAB_COL_STATISTICS having a LOW_VALUE and HIGH_VALUE column, but I am not sure whether those are the values I am looking for.

我需要找到一种有效的方法来向DBS询问这些统计信息.在大型表上使用常规的MIN(a)和MAX(a)查询会太慢.

I need to find an efficient way to ask the DBS for those statistics. Using a regular MIN(a) and MAX(a) query would be too slow on large tables.

谢谢.

推荐答案

是的，LOW_VALUE和HIGH_VALUE会告诉您但列中的最小值和最大值:

Yes, LOW_VALUE and HIGH_VALUE will tell you the minimum and maximum values in the column but:

• 它们存储为RAW(32)列，因此含义不会立即显现
• 它们将是上一次为该表收集统计信息的时间，因此可能并不准确(除非您在使用统计信息之前明确收集了这些统计信息)

如果您为该列建立索引，则MIN(a)和MAX(a)应该非常快，如本例中T1有50000行并在OBJECT_ID上进行索引:

If you index the column then MIN(a) and MAX(a) should be very fast as in this example where T1 has 50000 rows and is indexed on OBJECT_ID:

SQL> select min(object_id) from t1;

MIN(OBJECT_ID)
--------------
           100

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     5 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |       |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_ID | 53191 |   259K|     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        419  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

如果选择MAX而不是MIN，则结果相同.但是，如果在单个select语句中选择MIN和MAX，则结果将不同:

The result is the same if you select the MAX instead of the MIN. However, if you select the MIN and MAX in a single select statement the result is different:

SQL> select min(object_id), max(object_id) from t1;

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
           100          72809


-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |     5 |    34   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_ID | 53191 |   259K|    34   (0)| 00:00:01 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        125  consistent gets
          0  physical reads
          0  redo size
        486  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这表明最好将它们分开，尽管我还没有最终证明这一点.

This suggests that it may be better to get them separately, though I haven't conclusively proved that.

这篇关于Oracle 10g:MIN/MAX列值估计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！