Tuesday, December 17, 2013

TABLE ACCESS FULL on NULL column

TABLE ACCESS FULL on NULL column

Issue :

We had a new query performing a join on few tables and users were reporting performance issue.
Upon checking the plan, we found that the query was doing a full table scan on one of the tables.

Here’s the execution plan.
Execution Plan
----------------------------------------------------------
Plan hash value: 2413029803

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                          |     1 |   127 | 36234   (1)| 00:07:15 |       |       |
|   1 |  SORT ORDER BY                          |                          |     1 |   127 | 36234   (1)| 00:07:15 |       |       |
|*  2 |   HASH JOIN OUTER                       |                          |     1 |   127 | 36233   (1)| 00:07:15 |       |       |
|   3 |    NESTED LOOPS                         |                          |       |       |            |          |       |       |
|   4 |     NESTED LOOPS                        |                          |     1 |   106 |    27   (0)| 00:00:01 |       |       |
|   5 |      PARTITION RANGE ALL                |                          |     1 |    97 |    26   (0)| 00:00:01 |     1 |    24 |
|   6 |       PARTITION HASH SINGLE             |                          |     1 |    97 |    26   (0)| 00:00:01 |     2 |     2 |
|   7 |        TABLE ACCESS BY LOCAL INDEX ROWID| SELLER_OFFERING          |     1 |    97 |    26   (0)| 00:00:01 |       |       |
|*  8 |         INDEX RANGE SCAN                | IX1_SELLER_CONFIGURATION |     1 |       |    25   (0)| 00:00:01 |       |       |
|*  9 |      INDEX UNIQUE SCAN                  | PK_SALE_CONFIGURATION    |     1 |       |     0   (0)| 00:00:01 |       |       |
|  10 |     TABLE ACCESS BY INDEX ROWID         | SALE_CONFIGURATION       |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|  11 |    PARTITION RANGE ALL                  |                          |  7152K|   143M| 36184   (1)| 00:07:15 |     1 |    24 |
|  12 |     PARTITION HASH ALL                  |                          |  7152K|   143M| 36184   (1)| 00:07:15 |     1 |     8 |
|  13 |      TABLE ACCESS FULL                  | PURCHASE                 |  7152K|   143M| 36184   (1)| 00:07:15 |     1 |   192 |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("PR"."SELLER_OFFERING_ID"(+)="SO"."SELLER_OFFERING_ID")
   8 - access("SO"."SERVICE_ORDER_ID"=10145663)
   9 - access("SC"."SALE_CONFIGURATION_ID"="SO"."SALE_CONFIGURATION_ID")




We found that there was a newly added column on the PURCHASE table and it is not NOT NULL.
Upon investigating, we came to know that 99% of the existing records were null on this field. The field will be getting records going forward.
There was a btree index on the column but it was not being used by the optimizer.

So, the join was on a column whose majority of the records were NULL as of now and hence the full table scans.


Resolution:

There were some debates on what is the best way to address this. Can we update the field with some default value and then the index will come into play? There were few other suggestions. Well, one workaround worked.

As a temporary solution, I dropped the regular index on the column and created a function based index using NVL function.

The NVL function replaces a NA value or an empty string with a string.
Syntax
NVL (exp , replacement-exp)


SQL> drop index APPL.IX4_PURCHASE;

Index dropped.

SQL> create index APPL.IX4_PURCHASE ON APPL.PURCHASE nvl(SELLER_OFFERING_ID,null);

Index created.


This is the new execution plan and the query executes in sub seconds. The function based index comes into play.
Execution Plan
----------------------------------------------------------
Plan hash value: 3975705781

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                          |     1 |   127 |   129   (1)| 00:00:02 |       |       |
|   1 |  SORT ORDER BY                         |                          |     1 |   127 |   129   (1)| 00:00:02 |       |       |
|   2 |   NESTED LOOPS OUTER                   |                          |     1 |   127 |   128   (0)| 00:00:02 |       |       |
|   3 |    NESTED LOOPS                        |                          |     1 |   106 |    27   (0)| 00:00:01 |       |       |
|   4 |     PARTITION RANGE ALL                |                          |     1 |    97 |    26   (0)| 00:00:01 |     1 |    24 |
|   5 |      PARTITION HASH SINGLE             |                          |     1 |    97 |    26   (0)| 00:00:01 |     2 |     2 |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID| SELLER_OFFERING          |     1 |    97 |    26   (0)| 00:00:01 |       |       |
|*  7 |        INDEX RANGE SCAN                | IX1_SELLER_CONFIGURATION |     1 |       |    25   (0)| 00:00:01 |       |       |
|   8 |     TABLE ACCESS BY INDEX ROWID        | SALE_CONFIGURATION       |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|*  9 |      INDEX UNIQUE SCAN                 | PK_SALE_CONFIGURATION    |     1 |       |     0   (0)| 00:00:01 |       |       |
|  10 |    TABLE ACCESS BY GLOBAL INDEX ROWID  | PURCHASE                 |     1 |    21 |   101   (0)| 00:00:02 | ROWID | ROWID |
|* 11 |     INDEX RANGE SCAN                   | IX4_PURCHASE             |   105 |       |     1   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("SO"."SERVICE_ORDER_ID"=10145663)
   9 - access("SC"."SALE_CONFIGURATION_ID"="SO"."SALE_CONFIGURATION_ID")
  11 - access("PR"."SELLER_OFFERING_ID"(+)="SO"."SELLER_OFFERING_ID")


I’m wondering if there could be noticeable performance issue for dml’s. So far I have not heard of any issues. We may move away from the function based index. But so far, it did the job.

There could be other better solutions and I’d like to hear them all. Thanks.


No comments:

Post a Comment