Re: When does a query use an Index?

On Tue, 23 Sep 1997 10:40:46 +1000, John Smith <jsmith_at_company.com> wrote:

>1. Does a query use an Index when LIKE is used?

Yes, but only if the wildcard is not used in front of the searchpattern. Oracle will perform a range scan if you, for example, use LIKE as follows: select ename from emp where ename like 'BL%' Of course, Oracle cannot use an index if the beginning of the searchpattern is not known.

Also, be aware of the correct use of the where clause. Oracle will not use the index if you use functions around the column as in : select ename from emp where upper(ename) like 'BL%'.

>2. Does a query use an Index when an outer join is performed?

Yes, but in the most simple situation, the base table will have a full table scan, as in:

select	d.deptno
,	e.empno
from	scott.dept	d
,	scott.emp	e
where	d.deptno = e.deptno (+);

If you have a restriction in the where clause for the base table with an index on that specific column, Oracle will of course use an index on the base table:

select	d.deptno
,	e.empno
from	scott.dept	d
,	scott.emp	e
where	d.deptno = e.deptno (+)
and	d.deptno = 10;

According to the tuning book, th following should result in using an index for the outer joined table:

select 	company.name
from	company
,	sales
where	company.company_id = sales.company_id (+)
and	sales.period_id = 3
and	sales.sales_total > 1000;

>3. Does an Index get used when a view is used?

Sure, a view is nothing more or less than a query. If you use a view together with more statements, it's possible to not make correct use of indexes.

>Can anyone answer these please?

Any time...

• Martin Jesterhoudt
• martinj(replace this by @)worldonline.nl

  ---