CUBE : requête analytique

Il existe de nombreuses fonctions analytiques permettant d'obtenir des rapports multi-dimensionnels plus ou moins complexes.

Exemple de Cube :

SET LINES 150
SET PAGES 30
SET FEED OFF
SET ESCAPE \

BREAK ON typ SKIP 1 ON tbs SKIP 0
COL tbs FOR A10 TRUNC

SELECT
    NVL(t.typ,'ALL') AS typ,
    NVL(t.tablespace_name,'________') AS tbs,
    ROUND(SUM(t.bytes)/1024/1024,0) AS siz_mo
FROM (
    SELECT
   (CASE
        WHEN s.segment_type LIKE 'TAB%' THEN 'TAB'
        WHEN s.segment_type LIKE 'IND%' THEN 'IND'
        ELSE 'OTH'
    END
    ) AS typ,
    s.tablespace_name,
    s.bytes
    FROM dba_segments S
    WHERE owner='SYS'
) T
GROUP BY CUBE(t.typ, t.tablespace_name)
ORDER BY 1 DESC, 2 DESC
/

SET FEED ON
SET ESCAPE OFF

 

Résultat :
 

TYP TBS            SIZ_MO
--- ---------- ----------
TAB TOOLS              23
    SYSTEM            402
    SYSAUX            205
    ________          629

OTH UNDOTBS            52
    SYSTEM            218
    SYSAUX             21
    ________          291

IND TOOLS               0
    SYSTEM            370
    SYSAUX            283
    ________          654

ALL UNDOTBS            52
    TOOLS              23
    SYSTEM            990
    SYSAUX            508
    ________         1573