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.
