- 浏览: 4373879 次
- 性别:
- 来自: 厦门
文章分类
- 全部博客 (634)
- Oracle日常管理 (142)
- Oracle体系架构 (45)
- Oracle Tuning (52)
- Oracle故障诊断 (35)
- RAC/DG/OGG (64)
- Oracle11g New Features (48)
- DataWarehouse (15)
- SQL, PL/SQL (14)
- DB2日常管理 (9)
- Weblogic (11)
- Shell (19)
- AIX (12)
- Linux/Unix高可用性 (11)
- Linux/Unix日常管理 (66)
- Linux桌面应用 (37)
- Windows (2)
- 生活和工作 (13)
- 私人记事 (0)
- Python (9)
- CBO (15)
- Cognos (2)
- ORACLE 12c New Feature (2)
- PL/SQL (2)
- SQL (1)
- C++ (2)
- Hadoop大数据 (5)
- 机器学习 (3)
- 非技术 (1)
最新评论
-
di1984HIT:
xuexilee!!!
Oracle 11g R2 RAC高可用连接特性 – SCAN详解 -
aneyes123:
谢谢非常有用那
PL/SQL的存储过程和函数(原创) -
jcjcjc:
写的很详细
Oracle中Hint深入理解(原创) -
di1984HIT:
学习了,学习了
Linux NTP配置详解 (Network Time Protocol) -
avalonzst:
大写的赞..
AIX内存概述(原创)
创建一个表,含有位图index和b-tree index
SQL> create table t as select object_id id ,object_name from dba_objects;
Table created.
SQL> create index b_tree_ind on t(id);
Index created.
SQL> create bitmap index b_bm_name on t(object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','T',cascade=> true);
SELE
PL/SQL procedure successfully completed.
SQL> set autotrace trace exp stat
SQL> select id from t;
50365 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47448 | 602K| 57 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 47448 | 602K| 57 (2)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
3646 consistent gets
0 physical reads
0 redo size
728900 bytes sent via SQL*Net to client
37312 bytes received via SQL*Net from client
3359 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50365 rows processed
这里因为object_id列可能有null值,所以不会使用b_tree_t_xifenfei索引,预料之中事件
SQL> select /*+ index(t b_tree_ind) */ id from t
50365 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3465251059
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47448 | 602K| 805 (1)| 00:00:10 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 47448 | 602K| 805 (1)| 00:00:10 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP INDEX FULL SCAN | B_BM_NAME | | | | |
------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
27318 consistent gets
0 physical reads
0 redo size
2147500 bytes sent via SQL*Net to client
37312 bytes received via SQL*Net from client
3359 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50365 rows processed
这里因为object_id列可能有null值,所以不会使用b_tree_t_xifenfei索引,这里的疑惑是:
就算不会使用b_tree_t_xifenfei index也不应该会使用BITMAP_T_XIFENFEI index,因为使用这个的cost会大于全表扫描
SQL> select /*+ index(t aaa) */ id from t;
50365 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3465251059
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50365 | 245K| 805 (1)| 00:00:10 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 50365 | 245K| 805 (1)| 00:00:10 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP INDEX FULL SCAN | B_BM_NAME | | | | |
------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
27318 consistent gets
0 physical reads
0 redo size
2147500 bytes sent via SQL*Net to client
37312 bytes received via SQL*Net from client
3359 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50365 rows processed
这里使用了一个无效的index,也使用了BITMAP_T_XIFENFEI,让人更加的感觉奇怪。
官方文档上有如下解释
If the INDEX hint specifies a single available index, then the database performs a scan on this index. The optimizer does not consider a full table scan or a scan of another index on the table.
For a hint on a combination of multiple indexes, Oracle recommends using INDEX_COMBINE rather than INDEX, because it is a more versatile hint. If the INDEX hint specifies a list of available indexes, then the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The database does not consider a full table scan or a scan on an index not listed in the hint.
If the INDEX hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.
如果我们使用hint指定了一个无效的index,优化器会扫描表中所有可以使用的index,然后选择cost最小的index或者index组合,而不会选择全表扫描。
因为我们hint指定b_tree_t_xifenfei index的时候,因为object_id可能有值为空(列没定义为not null),所以不能使用该index,从而也就是相当于一个无效的index,从而扫描该表的其他可以使用的index,导致使用了位图索引(该类型index不排除null),而不是全表扫描.在9i和10g上有上述现象,11g时Oracle对上述现象有所改进
注意:使用hint指定index的时候需要慎重,如果不合适或者无效,可能导致程序效率更低
感谢惜分飞的指点
参考至:http://www.xifenfei.com/2865.html
如有错误,欢迎指正
邮箱:czmcj@163.com
发表评论
文章已被作者锁定,不允许评论。
-
Oracle Redo 并行机制
2017-04-07 11:31 949Redo log 是用于恢复和一个高级特性的重要数据,一个r ... -
Append Values and how not to break the database
2015-09-29 20:12 708With the advent of the /*+ APP ... -
基于案例学习sql优化第6周脚本
2015-04-13 04:29 0===============BEGIN=========== ... -
Oracle表高水平位的优化与监控
2015-02-13 09:21 2173高水平位虚高的案例 --构造表drop table t p ... -
Oracle行迁移和行链接详解(原创)
2015-02-13 09:00 11896行迁移成 因:当发出u ... -
Parse CPU to Parse Elapsd%的理解
2015-01-19 13:59 1383Parse CPU to Parse Elapsd%是指sq ... -
ALTER INDEX COALESCE: 10g Improvements
2014-08-02 21:34 887I thought it might be worth me ... -
Differences and Similarities Between Index Coalesce and Shrink Space
2014-08-02 21:21 912As already discussed, ALTER IN ... -
Alter index coalesce VS shrink space
2014-08-02 17:56 97410g中引入了对索引的shrink功能,索引shrink操 ... -
SQL Plan Management Creating SQL plan baselines(原创)
2014-08-01 23:56 1328SQL Plan Management SQL Plan ... -
WITH Clause : Subquery Factoring
2014-07-23 08:43 1155Subquery Factoring The WIT ... -
Query Transformations : Subquery unnesting(原创)
2014-07-23 08:42 2850Subquery Unnesting Subqueries ... -
Automating Parallelism
2014-07-17 17:49 769Parallel query, the essence of ... -
Parallel Execution: Large/Shared Pool and ORA-4031 (文档 ID 238680.1)
2014-07-17 17:47 2070Applies toOracle Database - En ... -
Optimizer Transformations: Star Transformation
2014-06-30 07:32 749Star transformation was intro ... -
Star Transformation And Cardinality Estimates
2014-06-30 07:33 846If you want to make use of Orac ... -
Optimizer statistics-driven direct path read decision for full table scans
2014-06-06 16:09 1042Hello all fellow Oracle geeks ... -
Cut out from Ask Tom-- Thanks for the question regarding "10053", version 9.2.6
2014-03-09 23:38 1376You AskedDear tom,A. your new ... -
ORACLE SQL TUNING各种技巧及复杂实例
2014-02-25 23:17 6455一.优化器模式ORACLE的优化器共有3种:a. RULE ... -
Oracle Predicate Pushing(原创)
2014-02-22 21:17 4541IntroductionThe join predicate ...
相关推荐
js hint 提示js hint 提示js hint 提示js hint 提示js hint 提示js hint 提示js hint 提示js hint 提示js hint 提示js hint 提示js hint 提示js hint 提示
介绍了oracle中的hint,常用的 ordered、use_nl、use_hash、index、full 五种, 给出使用实例和适用场景
在MySQL中还有一种提示,叫做hint,hint是对数据库的提示,我们都知道, 在执行一条SQL语句的时候,MySQL都会生成一个执行计划,而hint就是用来告诉优化器按照我们告诉它的方式生成执行计划。Hint可以基于表连接的...
Hint概述(/*+??*/的用法) 基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,减轻了DBA的负担。但有时它也聪明反被聪明误,选择了很差的执行计划,使某个语句的执行变得奇慢无比。 此时就...
提供的是哦oracle中hint的用法。可以加快查詢速度,按照預設的執行計劃執行
1. /*+ INDEX */ 和 /*+ INDEX(TABLE INDEX1, index2) */ 和 /*+ INDEX(tab1.col1 tab2.col2) */ 和 /*+ NO_INDEX */ 和 /*+ NO_INDEX(TABLE INDEX1, index2) */ 2. /*+ ORDERED */ 3. /*+ PARALLEL(table1,DEGREE) ...
修改EditText的hint,主要为hint的字体大小的修改。
由于cxGrid把Hint给封装了(它的Hint须在列宽不够大时才显示,且显示的内容不能随意),使得Hint无法方便地使用,经在cxGrid源码中分析,弄出这个给cxGrid附加格式化Hint的方法,在Delphi6、ExpressQuantumGrid_v5.8...
oracle hint 语句优化hint,拷下来分享下、 在 SQL 语句优化过程中,我们经常会用到 hint,
oracle_hint教程汇总,详细分析各种oracle的hint的用途及用法
delphi7实现 combobox下拉hint
里面是ORACLE SQL 优化时会用到的hint示例汇总。总共有30个hint。 全部都是hint说明及示例。下面展示一个示例。 /*+noappend*/ 通过在插入语句生存期内停止并行模式来启动常规插入. insert /*+noappend*/ into test...
delphi 自制hint delphi 自制hint delphi 自制hint delphi 自制hint delphi 自制hint delphi 自制hint
Oracle 中HINT的使用
NULL 博文链接:https://heaven022.iteye.com/blog/1670450
Oracle hint的用法,优化语句效率
漂亮简洁的气泡Hint提示,简单实用。如果嫌Delphi的提示太死板,不够漂亮,那么就看看这个吧,一定会有意外的惊喜。
Android实现 点击EditText编辑框清除hint预设字 的功能实现Demo。
sql学习 hint无法生效原因2_组合hint有矛盾.sql
delphi 2010的Balloon Hint改到2007