Incorrect Refer to a column alias causing performance degradation

For easy of referencing and better code readability we add aliases to table reference in most of queries.
For correlated queries we add conditions based on correlation columns between any two tables or entities.

Below is one of query shared by the application team causing performance degradations due to incorrect use of column aliases.

Let take a look at it and analysis covered.
Pls Note : All examples are tested in oracle 11g version.

SQL Text :

SELECT DISTINCT
VO.ORDER_NO
FROM
VISTA_ORDER VO
WHERE
1=1
AND VO.ARCHIVE_STATUS = 'L'
AND COMMON_STATUS_POINT NOT IN ('200', '205')
AND VO.ORDER_NO IN
(
SELECT
VO.ORDER_NO
FROM
VISTA_ORDER_SHIPPING VOS
WHERE
VOS.SBLI_NO IN ('123222', '120578')
)
ORDER BY
VO.ORDER_NO ASC;

Observations.
1. As a first step of analysis, had checked the underlying plan for SQL.

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88M (1)|293:53:56 |
| 1 | SORT ORDER BY | | 1 | 88M (1)|293:53:56 |
|* 2 | FILTER | | | | |
| 3 | PARTITION LIST SINGLE| | 723K| 10322 (1)| 00:02:04 |
|* 4 | TABLE ACCESS FULL | VISTA_ORDER | 723K| 10322 (1)| 00:02:04 |
|* 5 | FILTER | | | | |
| 6 | PARTITION LIST ALL | | 3 | 123 (0)| 00:00:02 |
|* 7 | TABLE ACCESS FULL | VISTA_ORDER_SHIPPING | 3 | 123 (0)| 00:00:02 |
----------------------------------------------------------------------------------------

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

2 - filter( EXISTS (SELECT 0 FROM "VISTA_ORDER_SHIPPING" "VOS" WHERE :B1=:B2 AND ("VOS"."SBLI_NO"=120578 OR
"VOS"."SBLI_NO"=123222)))
4 - filter("COMMON_STATUS_POINT"205 AND "COMMON_STATUS_POINT"200)
5 - filter(:B1=:B2)
7 - filter("VOS"."SBLI_NO"=120578 OR "VOS"."SBLI_NO"=123222)

2.
Plan for SQL was quite surprising.
For each record from the parent table (VISTA_ORDER) it was invoking child table with FULL Scan.

3.
2 - filter( EXISTS (SELECT 0 FROM "VISTA_ORDER_SHIPPING" "VOS" WHERE :B1=:B2 AND ("VOS"."SBLI_NO"=120578 OR
"VOS"."SBLI_NO"=123222)))

Query Transformation was taking place and only existence of same records set ((“VOS”.”SBLI_NO”=120578 OR “VOS”.”SBLI_NO”=123222)
was checked on each iteration from the parent.

Such transformation was unusual, it drives me to check Inner part of SQL.

SELECT
VO.ORDER_NO
FROM
VISTA_ORDER_SHIPPING VOS
WHERE
VOS.SBLI_NO IN ('123222', '120578')

Bingo, got the issue.
Incorrect column aliases was referred as highlighted in Select part of inner query.
Causing complete SQL to change functionally, i.e. only checking existence of records in the VISTA_ORDER_SHIPPING table for each records return from VISTA_ORDER.

4.
Below was the Query Transformation done by Optimizer as retrieve from 10053 trace.

SELECT "VO"."ORDER_NO" "ORDER_NO"
FROM "NVTPR"."VISTA_ORDER" "VO"
WHERE "VO"."ARCHIVE_STATUS" ='L'
AND "VO"."COMMON_STATUS_POINT"200
AND "VO"."COMMON_STATUS_POINT"205
AND EXISTS
(SELECT 0
FROM "NVTPR"."VISTA_ORDER_SHIPPING" "VOS"
WHERE "VO"."ORDER_NO"="VO"."ORDER_NO"
AND ("VOS"."SBLI_NO" =123222
OR "VOS"."SBLI_NO" =120578)
)
ORDER BY "VO"."ORDER_NO;

An Incorrect Column alias in inner part cause changes in complete SQL functionality, hence transformation and Bad run.

5.
We have same name column “ORDER_NO” in VISTA_ORDER_SHIPPING table and it was ideally supposed to get referred.

SELECT DISTINCT
VO.ORDER_NO
FROM
VISTA_ORDER VO
WHERE
1=1
AND VO.ARCHIVE_STATUS = 'L'
AND COMMON_STATUS_POINT NOT IN ('200', '205')
AND VO.ORDER_NO IN
(
SELECT
VOS.ORDER_NO
FROM
VISTA_ORDER_SHIPPING VOS
WHERE
VOS.SBLI_NO IN ('123222', '120578')
)
ORDER BY
VO.ORDER_NO ASC;

Plan hash value: 1153039527

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Cost (%CPU)|
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80 | 9858 (1)|
| 1 | SORT ORDER BY | | 80 | 9858 (1)|
| 2 | NESTED LOOPS | | 80 | 9857 (1)|
| 3 | NESTED LOOPS | | 80 | 9857 (1)|
| 4 | PARTITION LIST ALL | | 80 | 9697 (1)|
|* 5 | TABLE ACCESS FULL | VISTA_ORDER_SHIPPING | 80 | 9697 (1)|
|* 6 | INDEX UNIQUE SCAN | ORD_PK | 1 | 1 (0)|
|* 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| VISTA_ORDER | 1 | 2 (0)|
-----------------------------------------------------------------------------------------

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

5 - filter("VOS"."SBLI_NO"=120578 OR "VOS"."SBLI_NO"=123222)
6 - access("VO"."ORDER_NO"="VOS"."ORDER_NO")
7 - filter("COMMON_STATUS_POINT"205 AND "COMMON_STATUS_POINT"200 AND "VO"."ARCHIVE_STATUS"='L')

Wrong column reference in Inner SQL Cause complete changed in query functionality.
Always take a note on the column reference for any correlation defined.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s