关于索引的问题
老师：比如有一个组合索引emp(id,name)如果我select * fromempwhere name='kk' and id=1111 就是将前导列放到后面。这样会影响索引的性能吗?
不会的，CBO下测试
SQL> desc object;
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 idx_idname on object(owner,object_name);
Index created.
SQL> exec dbms_stats.gather_index_stats('SCOTT','IDX_IDNAME');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SCOTT','OBJECT');
PL/SQL procedure successfully completed.
SQL> explain plan for select * from object where owner='SCOTT' and object_name='EMP';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1865794174
------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 | 2 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| OBJECT | 1 | 84 | 2 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN | IDX_IDNAME | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OWNER"='SCOTT' AND "OBJECT_NAME"='EMP')
14 rows selected.
SQL> explain plan for select * from object where object_name='DEPT' and owner='SCOTT';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1865794174
------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 | 2 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| OBJECT | 1 | 84 | 2 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN | IDX_IDNAME | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OWNER"='SCOTT' AND "OBJECT_NAME"='DEPT')
14 rows selected.
RBO下测试
SQL> explain plan for select /*+ rule */ * from object where owner='SCOTT' and object_name='EMP';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1865794174
--------------------------------------------------
| Id| Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |TABLE ACCESS BY INDEX ROWID| OBJECT |
|*2 | INDEX RANGE SCAN | IDX_IDNAME |
--------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OWNER"='SCOTT' AND "OBJECT_NAME"='EMP')
Note
-----
- rule based optimizer used (consider using cbo)
18 rows selected.
SQL> explain plan for select /*+ rule */ * from object where object_name='DEPT' and owner='SCOTT';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1865794174
--------------------------------------------------
| Id| Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |TABLE ACCESS BY INDEX ROWID| OBJECT |
|*2 | INDEX RANGE SCAN | IDX_IDNAME |
--------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OWNER"='SCOTT' AND "OBJECT_NAME"='DEPT')
Note
-----
- rule based optimizer used (consider using cbo)
18 rows selected.
可以使用set autot exp具体执行一下进行比较，不过可能需要先执行
alter system flush buffer_cache;和alter system flush shared_pool;否则测试结果不准确；
期待老师更深层次分析； 呵呵,oracle cbo这点智商还是有的！
页:
[1]