Scratching the surface of Oracle 11g new features - Part 1
PIVOTING DATA
select cust_id, state_code, times_purchased
from customer order by cust_id;
CUST_ID STATE_ TIMES_PURCHASED
———- —— —————
1 CT 1
2 NY 10
3 NY 2
4 NY 15
5 CA 1
6 OH 0
7 OH 0
select state_code, times_purchased, count(*) as cnt from customer
group by state_code, times_purchased;
STATE_ TIMES_PURCHASED CNT
—— ————— ———-
NY 10 1
NY 2 1
NY 15 1
CA 1 1
CT 1 1
OH 0 2
select * from (select times_purchased,state_code from customer t)
pivot
(count(state_code) for state_code in (’NY’,'CA’,'CT’,'OH’))
order by times_purchased
/
TIMES_PURCHASED ‘NY’ ‘CA’ ‘CT’ ‘OH’
————— ———- ———- ———- ———-
0 0 0 0 2
1 0 1 1 0
2 1 0 0 0
10 1 0 0 0
15 1 0 0 0
INCREMENTAL GLOBAL STATISTICS
Global stats for a partition table can now be updated incrementally based on the changed partition alone.
dbms_stats.set_table_prefs(‘SCHEMA_NAME’,’TABNAME’,’INCREMENTAL’,’TRUE’)
And combine this with granularity => ‘AUTO’
dbms_stats.gather_table_stats (
ownname => ‘SCHEMA_NAME’,
tabname => ‘TABNAME’,
partname => 'P_FEED_12345’,
granularity => 'AUTO')
RESULT CACHING
This thing is very exciting (well, at least to me, anyway). I think this is going to be so useful for long running queries, or queries that involve large amount of data. This is not to be confused with materialized views.
Just imagine for a few second that this is a very complicated , resource intensive long running query…
explain plan set statement_id=’hilda1′ for select sum(times_purchased) from customer;
set pagesize 25
set linesize 100
set long 1000000
col xplan format a80
select * from table(dbms_xplan.display(’PLAN_TABLE’,'hilda1′,’TYPICAL’));
PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 487422484
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| CUSTOMER | 7 | 91 | 3 (0)| 00:00:01 |
——————————————————————————-
Note
—–
- dynamic sampling used for this statement
13 rows selected.
Now we are going do it again, but this time with this /*+ result_cache */ hint
explain plan set statement_id=’hilda2′ for select /*+ result_cache */ sum(times_purchased) from customer;
select * from table(dbms_xplan.display(’PLAN_TABLE’,'hilda2′,’TYPICAL’));
PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 487422484
————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | 2rafyj4xfv3f725guctywn2n3t | | | | |
| 2 | SORT AGGREGATE | | 1 | 13 | | |
| 3 | TABLE ACCESS FULL| CUSTOMER | 7 | 91 | 3 (0)| 00:00:01 |
————————————————————————————————–
Result Cache Information (identified by operation id):
——————————————————
1 - column-count=1; dependencies=(HILDA.CUSTOMER); attributes=(single-row); name=”select /*+ resu
lt_cache */ sum(times_purchased) from customer”
Note
—–
- dynamic sampling used for this statement
19 rows selected.
You can flush the cache by executing dbms_result_cache.flush
VIRTUAL COLUMNS
Normally things like this can only be achieved using a trigger or manual population.
create table employee (name varchar2(20), salary number, grade varchar2(50)
generated always as
(case when salary <=10000 then ‘LOW’
when salary <=20000 then ‘MEDIUM’
when salary <=30000 then ‘HIGH’
when salary <=40000 then ‘are you a CEO?’ end) virtual);
insert into employee (name,salary) values (’Jack’,10000);
insert into employee (name,salary) values (’Jill’,25000);
insert into employee (name,salary) values (’Tom’,5000);
insert into employee (name,salary) values (’Jane’,32000);
commit;
select * from employee;
NAME SALARY GRADE
———- ———- ——————————————
Jack 10000 LOW
Jill 25000 HIGH
Tom 5000 LOW
Jane 32000 are you a CEO?
Although the name is “Virtual”, this virtual column will take on physical space in the database, just like the other columns.
INVISIBLE INDEXES
This feature will be useful when trying to determine an impact an index will have on a query plan.
Let’s just create a simple index on our existing table.
create unique index customer_ix01 on customer(cust_id,cust_name);
explain plan set statement_id=’hilda1′ for select * from customer where cust_id=4 and cust_name=’Customer4′;
select * from table(dbms_xplan.display(’PLAN_TABLE’,'hilda1′,’BASIC’));
—————————————————–
| Id | Operation | Name |
—————————————————–
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER |
| 2 | INDEX UNIQUE SCAN | CUSTOMER_IX01 |
—————————————————–
Now let’s make the index invisible
alter index customer_ix01 invisible;
explain plan set statement_id=’hilda2′ for select * from customer where cust_id=4 and cust_name=’Customer4′;
select * from table(dbms_xplan.display(’PLAN_TABLE’,'hilda2′,’BASIC’));
————————————–
| Id | Operation | Name |
————————————–
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| CUSTOMER |
————————————–
You now see that the index has now been made invisible, thus not used by the query.
To force a session to “see” invisible indexes you can change session parameter OPTIMIZER_USE_INVISIBLE_INDEXES
alter session set optimizer_use_invisible_indexes=true;
explain plan set statement_id=’hilda3′ for select * from customer where cust_id=4 and cust_name=’Customer4′;
select * from table(dbms_xplan.display(’PLAN_TABLE’,'hilda3′,’BASIC’));
—————————————————–
| Id | Operation | Name |
—————————————————–
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMER |
| 2 | INDEX UNIQUE SCAN | CUSTOMER_IX01 |
—————————————————–
A new column has been added to the USER_INDEXES system view to show index visibility.
select visibility from user_indexes where index_name=’CUSTOMER_IX01′;
VISIBILITY
—————————
INVISIBLE