2-1-5. 힌트의 기본 사용법

-- [SQL-2-1-5-1-a]

EXPLAIN
SELECT  /*+ SeqScan(t1) */
        t1.* 
FROM    startdbpg.tr_ord t1
WHERE t1.ord_no = 1;
SQL
복사

-- [SQL-2-1-5-1-b]

EXPLAIN
SELECT  /* SeqScan(t1) */
        t1.* 
FROM    startdbpg.tr_ord t1 
WHERE   t1.ord_no = 1;

EXPLAIN
SELECT  /* + SeqScan(t1) */
        t1.* 
FROM    startdbpg.tr_ord t1
WHERE   t1.ord_no = 1;
SQL
복사

-- [SQL-2-1-5-1-c]

EXPLAIN
SELECT  /*+ SQLID:test.sample.sql01 SeqScan(t1) */
        t1.* 
FROM    startdbpg.tr_ord t1
WHERE   t1.ord_no = 1;
SQL
복사

-- [SQL-2-1-5-2-a]

SHOW pg_hint_plan.hints_anywhere;
SQL
복사

-- [SQL-2-1-5-2-b]

EXPLAIN
SELECT  /*+ SeqScan(t1) */
        -- SQL ID: test.sample.sql01
        t1.* 
FROM    startdbpg.tr_ord t1
WHERE   t1.ord_no = 1;

EXPLAIN
/*+ SeqScan(t1) */
-- SQL ID: test.sample.sql01
SELECT  t1.* 
FROM    startdbpg.tr_ord t1 
WHERE   t1.ord_no = 1;
SQL
복사

-- [SQL-2-1-5-2-c] hints_anywhere가 off일 때 힌트가 동작하지 않는 예

-- SQL ID: test.sample.sql01
EXPLAIN
SELECT  /*+ SeqScan(t1) */
        t1.* 
FROM    startdbpg.tr_ord t1 
WHERE   t1.ord_no = 1;


EXPLAIN
-- SQL ID: test.sample.sql01
SELECT  /*+ SeqScan(t1) */
        t1.* 
FROM    startdbpg.tr_ord t1
WHERE   t1.ord_no = 1;

EXPLAIN
-- SQL ID: test.sample.sql01
/*+ SeqScan(t1) */
SELECT  t1.* 
FROM    startdbpg.tr_ord t1
WHERE   t1.ord_no = 1;

EXPLAIN
-- SQL ID: test.sample.sql01
SELECT  t1.* 
        /*+ SeqScan(t1) */
FROM    startdbpg.tr_ord t1
WHERE   t1.ord_no = 1;
SQL
복사

-- [SQL-2-1-5-2-d]

EXPLAIN
SELECT  /*+ SeqScan(t1) */
        t1.* 
FROM    startdbpg.tr_ord t1 
WHERE   t1.ord_no = 1;

-- EXPLAIN
SELECT  /*+ SeqScan(t1) */
        t1.* 
FROM    startdbpg.tr_ord t1 
WHERE   t1.ord_no = 1;
SQL
복사

-- [SQL-2-1-5-3-a]

EXPLAIN
SELECT  /*+ SeqScan(t1) */ -- > 소문자
        t1.*
FROM    startdbpg.tr_ord t1 -- > 소문자
WHERE   t1.ord_no = 1;

EXPLAIN
SELECT  /*+ SeqScan(t1) */ -- > 소문자
        t1.*
FROM    startdbpg.tr_ord T1 -- > 대문자
WHERE   t1.ord_no = 1;
SQL
복사

-- [SQL-2-1-5-3-b]

EXPLAIN
SELECT  /*+ SeqScan(T1) */ -- > 대문자
        t1.* 
FROM    startdbpg.tr_ord T1 -- > 대문자
WHERE   t1.ord_no = 1;

EXPLAIN
SELECT  /*+ SeqScan(T1) */ -- > 대문자
        t1.* 
FROM    startdbpg.tr_ord t1  -- > 소문자
WHERE   t1.ord_no = 1;
SQL
복사

-- [SQL-2-1-5-4-a]

EXPLAIN 
SELECT  /*+ SeqScan(t1) */
        t1.* ,t2.*
FROM    startdbpg.ms_mbr t1 -- > 별칭 중복
        INNER JOIN (
            SELECT  t1.mbr_id
                    ,SUM(t1.ord_amt) ord_amt_sum
            FROM    startdbpg.tr_ord t1 -- > 별칭 중복
            WHERE   t1.ord_no <= 1000
            GROUP BY t1.mbr_id
        ) t2
        ON (t1.mbr_id = t2.mbr_id)
WHERE   t1.mbr_id IN ('M00001','M00002');
SQL
복사

-- [SQL-2-1-5-5-a]

EXPLAIN
SELECT  /*+ IndexScan(t1 tr_ord_pk) */
        t1.* 
FROM    startdbpg.tr_ord  t1 
WHERE   t1.ord_no BETWEEN 1 AND 700000;
SQL
복사

-- [SQL-2-1-5-5-b]

EXPLAIN
SELECT  /*+ IndexScan(t1) */
        t1.* 
FROM    startdbpg.tr_ord  t1 
WHERE   t1.ord_no BETWEEN 1 AND 700000;
SQL
복사