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?
后面这句会不会使用到复合索引?
在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?
后面这句会不会使用到复合索引?
解决方案 »
- 帮忙看个视图怎么写
- Oracel数据库采用双机热备后出现
- 如何修改storage中的参数值
- 请问oracle中的策略函数是否有执行最大行数的限制?
- 求教一个oracle的查询问题
- 如何将oracle9i的库导到mysql,谢谢!
- 求教dba_data_files,dba_free_space中的问题
- 请教进制数转换的函数
- 求助:请问哪里有oracle8.05中文版或oracle8.06中文版下载,谢谢
- powerdesigner用reort导出文档,列显示为true/false,如何设置true的显示为“X”
- 新手很着急的提问,Linux中查看oracle的服务器名?
- Oracle7.3能否建临时表??
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(*)
----------
2737SQL> 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 SYSAUXOWNER 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 processedSQL>------------------------------------------------------------------------------
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