Weblog for the AMIS Technology corner
Posts tagged rdbms
Oracle RDBMS 11gR2 – alter or replace user defined types even when there are dependencies
Sep 2nd
In our series on the 11g R2 release of the Oracle RDBMS, AMIS staff discusses various functions, features an facilities that have been added, improved or extended in this latest release. These articles are the fruit of several months of studying this new release and projecting which new features and changes would be the most useful to us in our daily practice. This article looks at apparently relatively minor changes in functionality that may well have a substantial impact on the usability of Abstract Data Types (ADTs) or user defined (object) types.
Using ADTs or user defined types in the Oracle RDBMS is quite often very useful. Much more useful by the way than many developers realize. The integration for example between SQL and PL/SQL can be streamlined using user defined types in conjunction with TABLE and MULTISET or the BULK COLLECT operations. Presenting a service-style interface from PL/SQL packages is also much easier realized using custom type definitions – easier both for the definition of the package’s "service contract", the consumer of the package (at least the Database Adapter used in the SOA Suite) and the developers implementing the service contract. Such a contract in terms of nested types quite close resembles the typicall WebService contract that used nested XML documents.
One less agreeable aspect of types (prior to 11gR2 that is) is their administration. Especially the fact that once a type has been created and has been referenced by other types, it cannot be altered. In order to change a small (or big thing) in a single type, it may be necessary to drop a whole bunch of types that are all somehow related to each other. When the types are all recreated, they can be recreated with the required change inside. Types are much like views or packages: database objects that are owned, have associated privileges and synonyms, and add to the general complexity of database applications.
Note: from the administration perspective, it makes quite a difference whether the types are used only for PL/SQL programs and SQL/PLSQL integration – to define the record structure used for the interaction and to create in memory data structures – or whether the types describe the storage structure for columns or nested tables. In the latter case, changing the type has far more impact than in the previous case – where changing types should not have to have a lot of effect.
In 11gR2, the impact of changing types has been brought to where it really should be: changing a type is allowed even if there are dependencies on that type – as long as those dependencies are not from nested tables/columns. Dependencies from PL/SQL program units or views or other types (that are not used for storage structures) are fine and will allow the change of the type (and even a drop of the type). This ability to modify types without recreating the entire nested structure of inter-dependent types will make use of types in PL/SQL applications much more acceptable to DBAs.
I will show you the scenario that up to 11gR2 would cause the nastiness that has now been resolved in 11gR2:
Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it
Sep 1st
Many years ago, Oracle basically set the standard in SQL. Whatever was Oracle SQL could be seen as the standard. This has never been absolutely true – ANSI SQL was different from Oracle SQL. In some respects the differences can be traced back to lack of functional richness in the standard. However, in certain areas, Oracle has walked its own path with certain functions and features with the rest of the RDBMS pack following another route. It seems to be as if starting with Oracle RDBMS 9iR2, Oracle has made several important steps towards embracing the ANSI SQL standard syntax, usually while maintaining its own specific flavor of those same functions.
Some examples of Oracle specific syntax that are also available through their ANSI SQL counterpart – that in most instances has even more functionality – and that is almost always the preferred approach going forward:
- outer join syntax: in addition to Oracle’s (+) notation, 9iR2 introduced the left outer join, right outer join and full outer join (like (+) on both ends of the join condition, something that Oracle does not support)
- the Decode function that with 9iR2 can (and should) be replace by the CASE expression
- the to_char function that in many instances can be replace by extract {year|month|day|HOUR | MINUTE | SECOND} from date-value
- the ltrim and rtrim functions that can be replaced by the trim function with leading and trailing settings
The 11gR2 release has another example of Oracle specific SQL that is now complemented with the ANSI SQL standard approach:the CONNECT BY syntax for hierarchical querying that can now be replaced by the ANSI SQL standard Recursive Subquery Factoring clause, which is an extension to the WITH clause introduced as long ago as Oracle7 and still relatively underused by Oracle SQL developers.
DBA 2.0 – how options are really not optional or: “the infinite database”
Sep 22nd
After Web 2.0, Enterprise 2.0 and Wife 2.0, Oracle introduces the term DBA 2.0. It seems to refer to the DBA of the very near future. The one who can rely on a formidable pal hiding inside the database and taking on many of the boring, repeating tasks as well as advising on some of the tougher challenges when it comes to managing the database. Manageability is one of the focus areas for Oracle in the marketing strategy for the database as well as in the development budget allocation. Lots of time and energy are invested in making the database more manageable and striking the optimal balance between automated management and human decision making.
DBA 2.0 is perhaps also the guy about whom Mark Townsend,Vice President Product Management for Server Technologies, said: "to really screw a system up, you have to be a DBA!". To which he added:
Read the rest of this entry »
