CSQL에서 PreparedStatement 사용하여 Query Plan 확인하기

1. Prepared Statement 생성

PREPARE stmt_name FROM 'sql문';

2. Prepared Statement 실행

EXECUTE stmt_name [USING value, value2 ...];

3. Prepared Statement 해제

{DEALLOCATE | DROP} PREPARE stmt_name;

 1.

   csql > PREPARE pstmt FROM 'SELECT 1 + ?';

   csql > EXECUTE pstmt USING 4;

   csql > DROP PREPARE pstmt;

 2.

   csql > PREPARE pstmt FROM 'SELECT col1 + ? FROM tbl WHERE col2 = ?';

   csql > SET @a=3, @b='abc';

   csql > EXECUTE pstmt USING @a, @b;

   csql > DROP PREPARE pstmt;

csql >;trace on

1. BIND 사용

PREPARE pstmt FROM '
SELECT /*+ recompile */
    (
        SELECT
            COUNT(id)
        FROM
            tbla
        WHERE
            id = a.id
    ) col1,
    (
        SELECT
            NVL(MAX(TO_NUMBER(SUBSTR(id, 5))), 0)
        FROM
            tbla
        WHERE
            id LIKE SUBSTR(?, 1, 3) || ''%''
            AND id IS NOT NULL
    ) col2
FROM
    tbla a
LIMIT ?, ?';
EXECUTE pstmt USING 'A123456', 10, 10;

=== <Result of SELECT Command in Line 1> ===

~

2 command(s) successfully processed.

Query Plan:
  TABLE SCAN (tbla)

  rewritten query: (select nvl(max( to_number( substr(tbla.id, 5))), 0) from tbla tbla where tbla.id like  substr( ?:0 , 1, 3)|| cast('%' as varchar))

  INDEX SCAN (tbla.idx1) (key range: tbla.id=a.id, covered: true)

  rewritten query: (select count(tbla.id) from tbla tbla where tbla.id=a.id)

  TABLE SCAN (a)

  rewritten query: select (select count(tbla.id) from tbla tbla where tbla.id=a.id), (select nvl(max( to_number( substr(tbla.id, 5))), 0) from tbla tbla where tbla.id like  su
bstr( ?:0 , 1, 3)|| cast('%' as varchar)) from tbla a where (inst_num()> ?:1 ) and (inst_num()<= ?:1 + ?:2 )

Trace Statistics:
  SELECT (time: 8, fetch: 84, ioread: 0)
    SCAN (table: tbla), (heap time: 0, fetch: 21, ioread: 0, readrows: 21, rows: 21)
    SUBQUERY (uncorrelated)
      SELECT (time: 2, fetch: 13, ioread: 0)
        SCAN (table: tbla), (heap time: 1, fetch: 13, ioread: 0, readrows: 5121, rows: 1024)
    SUBQUERY (correlated)
      SELECT (time: 5, fetch: 50, ioread: 0)
        SCAN (index: tbla.idx1), (btree time: 4, fetch: 40, ioread: 0, readkeys: 10, filteredkeys: 0, rows: 10240, covered: true)

2. 상수 사용

SELECT 
    (
        SELECT
            COUNT(id)
        FROM
            tbla
        WHERE
            id = a.id
    ) col1,
    (
        SELECT
            NVL(MAX(TO_NUMBER(SUBSTR(id, 5))), 0)
        FROM
            tbla
        WHERE
            id LIKE SUBSTR('A123456', 1, 3) || '%'
            AND id IS NOT NULL
    ) col2
FROM
    tbla a
LIMIT 10, 10;

=== <Result of SELECT Command in Line 1> ===

~

1 command(s) successfully processed.

Query Plan:
  INDEX SCAN (tbla.idx1) (key range: (tbla.id>= ?:4  and tbla.id< ?:5 ), covered: true)

  rewritten query: (select nvl(max( to_number( substr(tbla.id, 5))), 0) from tbla tbla where (tbla.id>= ?:4  and tbla.id< ?:5 ))

  INDEX SCAN (tbla.idx1) (key range: tbla.id=a.id, covered: true)

  rewritten query: (select count(tbla.id) from tbla tbla where tbla.id=a.id)

  TABLE SCAN (a)

  rewritten query: select (select count(tbla.id) from tbla tbla where tbla.id=a.id), (select nvl(max( to_number( substr(tbla.id, 5))), 0) from tbla tbla where (tbla.id>= ?:4
and tbla.id< ?:5 )) from tbla a where (inst_num()> ?:0  and inst_num()<= ?:1 )

Trace Statistics:
  SELECT (time: 7, fetch: 76, ioread: 0)
    SCAN (table: tbla), (heap time: 0, fetch: 21, ioread: 0, readrows: 21, rows: 21)
    SUBQUERY (uncorrelated)
      SELECT (time: 1, fetch: 5, ioread: 0)
        SCAN (index: tbla.idx1), (btree time: 0, fetch: 4, ioread: 0, readkeys: 1, filteredkeys: 0, rows: 1024, covered: true)
    SUBQUERY (correlated)
      SELECT (time: 5, fetch: 50, ioread: 0)
        SCAN (index: tbla.idx1), (btree time: 4, fetch: 40, ioread: 0, readkeys: 10, filteredkeys: 0, rows: 10240, covered: true)