Row Data

There are no restrictions on the size of your rows, or the amount of data that you store in a field. However, you should consider your store's performance when deciding how large you are willing to allow your individual tables and rows to become. As is the case with any data storage scheme, the larger your rows, the longer it takes to read the information from storage, and to write the information to storage.

On the other hand, every table row carries with it some amount of overhead. Also, as the number of your rows grows very large, search times may be adversely affected. As a result, choosing to use a large number of tables, each of which use rows with just a small handful of fields, can also harm your store's performance.

Therefore, when designing your tables' content, you must find the appropriate balance between a small number of tables, each of which uses very large rows; and a large number of tables, each of which uses very small rows. You should also consider how frequently any given piece of information will be accessed.

For example, suppose your table contains information about users, where each user is identified by their first and last names (surname and familiar name). There is a set of information that you want to maintain about each user. Some of this information is small in size, and some of it is large. Some of it you expect will be frequently accessed, while other information is infrequently accessed.

Small properties are:

Large properties are:

There are several possible ways you can organize this data. How you should do it depends on your data access patterns.

For example, suppose your application requires you to read and write all of the properties identified above every time you access a row. (This is unlikely, but it does represent the simplest case.) In that event, you might create a single table with rows containing fields for each of the properties you maintain for the users in your application.

However, the chances are good that your application will not require you to access all of a user's properties every time you access his information. While it is possible that you will always need to read all of the properties every time you perform a user look up, it is likely that on updates you will operate only on some properties.

Given this, it is useful to consider how frequently data will be accessed, and its size. Large, infrequently accessed properties should be placed in tables other than that used by the frequently accessed properties.

For example, for the properties identified above, suppose the application requires:

In this case, you might store user properties using a table and a child table. The parent table holds rows containing all the small properties, plus public keys. The child table contains the image file and voice greeting.

## Enter into table creation mode
table create -name userInfo
## Now add the fields
add-field -type STRING -name surname
add-field -type STRING -name familiarName
add-field -type STRING -name gender
add-field -type STRING -name street
add-field -type STRING -name city
add-field -type STRING -name state
add-field -type STRING -name zipcode
add-field -type STRING -name userPhone
add-field -type BINARY -name publickey1
add-field -type BINARY -name publickey2
primary-key -field surname -field familiarName
shard-key -field surname
## Exit table creation mode
### Must add the parent table before we add the child
plan add-table -name userInfo -wait 

table create -name userInfo.largeProps
add-field -type STRING -name propType
add-field -type BINARY -name voiceGreeting
add-field -type BINARY -name imageFile
primary-key -field propType
plan add-table -name userInfo.largeProps -wait 

Because the parent table contains all the data that is accessed whenever user data is accessed, you can update that data all at once using a single atomic operation. At the same time, you avoid retrieving the big data values whenever you retrieve a row by splitting the image data and voice greeting into a child table.


You might want to consider using the key/value API for the image data and voice greeting. By doing that, you can use the Oracle NoSQL Database large object interface, which is optimized for large object support. See the Oracle NoSQL Database Getting Started with the Key/Value API for information on working with large objects. Note that if you use the large object interface, you can store references to the large objects (which are just strings) in your tables.