mysql 复合索引引导列_使用复合索引不一定需要引导列

最新推荐文章于 2021-04-03 04:39:29 发布

書淺

最新推荐文章于 2021-04-03 04:39:29 发布

阅读量158

点赞数

文章标签： mysql 复合索引引导列

本文链接：https://blog.csdn.net/weixin_34576293/article/details/113334178

版权

这篇博客展示了在Oracle数据库中进行查询操作的过程，包括创建表、索引及执行不同类型的查询。作者通过举例说明了全表扫描与索引扫描的执行计划和性能差异，并强调了ROWNUM在限制返回行数时的作用。文章还提到了数据库版本为Oracle 9i，暗示了数据库查询优化的重要性。

摘要由CSDN通过智能技术生成

我的测试了一下，却需要引导列：

SQL> create table test as select * from user_objects;

±íÒÑ´´½¨¡£

SQL> create index IX_TEST_FH on test(object_id,object_name) ;

Ë÷ÒýÒÑ´´½¨¡£

SQL> select object_name from test where rownum<2;

OBJECT_NAME

--------------------------------------------------------------------------------

SQL> set autot trace exp stat

SQL> select object_name from test where OBJECT_NAME='AA';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (FULL) OF 'TEST'

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

62 consistent gets

0 physical reads

0 redo size

518 bytes sent via SQL*Net to client

655 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select object_name,object_id from test where OBJECT_NAME='AA';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (FULL) OF 'TEST'

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

62 consistent gets

0 physical reads

0 redo size

588 bytes sent via SQL*Net to client

655 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select object_id,object_name from test where rownum<2;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 COUNT (STOPKEY)

2 1 TABLE ACCESS (FULL) OF 'TEST'

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

588 bytes sent via SQL*Net to client

655 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set autot off

SQL> select object_id,object_name from test where rownum<2;

OBJECT_ID

----------

OBJECT_NAME

--------------------------------------------------------------------------------

37606

SQL> select object_name from test where OBJECT_id='37606';

OBJECT_NAME

--------------------------------------------------------------------------------

SQL> set autot trace exp;

SQL> select object_name from test where OBJECT_id='37606';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 INDEX (RANGE SCAN) OF 'IX_TEST_FH' (NON-UNIQUE)

SQL> select object_id,object_name from test where OBJECT_id='37606';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 INDEX (RANGE SCAN) OF 'IX_TEST_FH' (NON-UNIQUE)

SQL>

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

PL/SQL Release 9.2.0.8.0 - Production

CORE 9.2.0.8.0 Production

TNS for Solaris: Version 9.2.0.8.0 - Production

NLSRTL Version 9.2.0.8.0 - Production

書淺

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 复合索引引导列_使用复合索引不一定需要引导列

我的测试了一下，却需要引导列：SQL> create table test as select * from user_objects;±íÒÑ´´½¨¡£SQL> create index IX_TEST_FH on test(object_id,object_name) ;Ë÷ÒýÒÑ´´½¨¡£SQL> select object_name from test where ...
复制链接

扫一扫