索引问题

Question

SQL> select * from user_ind_columns where table_name='C01D';INDEX_NAME                     TABLE_NAME                     COLUMN_NAME                                                                      COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- ------------- ----------- -------
IND_C01D12                     C01D                           C01D12                                                                                         1            22           0 ASC
IND_C01D_C0109                 C01D                           C01D09                                                                                         1             6           6 ASC
IND_C01D_C0126                 C01D                           C01D26                                                                                         1             6           6 ASC
IND_C01D_C0133                 C01D                           C01D33                                                                                         1             8           8 ASC
IND_C01D_C0165                 C01D                           C01D65                                                                                         1             6           6 ASC
SYS_C003420                    C01D                           C01D00                                                                                         1             2           2 ASC
SYS_C003420                    C01D                           C01D01                                                                                         2             8           8 ASC7 rows selected
怎么回出来两个索引一样的名字呢？  而且查询的时候走的全表
select * from c01d where c01d00='13'
SELECT STATEMENT, GOAL = CHOOSE Cost=2601 Cardinality=52068 Bytes=11090484
 TABLE ACCESS FULL Object owner=FZDC Object name=C01D Cost=2601 Cardinality=52068 Bytes=11090484但是。
select * from c01d where c01d01='13'
SELECT STATEMENT, GOAL = CHOOSE Cost=15 Cardinality=4 Bytes=852
 TABLE ACCESS BY INDEX ROWID Object owner=FZDC Object name=C01D Cost=15 Cardinality=4 Bytes=852
  INDEX SKIP SCAN Object owner=FZDC Object name=SYS_C003420 Cost=13 Cardinality=4 如果两个都用的话就是全表怎么让两列索引都起到作用呢？

Answer 1

C01D00与C01D01列上是不是建的联合索引哟！
并且建立索引时的列的顺序是：
create index on co1d（c01d01,c01d00）;

Answer 2

SQL> create index my_test_ind on  TEST_DISTINCT_TAB(name1,name2);索引已创建。SQL> select index_name,table_name from user_ind_columns where table_name='TEST_DISTINCT_TAB';INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
MY_TEST_IND                    TEST_DISTINCT_TAB
MY_TEST_IND                    TEST_DISTINCT_TAB

Answer 3

SQL> select * from test_distinct_tab where name2='ss';未选定行
执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=1 Bytes=24)
   1    0   TABLE ACCESS (FULL) OF 'TEST_DISTINCT_TAB' (TABLE) (Cost=8
           Card=1 Bytes=24)
SQL> select * from test_distinct_tab where name1='ss';未选定行
执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=0 Card=1 Bytes=24)
   1    0   INDEX (RANGE SCAN) OF 'MY_TEST_IND' (INDEX)1.当你使用联合索引的前导列进行查询的时候是会走索引的；但如果你表中的数据量比较大时，用非前导列
进行查询的时间，oracle会选择基于代价的优化器，自己选择是否走索引（如果表中的数据量小的话，oracle也会走索引--我本地测试过），还是进行全表扫描。

Answer 4

可以强制使用索引：
select /*+index(c01d SYS_C003420)*/  * from c01d where c01d00='13'