Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

29

积分

0

好友

0

主题
1#
发表于 2012-7-9 14:40:10 | 查看: 6162| 回复: 3
RT,请问在11.2.0.3 版本的oracle,where子句中不包含组合索引的前导列的情况下,执行计划会选择走组合索引吗?

以下是测试过程,请帮忙分析下,谢谢!

一:10g版本
10.2.0.1的数据库,rhel5.4 32bit
SQL> create user test  identified by test;
User created.
SQL> grant connect,resource,select_catalog_role to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> desc t1;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                          NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
SQL> create index i_t1_mul1 on t1(object_id,object_type);
Index created.
SQL> set autot traceonly exp
SQL> select * from t1 where object_id < 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1186876071
---------------------------------------------------------------------------------------
--
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     
|
---------------------------------------------------------------------------------------
--
|   0 | SELECT STATEMENT            |           |    98 | 17346 |     4   (0)| 00:00:01
|
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |    98 | 17346 |     4   (0)| 00:00:01
|
|*  2 |   INDEX RANGE SCAN          | I_T1_MUL1 |    98 |       |     2   (0)| 00:00:01
|
---------------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<100)
Note
-----
   - dynamic sampling used for this statement

SQL> select * from t1 where object_type='INDEX';
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   531 | 93987 |   159   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |   531 | 93987 |   159   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='INDEX')
Note
-----
   - dynamic sampling used for this statement

SQL> set autot off
SQL> select count(*) from t1;
  COUNT(*)
----------
     50380
SQL> select object_type,count(*) from t1  group by object_type;
OBJECT_TYPE           COUNT(*)
------------------- ----------
CONSUMER GROUP               5
INDEX PARTITION            276
SEQUENCE                   143
QUEUE                       27
SCHEDULE                     1
TABLE PARTITION            128
RULE                         4
JAVA DATA                  306
PROCEDURE                   85
OPERATOR                    57
LOB PARTITION                1
OBJECT_TYPE           COUNT(*)
------------------- ----------
WINDOW                       2
LOB                        566
PACKAGE                    848
PACKAGE BODY               791
LIBRARY                    150
RULE SET                    19
PROGRAM                     12
TYPE BODY                  173
CONTEXT                      5
JAVA RESOURCE              770
XML SCHEMA                  26
OBJECT_TYPE           COUNT(*)
------------------- ----------
TRIGGER                    171
JOB CLASS                    2
UNDEFINED                    6
DIRECTORY                    9
DIMENSION                    5
MATERIALIZED VIEW            2
TABLE                     1636
INDEX                     1800
SYNONYM                  20026
VIEW                      3671
FUNCTION                   270
OBJECT_TYPE           COUNT(*)
------------------- ----------
WINDOW GROUP                 1
JAVA CLASS               16417
INDEXTYPE                   10
CLUSTER                     10
TYPE                      1926
RESOURCE PLAN                3
EVALUATION CONTEXT          14
JOB                          6
41 rows selected.
SQL> set autot traceonly exp
SQL> select /*+index(i_t1_mul1)*/ * from t1 where object_type='INDEX';
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   531 | 93987 |   159   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |   531 | 93987 |   159   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='INDEX')
Note
-----
   - dynamic sampling used for this statement

二:11g上测试,11.2.0.3 windows enterprise editon 32bit
SQL> create index idx_t1_mul5 on t1(password,signinid);
Index created.
SQL> select * from (select signinid,count(*) from t1 group by signinid) where rownum<10;
SIGNINID                                                       COUNT(*)
------------------------------------------------------------ ----------
000000yu                                                              1
0000410265269                                                         1
00006789                                                              1
00009746                                                              1
000113                                                                1
0001mwm                                                               1
0002081                                                               1
000317                                                                1
00032156688                                                           1
9 rows selected.
SQL> select * from t1 where signinid='000000yu';
Execution Plan
----------------------------------------------------------
Plan hash value: 2246799375
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |  1296 |  3706   (1)|00:00:45 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |     1 |  1296 |  3706   (1)|00:00:45 |
|*  2 |   INDEX SKIP SCAN           | IDX_T1_MUL5 |     1 |       |  3705   (1)|00:00:45 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SIGNINID"='000000yu')
       filter("SIGNINID"='000000yu')
SQL> select * from t1 where createddate < sysdate and signinid='000000yu';
Execution Plan
----------------------------------------------------------
Plan hash value: 2246799375
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |  1296 |  3706   (1)|00:00:45 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1          |     1 |  1296 |  3706   (1)|00:00:45 |
|*  2 |   INDEX SKIP SCAN           | IDX_T1_MUL5 |     1 |       |  3705   (1)|00:00:45 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("CREATEDDATE"<SYSDATE@!)
   2 - access("SIGNINID"='000000yu')
       filter("SIGNINID"='000000yu')

字段类型:
PASSWORD                                           RAW(255)
SIGNINID                                                 VARCHAR2(20 CHAR)

备注:在11g上做测试一,同样执行计划不选择走组合索引!

求真相!
2#
发表于 2012-7-9 15:01:31
请参考这篇文章

CBO为什么不走索引?

http://www.oracledatabase12g.com ... BC%95%EF%BC%9F.html

回复 只看该作者 道具 举报

3#
发表于 2012-7-9 15:37:03
11g:
SQL> exec dbms_stats.gather_table_stats('HR,'T11',CASCADE=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 2');
PL/SQL procedure successfully completed.

SQL>  select * from t11 where object_type='INDEX';
Execution Plan
----------------------------------------------------------
Plan hash value: 3387822805
--------------------------------------------------------------------------------
----------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------
----------
|   0 | SELECT STATEMENT            |            |  1757 |   168K|   294   (0)|
00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T11        |  1757 |   168K|   294   (0)|
00:00:04 |
|*  2 |   INDEX SKIP SCAN           | I_T11_MUL1 |  1757 |       |   265   (0)|
00:00:04 |
--------------------------------------------------------------------------------
----------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='INDEX')
       filter("OBJECT_TYPE"='INDEX')

10g:
SQL> exec dbms_stats.gather_table_stats('TEST','T1',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 2')
PL/SQL procedure successfully completed.
SQL> set autot traceonly exp

SQL> select * from t1 where object_type='INDEX';
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 12595 |  1143K|   160   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   | 12595 |  1143K|   160   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='INDEX')

根据刘总指示: 收集完直方图信息后执行计划正常,感谢指导!
看来11g在这方面确实比10g先进了许多!

[ 本帖最后由 ylw6006 于 2012-7-9 15:40 编辑 ]

回复 只看该作者 道具 举报

4#
发表于 2012-7-11 16:59:46

回复 3# 的帖子

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create table wangxi1 as select * from dba_objects;

Table created.

SQL> update wangxi1 set status='INVALID' where owner='WANGXI';

5 rows updated.

SQL> commit;

Commit complete.

SQL> create index ind_wangxi1 on wangxi1(status);

Index created.

SQL> exec dbms_stats.gather_table_stats('wangxi','wangxi1',cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 2');

PL/SQL procedure successfully completed.

SQL> set autot traceonly exp
SQL> set linesize 140 pagesize 1400               
SQL> select * from wangxi1 where status='INVALID';

Execution Plan
----------------------------------------------------------
Plan hash value: 3434130650

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    10 |   930 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WANGXI1     |    10 |   930 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_WANGXI1 |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access("STATUS"='INVALID')

SQL>

回复 只看该作者 道具 举报

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

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-25 14:07 , Processed in 0.046110 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569