How to utilize TABLE ACCESS BY INDEX ROWID

Question

I have a problem with a query that uses a FULL TABLE SCAN.

When this query runs on our UAT env, it uses a TABLE ACCESS BY INDEX ROWID, but in prod it uses FULL TABLE SCAN. UAT runs much better than PROD.

We have the same tables and indexes structure in prod and uat.

I already tried rebuilding and recreating the indexes but the same explain plan is used.

Table and indexes statics were also updated.

Can you help me to make this query use INDEX access instead of FUll table access?

Please see below the explain plan of our prod and uat.

EXPLAIN PLAN PROD 
=====================================================================

SQL> explain plan for
SELECT ASV_ODC_BRANCH.CODE, ASV_ODC_BRANCH.DESCRIPTION, ASV_ODC_BRANCH.BRSTN, DEB.VIEW_DORMANT.ACCTNO AS DORMANT_ACCT,
DEB.VIEW_DORMANT.SHORTNAME AS DORMANT_NAME, DEB.VIEW_DORMANT.OPID_ENTRY, DEB.CUSTOMER.CUSTOMERNO,
DEB.CUSTOMER.TIME_STAMP_ENTRY
FROM ASV_ODC_BRANCH, DEB.VIEW_DORMANT, DEB.CUSTOMER
WHERE trim(ASV_ODC_BRANCH.CODE) = decode(SUBSTR(DEB.VIEW_DORMANT.ACCTNO, 3, 1) || SUBSTR(DEB.VIEW_DORMANT.ACCTNO, 7, 1), ’29’,
SUBSTR(DEB.VIEW_DORMANT.ACCTNO, 4, 3), SUBSTR(DEB.VIEW_DORMANT.ACCTNO, 3, 3)) AND
DEB.VIEW_DORMANT.ACCTNO = DEB.CUSTOMER.CUSTOMERNO AND (DEB.VIEW_DORMANT.ACCTNO = :Xacct)
ORDER BY ASV_ODC_BRANCH.CODE, DORMANT_ACCT;

Explained.


PLAN_TABLE_OUTPUT                                                                                                                                                                     | Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)|
|   0 | SELECT STATEMENT                |                |     3 |   489 |  3601   (2)|
|   1 |  SORT ORDER BY                  |                |     3 |   489 |  3601   (2)|
|   2 |   HASH JOIN                     |                |     3 |   489 |  3600   (2)|
|   3 |    MERGE JOIN CARTESIAN         |                |     1 |    90 |  3595   (2)|
|   4 |     NESTED LOOPS                |                |     1 |    66 |  3592   (2)|
|   5 |      **TABLE ACCESS FULL**      | ACCOUNT        |     1 |    56 |  3590   (2)|
|   6 |      TABLE ACCESS BY INDEX ROWID| EXTENSION1     |     1 |    10 |     2   (0)|
|   7 |       INDEX UNIQUE SCAN         | PKEXT10        |     1 |       |     1   (0)|
|   8 |     BUFFER SORT                 |                |     1 |    24 |  3593   (2)|
|   9 |      TABLE ACCESS BY INDEX ROWID| CUSTOMER       |     1 |    24 |     3   (0)|
|  10 |       INDEX RANGE SCAN          | UXCUST1        |     1 |       |     2   (0)|
|  11 |    TABLE ACCESS FULL            | ASV_ODC_BRANCH |   334 | 24382 |     5   (0)|                                                                                                                                                                                    

**EXPLAIN PLAN UAT**
======================================================================================

SQL> explain plan for
SELECT ASV_ODC_BRANCH.CODE, ASV_ODC_BRANCH.DESCRIPTION, ASV_ODC_BRANCH.BRSTN, DEB.VIEW_DORMANT.ACCTNO AS DORMANT_ACCT,
DEB.VIEW_DORMANT.SHORTNAME AS DORMANT_NAME, DEB.VIEW_DORMANT.OPID_ENTRY, DEB.CUSTOMER.CUSTOMERNO,
DEB.CUSTOMER.TIME_STAMP_ENTRY
FROM ASV_ODC_BRANCH, DEB.VIEW_DORMANT, DEB.CUSTOMER
WHERE trim(ASV_ODC_BRANCH.CODE) = decode(SUBSTR(DEB.VIEW_DORMANT.ACCTNO, 3, 1) || SUBSTR(DEB.VIEW_DORMANT.ACCTNO, 7, 1), ’29’,
SUBSTR(DEB.VIEW_DORMANT.ACCTNO, 4, 3), SUBSTR(DEB.VIEW_DORMANT.ACCTNO, 3, 3)) AND
DEB.VIEW_DORMANT.ACCTNO = DEB.CUSTOMER.CUSTOMERNO AND (DEB.VIEW_DORMANT.ACCTNO = :Xacct)
ORDER BY ASV_ODC_BRANCH.CODE, DORMANT_ACCT;

Explained.

SQL> /

PLAN_TABLE_OUTPUT

| Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)|
|   0 | SELECT STATEMENT                |                |     5 |  5930 |    19  (11)|
|   1 |  SORT ORDER BY                  |                |     5 |  5930 |    19  (11)|
|   2 |   HASH JOIN                     |                |     5 |  5930 |    18   (6)|
|   3 |    MERGE JOIN CARTESIAN         |                |     2 |  2220 |    12   (0)|
|   4 |     NESTED LOOPS                |                |     1 |  1085 |     9   (0)|
|   5 |  **TABLE ACCESS BY INDEX ROWID**| ACCOUNT        |     1 |    57 |     7   (0)|
|   6 |       INDEX SKIP SCAN           | UXACCT2        |     1 |       |     6   (0)|    
|   7 |      TABLE ACCESS BY INDEX ROWID| EXTENSION1     |     1 |  1028 |     2   (0)|
|   8 |       INDEX UNIQUE SCAN         | PKEXT10        |     1 |       |     1   (0)|
|   9 |     BUFFER SORT                 |                |     1 |    25 |    10   (0)|
|  10 |      TABLE ACCESS BY INDEX ROWID| CUSTOMER       |     1 |    25 |     3   (0)|
|  11 |       INDEX RANGE SCAN          | UXCUST1        |     1 |       |     2   (0)|
|  12 |    TABLE ACCESS FULL            | ASV_ODC_BRANCH |   336 | 25536 |     5   (0)|

How one database is using FULL table scan while other not ,that is the question and how can we improve it — Gaurav Soni
– Gaurav Soni, Commented Aug 26, 2014 at 10:02

Srini V · Accepted Answer · 2014-08-26 13:22:45Z

The difference is in

EXPLAIN PLAN PROD

|   5 |      **TABLE ACCESS FULL**      | ACCOUNT        |     1 |    56 |  3590   (2)|

EXPLAIN PLAN UAT

|   5 |  **TABLE ACCESS BY INDEX ROWID**| ACCOUNT        |     1 |    57 |     7   (0)|
|   6 |       INDEX SKIP SCAN           | UXACCT2        |     1 |       |     6   (0)|

How does it work?

Rather than restricting the search path using a predicate from the statement, Skip Scans are initiated by probing the index for distinct values of the prefix column. Each of these distinct values is then used as a starting point for a regular index search. The result is several separate searches of a single index that, when combined, eliminate the affect of the prefix column. Essentially, the index has been searched from the second level down.

The optimizer uses statistics to decide if a skip scan would be more efficient than a full table scan.

Optimizer considers his as an advantage over a FTS because

It reduces the number of indexes needed to support a range of queries. This increases performance by reducing index maintenance and decreases wasted space associated with multiple indexes.
The prefix column should be the most discriminating and the most widely used in queries. These two conditions do not always go hand in hand which makes the decision difficult. In these situations skip scanning reduces the impact of makeing the "wrong" decision.

You can consider the following

Check the optimizer mode across the environments.
Gather stats on all the tables used in the query. For example, if a table has not been analyzed since it was created, and if it has less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high water mark, then the optimizer thinks that the table is small and uses a full table scan. Review the LAST_ANALYZED and BLOCKS columns in the ALL_TABLES table to examine the statistics.
Though your environment is similar and code is same, optimizer is going to check on the fly and choose the best available method. So do your UAT with same data setup. Since it is a UAT (almost a preproduction in most o the companies), it should be the closest to production in terms of size.

+1 for gathering stats. This looks like a boring missing-or-stale-statistics issue, since the execution plans have such low cardinality estimate.

Emmanuel · Accepted Answer · 2013-07-03 07:48:41Z

1

From what I can see, table DEB.VIEW_DORMANT is a view on tables ACCOUNT and EXTENSION1, and you'd like to use index UXACCT2 from the former. I guess a hint inside this request should allow you to do what you want, something like:

SELECT /*+ INDEX(D UXACCT2) */ ASV_ODC_BRANCH.CODE,
...
FROM ASV_ODC_BRANCH, DEB.VIEW_DORMANT D, DEB.CUSTOMER
...

PS: if this is a query you manage (not generated by any high-level software), I suggest you use aliases for your table as I did, that makes queries so much more readable...

answered Jul 3, 2013 at 7:48

Emmanuel

14.2k12 gold badges53 silver badges73 bronze badges

2 Comments

agomez Over a year ago

Hi Emmanuel, Thanks for your response. Unfortunately, I can't edit anything on the script even on objects. This is an outsourced software we have.

Olaf H Over a year ago

Maybe SQL Plan Management will help: docs.oracle.com/cd/E11882_01/server.112/e16638/…

Falco · Accepted Answer · 2014-08-26 12:46:08Z

Can I help you to make it a INDEX-ACCESS instead of a FTS? Probably...

Do you really want that? - Probably NOT

Why is the database behaving differently? Because you operate on different data - As your sample shows, the query returns a different number of rows, so I don't even have to ask if your production and test-data is the same. If you have different data (different amounts or different values in indexed columns) you Database-Stats will be different, your indexes will look different and so the optimizer will come to a different query-plan!

What should you do? Make sure all you indexes are up to date, your partitions are sanely arranged, all you database-stats are up-to-date and no strange tuning-settings are in place ( query-plans, environment settings...) Then the optimizer will in most cases find the best plan - and in many cases a full-table-scan is the FASTER alternative.

But if you measure the times and the optimizer clearly takes the wrong path, although it has accurate table-stats, you should file a bug-report with oracle.

If you have other reasons for wanting the optimizer to do indexed-access:

If you cannot enter an Optimizer-HINT like Emmanuel offered, you can try Profiles or Baselines, which offer nice tuning possibilities. You can write your own statement, with different WHERE-Predicates until you get a plan with index-access and use this as an SQL-Profile and link this profile to the original statement which will then use the same query-plan.

Collectives™ on Stack Overflow

How to utilize TABLE ACCESS BY INDEX ROWID

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related