Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
  ID    NUMBER(10,0) PRIMARY KEY,
  Cond1 VARCHAR2(3) CHECK ( Cond1 IN ( 'Yes', 'No' ) ),
  Cond2 VARCHAR2(3) CHECK ( Cond2 IN ( 'Yes', 'No' ) ),
  Cond3 VARCHAR2(3) CHECK ( Cond3 IN ( 'Yes', 'No' ) ),
  Cond4 VARCHAR2(3) CHECK ( Cond4 IN ( 'Yes', 'No' ) ),
  Cond5 VARCHAR2(3) CHECK ( Cond5 IN ( 'Yes', 'No' ) ),
  Name1 VARCHAR2(10),
  Name2 VARCHAR2(10),
  Name3 VARCHAR2(10),
  Name4 VARCHAR2(10),
  Name5 VARCHAR2(10),
  CHECK ( ( Cond1 = 'Yes' AND Name1 IS NOT NULL ) OR ( Cond1 = 'No' AND ( Name1 IS NULL OR Name1 = 'n/a' ) ) ),
  CHECK ( ( Cond2 = 'Yes' AND Name2 IS NOT NULL ) OR ( Cond2 = 'No' AND ( Name2 IS NULL OR Name2 = 'n/a' ) ) ),
  CHECK ( ( Cond3 = 'Yes' AND Name3 IS NOT NULL ) OR ( Cond3 = 'No' AND ( Name3 IS NULL OR Name3 = 'n/a' ) ) ),
  CHECK ( ( Cond4 = 'Yes' AND Name4 IS NOT NULL ) OR ( Cond4 = 'No' AND ( Name4 IS NULL OR Name4 = 'n/a' ) ) ),
  CHECK ( ( Cond5 = 'Yes' AND Name5 IS NOT NULL ) OR ( Cond5 = 'No' AND ( Name5 IS NULL OR Name5 = 'n/a' ) ) )
);
INSERT INTO mytable ( ID, Cond1, Cond2, Cond3, Cond4, Cond5, Name1, Name2, Name3, Name4, Name5 )
SELECT 1, 'No',  'Yes', 'No',  'No', 'Yes', null,      'Result1', 'n/a',     null, 'Result2' FROM DUAL UNION ALL
SELECT 2, 'Yes', 'No',  'Yes', 'No', 'Yes', 'Result3', 'n/a',     'Result4', null, 'Result5' FROM DUAL;
2 rows affected

EXPLAIN PLAN FOR
select id,  row_number() over(partition by id order by seq) couting, name
from (
    select id, name1 name, 1 seq from mytable where cond1 = 'Yes'
    union all select id, name2, 2 from mytable where cond2 = 'Yes'
    union all select id, name3, 3 from mytable where cond3 = 'Yes'
    union all select id, name4, 4 from mytable where cond4 = 'Yes'
    union all select id, name5, 5 from mytable where cond5 = 'Yes'
) x
order by id, couting
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


PLAN_TABLE_OUTPUT


Plan hash value: 2202698099

 

--------------------------------------------------------------------------------

| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |         |     6 |   138 |    15   (0)| 00:00:01 |

|   1 |  WINDOW SORT         |         |     6 |   138 |    15   (0)| 00:00:01 |

|   2 |   VIEW               |         |     6 |   138 |    15   (0)| 00:00:01 |

|   3 |    UNION-ALL         |         |       |       |            |          |

|*  4 |     TABLE ACCESS FULL| MYTABLE |     1 |    23 |     3   (0)| 00:00:01 |

|*  5 |     TABLE ACCESS FULL| MYTABLE |     1 |    23 |     3   (0)| 00:00:01 |

|*  6 |     TABLE ACCESS FULL| MYTABLE |     1 |    23 |     3   (0)| 00:00:01 |

|*  7 |     TABLE ACCESS FULL| MYTABLE |     1 |    23 |     3   (0)| 00:00:01 |

|*  8 |     TABLE ACCESS FULL| MYTABLE |     2 |    46 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - filter("COND1"='Yes')

   5 - filter("COND2"='Yes')

   6 - filter("COND3"='Yes')

   7 - filter("COND4"='Yes')

   8 - filter("COND5"='Yes')

 

Note

-----

   - dynamic sampling used for this statement (level=2)