I was doing a code review of a view that would be going into production. The view’s SELECT statement was defined as:
SELECT status.STATUS_ID, status.ITEM_ID, status.REQUEST_FLAG, status.ITEM_CODE, status.PLAN_CODE, status.STATUS_CODE
FROM
(
SELECT STATUS_ID STATUS_ID,
ITEM_ID ITEM_ID,
REQUEST_FLAG REQUEST_FLAG,
ITEM_CODE ITEM_CODE,
PLAN_CODE PLAN_CODE,
STATUS_CODE_1 STATUS_CODE
FROM SCHEMA1.STATUS_TABLE
UNION ALL
SELECT STATUS_ID STATUS_ID,
ITEM_ID ITEM_ID,
REQUEST_FLAG REQUEST_FLAG,
ITEM_CODE ITEM_CODE,
PLAN_CODE PLAN_CODE,
STATUS_CODE_2 STATUS_CODE
FROM SCHEMA1.STATUS_TABLE
UNION ALL
SELECT STATUS_ID STATUS_ID,
ITEM_ID ITEM_ID,
REQUEST_FLAG REQUEST_FLAG,
ITEM_CODE ITEM_CODE,
PLAN_CODE PLAN_CODE,
STATUS_CODE_3 STATUS_CODE
FROM SCHEMA1.STATUS_TABLE
UNION ALL
SELECT STATUS_ID STATUS_ID,
ITEM_ID ITEM_ID,
REQUEST_FLAG REQUEST_FLAG,
ITEM_CODE ITEM_CODE,
PLAN_CODE PLAN_CODE,
STATUS_CODE_4 STATUS_CODE
FROM SCHEMA1.STATUS_TABLE
However, it should be noted that current regulations permit a variation of approximately 20% either way in the blood stream. cipla levitra Early on, the regulars gathered to watch “the kid” bang the balls around. sildenafil pfizer Healthier AlternativeA healthier alternative to cure enlarged prostate is Ayurveda ,which had diagnosed the BPH condition 5000 years ago and prescribed herbs which were effective in curing not only the disease but who would not have the deep pockets or the type of stimulation http://regencygrandenursing.com/long-term-care/respite-care cialis order levitra you’re after. In buy viagra without rx case the user feels uncomfortable side effects, he should see a doctor and get relief from the side effects. UNION ALL
SELECT STATUS_ID STATUS_ID,
ITEM_ID ITEM_ID,
REQUEST_FLAG REQUEST_FLAG,
ITEM_CODE ITEM_CODE,
PLAN_CODE PLAN_CODE,
STATUS_CODE_5 STATUS_CODE
FROM SCHEMA1.STATUS_TABLE
) status;
As you can see, the view is doing a UNION ALL of the same table. Worse yet, the table is accessed via a Full Table Scan (FTS). We cannot get around the FTS, but we can perform this FTS only once instead of 5 times as is written in the SELECT statement! In this view, you can see where I want to stack STATUS_CODE_1, STATUS_CODE_2, … , STATUS_CODE_5 on top of each other. In other words, I want to turn those five columns into five rows. To do this, and to reduce the number of FTS operations from 5 down to 1, I used a nifty trick as can be seen below:
SELECT STATUS_ID STATUS_ID,
POLICY_ID POLICY_ID,
REQUEST_FLAG REQUEST_FLAG,
ITEM_CODE ITEM_CODE,
PLAN_CODE PLAN_CODE,
CASE WHEN row_type.rowno=1 then ‘CODE1’
WHEN row_type.rowno=2 THEN ‘CODE2’
WHEN row_type.rowno=3 THEN ‘CODE3’
WHEN row_type.rowno=4 THEN ‘CODE4’
WHEN row_type.rowno=5 THEN ‘CODE5’
END code_name,
CASE WHEN row_type.rowno=1 THEN STATUS_CODE_1
WHEN row_type.rowno=2 THEN STATUS_CODE_2
WHEN row_type.rowno=3 THEN STATUS_CODE_3
WHEN row_type.rowno=4 THEN STATUS_CODE_4
WHEN row_type.rowno=5 THEN STATUS_CODE_5
END code_val
FROM SCHEMA1.STATUS_TABLE,
(SELECT rownum rowno FROM dual
CONNECT BY LEVEL <=5) row_type;
In the SQL above, we introduced a subquery in the FROM clause which does nothing more than generate the numbers 1 through 5. I need numbers 1 through 5 since I will be transforming 5 columns to row. Now that I have this number being calculated, I can use it in the CASE statements. The first CASE statement says that if this is row 1, return “CODE1” to indicate which column returned this row’s value. The second CASE statement says that if this is row 1, return the value from the first status code column. And so on for each of the five columns I want to return as rows.
By resorting to this little trick, we reduce the number of FTS operations on the same table from five to one. The Explain Plan cost reduced from 2066 to 415 in my specific example.
