Home » RDBMS Server » Performance Tuning » FTS even there is an index on table.
FTS even there is an index on table. [message #123562] Mon, 13 June 2005 23:21 Go to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi,

My Question is

[1] In which circumstance full table scan take place even if there is indexing on that table?

From :- sunil
Re: FTS even there is an index on table. [message #123582 is a reply to message #123562] Tue, 14 June 2005 01:05 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
FTS or no FTS is for CBO to decide in case u r using CBO as optimizer or when hint FULL is used in SQL statement.

If CBO gets a plan that is cheaper with use of FTS and other plan which is using index but is costly, then , plan with FTS will be used for data fetch.
Re: FTS even there is an index on table. [message #123651 is a reply to message #123582] Tue, 14 June 2005 04:39 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi,

thanks for your reply ..Smile

From:- sunil
Re: FTS even there is an index on table. [message #123667 is a reply to message #123582] Tue, 14 June 2005 06:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
girish.rohini wrote on Tue, 14 June 2005 08:05

or when hint FULL is used in SQL statement.
Even that is no guarantee that the CBO will do that. It has been discussed some time ago and the conclusion is: hints are called 'hints' for some reason: they do nothing more than push the CBO in a given direction. But it might still decide not to follow your guidance...

MHE
Re: FTS even there is an index on table. [message #123668 is a reply to message #123667] Tue, 14 June 2005 06:31 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
HI Maaher

On link http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#5078

its stated that

The FULL hint explicitly chooses a full table scan for the specified table.

For example:

SELECT /*+ FULL(e) */ employee_id, last_name
FROM employees e
WHERE last_name LIKE :b1;


Oracle performs a full table scan on the employees table to execute this statement, even if there is an index on the last_name column that is made available by the condition in the WHERE clause.

My query is , if this is the case, then when will oracle not use FTS if Full hint is given. This is w.r.t 9i.
Meanwhile, I will be looking for the links as well of the earlier discussion about use of hints.
Re: FTS even there is an index on table. [message #123695 is a reply to message #123668] Tue, 14 June 2005 08:39 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You're right. I still learn every day. I was browsing Asktom and found out this:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:8912905298920

Seems you're right all along!

I rarely use hints myself.
MHE
Previous Topic: HELP ON STATSPACK
Next Topic: RBO is there in oracle 10g?
Goto Forum:
  


Current Time: Wed Sep 30 11:57:59 CDT 2020