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
Tom Kyte does appreciate this one
I’m sure he also likes all those trigger announcements too! 🙂
But I like that “select count(*)” [at every screen] caching … now even the bad, useless sql can execute fast! Sweet. Kind of curious what happens with the huge result sets … is the cache coming from the user process? After all my select count(*) is different than yours … uncommitted data and all.
[Just trying to save the travel/lodging fare here :]
Thanks for the writeup
I like the “x:=s.nextval in plsql”.
If the result cache is invalidated on a dml —> it then pretty much can be used only on Static Table, why not go with the Table “CACHE” option then.
Gabe: yes you can cache uncommited result. So for the same query, you may end with different results cached
vidya,
the idea with result cache is that you will cache only one or a few rows, not the whole table
Once i have read oramag in odp.net column that a resultset can be “alerted” when it was changed. This idea was taken for new feature of 11G(cachability)
well not very happy about that disabled trigger thing, but the sequence improvemnt is good feature. so is continue in pl/sql loops. keeping my fingers crossed about others.
“DBMS_SQL improvement. Ways to bypass the 32k limitation. I have not understand very well this change”
mmm, suggests this is about the max. length of the SQL statement that can be parsed. (whereas now you can only get around it using the table of varchar(255) kludge.
Anyway – I’m a little bit surprised to see the cache hint – it does not seem to be a very advanced feature – MySQL has this since version 4.1 (around 2001 or even earlier)
— Roland Bouman
11d is going to have significantly improved NAS features as well…
oops, typo…that’s 11g…I usually spell it correctly on my blog 🙂 kevinclosson.wordpress.com
Pingback: Eye on Oracle – A SearchOracle.com Blog » Database 11g: Ho-hum or hurray?
Useful information, but I was just informed that the base platform release of 11g, Linux 32-bit, has been delayed – and that the hp-ux beta will not be available for another couple of months. ;-(
The base release has been delayed? How can it be delayed when there is no release date 😕
Hi Laurent,
I hope you remember me from PL/SQL Discussion Forum (“Treatment of zero-length strings as NULLs”).
I would like to know the following. I’ve heard that 11g would have virtual columns (computed columns) feature. A computed column can be defined in terms of (alternatively):
1) regular columns only
2) mixed (regular and computed columns already defined), something as spreadsheet table
What alternative is used in the 11g?
Thank a lot in advance.
Regards
Albert Richter
[email protected]
in Oracle 10g and before you can have a view
create view v as select emp.*, sal+comm as salcomm from emp;
but then you have a view and a table. If you want to index salcomm, you can create a function based index on emp(sal+comm)
in 11g, you can simply add one column to emp
alter table emp add (salcomm as (sal+comm));
and you can add an index on salcomm (which is a FBI actually).
it is nice, because you do not have to maintain a view
Thanks a lot. Is this example possible in 11g:
CREATE TABLE T1 (
rc1 NUMBER,
rc2 NUMBER,
rc3 NUMBER,
cc1 AS (rc1 + rc2),
cc2 AS (rc3 * cc1) — is it possible ???
);
where
rc = regular column
cc = computed (virtual) column
INSERT INTO T1 VALUES(10, 5, 3);
SELECT * FROM T1;
RC1 RC2 RC3 CC1 CC2
——– ——– ——— ——– ——–
10 5 3 15 45
Regards
Albert
not possible
SQL> create table t(x number, x1 as (x+1), x2 as (x1+1));
create table t(x number, x1 as (x+1), x2 as (x1+1))
*
ERROR at line 1:
ORA-54012: virtual column is referenced in a column expression
in your example your insert is also invalid
SQL> insert into t values(1); insert into t values(1) * ERROR at line 1: ORA-00947: not enough values SQL> insert into t values(1,2); insert into t values(1,2) * ERROR at line 1: ORA-54013: INSERT operation disallowed on virtual columns SQL> insert into t(x) values(1); 1 row created. SQL> insert into t values(2,default); 1 row created.Thanks. Exactly what I thought – the definition of COMPUTED columns (VIRTUAL – not stored or MATERIALIZED – stored) in a table is allowed only in terms of regular columns in the table, not in terms of other computed columns already defined in that table in top-down order. The same unnecessary limitation just like in Sybase-ASE and MS SQL Server!!! Otherwise, we would have a great opportunity to define and create real spreadsheet table by inserting only a few values in regular columns. It’s beyond me to understand such a limitation in brains of database software designers. They are not even intelligent to apply simple logic: if a table consists of regular and computed columns, then insertion of values into the table implicitly applies only to regular columns
INSERT INTO T1 VALUES (10, 5, 3);
without necessity to explicitly enumerate the names of regular columns
INSERT INTO T1 (rc1, rc2, rc3) VALUES (10, 5, 3);
since insertion of values into computed columns (cc1 and cc2) is in any way impossible.
My disappointment is much greater insofar as I know that Canadian Sterling ANSWER:Zim RDBMS had the feature to define and create real spreadsheet table by means of computed columns 17 years ago.
Once again, thanks a lot.
Regards
Albert