CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11gpartitioning
Ouissal
Ouissal

Reputation: 1559

Simulating and manipulating a partitioned database on multiple locations

Let's say that I created the following table :

CREATE TABLE TABLE1 
( 
id INT NOT NULL
country VARCHAR(10) NOT NULL
name VARCHAR(10) NOT NULL
productType VARCHAR(10) NOT NULL
)

If for example I have different values for producType ( type1, type2 .. etc) and I do a partitioning based on those types such as partition1 is for type1 and so on. And the possible values for country are : country1, country2 and country3. How can I perform or simulate a horizontal fragmentation based on the country, and be able to use a similar command to this :

SELECT * FROM partition1@country1
WHERE name = someName

Is it a requirement that the database has to be stored on multiple servers in order to be able to do that? If so, is there another way to do it that doesn't require that (on Oracle 11g) ?

Upvotes: 1

Views: 69

Answers (1)

APC
APC

Reputation: 146249

Do you want to subpartition the tables? That is, partition by list( ProductType), subpartition by list (country)?

That can be done.

Subpartitions have generated names which don't reflect the name of the key. So they don't lend themselves to explicit referencing the way partitions can The select statement would reference the country in the WHERE clause:

select * from your_table partition (p_prodtype_0001)
where country = 'country1'

The optimizer can still apply subpartition pruning in this case.

You could fake sharing by having different tables of the same name in different schemas, either in the same database or different ones. You could create database links with the name of your different countries. That would allow you to employ @country1 or whatever. Inserting would be tricky. Probably you would have to have a view and an INSTEAD OF trigger.

Upvotes: 1

Related Questions