OraFAQ Forum: Performance Tuning » FTS even there is an index on table.

Home » RDBMS Server » Performance Tuning » FTS even there is an index on table.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

FTS even there is an index on table. [message #123562]

Mon, 13 June 2005 23:21

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

Report message to a moderator

Re: FTS even there is an index on table. [message #123582 is a reply to message #123562]

Tue, 14 June 2005 01:05

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.

Report message to a moderator

Re: FTS even there is an index on table. [message #123651 is a reply to message #123582]

Tue, 14 June 2005 04:39

sunil_v_mishra
Messages: 506
Registered: March 2005

Senior Member

hi,

thanks for your reply .. Smile

From:- sunil

Report message to a moderator

Re: FTS even there is an index on table. [message #123667 is a reply to message #123582]

Tue, 14 June 2005 06:23

Maaher
Messages: 7065
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

Report message to a moderator

Re: FTS even there is an index on table. [message #123668 is a reply to message #123667]

Tue, 14 June 2005 06:31

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.

Report message to a moderator

Re: FTS even there is an index on table. [message #123695 is a reply to message #123668]

Tue, 14 June 2005 08:39

Maaher
Messages: 7065
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

Report message to a moderator

Previous Topic:	HELP ON STATSPACK
Next Topic:	RBO is there in oracle 10g?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 09 06:45:09 CST 2025