Table Access Full [message #631356] |
Tue, 13 January 2015 01:07 |
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 #631375 is a reply to message #631357] |
Tue, 13 January 2015 03:25 |
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 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
fairdaus wrote on Mon, 12 January 2015 23:07Hi 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.
|
|
|