复合索引怎么用?

[minoboy]

A表有个复合索引,(cdsc_id,cpl_dt)


在select * from A ,B where (a.cdsc_id,a.cpl_dt)=(b.cdsc_id,b.cpl_dt) 能不能这样使用?


还是一定要分开来
select * from a,b where a.cdsc_id=b.cdsc_id and a.cpl_dt=b.cpl_dt?


后面这句会不会使用到复合索引?

[Dave]

select * from A ,B where (a.cdsc_id,a.cpl_dt)=(b.cdsc_id,b.cpl_dt)

没有这种语法。 其实实验是最好的老师，你把SQL 运行一下，就知道行不行了。

在where 中包含复合索引的字段就可以了。

SQL> create table test as select owner,tablespace_name from dba_tables;

表已创建。

SQL> select count(*) from test;

  COUNT(*)
----------
      2737

SQL> create index ind_t on test(owner,tablespace_name);

索引已创建。

SQL> set autot on
SQL> select * from test where owner='EXFSYS' and tablespace_name='SYSAUX';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX
EXFSYS                         SYSAUX

已选择20行。


执行计划
----------------------------------------------------------
Plan hash value: 3131770069

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |    20 |   680 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_T |    20 |   680 |     1   (0)| 00:00:01 |--------------------------------------------------------------------------

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

   1 - access("OWNER"='EXFSYS' AND "TABLESPACE_NAME"='SYSAUX')

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         17  consistent gets
          1  physical reads
          0  redo size
        766  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL>

------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977

[sql_xeppp]

在select * from A ,B where (a.cdsc_id,a.cpl_dt)=(b.cdsc_id,b.cpl_dt) 能不能这样使用?

楼主很有想像力 呵呵

开始跟踪 set autot on

执行sql 后 会显示跟踪信息