How to decide which tables to designate as in‑memory tables
Ideally your computer would have enough memory to store all of your tables in memory and thus would provide the best possible performance for database transactions. However, in practice most users will have to choose a subset of tables to store in memory, while the remaining tables will be disk-based.
If you cannot fit all tables in memory, try to put the most-frequently-used data in memory. In principle, small frequently-used tables should go into memory, and large rarely-used tables can be left on disk. With other possible combinations, such as large tables that are heavily used, or small tables that are not heavily used, the table type should depend on the “density” of access to a table. In-memory tables work best the higher the number of accesses is per megabyte per second.
Once you have decided to store a table in memory, you must choose whether to store the data in a persistent table, a transient table, or a temporary table. The basic guidelines are as shown below.
You can decide on the most appropriate type of table by asking the questions below until you reach the first question for which you answer “Yes”.
1 Do you need the data to be available again the next time that the server starts? If yes, use a persistent table.
2 Do you need the data to be copied to the Secondary HotStandby server? If yes, use a persistent table.
3 Do you need the data only during the current server session, but the data must be available to multiple users (or multiple connections from the same user)? If yes, use a transient table.The term server session refers to a single run of the server, from the time that it starts until the time that it is either deliberately shut down or it goes down for an unexpected reason (such as a power failure). A connection lasts from the time that a single user connects to the server until the time that user disconnects the same connection. A user may establish multiple connections, but each of these is independent.
4 If none of the above rules applied, use a temporary table.
Note Temporary and transient tables have restrictions that might affect your decision. For example, a temporary table can reference another temporary table, but it cannot reference transient or persistent tables. No other type of table can reference a temporary table.