DBMS_STATS package in version 11g includes "CREATE_EXTENDED_STATS"
function. This package creates an "extension", consisting of group of
columns, for which cardinality and distribution are calculated separately.
This is probably the 1st and the last place to note that I absolutely
abhor the fact that it is a function and not a procedure. Other then that,
it is a great tool for handling correlated columns, like the famous
Jonathan's example of months and zodiac signs. Below is a little script
to test the new feature:

set trimout on
set trimspool on
connect scott/tiger
spool /tmp/test_stats.out
drop table test_stats purge;
CREATE TABLE TEST_STATS (
A NUMBER(12,0),
B NUMBER(12,0),
C VARCHAR2(32)
);
declare
sd integer;
i integer;
t varchar2(64);
ext varchar2(64);
begin
select to_number(to_char(sysdate,'J')) into sd from dual;
dbms_random.seed(sd);
for sd in 1..100000
loop
i:=dbms_random.random;
t:=dbms_random.string('A',32);
insert into test_stats values(i,i,t);
end loop;
commit;
select dbms_stats.create_extended_stats('SCOTT','TEST_STA TS','(A,B)')
into ext
from dual;
dbms_output.put_line('Created extension is:'||ext);
end;
/
prompt 'Analyzing table...'
analyze table test_stats compute statistics for all columns size 16;
column column_name format a32
select column_name,count(*) endpoints from user_histograms
where table_name='TEST_STATS'
group by column_name
order by column_name;
exit;

After the script is finished, you should see the following result:

Table dropped.

Elapsed: 00:00:00.21

Table created.

Elapsed: 00:00:00.26
Created extension is:SYS_STUNA$6DVXJXTP05EH56DTIR0X

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.44
'Analyzing table...'

Table analyzed.

Elapsed: 00:00:02.49

COLUMN_NAME ENDPOINTS
-------------------------------- ----------
A 17
B 17
C 17
SYS_STUNA$6DVXJXTP05EH56DTIR0X 17

Elapsed: 00:00:00.01
Disconnected from Oracle Database 11g Enterprise Edition Release
11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

Not only that, but a closer examination detects that DBMS_METADATA package
now returns the following as the DDL for the table:

CREATE TABLE "SCOTT"."TEST_STATS"
( "SYS_STUNA$6DVXJXTP05EH56DTIR0X" NUMBER GENERATED ALWAYS AS
(SYS_OP_COMBINED_HASH("A","B")) VIRTUAL HIDDEN ,
"A" NUMBER(12,0),
"B" NUMBER(12,0),
"C" VARCHAR2(32)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;


There is a virtual and hidden column added to our table, as hash of
columns A and B. Mere "DESCRIBE" will not show the virtual column and
neither will USER_TAB_COLUMNS.
Virtual columns, with "GENERATED ALWAYS" option are the new feature of
Oracle11g. Virtual columns are described here:http://tinyurl.com/44wm46


I found out that generating extensions for the columns that are
frequently used together in joins can help with getting a better plan. I
cannot give an example because of the NDA. The database, after all,
belongs to my employer and not to me.

--
Mladen Gogala
http://mgogala.freehostia.com