When we need to enforce uniqueness on a combination of table columns *only if* a certain condition is true, Oracle helps us implement this using a function-based index.
Database Design
The design of an overall database system, including the choice of data structures, identifying data relationships and defining models on the basis of these relationships.
Unique Constraint vs Unique Index in Oracle
A unique constraint and a unique index in Oracle, on the face of it, look very alike. Both enforce uniqueness, and the unique index seems to piggyback on the constraint when the constraint is created or dropped.
Storing JSON Data in Oracle: Why and How
JSON is a simple data interchange format, an alternative to XML that’s gaining wider favor by the day especially for big data storage and REST web services. With release 12c, Oracle has introduced JSON support too – handy new features for storage and retrieval of JSON data.
Here’s a look at how JSON can be persisted in Oracle, and uses cases where doing so would be apt.
DBMS_REDEFINITION: Convert Non-Partitioned Table to Partitioned [pre 12.2]
Oracle 12.2 has introduced a number of new features that ease partitioning, the most-awaited perhaps is the ALTER TABLE MODIFY syntax to convert a non-partitioned table to partitioned.
What do you do if your database version is pre-12.2? Partitioning a non-partitioned table in pre-12.2 databases is trickier, not impossible. Try DBMS_REDEFINITION: here’s how.
In Which Package Is This Procedure Placed?
“In which package is this procedure placed?”
Typical scenario: in a project’s design phase, procedureA is meant to be placed in packageX. During implementation, packages are refactored: packageX gets split into packageY and packageZ. All goes well — the application gets deployed and is running merrily – till a change request comes in.
A new developer refers to the design documentation to understand the change request.
The design documentation should have been updated to reflect the new package structure — but it isn’t.
Storing XML Data in Oracle: Binary XML, Object-Relational, CLOB
Oracle provides an abstract SQL data type called XMLType for storing XML data in the database. You can create an XMLType table, or an XMLType column in a relational table, to persist XML data.
Different storage models are available in Oracle to best fit the nature of the XML data and its expected use. These are:
- Binary XML storage
- Structured storage (object-relational)
- Unstructured XML storage (CLOB)
Here’s an overview of each of these XML storage models with the use cases the model is appropriate for.
DATE Format SQL for Dates Stored as Strings
It is well-acknowledged that attributes in the database should use the correct datatypes (numbers should go into NUMBER columns, dates into DATE columns, etc). Storing date values in VARCHAR2 columns is an open invitation for bugs and issues due to date format such as the one I’m about to describe. Besides, using string-type columns to represent dates rules out your ability to build upon features like range interval partitioning. Yes – ideally you should never have to convert VARCHAR2 to date.
It is equally well-acknowledged that we do not live in an ideal world. In some situations, we do not control the database design; we have to live with what exists and provide a solution to the problem.
This article addresses one such problem — how to compare two dates when they are stored as strings with different DATE formats.
Oracle Partitioning: Range Interval Partitioning
Oracle partitioning features in 11G brought with it a beautiful new twist to range partitioning — the ability to create partitions on-the-fly.
Hello range interval partitioning.
Oracle Total Recall for Tracking History
Oracle 11g Flashback Data Archive (Oracle Total Recall), meant as a DBA tool to recover from logical corruptions and human errors, can be put to good use for another standard requirement in applications. Most applications need an audit trail or history of changes for important entities — say, the trail of changes to customer’s data needs to be tracked. Such requirements becomes trivial with Total Recall, eliminating a huge chunk of coding and testing effort.
10 Important Lessons in Data Modeling
Are you data modeling for a custom OLTP application starting from scratch, with business requirements as the only initial input?
No matter what the size, domain or location of your project, the following lessons will help to ensure that your physical data modeling process is smooth and that you end up with a database design that is accurate, usable and scalable.