如何取消一个列的直方图

棉花糖ONE

1.那2行就是最大值和最小值,10g里的delete=histogram+列基本信息,最大值和最小值是属于列的基本信息,不收集直方图执行计划没改变很正常
2.你的情况我更倾向于使用hint no_index(table INDX_NTHWKGRN_GROUPNMC) ,因为这个索引对你这语句没啥帮助,那地方显示的虽然是index range scan,但是实际上是index full scan

[ 本帖最后由 棉花糖ONE 于 2009-9-9 14:45 编辑 ]

棉花糖ONE

我原来测试
select * from table where column like :1;
是因为我想如果第一次输入 'a%'这样的条件的时候,那么bind peeking之后可能走index range scan,那第二次输入'%a%'的时候是不是会重新生成一个执行计划,测试发现不会生成新的执行计划,还是index range scan,但是实际上
就是index full scan.

index full scan也是index range scan的一种特殊情况

[ 本帖最后由 棉花糖ONE 于 2009-9-9 15:53 编辑 ]

goobaile

索引情况：
INDX_NTHWKGRN_GROUPNMC (GROUPNMC)
INDX_NTHWKGRN_ZS        (ZONENO SUBSTR("BASEACC",5,4))
INDX_NTHWKGRN_GROUPNMC索引确实在这没多大用，有了zoneno的筛选效果已经不错了。

〇〇

记录学习

Yong Huang

原帖由 棉花糖ONE 于 2009-9-9 00:44 发表
2.你的情况我更倾向于使用hint no_index(table INDX_NTHWKGRN_GROUPNMC) ,因为这个索引对你这语句没啥帮助,那地方显示的虽然是index range scan,但是实际上是index full scan

Are you saying even though the plan is shown as index range scan, it's actually index full scan? I checked with select * from table(dbms_xplan.display_cursor('..., which fetches the plan from v$sql_plan. It *is* showing an index range scan. Tested in 10.2.0.1. What's your version of Oracle that actually shows index full scan?

Yong Huang

棉花糖ONE

是的,我知道是显示index range scan,但是这个地方它肯定要扫描所有的叶子节点,所以就等于index full scan,只是oracle为了执行计划共享,所以还是现实的range scan ,index full scan只不过是一种特殊的range scan

[ 本帖最后由 棉花糖ONE 于 2009-9-10 08:20 编辑 ]

人呐

先MARK一下，回头好好研究哈。。

viadeazhu

首先lz根本就没有搜集柱状图。
method_opt=>'size 1'代表的就是不搜集柱状图，这样分析之后，默认就会有两条记录在user_histograms中。所以，有两条记录恰好说明的是没有搜集柱状图。
SQL> select * from user_histograms
  2  where table_name='TEST2' and column_name='OBJECT_ID';

no rows selected

SQL> exec dbms_stats.gather_table_stats('LONGRAW_USER','TEST2',estimate_percent=>100,cascade=>true,no_invalidate=>true,method_opt=>'for all columns size 1');


PL/SQL procedure successfully completed.

SQL> SQL> select * from user_histograms
  2  where table_name='TEST2' and column_name='OBJECT_ID';

TABLE_NAME
------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
-------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST2
OBJECT_ID
              0              2


TEST2
OBJECT_ID
              1          61349


其次，对于where xxx like '%'||:bind_var会显示index range scan，我学习到了，以前没注意过。
果然如棉花糖说的，虽然显示的是range scan，但实际是走的full scan。
这个可以从他们的statistics看出来。


SQL> select /*+index(test)*/ object_id
  2  from test where object_name like '%test';
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   569 | 12518 |   255   (1)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |   569 | 12518 |   255   (1)| 00:00:04 |
|*  2 |   INDEX FULL SCAN           | IDXTEST |   569 |       |    49   (3)| 00:00:01 |
---------------------------------------------------------------------------------------

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


SQL> var a char(4);
SQL> exec :a:='test'

PL/SQL procedure successfully completed.

SQL> select /*+index(test)*/ object_id
  2  from test where object_name like '%'||:a;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   569 | 12518 |    34   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |   569 | 12518 |    34   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDXTEST |   102 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         48  consistent gets
          0  physical reads
          0  redo size
        326  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Yong Huang

I confirmed the conclusion of 棉花糖ONE and viadeazhu that the plan with '%' || :bind_variable in the where clause should better be displayed as index full scan. The display of index range scan is incorrect, or misleading to say the least. The fact that they have the same consistent gets is one proof. I went further and checked (in 10.2.0.4) all the consistent get blocks with event 10202, and compared with an index tree dump. All the blocks in the index tree dump appear in the blocks got in CR mode, and the latter has two additional CR blocks (1 for table and 1 for index) compared to the tree dump. Event 10202 is set for running two SQLs:
select /*+ index(t) */ * from t where x like '%' || :s;
select /*+ index(t) */ * from t where x like '%QA643PMYC9';
They generate exactly the same CR blocks shown in the two trace files in the same access order.

I searched Metalink but didn't find a bug report. It's probably a known problem, but it's hard to come up with good keywords to search for it.

Yong Huang

[ 本帖最后由 Yong Huang 于 2009-10-5 14:38 编辑 ]