FBI? MAX? INDEX FULL SCAN (MIN/MAX)?

这个是我在comp.database.oracle.server新闻组问的问题，自己一直不知道为什么？昨天看了人家的解答，记录下来。

http://jonathanlewis.wordpress.com/2007/03/12/methods/

这个地址好像国内无法访问。

March 12, 2007

Methods

Filed under: Troubleshooting — Jonathan Lewis @ 9:52 pm UTC Mar 12,2007

The following question appeared a little while ago on comp.databases.oracle.server, with reference to Oracle 10.2.0.1:

drop table t1 purge;      

create table t1
as
select * from all_objects;       

create index t1_fbi1   on t1(lower(object_name));
create index t1_i1 on t1(object_name);       

-- now generate statistics

Given the above data and indexes, why do the following two queries show significantly different execution plans ?

SQL> set autotrace traceonly explain     

SQL> select  max(object_name)
  2  from    t1
  3  ;     

Execution Plan
----------------------------------------------------------
Plan hash value: 1743745495     

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |       |     1 |    25 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_I1 | 46531 |  1136K|     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------     

SQL> select  max(lower(object_name))
  2  from    t1
  3  ;     

Execution Plan
----------------------------------------------------------
Plan hash value: 3308075536     

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    25 |    65   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |         |     1 |    25 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_FBI1 | 46531 |  1136K|    65   (2)| 00:00:01 |
---------------------------------------------------------------------------------

Note how the query that can use the simple index uses the special min/max optimisation for index range/full scans that appeared in the 8.1 timeline, but the query that can use the function-based index does a fast full scan and sort of the index - and a check of the resource usage shows that autotrace is telling us the truth about the plans in both cases.

To investigate this type of problem, one of my first “tricks” is simply to tell the optimizer to do what I think it should do. In this case, give it a hint to use the index properly.

SQL> select  /*+ index(t1) */
  2  	     max(lower(object_name))
  3  from    t1
  4  ;    

Execution Plan
----------------------------------------------------------
Plan hash value: 1546143440    

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    25 |   235   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE             |         |     1 |    25 |            |          |
|   2 |   FIRST ROW                 |         | 46531 |  1136K|   235   (1)| 00:00:03 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| T1_FBI1 | 46531 |  1136K|   235   (1)| 00:00:03 |
---------------------------------------------------------------------------------------

The path I was expecting has appeared - with an interesting “first row” operation and a surprising cost ! A quick check of resource usage shows that Oracle used the path given, with minimal resource usage, confirming that the cost is a serious miscalculation. So where does that cost come from.

Change the query slightly, and you’ll see:

SQL> select	/*+ index(t1) */
  2  	object_name
  3  from	t1
  4  ;   

Execution Plan
----------------------------------------------------------
Plan hash value: 2969740442   

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       | 46531 |  1136K|   235   (1)| 00:00:03 |
|   1 |  INDEX FULL SCAN | T1_I1 | 46531 |  1136K|   235   (1)| 00:00:03 |
--------------------------------------------------------------------------

The index-hinted query to find the max() did actually use the min/max access path at run-time - we can see that from the resource usage - but the optimizer used the cost for a simple full scan, which is rather expensive and made the default behaviour switch to the fast full scan with sort. It’s some sort of bug in the optimizer.

Interestingly, we can get the min/max plan to appear by adding a predicate to the query that (notionally) addresses any problems that might be caused by nulls:

SQL> select  max(lower(object_name))
  2  from    t1
  3  where   lower(object_name) is not null
  4  ;  

Execution Plan
----------------------------------------------------------
Plan hash value: 1546143440  

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |         |     1 |    25 |            |          |
|   2 |   FIRST ROW                 |         | 46531 |  1136K|     2   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| T1_FBI1 | 46531 |  1136K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------  

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(LOWER("OBJECT_NAME") IS NOT NULL)

So ultimately we have to go to the 10053 trace file to try and pin down the exact nature of the bug. In the trace file, we can see that the version of code with the ‘not null’ predicate is the only one that results in a section of the Single Table Access Path calculation that examines the possibility of the min/max path.

  Access Path: index (Min/Max)
    Index: T1_FBI1
    resc_io: 2.00  resc_cpu: 14443
    ix_sel: 2.1491e-005  ix_sel_with_filters: 2.1491e-005
    Cost: 2.00  Resp: 2.00  Degree: 1

From this, we can conclude the problem lies in the optimizer failing to spot the option for using the min/max path in the default scenario, rather than the optimizer doing the wrong calculation for the path.

It doesn’t however, tell us why the run-time engine can apparently use the min/max optimisation when the optimizer obeyed our hint and generated a plan that included a full index scan. The trace file in the hinted case only showed the full scan calculation, it didn’t suggest a min/max, nor a “first row”, nor a descending scan.

And just one final thought - the problem shouldn’t have anything to do with the fact that we have supplied an explicit ‘not null’ predicate. If this were an example of a “nulls not in index” problem, the optimizer would have to fall back to using a tablescan in every case.

And at this point, I usually pass the buck to Oracle support.

时间： 2024-10-27 04:22:19

FBI? MAX? INDEX FULL SCAN (MIN/MAX)?的相关文章

min ? max ? 执行计划？

create table big_table as select * from dba_objects;insert into big_table select * from big_table;insert into big_table select * from big_table; ALTER TABLE BIG_TABLE MODIFY(OBJECT_ID NULL); CREATE INDEX I_BT_OBJECT_ID ON BIG_TABLE(OBJECT_ID) select

PostgreSQL 流式统计 - insert on conflict 实现 流式 UV(distinct), min, max, avg, sum, count ...

标签 PostgreSQL , 流式统计 , insert on conflict , count , avg , min , max , sum 背景 流式统计count, avg, min, max, sum等是一个比较有意思的场景,可用于实时大屏,实时绘制统计图表. 比如菜鸟.淘宝.阿里游戏.以及其他业务系统的FEED日志,按各个维度实时统计输出结果.(实时FEED统计,实时各维度在线人数等) PostgreSQL insert on conflict语法以及rule, trigger的功

LINQ to SQL语句(3)之Count/Sum/Min/Max/Avg

[1] Count/Sum讲解 [2] Min讲解 [3] Max讲解 [4] Average和Aggregate讲解 Count/Sum/Min/Max/Avg操作符 适用场景: 统计数据吧,比如统计一些数据的个数,求和,最小值,最大值,平均数. Count 说明:返回集合中的元素个数,返回INT类型:不延迟.生成 SQL语句为:SELECT COUNT(*) FROM 1.简单形式: 得到数据库中客户 的数量: var q = db.Customers.Count(); 2.带条件形 式:

LINQ体验(5)——LINQ to SQL语句之Select/Distinct和Count/Sum/Min/Max/Avg

上一篇讲述了LINQ,顺便说了一下Where操作,这篇开始我们继续说LINQ to SQL语句,目的让大家从语句的角度了解LINQ,LINQ包括LINQ to Objects.LINQ to DataSets.LINQ to SQL.LINQ to Entities.LINQ to XML,但是相对来说LINQ to SQL在我们程序中使用最多,毕竟所 有的数据都要在数据库运行着各种操作.所以先来学习LINQ to SQL,其它的都差不多了,那么就从Select说起吧,这个在编写程序中也最为常

linq to sql （Group By/Having/Count/Sum/Min/Max/Avg操作符）

原文:linq to sql (Group By/Having/Count/Sum/Min/Max/Avg操作符) Group By/Having操作符 适用场景:分组数据,为我们查找数据缩小范围. 说明:分配并返回对传入参数进行分组操作后的可枚举对象.分组:延迟 1.简单形式: var q = from p in db.Products group p by p.CategoryID into g select g; 语句描述:使用Group By按CategoryID划分产品. 说明:fro

关于Oracle 9i 跳跃式索引扫描(Index Skip Scan)的小测试

oracle|索引 在Oracle9i中我们知道能够使用跳跃式索引扫描(Index Skip Scan).然而,能利用跳跃式索引扫描的情况其实是有些限制的. 从Oracle的文档中我们可以找到这样的话: Index Skip ScansIndex skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.Sk

c++-C++中函数重载的问题，为什么没有调用double Max,而是调用了int Max?

问题描述 C++中函数重载的问题,为什么没有调用double Max,而是调用了int Max? 函数Max有以下两种重载形式: int Max(int x, int y) { return x >= y ? x : y; } double Max(double x, double y) { return x >= y ? x : y; } 在main函数中调用Max代码如下 double m = 11.5, n = 15.5; cout << " Max="

index full scan 和 index fast full scan （IFS,FFS）的不同

转自ITPUB 首先来看一下IFS,FFS能用在哪里:在一句sql中,如果我们想搜索的列都包含在索引里面的话,那么index full scan 和 index fast full scan 都可以被采用代替full table scan.比如以下语句: SQL> CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1; SQL> CREATE INDEX ind_test_id ON TEST(object_id); SQL>

INDEX FULL SCAN和INDEX FAST FULL SCAN的区别

原创 转载请注明出处 (原创)    关于INDEX FULL SCAN和INDEX FAST FULL SCAN的区别在于,前者在对索引进行扫描的时候会考虑大索引的结构,而且会按照索引的排序,    而后者则不会,INDEX FAST FULL SCAN不会去扫描根块和分支块,对索引像访问堆表一样访问,所以这两个扫描方式用在不同的场合    如果存在ORDER BY这样的排序,INDEX FULL SCAN是合适的,如果不需要排序,那INDEX FAST FULL SCAN效率是更高的.