ねら~ITエンジニア雑記

やきうのお兄ちゃんが綴るOracle Databaseメインのブログ

Oracle Database 12cR2(12.2)だとTABLEファンクションのTABLE句が省略できるらしいのでDBMS_XPLANで試してみる。

※本エントリで記述されている事項はドキュメント非記載のため、原則非サポートと考えて下さい。
※(2018/12/24追記)マニュアルに載ってました。

Oracle DatabaseデータベースPL/SQL言語リファレンス 18c
12.5.1 テーブル・ファンクションの概要
https://docs.oracle.com/cd/E96517_01/lnpls/plsql-optimization-and-tuning.html#GUID-4E10CBFA-4B6A-4761-8905-83C26C112694
次のように、同等の問合せをTABLE演算子なしで記述できます。
SELECT * FROM table_function_name(parameter_list)

下記ツイートで見かけたOracle Database 12cR2(12.2)の隠し機能彡(゚)(゚)

https://twitter.com/kibeha/status/955557265687359488
Huh? Is this new #OrclDB 12.2 undocumented feature that you can skip TABLE if you just add empty parentheses instead? Or is it just me that didn't know this was possible?
#SQL #PLSQL

12cR2(12.2)だとTABLEファンクションのTABLE句が省略できるらしい?彡(゚)(゚)
TABLEファンクションの代表と云えばDBMS_XPLAN、下記のようTABLE句を付けて使うのがセオリーですが。。。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '9ht3ba3arrzt3'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9ht3ba3arrzt3, child number 0
-------------------------------------
UPDATE KET$_CLIENT_TASKS     SET STATUS = CASE STATUS WHEN 13 THEN 2
ELSE STATUS END,         WINDOW_NAME = NULL,         CURR_JOB_NAME =
NULL,         RETRY_COUNT = RETRY_COUNT+1,         LT_JOB_LOG_ID =
:log,         LT_TERM_CODE = 11,         LT_PRIORITY = TASK_PRIORITY,
      LT_ERROR = :err,         LT_DATE = :end,         LT_DURATION =
:ela,         LT_CPU_TIME = :cpu   WHERE CLIENT_ID = :cid     AND
OPERATION_ID = :oid     AND TARGET_TYPE = :tgt     AND TARGET_NAME =
:tgn

Plan hash value: 3863298075

-----------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                   |       |       |     1 (100)|
|   1 |  UPDATE            | KET$_CLIENT_TASKS |       |       |            |
|*  2 |   INDEX UNIQUE SCAN| KET$_TSK_PK       |     1 |   112 |     0   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLIENT_ID"=:CID AND "OPERATION_ID"=:OID AND
              "TARGET_TYPE"=:TGT AND "TARGET_NAME"=:TGN)


27 rows selected.

SQL>

これを12cR2(12.2)環境でTABLE句無しで実行してみると……彡(゚)(゚)

SQL> SELECT * FROM V$VERSION;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '9ht3ba3arrzt3');

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  9ht3ba3arrzt3, child number 0
-------------------------------------
UPDATE KET$_CLIENT_TASKS     SET STATUS = CASE STATUS WHEN 13 THEN 2
ELSE STATUS END,         WINDOW_NAME = NULL,         CURR_JOB_NAME =
NULL,         RETRY_COUNT = RETRY_COUNT+1,         LT_JOB_LOG_ID =
:log,         LT_TERM_CODE = 11,         LT_PRIORITY = TASK_PRIORITY,
      LT_ERROR = :err,         LT_DATE = :end,         LT_DURATION =
:ela,         LT_CPU_TIME = :cpu   WHERE CLIENT_ID = :cid     AND
OPERATION_ID = :oid     AND TARGET_TYPE = :tgt     AND TARGET_NAME =
:tgn

Plan hash value: 3863298075

-----------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                   |       |       |     1 (100)|
|   1 |  UPDATE            | KET$_CLIENT_TASKS |       |       |            |
|*  2 |   INDEX UNIQUE SCAN| KET$_TSK_PK       |     1 |   112 |     0   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLIENT_ID"=:CID AND "OPERATION_ID"=:OID AND
              "TARGET_TYPE"=:TGT AND "TARGET_NAME"=:TGN)


27 rows selected.

マジやった!TABLE句省略できるやんけ!彡(゚)(゚)