Partitioned tables

Question

Question

Rajeev Karki · Apr 17, 2016

Is there something in Cache that is equivalent to partitioning a table in Oracle? I'm trying to break some big tables into groups so that the most frequently accessed data is faster to retrieve.

Here is some information on this concept from Oracle.

https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm

Discussion (4)0

Log in or sign up to continue

Jenna Poindexter · Apr 28, 2016

In Cache, due to the way we store data on disk, partitioning the data may not give you a significant benefit in access time. Data is stored in a highly optimized fashion and the effort to retrieve a record stored at the "top" of a table takes no more effort to retrieve than a record stored at the "bottom" of a table.

If you have records within a table that need to be located quicker (different than being retrieved from disk) you may want to consider setting up some useful indexes that you can use to locate specific records within the table.

Do you have an example of your table structure and could you explain what you mean by more frequently accessed data?

1 0

score 2 · Answer 1 · 2016-04-18T04:02:13-04:00

Caché has very useful feature names as Mapping, in your case you can look at Global Mappings

How it can help now. With a mapping you can split your globals which stores your tables.
For example You have table Sample.Person, with globals ^Sample.PersonD for Data and ^Sample.PersonI for indices.
then you may create new databases like PERSONDATA1, PERSONDATA2 and PERSONINDEX

And create mappings likes on picture

In this case we send first 10000 rows to database PERSONDATA1, and all next data to PERSONDATA2. When you decide to split it again, just create new database, and edit mappings.

And you can place every database in any place as you want.

But in future releases we expect to see new feature: Distributed SQL

score 0 · Answer 2 · 2016-04-23T10:48:58-04:00

Thanks Dmitry!The global mappings may work for some of my scenarios. I am looking forward to the sharding though.

score 0 · Answer 3 · 2018-03-23T04:27:11-04:00

How would this meet with mirroring?

I know that mirroring wont sync HS configuration (such as mappings) and this will have to be done manually with some process running in both sides to keep the configuration consistent.

The problem that I foresee is being able to do the mapping synchronization at the exact same instant (in index meaning) so that no inserts are lost between the database "PERSONADATA2" creation in the primary and the creation in the secondary.