1. 10건 이상 조회되었을 때는요?
  - 모두 조회 vs 10건만 조회
2. 혹시, 조회시 정렬 기준이 따로 있나요?
  - 있다 vs 없다

본문이랑 댓글이랑 내용이 일치하지 않는 것 같은데..

본문은 전체 데이터 기준 10건인데

댓글은 코드별로 10건이네요. 본문을 댓글 기준대로 하면 총 30 row를 출력해야 해서 27개의 null인 row를 생성해야할 것 같은데 어느게 맞는 건가요?

WITH t AS
(
SELECT 1 a, 'A' b FROM dual
UNION ALL SELECT 1, 'B' FROM dual
UNION ALL SELECT 1, 'C' FROM dual
UNION ALL SELECT 2, 'A' FROM dual
UNION ALL SELECT 2, 'B' FROM dual
UNION ALL SELECT 2, 'C' FROM dual
UNION ALL SELECT 2, 'D' FROM dual
UNION ALL SELECT 2, 'E' FROM dual
UNION ALL SELECT 2, 'F' FROM dual
UNION ALL SELECT 2, 'G' FROM dual
UNION ALL SELECT 2, 'H' FROM dual
UNION ALL SELECT 2, 'I' FROM dual
UNION ALL SELECT 2, 'J' FROM dual
UNION ALL SELECT 2, 'K' FROM dual
)
-- 1. 10 건 집합을 생성하여 파티션 아우터 조인
SELECT b.a, b.b
  FROM (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 10) a
  LEFT OUTER JOIN
       (SELECT ROWNUM rn, a, b FROM t WHERE a = :v_a) b
  PARTITION BY (CEIL(rn/10))
    ON lv = MOD(rn-1, 10)+1
 ORDER BY rn
;
-- 2. Model 절 활용 --
SELECT a, b
  FROM (SELECT ROWNUM rn, a, b FROM t WHERE a = :v_a)
 MODEL
 PARTITION BY (CEIL(rn/10) x)
 DIMENSION BY (MOD(rn-1, 10)+1 y)
 MEASURES (a, b)
 RULES (b[FOR y FROM 1 TO 10 INCREMENT 1] = b[CV()])
 ORDER BY x, y
;
-- 3. 부족한 만큼만 Union 붙이기
SELECT a, b
  FROM t
 WHERE a = :v_a
 UNION ALL
SELECT null, null
  FROM (SELECT 10 - (MOD(COUNT(*)-1, 10)+1) cnt FROM t WHERE a = :v_a)
 CONNECT BY LEVEL <= cnt
;

저의 미천한 이해력이 또 혼돈을 일으켰는데 마농님이 이해하신 게 아마도 맞겠죠?

전체 데이터 row 기준으로 10개 단위로 null값 생성..

WITH T AS
(
SELECT 'A' COL FROM DUAL UNION ALL
SELECT 'B' COL FROM DUAL UNION ALL
--SELECT 'D' COL FROM DUAL UNION ALL
--SELECT 'E' COL FROM DUAL UNION ALL
--SELECT 'F' COL FROM DUAL UNION ALL
--SELECT 'G' COL FROM DUAL UNION ALL
--SELECT 'H' COL FROM DUAL UNION ALL
--SELECT 'I' COL FROM DUAL UNION ALL
--SELECT 'J' COL FROM DUAL UNION ALL
--SELECT 'K' COL FROM DUAL UNION ALL
--SELECT 'L' COL FROM DUAL UNION ALL
--SELECT 'M' COL FROM DUAL UNION ALL
--SELECT 'N' COL FROM DUAL UNION ALL
SELECT 'C' COL FROM DUAL
)
SELECT COL
  FROM (SELECT COL, COUNT(COL) OVER () TOTCNT
          FROM (SELECT COL FROM T
                UNION ALL
                SELECT NULL FROM DUAL CONNECT BY LEVEL <= 10
               )
       )
 WHERE ROWNUM <= DECODE(MOD(TOTCNT,10), 0, TOTCNT, TOTCNT + 10 - MOD(TOTCNT,10) )

WITH TT AS 
(
SELECT EMPNO
FROM EMP
)

SELECT EMPNO FROM TT

UNION ALL
         
SELECT NULL
FROM DUAL
CONNECT BY LEVEL <= 10-(SELECT Mod(Count(*)-1,10)+1 FROM TT)