Friday, January 06, 2006

12 Selects of christmas (the fourth instalment)

9. Unnesting a nested table

Okay, first I'll build a nested table using 10G's COLLECT operator.
Then I'll unnest it again..
A pointless exercise really, but then I haven't actually found a point to nested tables in the database anyway.
select m.table_name, c.column_value from
(select table_name,
cast ((select collect(column_name)
from user_tab_columns u
where u.table_name = t.table_name) as sys.dbms_debug_VC2coll) cols
from user_tables t) m, table(cols) c


10. ROLLUP
select data_type, nullable, count(*)
from user_tab_columns
group by rollup (data_type, nullable)

As well as the counts for the individual data_type/nullable entries, it also gives subtotals for each data type, plus a grand total

11. CUBE
select data_type, nullable, count(*)
from user_tab_columns
group by cube (data_type, nullable)

In addition to the ROLLUP results, this will also gives subtotals for nullable.

No comments: