0

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.

Bobby
  • 534
  • 3
  • 7
  • 21
  • 2
    Do you even need the index hints? Oracle has a pretty good optimizer. – Gordon Linoff Jul 04 '18 at 13:22
  • 2
    For a query as simple as this one don't add hints. The cost-based optimizer in Oracle will use the best indexes availables on all participating tables. Use hints as a last resource, on complex queries, when they are not behaving as you expect. – The Impaler Jul 04 '18 at 13:25
  • 3
    No index will speed that up unless one of the tables as **substantially** fewer rows than the other. A full table scan on both tables is most probably the most efficient plan. Unrelated, but: you should really stop using the ancient, outdated and fragile implicit joins in the where clause and switch to a modern explicit `JOIN` operator –  Jul 04 '18 at 19:26

2 Answers2

1

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

Moudiz
  • 7,211
  • 22
  • 78
  • 156
  • 1
    "*the result of the select will be ordered because the usage of the index*" is plain wrong. –  Jul 04 '18 at 19:26
  • @a_horse_with_no_name well in the above example , the result of the data when the index is used was ordered. I can show you an image. beside the index when created by default is specified order asc so when it reads from the index list , it reads in order way. it reads the id ordered. [Ascending and Descending Indexes](https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1895) – Moudiz Jul 05 '18 at 06:45
  • 1
    It might happen that the database returns the data in a sorted way. But there is absolutely **no** way you can rely on that order. The **only** (really: the only) way to get a _guaranteed_ sort order is to use `order by`. Any "order" you see without an `order by` is pure coincidence and can not be relied on. –  Jul 05 '18 at 06:47
  • @a_horse_with_no_name Well can you please explain to me then what do they mean in oracle document, what is the usage then in index ther order ?i added the link if you prefer a question i can post it ...In an ascending index, Oracle Database stores data in ascending order. By default, character data is ordered by the binary values contained in each byte of the value, numeric data from smallest to largest number, and date from earliest to latest value. ? – Moudiz Jul 05 '18 at 06:51
  • An index is used to _find_ data in a table. One side effect _can_ be that if an `order by` is also part of the query, the optimizer might use pre-sorted data to optimize that sorting. If the optimizer e.g. decides to use a hash join between those two tables then the result will definitely not be sorted despite the use of an index to find the rows –  Jul 05 '18 at 06:56
  • @a_horse_with_no_name so as understand from you the oder in the index is not always used right ? I am thinking to post this subject as a question – Moudiz Jul 05 '18 at 07:01
  • 1
    That has been asked before [here](https://stackoverflow.com/questions/899514) and [here](https://stackoverflow.com/questions/48297005) and [here](https://stackoverflow.com/questions/17059131) and [here](https://stackoverflow.com/questions/25966929) –  Jul 05 '18 at 07:10
1
SELECT /*+ parallel index(<table_1_name_or_alias>)  index(<table_2_name_or_alias>) */

Usually works

Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
murilo
  • 11
  • 1