One way to do this would be Object Types, in this case the type would be analagous to your #t1. So it would need to be defined somewhere but it would not need to be global, it could be per-schema or per-procedure even. First, we can create a type:
SQL> create or replace type t1_type as object (x int, y int, z int)
2 /
Type created.
SQL> create or replace type t1 as table of t1_type
2 /
Type created.
Now set up some sample data:
SQL> create table xy (x int, y int)
2 /
Table created.
SQL> insert into xy values (1, 2)
2 /
1 row created.
SQL> insert into xy values (3, 4)
2 /
1 row created.
SQL> commit
2 /
Commit complete.
And create a function over this data returning our "temporary" type:
SQL> create or replace function fn_t1 return t1 as
2 v_t1 t1 := t1(); -- empty temporary table (really an array)
3 v_ix number default 0; -- array index
4 begin
5 for r in (select * from xy) loop
6 v_ix := v_ix + 1;
7 v_t1.extend;
8 v_t1(v_ix) := t1_type(r.x, r.y, (r.x + r.y));
9 end loop;
10 return v_t1;
11 end;
12 /
Function created.
And finally:
SQL> select * from the (select cast (fn_t1 as t1) from dual)
2 /
X Y Z
---------- ---------- ----------
1 2 3
3 4 7
As you can see this is pretty clunky (and uses collection pseudo-functions, which is an obscure feature at the best of times!), as I always say, porting from DB to DB is not merely about syntax and keywords in their SQL dialects, the real difficulty comes in different underlying assumptions (in the case of SQL Server, that cursors are expensive and their use avoided/worked around at all costs).
That's a long question.
First off, my current project (I'm the database guy, there are MMO engine experts to deal with that) is a form of MMORPG based on an off-the-shelf engine. Volumes would be like Eve Online" or "World of Tanks" volumes.
Now for an orthogonal short answer:
- separate DB and Engine completely
Don't mix and match because of hardware optimisations
- hardware: DB and engine servers will be way different specs
- design your database normally
There is a whole lot more of course, but I'd suggest you're over-thinking the problem and shooting yourself in the foot. I'm simply applying the same techniques to my MMO that I used in Investment Banking because IMO most high volume systems should converge to the similar architecture
Best Answer
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.