1) “With” clause can define PL/SQL functions
2) Improved defaults, including Default col to a sequence or “default if (on) null”. Or always use a generated as an identity (with optional sequence def info). Or Metadata-only defaults (default on an added column).
3) Bigger varchar2, nvarchar2, raw -up to 32K. But follows rules like LOB, if over 4K will be out of line. (max_SQL_String_Size init param)
4) TopN and Pagenation queries using the ‘OFFSET’ clause + optional ‘FETCH next N rows’ in SELECT. Eg: SELECT … FROM t ORDER BY y FETCH FIRST 5 ROWS
5) Row pattern matching using the “MATCH_RECOGNIZE” clause. Gonna take a while to get this one.
6) Partitioning improvements including ASYNC Global Index maintenance (includes new jobs to do work ‘later’), cascade truncate & exchange, multi ops in a single DDL, online partition moves (no RDBMS_REDEFINITION), “interval + reference” partitioning.
7) Adaptive execution plans, which sets thresholds and allows execution plans to switch if threshold is exceeded. (Also ‘gather_plan_statistics’ hint.) Shown by ‘Statistics Collector’ steps in trace/tkprof.
8) Enhanced statistics. Dynamic sampling goes to ‘eleven’, turning it persistent. New histograms: hybrid (for more than 254 distinct values, instead of height-balanced) and top. Stats gathered on data loads automatically. (By the way, don’t regather stats if not needed.) Session private statistics for GTTs.
9) UNDO for temporary objects, managed in TEMP, which eliminates REDO on the permanent UNDO. (ALTER SESSION/SYSTEM SET TEMP_UNDO_ENABLED=TRUE/FALSE)
10) Data optimization, or Information Lifecycle Management, which detects block use – hot, medium, dormant – and allows policies in table defintion (new ILM clause) to compress or archive data after time.
11) “transaction Guard’ to preserve commit state, which includes TAF r/w transfer and restart for some types of transactions.
12) pluggable databases! Implications too numerous to list right now. Suffice it to say, huge resource improvements, huge consolidation possibilities. Looking forward to reality.