CODEWITHSUNDEEP
sqloracle-databaseindexing
Bobby
Bobby

Reputation: 584

Can I use indexes from multiple tables in Oracle SQL?

Can I use it like this?

SELECT /*+ index (T1 index1_name, T2 index2_name)*/
FROM T1, T2
WHERE T1.ID = T2.ID

Is this corrent syntax? All examples I found show using indexes from one table, even when few tables are used.

Upvotes: 0

Views: 14378

Answers (2)

murilo
murilo

Reputation: 11

SELECT /*+ parallel index(<table_1_name_or_alias>)  index(<table_2_name_or_alias>) */

Usually works

Upvotes: 1

Moudiz
Moudiz

Reputation: 7377

Can I use indexes from multiple tables in Oracle SQL?

Well ofcourse, check the below example.

drop table t1
/
create table t1
(id number(8) null,
p_name varchar2(100) null)
/
drop table t2
/
create table t2
(id number(8) null,
c_name varchar2(100) null)
/
create index idx_t1_id on t1 (ID)
/
create index idx_t2_id on t2 (ID)
/
insert into t1 (id) 
select rownum from dual
   connect by rownum<=1000000
/
insert into t2 (id) 
select rownum from dual
   connect by rownum<=1000000
/

okay now lets run the query with force index ,and shows it is query plan (useally hint force index is used as last option because oracle optimizer use costing methods (CBO,RBO) to determine the most efficient way of producing the result of the query).

select /*+ index ( a idx_t1_id) index ( b idx_t2_id)*/  * from t1 a 
inner join  t2 b on a.id = b.id

enter image description here

Upvotes: 1

Related Questions