ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 458|回复: 10

关于order by

[复制链接]
论坛徽章:
0
跳转到指定楼层
1#
发表于 2018-1-7 00:12 | 只看该作者 |只看大图 回帖奖励 |倒序浏览 |阅读模式
create table tobj2kw as select * from dba_objects;
select owner,object_type,object_name,last_ddl_time from tobj2kw where owner='SYS' and object_type='LOB PARTITION' order by last_ddl_time;

如果需要对上面的语句建立索引,是该建owner, object_type上的联合索引,还是该建owner, object_type,last_ddl_time联合索引?

create index idx_tobj2kw_owner_type on tobj2kw(owner,object_type);

create index idx_tobj2kw_owner_type_ddl on tobj2kw(owner,object_type,last_ddl_time);

建了两个索引,但是发现语句都是走的上面第一个索引idx_tobj2kw_owner_type,WHY?
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
2#
发表于 2018-1-7 08:23 来自手机 | 只看该作者
因为第二个索引的最后一列没用。

使用道具 举报

回复
论坛徽章:
0
3#
 楼主| 发表于 2018-1-7 08:51 | 只看该作者
我想知道的是第二个索引能消除排序吗?

使用道具 举报

回复
论坛徽章:
0
4#
 楼主| 发表于 2018-1-7 08:57 | 只看该作者
set autotrace on
select owner,object_type,object_name,last_ddl_time from tobj2kw where owner='SYS' and object_type='LOB PARTITION' order by last_ddl_time;
Execution Plan
----------------------------------------------------------
Plan hash value: 1288284935

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        | 90310 |  4938K|       |  5280   (1)| 00:01:04 |
|   1 |  SORT ORDER BY               |                        | 90310 |  4938K|  6032K|  5280   (1)| 00:01:04 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TOBJ2KW                | 90310 |  4938K|       |  4045   (1)| 00:00:49 |
|*  3 |    INDEX RANGE SCAN          | IDX_TOBJ2KW_OWNER_TYPE | 90310 |       |       |   332   (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OWNER"='SYS' AND "OBJECT_TYPE"='LOB PARTITION')


Statistics
----------------------------------------------------------
       1506  recursive calls
          0  db block gets
        417  consistent gets
        144  physical reads
          0  redo size
       7610  bytes sent via SQL*Net to client
        612  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
         35  sorts (memory)
          0  sorts (disk)
        128  rows processed

强制走第二个索引
select /*+ index(tobj2kw idx_tobj2kw_owner_type_ddl) */owner,object_type,object_name,last_ddl_time from tobj2kw where owner='SYS' and object_type='LOB PARTITION' order by last_ddl_time;
Execution Plan
----------------------------------------------------------
Plan hash value: 4073907801

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            | 90310 |  4938K| 17824   (1)| 00:03:34 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TOBJ2KW                    | 90310 |  4938K| 17824   (1)| 00:03:34 |
|*  2 |   INDEX RANGE SCAN          | IDX_TOBJ2KW_OWNER_TYPE_DDL | 90310 |       |   434   (1)| 00:00:06 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYS' AND "OBJECT_TYPE"='LOB PARTITION')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        141  consistent gets
          5  physical reads
          0  redo size
       7610  bytes sent via SQL*Net to client
        612  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        128  rows processed


说明走第二个索引确实消除了排序,但COST却比第一个大,搞不明白

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
5#
发表于 2018-1-7 09:21 | 只看该作者
黄山情缘99 发表于 2018-1-7 08:57
set autotrace on
select owner,object_type,object_name,last_ddl_time from tobj2kw where owner='SYS'  ...

这个数据量就是这样子,避免排序也没用,得不偿失。

使用道具 举报

回复
认证徽章
论坛徽章:
0
6#
发表于 2018-1-7 10:55 | 只看该作者
本帖最后由 Johnelse 于 2018-1-7 10:56 编辑
黄山情缘99 发表于 2018-1-7 08:57
set autotrace on
select owner,object_type,object_name,last_ddl_time from tobj2kw where owner='SYS'  ...

表有2000万数据?哪个数据库版本啊?

我用11.2.0.3测试了一下,数据库都是用第二个索引tobj2kw idx_tobj2kw_owner_type_ddl的, 即使我使用Hints /*+ INDEX(idx_tobj2kw_owner_type) */


使用道具 举报

回复
论坛徽章:
0
7#
 楼主| 发表于 2018-1-7 17:19 | 只看该作者
我用的是11.2.0.1,看来你的这个结果才是合理的

使用道具 举报

回复
论坛徽章:
1
秀才
日期:2017-02-22 15:16:26
8#
发表于 2018-1-8 11:03 | 只看该作者
Execution Plan
----------------------------------------------------------
Plan hash value: 1892078718

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |   308 | 15400 |    20   (5)| 00:00:01 |
|   1 |  SORT ORDER BY               |                  |   308 | 15400 |    20   (5)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_IND_DIFF       |   308 | 15400 |    19   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_IND_OWNER_TYPE |   308 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OWNER"='SYS' AND "OBJECT_TYPE"='LOB PARTITION')

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


Statistics
----------------------------------------------------------
         49  recursive calls
          4  db block gets
         83  consistent gets
         12  physical reads
          0  redo size
        793  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          1  rows processed
         
         

Execution Plan
----------------------------------------------------------
Plan hash value: 86613888

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |   308 | 15400 |    29   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_IND_DIFF            |   308 | 15400 |    29   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND_OWNER_TYPE_LAST |   308 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYS' AND "OBJECT_TYPE"='LOB PARTITION')

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing


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

使用道具 举报

回复
论坛徽章:
1
秀才
日期:2017-02-22 15:16:26
9#
发表于 2018-1-8 11:06 | 只看该作者
使用第二个索引虽然减少排序但是增加了索引和回表的IO消耗,总体cost增加,当然oracle选择走第一个索引;--测试版本11.2.0.4 清楚buffer_cache和shared_pool

使用道具 举报

回复
论坛徽章:
0
10#
 楼主| 发表于 2018-1-9 21:43 | 只看该作者
解释不通吧?走第一个索引同样要回表啊

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表