Should I create separate table or have the information as columns in the same table

Shared nothing typically refers to hardware, though it could also be used when describing an SOA architecture.

From a hardware perspective, a "shared nothing" Oracle database simply means a machine using local disks, memory, etc.

Oracle RAC (real application clusters) is a shared disk architecture, but not shared memory. Horizontal scaling is then achieved by adding more machines to the cluster.

When talking in SOA terms, "shared nothing" means that each service has a corresponding database which is only accessed by that service. So the ACCOUNTS service accesses the ACCOUNTS_DB, ORDERS service the ORDERS_DB and so on. These databases could be shared nothing from a hardware perspective as well, or use RAC.

Ensuring consistency of data and references which would normally be handled using foreign keys becomes a challenge in SOA shared nothing databases.

Sharding typically refers to partitioning managed at the application level, rather than within the database. For example, you could partition accounts by email address and direct customers with address starting A-C to ACCOUNTS_DB01, D-F ACCOUNT_DB02 and so on. The shard mapping could be a simple range like this, a function on the input or a lookup database stating which database is stored in. The databases would be "hardware shared nothing" in this case as the idea is you use relatively cheap machines which are easily added and replaced.

You could shard your databases at the application level and still have Oracle partitioning at the table level within the database itself. So you could shard your ORDERS database by customer, then partition the orders table by order date as well inside the database.

The downside to both meanings of shared nothing comes if you frequently run queries that have to access several databases. In these cases your joins will be pushed into the application layer rather than the DB layer so are likely to be slower. Good governance is necessary to ensure this doesn't happen.

You can update a join in Oracle if the following conditions are met:

1. Only one base table is updated
2. All other tables are key-preserved: each of them must have at most one row for each row of the base table.

(additional restrictions on updating views apply)

In your example you update table PG_MACHINE only. Oracle has to make sure that for a single row of this table, only one row of the other can be found. This seems to be the case since you have a PK on PG_LABLOCATION.LABLOCID. Therefore you should be able to update the join. See for example this SQLFiddle with a similar setup.

In your case you should either:

• make sure that the primary key is enabled, validated, not deferrable (interestingly, a deferrable constraint prevents Oracle from updating the join!)
• use MERGE if PG_LABLOCATION.LABLOCID is unique for the relevant query. MERGE is less strict than update with joins and will only return an error if there is actually a duplicate in the result set (whereas UPDATE will fail if there is the possibility of a duplicate).

• review your query, since you don't need values from the parent table in the SELECT clause, you could rewrite it as a semi-join (that guarantees that no duplicate will be generated):

  UPDATE (SELECT mac.in_use, mac.update_time
            FROM pg_machine mac
           WHERE mac.lablocid IN (SELECT loc.lablocid 
                                    FROM pg_lablocation loc 
                                   WHERE loc.dnsname = 'value')
             AND to_date('02-JAN-2013') > mac.update_time)
     SET in_use = 1 - MOD(101, 2), 
         update_time = to_date('02-JAN-2013');
  

  This could be rewritten as:

  UPDATE pg_machine mac
     SET in_use = 1 - MOD(101, 2), 
         update_time = to_date('02-JAN-2013')
   WHERE mac.lablocid IN (SELECT loc.lablocid 
                            FROM pg_lablocation loc 
                           WHERE loc.dnsname = 'value')
     AND to_date('02-JAN-2013') > mac.update_time;
  

In this case I would go with the third option: in general you can't update the parent in a parent-child join.