DBA_OBJECTSビューでOWNER, OBJECT_TYPE毎の総数一覧を出力したい

Question

題名のとおりなのですが、
以下のようなSQLの練習の一環として以下のようなSQLを作成しました。
（SQL初心者なのでかなりへたくそなSQLですがご了承下さい）

SELECT Z.OWNER, A.INDEX_CNT, B.TABLE_CNT, C.VIEW_CNT FROM
(SELECT OWNER FROM DBA_OBJECTS GROUP BY OWNER) Z
,(SELECT OWNER, OBJECT_TYPE, COUNT() "INDEX_CNT" FROM dba_objects
WHERE OBJECT_TYPE = 'INDEX'
GROUP BY OWNER, OBJECT_TYPE) A
,(SELECT OWNER, OBJECT_TYPE, COUNT() "TABLE_CNT" FROM dba_objects
WHERE OBJECT_TYPE = 'TABLE'
GROUP BY OWNER, OBJECT_TYPE) B
,(SELECT OWNER, OBJECT_TYPE, COUNT(*) "VIEW_CNT" FROM dba_objects
WHERE OBJECT_TYPE = 'VIEW'
GROUP BY OWNER, OBJECT_TYPE) C
WHERE Z.OWNER = A.OWNER(+)
AND Z.OWNER = B.OWNER(+)
AND Z.OWNER = C.OWNER(+)

このSQLで各OWNERのOBJECT_TYPEが、TABLE、VIEW、INDEXの総数が出力されます。

私が実現したいのは、「各OWNERの各OBJECT_TYPE毎の総数一覧」を出力したいです。
上記のSQLで結合を繰り返せば可能ですが、もう少しスマートに実現できないでしょうか。

よろしくお願い致します。

---

すいません。
説明が足りませんでした。
以下のようにOBJECT_TYPEをカラムとしたいです。
(一番上部がヘッダーです）

OWENER table index view ・・・・・・・・・・
OWNER1 20 2 3 ・・・・・・・・・・
OWNER2 10 2 3 ・・・・・・・・・・
OWNER3 20 6 3 ・・・・・・・・・・
OWNER4 20 2 3 ・・・・・・・・・・
OWNER5 40 4 3 ・・・・・・・・・・
OWNER6 50 2 3 ・・・・・・・・・・
OWNER7 70 5 3 ・・・・・・・・・・
OWNER8 10 2 3 ・・・・・・・・・・

Comments

[SVC34]

オブジェクトごとの数のカラムを横に持たせたいということでしょうか

Answer 1

値をカラム名として横に並べるのは非リレーショナルな操作でSQLでは扱いにくいため、あまりお勧めしません。
Orlofskyさん回答のようにSQLでは単純にGROUP BYで集計するにとどめ、必要に応じてプログラミング言語側で編集した方がよいかと。

どうしてもSQLで処理する必要があるならば以下を参考にしてみてください。

• OracleのPIVOT機能を使った書き方

SELECT * FROM (
    SELECT owner, object_type
    FROM dba_objects
)
PIVOT (
    COUNT(object_type) AS cnt
    FOR object_type IN (
        'TABLE'
        ,'INDEX'
        ,'VIEW'
    )
)
ORDER BY owner
;

• 標準SQLでの書き方

SELECT
    owner
    ,COUNT(CASE WHEN object_type = 'TABLE' THEN 1 ELSE NULL END) AS table_cnt
    ,COUNT(CASE WHEN object_type = 'INDEX' THEN 1 ELSE NULL END) AS index_cnt
    ,COUNT(CASE WHEN object_type = 'VIEW' THEN 1 ELSE NULL END) AS view_cnt
FROM dba_objects
GROUP BY owner
ORDER BY owner
;

Comments

[Orlofsky]

その通り。使うOBJECT_TYPEが増えるたびにプログラムの修正が必要になります。

[kawase18]

ご回答ありがとうございます。
大変勉強になりました。

Answer 2

SELECT
    OB.OWNER
  , OB.OBJECT_TYPE
  , COUNT(*) AS COUNTS
FROM DBA_OBJECTS OB
GROUP BY
    OB.OWNER
  , OB.OBJECT_TYPE
ORDER BY
    OB.OWNER
  , OB.OBJECT_TYPE ;

ってところでしょうか？

GROUP BY を GROUP BY CUBE に変えると全OBJECT_TYPE毎の合計も取得できます。

COLUMN OWNER        FORMAT A30
COLUMN OBJECT_TYPE  FORMAT A21
COLUMN COUNTS       FORMAT 999,990
SELECT
    NVL(OB.OWNER      , 'OWNER TOTAL')       AS OWNER
  , NVL(OB.OBJECT_TYPE, 'OBJECT_TYPE TOTAL') AS OBJECT_TYPE
  , COUNT(*) AS COUNTS
FROM DBA_OBJECTS OB
GROUP BY CUBE
    (
    OB.OWNER
  , OB.OBJECT_TYPE
    )
ORDER BY
    OB.OWNER
  , OB.OBJECT_TYPE ;

頑張ってください。