Home » SQL & PL/SQL » SQL & PL/SQL » Index (Oracle 9i)
Index [message #328860] Mon, 23 June 2008 02:16 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Dear All,
Am having emp table and dept table which contains emp_id as common column name and both of the tables contains btree index on emp_id but the below query goes for full table scan.Can you please explain why and how to write a query that will use index.
Kindly suggets me.

SELECT * FROM EMP_MAST a , EMP_DEPT b WHERE a.emp_code=b.emp_code
Re: Index [message #328861 is a reply to message #328860] Mon, 23 June 2008 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
emp_id or emp_code?
Weird that dept has emp_id column.

The reason is that Oracle estimates it is faster to use FTS.

Regards
Michel
Re: Index [message #328872 is a reply to message #328860] Mon, 23 June 2008 03:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The main reason that it will go for a full table scan is that there are no conditions in your where clause that restrict the rows retrieved from each table.
Previous Topic: invalid number
Next Topic: MERGE...USING 'nothing' INSERT...UPDATE
Goto Forum:
  


Current Time: Sun Nov 03 08:13:59 CST 2024