Conditional Unique Indexes


Matrix : What you must learn is that these rules are no different than rules of a computer system. Some of them can be bent, others can be broken. Understand?
 
Usually an unique index grants the uniqueness of all rows in a specific table which have non-null values; But (more...)

InfoQ : Running the Largest Hadoop DFS Cluster

Since I joined a Big Data Event : Frankfurter Datenbanktage 2013 - I started to take also a look to non-relational technics too. The RDBMS is not for every asepct the correct and fitting and fulfilling answer to all data related IT challenges. 

Frequently I wondered about how facebook (more...)

Learn from the smaller one’s : Native support for ENUM Types

Gerhard a colleague is dealing with postgres databases and was very suprised how many features are very oracle like. For example the same concept of Sequences as in Oracle. But then he detected the postgres enum native type support in postgres and asked me if the same feature would exist in Oracle.

When a Query runs slower on second execution – a possible side effect of cardinality feedback

After a successful migration from Oracle 10gR2 to Oracle 11gR2, I observed a very odd behavior executing a query; On first execution the query run fast - means 0.3 s; On second not faster but with tremendous reduced speed - approximate 20 s; Now this behavior is the opposite I experienced with complex queries (lot of joins, lot of predicates) , the first time of execution needs the extra cost of hard parsing and disk reads if the data is not in cache. the second time even the query run initial several seconds it run in a fraction of (more...)

Oracle 11GR2 Upgrade Resources

Currently I am testing/migrating an Oracle 11.2 Installation of our Appliction;

And found by the way a very helpful resource for Oracle Upgrades to 11GR2;

http://www.oracle.com/technetwork/database/upgrade/index.html

For example a real helpful and detailed document is this : 11g Release 2 Upgrade Workshop Presentation 

Cheers!

/Karl

Modifying an execution plan inside a View using a Query Block Hint (QB_NAME hint)

Optimizer Hints like Index, Cardinality, Ordered ... are local settings to a SQL statement. Local means the context given is the context of the SQL statement, the scope is local. If you query a View you cannot use for example an Index hint because the table names and aliases are hidden behind the view. So normal hinting would not work without  changing the view to add a hint.

Include-Directive for SQL-Scripts with Powershell (II)

In the last recent article I presented the possibilities of the Powershell Import-Schema function; Now we do not want to use the function interactive via Powershell Console but also call it from a SQL-Script; Your SQL-Script will not process Powershell commands but a Powershell script could process the SQL file and execute all Powershell related commands and let SQL commands untouched. The stream then could be redirected to a pure SQL file.

Include-Directive for SQL-Scripts with Powershell (I)

One thing is to write SQL -, PL/SQL- Code for Packages, Trigger, Views ... and to bring every database object as DDL-File under source control. The other thing is to build a deployment package of the modified database objects(*) involved and to ship them to production. At my current customer the production database deployment package is a flat file without folder hierarchy as usually in development. So you have to merge all affected DDL-Scripts into flat files.

Extract Database Object Header Comment with Powershell

Some database objects in my current project are created by ORM or a PL/SQL-framework and hence need not to be put under source control; All manual created objects are strictly stored in source control.

Every database object definition under source control is stored as DDL file. And every file starts with a comment about the database object properties. Once an object is created in database and it's definition is added to source control it's time to automate some repeating stuff like creating a comment header. For the current project I use Windows Powershell for this. All stuff which needs to (more...)

Access Oracle Database with Windows Powershell 2.0

This year I plan to implement database build automation. There are lot of manual tasks for a developer DBA to create and deploy a release package.
I decided to use the Windows Powershell as build tool. it integrates best in the Windows environment to access ...