Types of Oracle joins

Just as a brush up of Oracle join understanding.

Suppose we are joining two tables in Oracle.

SELECT T1.X, T2.Y
FROM T1 JOIN T2 ON T1.X = T2.X

Assume, T1 is driving table (i.e. more rows than T2)

Nested Loop join
------------------

for each record in T1
find matching record in T2 where T1.X = T2.X
fetch that record into result set

Now if there is no index on column X in T2, Oracle will perform full table scan for each record in T1.

Hash join
---------

Oracle will load create an in-memory index (knowns as hash table) for T2 (i.e. smaller table)

It will still perform similar operation

for each record in T1
find matching record in T2 where T1.X = T2.X
fetch that record into result set

But even if there is no index on T2.X, Oracle will use the hash table which works similar to index!

Clearly this is suitable only when T2 is small otherwise [1] hash table may not fit in memory [2] creation of hash table may involve a long time (then we could have created the index on T2 in first place!)

Sorted Merge join
-------------------

If both tables are very big, hash join is not suitable due to memory/time requirement and nested loop will be slow or will result in full table scan.

The alternative way to speed up performance is to use sorted merge join.

In this case, both tables are sorted by Oracle (using temporary tablespace) by joining key (X in this case).

The advantage is, when joining two tables, Oracle just needs to scan only a small part of the table (e.g. as they are sorted, to find a record in T2 when T1.X = 15 will require Oracle search just from X=10 to X=20 in T2 as it cannot be beyond this range and so on).

The trade off is, it will take some resource (space/time/memory) to sort the tables.

Usually Oracle chooses best method of join based on statistics avaialble. However, user may force using a different type of join using hints like use_hash(T1,T2) etc.

What is Dimension in Oracle data warehousing?

If you are using Oracle (10g upwards) database for data warehousing, chances are you might have come across DIMENSION in Oracle.
What is it and how does it help?

In data warehousing environment, often you find tables do not have referential integreties among them. While, I do not condone it, sometimes it is un-avoidable as source data comes from mainframe where, due to historic design, RDBMS like constraints could not be implemented.

So, when these data are loaded to Oracle tables (without any cleanup), implementing RDBMS constraints becomes difficult.

Without primary/foreign key relationships, Oracle optimizer will be confused as it won't know how to join different tables.

This is where Dimension comes to help!

Dimensions are simply logical constrains defined among tables. A product code may be logically related to transaction table which is defined by CREATE DIMENSION command.

What is the advantage? Well, it then behaves as if it knows foreign key relationships between tables (though not exactly the case).

The main performance gain is that it helps Oracle to rewrite queries so that it can either use a materialized view or avoid complex joins.

If you run a query which does something like this - show all possible aggregations from a very_large_table, it may take a very long time to complete.

Where as, if your DIMENSIONs are defined then Oracle may fetch same result by quering a smaller_table (which may be materialized view).

By defining Dimensions, you tell Oracle where else it may look for to satisfy your complex aggregation requirements.

There are some pre-requisites though, you must enable query rewrite enabled parameter to true beforehand.

Oracle RAC - tiny intro

Introduction

An RAC database is a clustered database.

There are multiple Oracle instances which are connected via "interconnect" - which is a type of LAN.

The datafiles are stored in several disk drives which are connected by "cluster aware storage".

By adding multiple instances, we can add and remove and single Oracle instance without bringing the database down. So, database always remains available. That is the essence of RAC - high availability.

An RAC database is managed by "Oracle Clusterware". RAC operates in "shared everything" architecture mode.

Any storage (eg. SAN, SCSI etc.) can be used with RAC. However, good I/O speed is required for scalability of storage.

RAC supports up to 100 clusters which may be different at hardware level but must run same operating system!

Oracle recommends using ASM (Automatic Storage Management) for ease of dealing with clustered storage.

As a reminder, the RAC is used for high availability and scalability. When work load grows, you can simply add another server to the grid (RAC is a type of grid computing after all).

RAC can be managed via Oracle Enterprise Manager.

An Oracle RAC database requires three components - cluster nodes (the servers or computers running Oracle instances), shared storage (disk drives) and Oracle Clusterware (software application).

Installing RAC

The first step of working with RAC is to install "Oracle Clusterware" which is installed via Universal Installer.

Then you have to configure the clusterware.

Then install ASM (Automatic Storage Management)

Now install Oracle 11g database

Then perform post installation tasks. This ensures that clusterware and database are installed properly and they are aware of each other.

It is possible to convert your normal single instance Oracle database to an RAC database. You can achieve this via Enterprise Manager or "rconfig" utility.

Administering Clusterware

Oracle Clusterware includes two important components: the voting disk and the OCR.

The voting disk is a file that manages information about node membership, and the OCR is a file that manages cluster and Oracle RAC database configuration information.

RAC can be administered via Oracle Enterprise manager. On EM's web console mode, click on Availability tab to see details of Clusterware. You can click on Topology tab to see a visual represntation of your nodes. The Interconnect tab shows you info on interfaces. You can also add new instance to clusterware via EM (under Server tab).

Oracle Clusterware posts alert messages in alert log - which is under $CRS_home.

RAC data dictionary views are created by catclust.sql.

Cache Fusion

Oracle RAC uses "Cache Fusion" to synchronize the data stored in the buffer cache of each database instance.

Since all computers/instances in an RAC access the same database, the overall system must guarantee the coordination of data changes on different computers such that whenever a computer queries data it receives the current version - even if another computer recently modified that data. Oracle RAC refers to this functionality as Cache Fusion. It involves the ability of AC to "fuse" in-memory data cached physically separately on each computer into a single global cache.

Recommended Reference for further reading:

Oracle Database 2 day + RAC
http://download.oracle.com/docs/cd/B28359_01/rac.111/b28252.pdf

Converting single instance database to RAC

http://www.oracle.com/technology/pub/articles/chan_sing2rac_install.html

Other
http://www.orafaq.com/wiki/RAC_FAQ

What is g in Oracle's grid computing?

Oracle defines Grid Computing like this -

With grid computing, groups of independent, modular hardware and software components can be connected and rejoined on demand to meet the changing needs of businesses.

What does it exactly mean?

We already know what is parallel computing. A complex task is dividided into smaller parts and each part is processed independently by a computer. Then the outputs are combined to get final result.

How does grid computing differ from this parallel computing?

Grid computing is an abstract (or virtualization, as Oracle says) concept. A grid can be an infrastructure grid, an appllication grid, an information grid and so on.

What - getting more confused? Ok, please read on.

In early days (~ 1980s) of database, the relational database management was starting to gain populartity. In RDBMS terms, a customer buys products and a transaction is generated. All these info like customer, product, transaction etc. are stored in RDBMS. They are linked together (by foreign keys, in 3rd normal form). So from a high level view, customer, product, transaction are all part of RDBMS.

Now come to the present days. We are gathering data like never before! Besides RDBMS, we now have lots of different stuffs like OLAP (Business Intelligence/Data Warehouse etc.), OLTP (transactional data), BPEL (Business Process Execution Language), Web services (using XML), OWB (Oracle Warehouse Builder) etc.

The grid is collection of all these - i.e. different applications and data which speak with one another.

Oracle has product of almost everything nowadays - which is wrapped around a buzzy name called Fusion Middleware. These components are designed so that one component (say OWB ) can interact with another (say core Oracle database). This constitute an architectural grid.

The relational data on your RDBMS can be termed as information grid.

There's another aspect of grid. Take Oracle Real Application Cluster or RAC. Here multiple instances of database are inter-connected to safeguard single point of failure. This is an example of infrastructure grid. Everything is part of grid. It is a concept. It could have been told as Matrix computing too. (Did you enjoy Matrix series of movies?)

Now you know what the buzzword Grid computing mean!

What is Oracle Fusion Middleware (OFM) and Service Oriented Architecture (SOA)?

If you are not sure about what are these things and try to have a look at Oracle's website, there is a good chance that you might find yourself confused.

That's quite expected. Oracle's website is for marketing their product. It's not their interest to describe their products in a way that people think it is there is no magic about it!

So, here I try to explain the things in non-geeky terms.

The Fusion Middleware conisists of Oracle's non core-database products - some of which are not actually middleware!

This includes Oracle's Developer suite (Forms & Reports), Java related tools, web logic server, content management etc. OFM depends on open standards such as BPEL, SOAP, XML and JMS.

Oracle SOA is a part of OFM.

What is SOA?

The main essence of SOA is that applications will talk with each other in a language (i.e. data format, process steps) which is understood by all others applications communicating with.

SOA is about reuse.

SOA helps business to move, change, partner and re-invent itself with ease and grace.

SOA extends idea of reuse not only to web services but also with business services.

SOA components are loosely coupled.

SOA can contain web service, BPEL etc.

Web service

Example, you throw a postcode to Yahoo Geocoder and it gives you back latitude, longitude of that post code.

You ask for price of particular item to a website, it suplies you the price.

Usually, web service results are returned in XML format to ensure universal compatibility.

In SOA segment, you will often hear the term "Orchestration". What does it mean?

If you seen an orchestra, you know that conductor just draws some invisible drawings in mid air by moving his magic wand from one side to another. However, musicians can decipher his rhythm and plays their instruments so that every one plays same tune at same pace.

Orchestration in SOA has similar meaning. It ensures that all applications under SOA, know how to be in sync with other applications in the group.

But how orchestration is implemented?

It is done via BPEL or Business Process Execution Language.

BPEL is a tool, using which you can draw how data moves from one application to another. For those who have not used it, it is like a Visio flow chart diagram editor. But, when you draw objects in BPEL, you tell them what to do. You instruct them where to read data from, how to process it and where to send output after processing finished. So, basically it is a graphical tool to define business process. Without BPEL, the whole process will look like thousands of lines of PL/SQL (or Java or C++ or whatever) codes!

Behind the scene BPEL still writes codes - but it just make simpler (!) for any business user to understand and define the process. Now whether that is good or bad is debatable - I am just outlining the concept here.

So, BPEL works to integrate several applications. BPEL usually follows XML standard as interface to several components.

Let us take a bigger example. Your supplies sends you a file which contains all the products, quantities and unit price you ordered for. You need to update your inventory accordingly. Now assume that your supplier quoted the price in € but you need to put that in £ in your database. Using absolute minium technology, you need to write a small program to convert € to £ while loading that data to your system. But if you are using BPEL, you can visually draw the program!