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 what if some data depended on a given business type in the the row of table needs to be unique and some not?

combining function based index feature from Oracle with a unique index makes this possible.

--
-- SCOPE : OASIS
--
-- DDL:CALLITEMS_UK01           :INDEX.MOD              - TEST
--

DROP   INDEX CALLITEMS_UK01;
CREATE UNIQUE INDEX CALLITEMS_UK01
       ON    CALLITEMS
             ( CASE UNIQUE_REF WHEN 1 THEN UPPER(TRIM(ITEMCODE)) ELSE NULL END
             , CASE UNIQUE_REF WHEN 1 THEN ITEMTYPE ELSE NULL END
             , CASE UNIQUE_REF WHEN 1 THEN ACTIVESINCE ELSE NULL END  )
  TABLESPACE OASISIXM
;

Dependend on the uniqeness flag UNIQUE_REF a row may be unique to others or not. Maybe this makes sense for specific call item types. In our project a CALLITEMTYPES table controlled the unqueness of specifc call item types , pupulating the UNIQUE_REF flag to the CATLLITEMS table.

cheers

/K

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 could handle such an dramatic amount of users and data growth. I found an interesting presentation from the facebooks HDFS - Development-Lead Hairong Kuang optimizing HDFS (Hadoop DFS) for Scalability, Storage Effiency and Availability.

An RDBMS would not scale to that amount of load - reasons for that is the explained in theory with the CAP-Theorem which I will post about later;

Now to the presentation on InfoQ :  http://www.infoq.com/presentations/Hadoop-HDFS-Facebook

enjoy

/

Karl

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.

Click to read more ...

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 a second.

Click to read 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