Home » RDBMS Server » Performance Tuning » How to avoid TableAccessFull (Performance Tuning)
How to avoid TableAccessFull [message #348573] Wed, 17 September 2008 05:24 Go to next message
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!!!
Re: How to avoid TableAccessFull [message #348577 is a reply to message #348573] Wed, 17 September 2008 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
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
Re: How to avoid TableAccessFull [message #348750 is a reply to message #348573] Wed, 17 September 2008 19:00 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Did you gather fresh statistics after you built the index?
Re: How to avoid TableAccessFull [message #349238 is a reply to message #348750] Fri, 19 September 2008 07:36 Go to previous messageGo to next message
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.
Re: How to avoid TableAccessFull [message #349246 is a reply to message #348573] Fri, 19 September 2008 08:26 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
and one more thing....
maybe ID is not number type? Smile
icon14.gif  Re: How to avoid TableAccessFull [message #349303 is a reply to message #348573] Fri, 19 September 2008 15:20 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
maybe ID is not number type?

Very valuable thought to check; - great deduction, Kriptas!
Previous Topic: Find out what query is running for specific session?
Next Topic: Tuning query approach
Goto Forum:
  


Current Time: Thu Dec 08 02:13:15 CST 2016

Total time taken to generate the page: 0.12972 seconds