Home » SQL & PL/SQL » SQL & PL/SQL » Table Access Full (Oracle 9i)
Table Access Full [message #631356] Tue, 13 January 2015 01:07 Go to next message
fairdaus
Messages: 14
Registered: May 2009
Location: KL
Junior Member
Hi All

I have one query show table access full when do explain plan table. I already check have index for the table.
my question why the query not use the index in oracle 9i.

explain plan for 
  2  SELECT DOC_TYPE_CODE,DOC_NUMBER   
    FROM BL_RECEIPT_REFUND_DTL  
    WHERE BANK_IN_BATCH_DOC_TYPE = :b1  
    AND BANK_IN_BATCH_DOC_NO = :b2  
    AND BANK_IN_BATCH_DOC_DATE = :b3  
    AND BANK_IN_BATCH_DOC_NO IS NOT NULL   
    AND BANK_IN_DOC_NO IS NULL  
    AND RECPT_REFUND_IND = 'R'  
    AND NVL(CONSOLIDATED_RECEIPT_YN,'N') = 'N' 
    FOR 
    UPDATE OF BANK_IN_DOC_NO NOWAIT  3    4    5    6    7    8    9   10   11   12  ;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation            |  Name                  | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                        |     1 |    48 |  2698 |
|   1 |  FOR UPDATE          |                        |       |       |       |
|   2 |   TABLE ACCESS FULL  | BL_RECEIPT_REFUND_DTL  |     1 |    48 |  2698 |
-------------------------------------------------------------------------------


Thank you


Edited by Lalit : Added code tags to preserve formatting

[Updated on: Tue, 13 January 2015 01:18] by Moderator

Report message to a moderator

Re: Table Access Full [message #631357 is a reply to message #631356] Tue, 13 January 2015 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Quote:
my question why the query not use the index in oracle 9i.


Given what you have posted: because there is no index... or maybe because there is only 1 row in the table.

[Updated on: Tue, 13 January 2015 01:16]

Report message to a moderator

Re: Table Access Full [message #631375 is a reply to message #631357] Tue, 13 January 2015 03:25 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
It'd help if you told us what columns are indexed, but I imagine it's as Michel said - if there's only a few rows oracle won't bother with an index - if it has to go to the table to check other columns then there's no benefit to using the index for small amounts of data.
Re: Table Access Full [message #631395 is a reply to message #631356] Tue, 13 January 2015 07:55 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
fairdaus wrote on Mon, 12 January 2015 23:07
Hi All

I have one query show table access full when do explain plan table. I already check have index for the table.
my question why the query not use the index in oracle 9i.

explain plan for 
  2  SELECT DOC_TYPE_CODE,DOC_NUMBER   
    FROM BL_RECEIPT_REFUND_DTL  
    WHERE BANK_IN_BATCH_DOC_TYPE = :b1  
    AND BANK_IN_BATCH_DOC_NO = :b2  
    AND BANK_IN_BATCH_DOC_DATE = :b3  
    AND BANK_IN_BATCH_DOC_NO IS NOT NULL   
    AND BANK_IN_DOC_NO IS NULL  
    AND RECPT_REFUND_IND = 'R'  
    AND NVL(CONSOLIDATED_RECEIPT_YN,'N') = 'N' 
    FOR 
    UPDATE OF BANK_IN_DOC_NO NOWAIT  3    4    5    6    7    8    9   10   11   12  ;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation            |  Name                  | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                        |     1 |    48 |  2698 |
|   1 |  FOR UPDATE          |                        |       |       |       |
|   2 |   TABLE ACCESS FULL  | BL_RECEIPT_REFUND_DTL  |     1 |    48 |  2698 |
-------------------------------------------------------------------------------


Thank you


Edited by Lalit : Added code tags to preserve formatting


Why would Oracle choose to use INDEX to get 1 row of data; when FTS is more efficient?
If you are dissatisfied with how Oracle behaves, your only resource is to submit Service Request to My Oracle Support.
Previous Topic: Rollback in DBMS_PARALLEL_EXECUTE
Next Topic: rownum query
Goto Forum:
  


Current Time: Fri Apr 19 00:47:35 CDT 2024