6-3-5. LIMIT MIN/MAX를 위한 인덱스

-- [SQL-6-3-5-a]

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  MAX(t1.ord_no) last_ord_no
FROM    startdbpg.tr_ord_big t1
WHERE   t1.mbr_id = 'M00050';
SQL
복사

-- [SQL-6-3-5-b]: [SQL-6-3-5-a]에 힌트 적용

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  /*+ IndexScan(t1 tr_ord_big_x04) */
        MAX(t1.ord_no) last_ord_no
FROM    startdbpg.tr_ord_big t1
WHERE   t1.mbr_id = 'M00050';
SQL
복사

-- [SQL-6-3-5-c]: [SQL-6-3-5-a]에 MAX 대상 컬럼을 일부러 변형

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  MAX(t1.ord_no::numeric) last_ord_no
FROM    startdbpg.tr_ord_big t1
WHERE   t1.mbr_id = 'M00050';
SQL
복사

-- [SQL-6-3-5-d]

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  t1.ord_no last_ord_no
FROM    startdbpg.tr_ord_big t1
WHERE   t1.mbr_id = 'M00050'
ORDER BY t1.ord_no DESC
LIMIT 1;
SQL
복사

-- [SQL-6-3-5-e]: 인덱스 생성후 [SQL-6-3-5-a] 재실행

CREATE INDEX tr_ord_big_x10 ON startdbpg.tr_ord_big(mbr_id, ord_no);

-- [SQL-6-3-5-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  MAX(t1.ord_no) last_ord_no
FROM    startdbpg.tr_ord_big t1
WHERE   t1.mbr_id = 'M00050';
SQL
복사

-- [SQL-6-3-5-f]

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  MAX(t1.ord_no) last_ord_no
FROM    startdbpg.tr_ord_big t1
WHERE   t1.mbr_id BETWEEN 'M00050' AND 'M00055';
SQL
복사