Explore Oracle Database 11g
This section briefly describes a few of the new features of Oracle Database 11g. A detailed description of the new features is beyond the scope of this guide. For a more comprehensive list, see the Oracle Database New Features Guide 11g Release 1 (11.1).

Automatic Memory Management - With Oracle Database 11g, memory management is further automated with the use of the dynamic parameter, memory_target. You would just be required to specify the total instance memory size and the database will automatically manages the memory distribution between the SGA and the PGA. The view, v$memory_target_advice provides advice on memory tuning.

Interval Partitioning improves partition table manageability by creating new table partitions automatically when inserted rows exceed the partition ranges.

Partitioning by integer value

SQL> create table patients (
2 patientid number not null,name varchar2(10),address varchar2(15)
3 )
4 partition by range (patientid)
5 interval (100)
6 (partition p1 values less than (100))
7 /

Table created.

SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='PATIENTS';

PARTITION_NAME HIGH_VALUE
--------------- ---------------
P1 100

SQL> insert into patients values (100,'ROBERT','4 BORNE AVE');

1 row created.

SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='PATIENTS';

PARTITION_NAME HIGH_VALUE
--------------- ---------------
P1 100
SYS_P81 200

SQL> select count(*) from patients partition (SYS_P81);

COUNT(*)
----------
1

Partitioning by date

SQL> create table userlogs (
2 transid number,
3 transdt date,
4 terminal varchar2(10)
5 )
6 partition by range (transdt)
7 interval (numtoyminterval(1,'YEAR'))
8 (
9 partition p1 values less than (to_date('01-01-2007','mm-dd-yyyy'))
10 );

Table created.

SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='USERLOGS';

PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------------------------------------
P1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL> insert into userlogs values (1,'11-AUG-07','XAV0004');

1 row created.

SQL> select partition_name,high_value
2 from user_tab_partitions
3 where table_name='USERLOGS';

PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------------------------------------
P1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P42 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL> select count(*) from userlogs partition (sys_p42);

COUNT(*)
----------
1

Reference Partitioning partitions a child table based on the partitioning scheme of the parent table.

SQL> create table patients (
2 patientid number not null,name varchar2(10), address varchar2(15)
3 )
4 partition by range (patientid)
5 (partition p1 values less than (100),
6 partition p2 values less than (200))
7 /

Table created.
SQL> alter table patients
2 add constraint patients_pk primary key (patientid);

Table altered.

SQL> create table invoices (
2 invoiceno number,amount number, patientid number not null,
3 constraint invoices_fk
4 foreign key (patientid) references patients
5 )
6 partition by reference (invoices_fk);

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','INVOICES','VCHAN') from dual;

DBMS_METADATA.GET_DDL('TABLE','INVOICES','VCHAN')
-----------------------------------------------------------------------
CREATE TABLE "VCHAN"."INVOICES"
( "INVOICENO" NUMBER,
"AMOUNT" NUMBER,
"PATIENTID" NUMBER NOT NULL ENABLE,
CONSTRAINT "INVOICES_FK" FOREIGN KEY ("PATIENTID")
REFERENCES "VCHAN"."PATIENTS" ("PATIENTID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
PARTITION BY REFERENCE ("INVOICES_FK")
(PARTITION "P1"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "P2"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS )

SQL> insert into patients values (1,'TOBY','88 Palace Ave');

1 row created.

SQL> insert into invoices values (150,262.12,1);

1 row created.

SQL> select count(*) from invoices partition (p1);

COUNT(*)
----------
1

SQL> select count(*) from invoices partition (p2);

COUNT(*)
----------
0

SQL> insert into patients values (110,'GILY','512 HILE STREET');

1 row created.

SQL> insert into invoices values (151,500.01,110);

1 row created.

SQL> select count(*) from invoices partition (p1);

COUNT(*)
----------
1

SQL> select count(*) from invoices partition (p2);

COUNT(*)
----------
1

Table Compression in Oracle Database 11g supports conventional DML and drop column operations. Compressed data are not uncompressed during reading and thus queries on compressed data are noticeably faster since there are fewer data block reads.

SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/db11g/tbs1_01.dbf' size 500M;

Tablespace created.

SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/db11g/tbs2_01.dbf' size 500M;

Tablespace created.

SQL> create table mytable_compress (col1 varchar2(26),col2 varchar2(26)) tablespace tbs1 compress for all operations;

Table created.

SQL> create table mytable_nocompress (col1 varchar2(26),col2 varchar2(26)) tablespace tbs2;

Table created.

SQL> alter system flush buffer_Cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> set timing on

SQL> insert into mytable_nocompress
2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ'
3 from (select 1 from dual connect by level <= 2000000);

2000000 rows created.

Elapsed: 00:00:8.07

SQL> commit;

Commit complete.

Elapsed: 00:00:00.07

SQL> alter system flush buffer_Cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> insert into mytable_compress
2 select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','ABCDEFGHIJKLMNOPQRSTUVWXYZ'
3 from (select 1 from dual connect by level <= 2000000);

2000000 rows created.

Elapsed: 00:00:41.79

SQL> commit;

Commit complete.

Elapsed: 00:00:00.04

SQL> select segment_name,extents from user_segments where segment_name like 'MYTABLE%';

SEGMENT_NAME EXTENTS
------------------------------ ----------
MYTABLE_COMPRESS 53
MYTABLE_NOCOMPRESS 88


SQL> select tablespace_name,bytes/1024/1024 from dba_free_space where tablespace_name like 'TBS%';

TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
TBS1 461.9375
TBS2 363.9375


SQL> alter table mytable_compress drop column col2;

Table altered.

Elapsed: 00:00:21.04