OraFAQ Forum: Performance Tuning » How to avoid TableAccessFull

Home » RDBMS Server » Performance Tuning » How to avoid TableAccessFull (Performance Tuning)

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

How to avoid TableAccessFull [message #348573]

Wed, 17 September 2008 05:24

srivishnuhari
Messages: 5
Registered: January 2006
Location: Chennai

Junior Member

Hi,

I am facing the issue with "TABLEACCESSFULL" for the cursor which using only INDEXED Column in Where Clause.

E.g.,

Select empno, ename
from emp
where id = 10

Here ID is INDEXED COLUMN. [Ie. Primary Key]

But its going Full table scan in Explain Plan.

Kindly guide me on this to avoid
Full table scan ie. TABLE ACCESS FULL

Thanks!!!

Report message to a moderator

Re: How to avoid TableAccessFull [message #348577 is a reply to message #348573]

Wed, 17 September 2008 05:33

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Also read How to Identify Performance Problem and Bottleneck and OraFAQ Oracle SQL Tuning Guide.
Then provide the requested and usual information.

Regards
Michel

Report message to a moderator

Re: How to avoid TableAccessFull [message #348750 is a reply to message #348573]

Wed, 17 September 2008 19:00

harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts

Senior Member

Did you gather fresh statistics after you built the index?

Report message to a moderator

Re: How to avoid TableAccessFull [message #349238 is a reply to message #348750]

Fri, 19 September 2008 07:36

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

How many rows are there in the table - if it's small (only a few blocks), then it will probably be quicker to read them all than it would to read the index and then the table.

Report message to a moderator