Category Archives: 11g

pivot table

4 Replies

First I apologize for the confusion with previous post of mines, where I defined row generators as pivot table.

Here I talking about transposing rows into columns PIVOT and transposing columns into rows UNPIVOT

Ok, back in 2003, I had a data model were all attributes were stored in same table (a miracle of java tools generating data models)

select person.name,
  property.type,
  property.value
from person, property
where 
  person.id=property.person;

NAME TYPE     VALUE
---- -------- ------
John gender   male
Mary category junior
Mary gender   female

for datawarehousing purpose, I had to get the attributes, if set, as a column, so I started with outer joining for each attribute (they were plenty, not just two)

select name, 
  gender.value gender, 
  category.value category
from person,
  property gender,
  property category
where
  person.id = gender.person(+) 
  and gender.type(+)='gender'
  and person.id = category.person(+)
  and category.type(+)='category';

NAME GENDER CATEGO
---- ------ ------
Mary female junior
John male

By using the Tom Kyte method described on asktom, I could have used aggregation.

select name,
  max(decode(type,'gender',value)) gender,
  max(decode(type,'category',value)) category
from person , property
where person.id = property.person (+)
group by name;

NAME GENDER CATEGO
---- ------ ------
John male
Mary female junior

To do the opposite, I posted once in a forum

select deptno,
  decode(x,1,'DNAME','LOC') type,
  decode(x,1,dname,loc) value
from dept,
 (select 1 x from dual union all
  select 2 from dual);

    DEPTNO TYPE  VALUE
---------- ----- --------------
        10 DNAME ACCOUNTING
        20 DNAME RESEARCH
        30 DNAME SALES
        40 DNAME OPERATIONS
        10 LOC   NEW YORK
        20 LOC   DALLAS
        30 LOC   CHICAGO
        40 LOC   BOSTON

Well, in the next generation database, this is going to be easier, maybe.

With the introduction of pivot keyword, the following should work

select name, type, value
from person , property
pivot (max(value) 
for type in (
  'gender' as gender,
  'category' as category))
where person.id = property.person (+);

and with the unpivot keyword

select * 
from dept 
unpivot (value 
for type in (
  dname as 'DNAME',
  loc as 'LOC'));

It would be interesting to compare the execution plans !

11g new features part II

4 Replies

I have been following 2 sessions with Juan Loaiza about new features.

A very few of them here :

SQL> create edition v1_0;
SQL> alter session set edition=v1_0;

You can create a versioning of your objects, so you will be able to have many versions of the same package at the same time. It is a bit similar to workspace, where you have many versions of the data at the same time. It is very profitable in release management and availability.

About the cache result hint, you can also use
alter session cache results;, which can be useful for lookup table. As commented on a previous post, you can still use the old alter table cache; or the keep buffer pool. It depends.

Performance enhancement in the RAC interconnect.

Server side connection pooling. An additional layer to the shared server, to enable faster [actually to bypass] session creation.
edit: check Database Resident Connection Pooling

Securefiles. This is a good one. It is a huge improvement to BLOB. Faster, with compression, encryption.

Partition interval. This is a marvelous one ! You can now partition by date, one partition per month for example, with automatic partition creation.

Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now rman can bypass those types of tablespace. Great for exporting a tablespace from backup.

Capture/replay workload. Sounds appealing. You can capture the workload in prod and apply it in development.

Many other enhancement.

Juan expect to see a Petabyte single database, with a terabyte of memory and 1000 cpus before the end of the decade

this was a 2006 announcement, not all features announced have been implemented in prod

11g new features

18 Replies

The presentation of Bryn Llewellyn was a good one! He talked about the 11g new features. Well, he said it is maybe not named 11g and maybe none of the features will be available. But since the beta is already available, I guess all of the features he talked will be available. It is very rich, and went fast, so I apologize for not relating all of them, or any mistakes that I include.

The result set caching. My favorite. This sounds like a huge performance improvement for many applications. I have an application which is doing a select count(*) at every screen. It is the bottleneck. A possible workaround is to use query rewrite and materialized view. Now in 11g, you just add a /*+result_cache*/ hint, so the result of the query will be cached. Justin Cave asked about the integrity. The answer is : there is no compromise of the integrity. There is an aggressive cache invalidation, so whatever DML happens on one dependent table, the cache result is invalidated. As for the materialized view rewrite in 9iR1, I guess there will be quite a few P1 bug for that hint in 11.1.0, but still it is a great announcement. This hint also works for PL/SQL functions, the declaration of the function can include result_cache relies on (emp)

Fine grained dependency tracking. Currently, if you have a view or a function based on a table, and if you modify that table, the view is getting invalid. This is what Bryn called : aggressive object invalidation. Now in 11g it has improved, so if you modify an element of the table which is not included in that view (ex: you add a column), the view is not invalidated. This is wise.

Regular expression improvements. This is rather a SQL improvement, glad Bryn did presented it too. One of this is a new function called regexp_count. All of the existing functions have been improved. I love that kind of new features, because they offer new way of solving queries. I hope I will be able to reanswer Re: Can any one retrive Nth column from a table with one less function in 11g.

Using the => syntax in sql. In 11g you can use select f(x=>1) from dual, which is a goodie.

Fine grained access control to the TCP packages family (utl_tcp, utl_smtp, …). This is a good one. You can grant execute on the package only for specific IP addresses.

Read-only table. Ok, it reminds me one thread how-to-set-a-table-in-read-only-mode. Glad to see this one too!

Fast triggers. Nothing to do about it, triggers are just faster in 11g.

Intra-Unit inlining. This is the 3rd level of PLSQL_optimize_level.

Trigger order. You have many triggers on one table, you can now specify in which order they will be fired.

Compound trigger. A new powerfull type of trigger, which enables you to specify a declarative section (and to have a kind of package global variable), a before procedure, an after each row procedure, and an after procedure. All this in a single trigger.

Create disabled trigger. It is now possible to create a disabled trigger. You can create an invalid trigger, and compile it later, but if not used appropriately by the developers, it may create a new kind of invalid objects in the databases. Good for the developer. Not necessarily pleasant for the DBA.

Using sequence in PL/SQL without DML. It is possible to use x:=s.nextval in plsql, instead of selecting from dual.

PLSQL_Warning. You can compile a procedure with PLSQL_WARNING=enable all, to get errors when others exceptions are caught and not raised. Tom Kyte does appreciate this one ;-)

A special mention to the super keyword. I am enthusiastic but somehow skeptic about the implementation of inheritance in PL/SQL. Well, I am looking forward from reading more about this.

Faster and real native compilation. Improvement have been done to bypass the filesystem and the external C compiler in this release.

DBMS_SQL improvement. Ways to bypass the 32k limitation. I have not understand very well this change.

The continue keyword. You can use the continue keyword in your plsql loops, as you would do in another languages.

disclaimer: the post was based on OOW 2006 announcement and may not reflect the production release of 11g