{"id":8330,"date":"2018-06-10T01:40:11","date_gmt":"2018-06-09T23:40:11","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=8330"},"modified":"2023-11-08T02:10:34","modified_gmt":"2023-11-08T01:10:34","slug":"memoptimized-rowstore-in-oracle-database-18c","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/","title":{"rendered":"MemOptimized RowStore in Oracle Database 18c"},"content":{"rendered":"\n<p>The MemOptimized RowStore introduced in Oracle Database 18c is designed to improve the performance of simple queries accessing data via primary key columns only. An example of such a query is <code>SELECT value FROM t WHERE key = :key<\/code>&nbsp;where <code>key<\/code> is the only primary key column of table <code>t<\/code>. This feature is available for the following Oracle Database offerings only (see&nbsp;<a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/dblic\/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87\">Licensing Information User Manual<\/a>):<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Oracle Database Enterprise Edition on Engineered Systems (EE-ES)<\/li>\n\n\n\n<li>Oracle Database Cloud Service Enterprise Edition &#8211; Extreme Performance (DBCS EE-EP)<\/li>\n\n\n\n<li>Oracle Database Exadata Cloud Service (ExaCS)<\/li>\n<\/ul>\n\n\n\n<p>For this blog post I&#8217;ve used a Docker container running an Oracle Database 18c version&nbsp;18.2.0.0.180417 on my MacBook Pro (Late 2016). The initialization parameter <code>_exadata_feature_on=TRUE<\/code><span class=\"pl-s\">&nbsp;<\/span>enabled technically the MemOptimized RowStore. This means that I expect the feature to work but with different performance metrics as on one of the officially supported environments.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Concept<\/h2>\n\n\n\n<p>The MemOptimized RowStore is conceptually best documented in <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/cncpt\/memory-architecture.html#GUID-D58DC90F-0ABB-4B1E-96C1-6094A04A5E12\">Database Concepts<\/a>. The idea is to store a heap-organized table completely in the memory within a subarea of the SGA. This subarea is named <code>Memoptimized Pool<\/code>&nbsp;and consists of the following two parts:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Memoptimize Buffer Area<\/strong><\/li>\n<\/ul>\n\n\n\n<p>This is a dedicated buffer cache for table blocks. 75% of the memoptimized pool is reserved for this buffer cache.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Hash Index<\/strong><\/li>\n<\/ul>\n\n\n\n<p>A hash index is a hash table\/map as we know it from Java and other programming languages (associative array in PL\/SQL). The primary key columns are used as key and a pointer to the block in the memoptimize buffer area is used as value. The hash index uses the other 25% of the memoptimized pool.<\/p>\n\n\n\n<p>The size of the memoptimized pool is set by the initialization parameter <code>MEMOPTIMIZE_POOL_SIZE<\/code>. The default size is <code>0<\/code>. Changing the value requires to restart of the database. The minimum size is <code>100M<\/code>.<\/p>\n\n\n\n<p>The following conditions must be met to use the MemOptimized RowStore:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>The table is marked as <code>MEMOPTIMIZE FOR READ<\/code>. See the <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/sqlrf\/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877\">memoptimize_read_clause<\/a> for&nbsp;<code>CREATE TABLE<\/code> and <code>ALTER TABLE<\/code>&nbsp;statements.<\/li>\n\n\n\n<li>The table is heap-organized.<\/li>\n\n\n\n<li>The table has a primary key.<\/li>\n\n\n\n<li>The primary key is not an identity column.<\/li>\n\n\n\n<li>The table is not compressed.<\/li>\n\n\n\n<li>The table is not reference-partitioned.<\/li>\n\n\n\n<li>The table has at least one segment (use <code>SEGMENT CREATION IMMEDIATE<\/code> when creating tables).<\/li>\n\n\n\n<li>The table has been loaded in the memoptimized pool using <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/arpls\/DBMS_MEMOPTIMIZE.html#GUID-001ED359-0547-443D-A8D7-2ECB1F553B41\">dbms_memoptimize.populate<\/a>.<\/li>\n\n\n\n<li>The table fits completely in the memoptimized pool.<\/li>\n\n\n\n<li>The query must be in the format <code>SELECT &lt;column_list&gt; FROM &lt;table&gt; WHERE &lt;primary_key_column&gt; = &lt;value&gt;<\/code>. The result columns must derive from the underlying table. Multiple primary key columns are supported, in this case, all primary key columns have to be defined in the <code>where_clause<\/code>. Additional predicates are not allowed.<\/li>\n\n\n\n<li>The initialization parameter <code>STATISTICS_LEVEL<\/code> must not be set to <code>ALL<\/code>.<\/li>\n\n\n\n<li>The optimizer hint <code>GATHER_PLAN_STATISTICS<\/code> must not be used.<\/li>\n\n\n\n<li>SQL trace must not be enabled.<\/li>\n\n\n\n<li>The query must not be executed within PL\/SQL (neither static nor dynamic SQL is supported).<\/li>\n\n\n\n<li>The query must not be executed using the default database connection in a Java-stored procedure.<\/li>\n\n\n\n<li><em>The query must not be executed from a JDBC thin driver connection. You have to use OCI, otherwise the performance will be really bad, see the succeeding blog post&nbsp;<a href=\"https:\/\/www.salvis.com\/blog\/2018\/06\/19\/memoptimized-rowstore-in-oracle-database-18c-with-oci\/\">MemOptimized RowStore in Oracle Database 18c with OCI<\/a>. This might be a bug. [added on 2018-06-19].<\/em><\/li>\n<\/ol>\n\n\n\n<p>If all conditions are met, then the <strong>row is fetched without a single logical I\/O<\/strong>. In all other cases, you get either an error message or the query is executed the conventional way, using at least 3 logical I\/Os (1 I\/O for the root index block, 1 I\/O for the index leaf block, 1 I\/O for the table block).<\/p>\n\n\n\n<p>Existing applications do not need to change their code to use the MemOptimized RowStore (besides some DDL).<\/p>\n\n\n\n<p><strong>But how can a row be fetched without an I\/O?&nbsp;<\/strong>Technically it is a new kind of I\/O which is no longer accounted for the wait event <code>consistent gets<\/code>. Instead, these operations are reported with new wait events:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>memopt r lookups<\/code> &#8211; counting every hash index lookup (regardless of the result)<\/li>\n\n\n\n<li><code>memopt r hits<\/code>&#8211; counting every successful hash index lookup (primary key found)<\/li>\n\n\n\n<li><code>memopt r misses<\/code>&#8211; counting every unsuccessful hash index lookup (primary key not found)<\/li>\n<\/ul>\n\n\n\n<p>There are 65 wait events in <code>v$statname<\/code> for the MemOptimized RowStore. You find some descriptions in the <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/refrn\/statistics-descriptions-2.html#GUID-2FBC1B7E-9123-41DD-8178-96176260A639\">Database Reference<\/a>&nbsp;as well.<\/p>\n\n\n\n<p><strong>But why is this supposed to be faster than a single block access in the keep pool using a single-table hash cluster?<\/strong> The answer is given in the <a href=\"http:\/\/www.oracle.com\/technetwork\/database\/oracledatabase18c-wp-4392576.pdf\">Introducing Oracle Database 18c<\/a> whitepaper:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>Key-value lookups then bypass the SQL execution layer and execute directly in the data access layer via an in-memory hash index.<\/p>\n<\/blockquote>\n\n\n\n<p><strong>And what are the expected performance gains?<\/strong> &#8211; I have not found any numbers in the documentation. Unfortunately, my tests are not conclusive in this area, since I&#8217;m running them in an unsupported environment. However, I&#8217;ve found an <a href=\"https:\/\/asktom.oracle.com\/pls\/asktom\/asktom.search?tag=memoptimized-rowstore-tables\">answer on the Ask TOM website<\/a>, where Maria Colgan states the following:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>the rowstore can be approximately 25% faster than a single-table hash cluster<\/p>\n<\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\">Configure Database<\/h2>\n\n\n\n<p>Before we can use the MemOptimized RowStore we have to set the size of the memoptimized pool. In this case, I set the minimum size and restart the database.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Set Memoptimize Pool Size<\/span><span role=\"button\" tabindex=\"0\" data-code=\"ALTER SYSTEM SET memoptimize_pool_size = 100M SCOPE=SPFILE;\nSHUTDOWN IMMEDIATE\nSTARTUP\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">ALTER<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">SYSTEM<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> memoptimize_pool_size = 100M SCOPE=SPFILE;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SHUTDOWN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">IMMEDIATE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">STARTUP<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Now the database has reserved 75 megabytes for the memoptimize buffer area and 25 megabytes for the hash index.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Create and Populate Table<\/h2>\n\n\n\n<p>Let&#8217;s create an empty table <code>t4<\/code> with an <code>memoptimize_read_clause<\/code>.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Create Table t4<\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE TABLE t4 (\n   key    INTEGER            NOT NULL,\n   value  VARCHAR2(30 CHAR)  NOT NULL,\n   CONSTRAINT t4_pk PRIMARY KEY (key)\n) \nSEGMENT CREATION IMMEDIATE\nMEMOPTIMIZE FOR READ;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">CREATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">TABLE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">t4<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">INTEGER<\/span><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #569CD6\">NOT NULL<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">value<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">VARCHAR2<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">CHAR<\/span><span style=\"color: #D4D4D4\">)  <\/span><span style=\"color: #569CD6\">NOT NULL<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">CONSTRAINT<\/span><span style=\"color: #D4D4D4\"> t4_pk <\/span><span style=\"color: #569CD6\">PRIMARY KEY<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">) <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">SEGMENT CREATION <\/span><span style=\"color: #569CD6\">IMMEDIATE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">MEMOPTIMIZE <\/span><span style=\"color: #569CD6\">FOR<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">READ<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Please note, that the primary key definition on line 4 is required to avoid an&nbsp;ORA-62142: MEMOPTIMIZE FOR READ feature requires NOT DEFERRABLE PRIMARY KEY constraint on the table. And without the clause on line 6, we&#8217;d get an&nbsp;ORA-62156: MEMOPTIMIZE FOR READ feature not allowed on segment with deferred storage.<\/p>\n\n\n\n<p>I use the following anonymous PL\/SQL block to populate the table <code>t4<\/code> with 100,000 rows and gather table statistics.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Populate Table t4<\/span><span role=\"button\" tabindex=\"0\" data-code=\"BEGIN\n   dbms_random.seed(0);\n   INSERT INTO t4 (key, value)\n   SELECT rownum AS key, \n          dbms_random.string('x', round(dbms_random.value(5, 30), 0)) AS value\n     FROM xmltable('1 to 100000');\n   COMMIT;\n   dbms_stats.gather_table_stats(ownname=&gt;USER, tabname=&gt;'T4');\nEND;\n\/\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   dbms_random.seed(<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">INSERT INTO<\/span><span style=\"color: #D4D4D4\"> t4 (<\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">value<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> rownum <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          dbms_random.string(<\/span><span style=\"color: #CE9178\">&#39;x&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #DCDCAA\">round<\/span><span style=\"color: #D4D4D4\">(dbms_random.value(<\/span><span style=\"color: #B5CEA8\">5<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\">), <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">value<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">     <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> xmltable(<\/span><span style=\"color: #CE9178\">&#39;1 to 100000&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">COMMIT<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   dbms_stats.gather_table_stats(ownname=&gt;USER, tabname=&gt;<\/span><span style=\"color: #CE9178\">&#39;T4&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">END<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>We may now run a query in an SQL client. I&#8217;ve used SQLcl because of the comprehensive statistics when using autotrace. The output is the result of the second execution.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Query t4 &#8211; Fallback to Conventional Index Access<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SET LINESIZE 100\nSET AUTOTRACE ON\nSELECT * FROM t4 WHERE key = 42;\n\n       KEY VALUE                         \n---------- ------------------------------\n        42 UKPBW05FQ1                    \n\nExplain Plan\n-----------------------------------------------------------\n\nPLAN_TABLE_OUTPUT                                                                                   \n----------------------------------------------------------------------------------------------------\nPlan hash value: 1143490106                                                                         \n                                                                                                    \n------------------------------------------------------------------------------------------------    \n| Id  | Operation                              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |    \n------------------------------------------------------------------------------------------------    \n|   0 | SELECT STATEMENT                       |       |     1 |    24 |     2   (0)| 00:00:01 |    \n|   1 |  TABLE ACCESS BY INDEX ROWID READ OPTIM| T4    |     1 |    24 |     2   (0)| 00:00:01 |    \n|*  2 |   INDEX UNIQUE SCAN READ OPTIM         | T4_PK |     1 |       |     1   (0)| 00:00:01 |    \n------------------------------------------------------------------------------------------------    \n                                                                                                    \nPredicate Information (identified by operation id):                                                 \n---------------------------------------------------                                                 \n                                                                                                    \n   2 - access(&quot;KEY&quot;=42)                                                                             \n\nStatistics\n-----------------------------------------------------------\n               1  CPU used by this session\n               1  CPU used when call started\n               2  DB time\n              42  Requests to\/from client\n              42  SQL*Net roundtrips to\/from client\n               3  buffer is not pinned count\n             598  bytes received via SQL*Net from client\n           83338  bytes sent via SQL*Net to client\n               3  calls to get snapshot scn: kcmgss\n               2  calls to kcmgcs\n               3  consistent gets\n               3  consistent gets examination\n               3  consistent gets examination (fastpath)\n               3  consistent gets from cache\n               2  execute count\n               1  index fetch by key\n           24576  logical read bytes from cache\n               1  memopt r lookups\n               1  memopt r misses\n              43  non-idle wait count\n               2  opened cursors cumulative\n               1  opened cursors current\n               2  parse count (total)\n               1  rows fetched via callback\n               1  session cursor cache hits\n               3  session logical reads\n               1  sorts (memory)\n            2011  sorts (rows)\n               1  table fetch by rowid\n              45  user calls\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> LINESIZE <\/span><span style=\"color: #B5CEA8\">100<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> AUTOTRACE <\/span><span style=\"color: #569CD6\">ON<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> t4 <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">KEY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">VALUE<\/span><span style=\"color: #D4D4D4\">                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------- ------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\"> UKPBW05FQ1                    <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Explain Plan<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-----------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">PLAN_TABLE_OUTPUT                                                                                   <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">----------------------------------------------------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Plan <\/span><span style=\"color: #569CD6\">hash<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">value<\/span><span style=\"color: #D4D4D4\">: <\/span><span style=\"color: #B5CEA8\">1143490106<\/span><span style=\"color: #D4D4D4\">                                                                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">------------------------------------------------------------------------------------------------    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">| Id  | Operation                              | <\/span><span style=\"color: #569CD6\">Name<\/span><span style=\"color: #D4D4D4\">  | <\/span><span style=\"color: #569CD6\">Rows<\/span><span style=\"color: #D4D4D4\">  | Bytes | Cost (%CPU)| <\/span><span style=\"color: #569CD6\">Time<\/span><span style=\"color: #D4D4D4\">     |    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">------------------------------------------------------------------------------------------------    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">|   <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> | <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">STATEMENT<\/span><span style=\"color: #D4D4D4\">                       |       |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |    <\/span><span style=\"color: #B5CEA8\">24<\/span><span style=\"color: #D4D4D4\"> |     <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |    <\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">|   <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |  <\/span><span style=\"color: #569CD6\">TABLE<\/span><span style=\"color: #D4D4D4\"> ACCESS <\/span><span style=\"color: #569CD6\">BY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">INDEX<\/span><span style=\"color: #D4D4D4\"> ROWID <\/span><span style=\"color: #569CD6\">READ<\/span><span style=\"color: #D4D4D4\"> OPTIM| T4    |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |    <\/span><span style=\"color: #B5CEA8\">24<\/span><span style=\"color: #D4D4D4\"> |     <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |    <\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">|*  <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\"> |   <\/span><span style=\"color: #569CD6\">INDEX<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">UNIQUE<\/span><span style=\"color: #D4D4D4\"> SCAN <\/span><span style=\"color: #569CD6\">READ<\/span><span style=\"color: #D4D4D4\"> OPTIM         | T4_PK |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |       |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">------------------------------------------------------------------------------------------------    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">Predicate<\/span><span style=\"color: #D4D4D4\"> Information (identified <\/span><span style=\"color: #569CD6\">by<\/span><span style=\"color: #D4D4D4\"> operation id):                                                 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------------------------------------------------                                                 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\"> - access(<\/span><span style=\"color: #CE9178\">&quot;KEY&quot;<\/span><span style=\"color: #D4D4D4\">=<\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">)                                                                             <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">Statistics<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-----------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  CPU used <\/span><span style=\"color: #569CD6\">by<\/span><span style=\"color: #D4D4D4\"> this <\/span><span style=\"color: #569CD6\">session<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  CPU used <\/span><span style=\"color: #569CD6\">when<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">call<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">started<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  DB <\/span><span style=\"color: #569CD6\">time<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">  Requests <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\">\/<\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net roundtrips <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\">\/<\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">buffer<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">is<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> pinned count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">             <\/span><span style=\"color: #B5CEA8\">598<\/span><span style=\"color: #D4D4D4\">  bytes received via <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">           <\/span><span style=\"color: #B5CEA8\">83338<\/span><span style=\"color: #D4D4D4\">  bytes <\/span><span style=\"color: #569CD6\">sent<\/span><span style=\"color: #D4D4D4\"> via <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  calls <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">get<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">snapshot<\/span><span style=\"color: #D4D4D4\"> scn: kcmgss<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  calls <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> kcmgcs<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  consistent gets<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  consistent gets examination<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  consistent gets examination (fastpath)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  consistent gets <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> cache<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">execute<\/span><span style=\"color: #D4D4D4\"> count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">index<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">fetch<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">by<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">key<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">           <\/span><span style=\"color: #B5CEA8\">24576<\/span><span style=\"color: #D4D4D4\">  logical <\/span><span style=\"color: #569CD6\">read<\/span><span style=\"color: #D4D4D4\"> bytes <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> cache<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  memopt r lookups<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  memopt r misses<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">43<\/span><span style=\"color: #D4D4D4\">  non-idle wait count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  opened cursors cumulative<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  opened cursors current<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  parse <\/span><span style=\"color: #DCDCAA\">count<\/span><span style=\"color: #D4D4D4\"> (total)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">rows<\/span><span style=\"color: #D4D4D4\"> fetched via callback<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">session<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">cursor<\/span><span style=\"color: #D4D4D4\"> cache hits<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">session<\/span><span style=\"color: #D4D4D4\"> logical reads<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  sorts (memory)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #B5CEA8\">2011<\/span><span style=\"color: #D4D4D4\">  sorts (<\/span><span style=\"color: #569CD6\">rows<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">fetch<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">by<\/span><span style=\"color: #D4D4D4\"> rowid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">45<\/span><span style=\"color: #D4D4D4\">  user calls<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>The execution plan on lines 20 and 21 shows the intention to use the MemOptimized RowStore (READ OPTIM). But the 3 consistent gets&nbsp;on line 41 indicates that a conventional index access has been used. We get the proof on lines 48 and 49. There was an access to the hash index (<code>1 memopt r lookups<\/code>), but no key with the value 42 has been found (<code>1 memopt r misses<\/code>). Hence the fallback to the conventional unique index access.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Populate MemOptimized RowStore<\/h2>\n\n\n\n<p>The following anonymous PL\/SQL block populates the memoptimized pool for the table <code>t4<\/code>.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Populate Memoptimized Pool<\/span><span role=\"button\" tabindex=\"0\" data-code=\"BEGIN\n   dbms_memoptimize.populate(schema_name=&gt;USER, table_name=&gt;'T4');\nEND;\n\/\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   dbms_memoptimize.populate(schema_name=&gt;USER, table_name=&gt;<\/span><span style=\"color: #CE9178\">&#39;T4&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">END<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>It is important to note, that the memoptimized pool is populated in the background by a space management worker process. This call is just a request, with a own wait event <code>memopt r populate tasks accepted<\/code>.&nbsp; Usually this is pretty fast, but to be sure you can check the relevant wait event before and after calling <code>dbms_memoptimize.populate<\/code>. Here&#8217;s an example:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Check Number of Rows Populated<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT n.name, s.value\n  FROM v$sysstat s \n  JOIN v$statname n\n    ON n.statistic# = s.statistic#\n WHERE n.name = 'memopt r rows populated';\n\nNAME                                                                  VALUE\n---------------------------------------------------------------- ----------\nmemopt r rows populated                                              100000\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> n.name, s.value<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> v$sysstat s <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">JOIN<\/span><span style=\"color: #D4D4D4\"> v$statname n<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">ON<\/span><span style=\"color: #D4D4D4\"> n.statistic# = s.statistic#<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> n.name = <\/span><span style=\"color: #CE9178\">&#39;memopt r rows populated&#39;<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">NAME<\/span><span style=\"color: #D4D4D4\">                                                                  <\/span><span style=\"color: #569CD6\">VALUE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------------------------------------------------------------- ----------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">memopt r <\/span><span style=\"color: #569CD6\">rows<\/span><span style=\"color: #D4D4D4\"> populated                                              <\/span><span style=\"color: #B5CEA8\">100000<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>100,000 rows are now in the memoptimized pool. Let&#8217;s query the table <code>t4<\/code> again.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Query t4 &#8211; Using MemOptimized RowStore<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SET LINESIZE 100\nSET AUTOTRACE ON\nSELECT * FROM t4 WHERE key = 42;\n\n       KEY VALUE                         \n---------- ------------------------------\n        42 UKPBW05FQ1                    \n\nExplain Plan\n-----------------------------------------------------------\n\nPLAN_TABLE_OUTPUT                                                                                   \n----------------------------------------------------------------------------------------------------\nPlan hash value: 1143490106                                                                         \n                                                                                                    \n------------------------------------------------------------------------------------------------    \n| Id  | Operation                              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |    \n------------------------------------------------------------------------------------------------    \n|   0 | SELECT STATEMENT                       |       |     1 |    24 |     2   (0)| 00:00:01 |    \n|   1 |  TABLE ACCESS BY INDEX ROWID READ OPTIM| T4    |     1 |    24 |     2   (0)| 00:00:01 |    \n|*  2 |   INDEX UNIQUE SCAN READ OPTIM         | T4_PK |     1 |       |     1   (0)| 00:00:01 |    \n------------------------------------------------------------------------------------------------    \n                                                                                                    \nPredicate Information (identified by operation id):                                                 \n---------------------------------------------------                                                 \n                                                                                                    \n   2 - access(&quot;KEY&quot;=42)                                                                             \n\nStatistics\n-----------------------------------------------------------\n              43  Requests to\/from client\n              43  SQL*Net roundtrips to\/from client\n             605  bytes received via SQL*Net from client\n           83447  bytes sent via SQL*Net to client\n               3  calls to get snapshot scn: kcmgss\n               2  calls to kcmgcs\n               2  execute count\n               1  memopt r hits\n               1  memopt r lookups\n              44  non-idle wait count\n               2  opened cursors cumulative\n               1  opened cursors current\n               2  parse count (total)\n               1  session cursor cache count\n               1  sorts (memory)\n            2011  sorts (rows)\n              46  user calls\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> LINESIZE <\/span><span style=\"color: #B5CEA8\">100<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> AUTOTRACE <\/span><span style=\"color: #569CD6\">ON<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> t4 <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">KEY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">VALUE<\/span><span style=\"color: #D4D4D4\">                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------- ------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\"> UKPBW05FQ1                    <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Explain Plan<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-----------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">PLAN_TABLE_OUTPUT                                                                                   <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">----------------------------------------------------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Plan <\/span><span style=\"color: #569CD6\">hash<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">value<\/span><span style=\"color: #D4D4D4\">: <\/span><span style=\"color: #B5CEA8\">1143490106<\/span><span style=\"color: #D4D4D4\">                                                                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">------------------------------------------------------------------------------------------------    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">| Id  | Operation                              | <\/span><span style=\"color: #569CD6\">Name<\/span><span style=\"color: #D4D4D4\">  | <\/span><span style=\"color: #569CD6\">Rows<\/span><span style=\"color: #D4D4D4\">  | Bytes | Cost (%CPU)| <\/span><span style=\"color: #569CD6\">Time<\/span><span style=\"color: #D4D4D4\">     |    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">------------------------------------------------------------------------------------------------    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">|   <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> | <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">STATEMENT<\/span><span style=\"color: #D4D4D4\">                       |       |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |    <\/span><span style=\"color: #B5CEA8\">24<\/span><span style=\"color: #D4D4D4\"> |     <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |    <\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">|   <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |  <\/span><span style=\"color: #569CD6\">TABLE<\/span><span style=\"color: #D4D4D4\"> ACCESS <\/span><span style=\"color: #569CD6\">BY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">INDEX<\/span><span style=\"color: #D4D4D4\"> ROWID <\/span><span style=\"color: #569CD6\">READ<\/span><span style=\"color: #D4D4D4\"> OPTIM| T4    |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |    <\/span><span style=\"color: #B5CEA8\">24<\/span><span style=\"color: #D4D4D4\"> |     <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |    <\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">|*  <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\"> |   <\/span><span style=\"color: #569CD6\">INDEX<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">UNIQUE<\/span><span style=\"color: #D4D4D4\"> SCAN <\/span><span style=\"color: #569CD6\">READ<\/span><span style=\"color: #D4D4D4\"> OPTIM         | T4_PK |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |       |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">------------------------------------------------------------------------------------------------    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">Predicate<\/span><span style=\"color: #D4D4D4\"> Information (identified <\/span><span style=\"color: #569CD6\">by<\/span><span style=\"color: #D4D4D4\"> operation id):                                                 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------------------------------------------------                                                 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\"> - access(<\/span><span style=\"color: #CE9178\">&quot;KEY&quot;<\/span><span style=\"color: #D4D4D4\">=<\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">)                                                                             <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">Statistics<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-----------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">43<\/span><span style=\"color: #D4D4D4\">  Requests <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\">\/<\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">43<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net roundtrips <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\">\/<\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">             <\/span><span style=\"color: #B5CEA8\">605<\/span><span style=\"color: #D4D4D4\">  bytes received via <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">           <\/span><span style=\"color: #B5CEA8\">83447<\/span><span style=\"color: #D4D4D4\">  bytes <\/span><span style=\"color: #569CD6\">sent<\/span><span style=\"color: #D4D4D4\"> via <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  calls <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">get<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">snapshot<\/span><span style=\"color: #D4D4D4\"> scn: kcmgss<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  calls <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> kcmgcs<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">execute<\/span><span style=\"color: #D4D4D4\"> count<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  memopt r hits<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  memopt r lookups<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">44<\/span><span style=\"color: #D4D4D4\">  non-idle wait count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  opened cursors cumulative<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  opened cursors current<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  parse <\/span><span style=\"color: #DCDCAA\">count<\/span><span style=\"color: #D4D4D4\"> (total)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">session<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">cursor<\/span><span style=\"color: #D4D4D4\"> cache count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  sorts (memory)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #B5CEA8\">2011<\/span><span style=\"color: #D4D4D4\">  sorts (<\/span><span style=\"color: #569CD6\">rows<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">46<\/span><span style=\"color: #D4D4D4\">  user calls<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>As before, the execution plan on lines 20 and 21 shows the intention to use the MemOptimized RowStore (READ OPTIM). But in this case, there are no <code>consistent gets<\/code>. And on line 38 we have a successful hash index lookup (<code>1 memopt r hits<\/code>). A SQL query without logical I\/Os, made possible by the MemOptimized RowStore.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Alternatives<\/h2>\n\n\n\n<p>What are your options, when your database does not provide a MemOptimized RowStore? I see the primarily the following alternatives:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Heap-organized table<\/li>\n\n\n\n<li>Index-organized table<\/li>\n\n\n\n<li>Single-table hash cluster<\/li>\n<\/ul>\n\n\n\n<p>Let&#8217;s elaborate on them.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1. Heap-Organized Table<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Heap-Organized Table t1<\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE TABLE t1 (\n   key    INTEGER            NOT NULL,\n   value  VARCHAR2(30 CHAR)  NOT NULL,\n   CONSTRAINT t1_pk PRIMARY KEY (key)\n)\nSTORAGE (BUFFER_POOL KEEP);\nALTER INDEX t1_pk STORAGE (BUFFER_POOL KEEP);\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">CREATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">TABLE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">t1<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">INTEGER<\/span><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #569CD6\">NOT NULL<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">value<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">VARCHAR2<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">CHAR<\/span><span style=\"color: #D4D4D4\">)  <\/span><span style=\"color: #569CD6\">NOT NULL<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">CONSTRAINT<\/span><span style=\"color: #D4D4D4\"> t1_pk <\/span><span style=\"color: #569CD6\">PRIMARY KEY<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">STORAGE (BUFFER_POOL <\/span><span style=\"color: #569CD6\">KEEP<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #569CD6\">ALTER<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">INDEX<\/span><span style=\"color: #D4D4D4\"> t1_pk STORAGE (BUFFER_POOL <\/span><span style=\"color: #569CD6\">KEEP<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>This is very similar to table <code>t4<\/code>. The storage_clauses on lines 6 and 7 ensure that the table and index blocks are stored in the KEEP buffer pool.&nbsp; This will reduce the physical I\/Os when querying the table.<\/p>\n\n\n\n<p>Accessing a single row requires 3 consistent gets as shown below.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Query t1<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SET LINESIZE 100\nSET AUTOTRACE ON\nSELECT * FROM t1 WHERE key = 42;\n\n       KEY VALUE                         \n---------- ------------------------------\n        42 UKPBW05FQ1                    \n\nExplain Plan\n-----------------------------------------------------------\n\nPLAN_TABLE_OUTPUT                                                                                   \n----------------------------------------------------------------------------------------------------\nPlan hash value: 2347959165                                                                         \n                                                                                                    \n-------------------------------------------------------------------------------------               \n| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |               \n-------------------------------------------------------------------------------------               \n|   0 | SELECT STATEMENT            |       |     1 |    24 |     2   (0)| 00:00:01 |               \n|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    24 |     2   (0)| 00:00:01 |               \n|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     1   (0)| 00:00:01 |               \n-------------------------------------------------------------------------------------               \n                                                                                                    \nPredicate Information (identified by operation id):                                                 \n---------------------------------------------------                                                 \n                                                                                                    \n   2 - access(&quot;KEY&quot;=42)                                                                             \n\nStatistics\n-----------------------------------------------------------\n              42  Requests to\/from client\n              42  SQL*Net roundtrips to\/from client\n               3  buffer is not pinned count\n             598  bytes received via SQL*Net from client\n           83426  bytes sent via SQL*Net to client\n               2  calls to get snapshot scn: kcmgss\n               2  calls to kcmgcs\n               3  consistent gets\n               3  consistent gets examination\n               3  consistent gets examination (fastpath)\n               3  consistent gets from cache\n               1  cursor authentications\n               2  execute count\n               1  index fetch by key\n           24576  logical read bytes from cache\n              42  non-idle wait count\n               2  opened cursors cumulative\n               1  opened cursors current\n               2  parse count (total)\n               1  rows fetched via callback\n               3  session logical reads\n               1  sorts (memory)\n            2011  sorts (rows)\n               1  table fetch by rowid\n              45  user calls\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> LINESIZE <\/span><span style=\"color: #B5CEA8\">100<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> AUTOTRACE <\/span><span style=\"color: #569CD6\">ON<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> t1 <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">KEY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">VALUE<\/span><span style=\"color: #D4D4D4\">                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------- ------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\"> UKPBW05FQ1                    <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Explain Plan<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-----------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">PLAN_TABLE_OUTPUT                                                                                   <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">----------------------------------------------------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Plan <\/span><span style=\"color: #569CD6\">hash<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">value<\/span><span style=\"color: #D4D4D4\">: <\/span><span style=\"color: #B5CEA8\">2347959165<\/span><span style=\"color: #D4D4D4\">                                                                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-------------------------------------------------------------------------------------               <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">| Id  | Operation                   | <\/span><span style=\"color: #569CD6\">Name<\/span><span style=\"color: #D4D4D4\">  | <\/span><span style=\"color: #569CD6\">Rows<\/span><span style=\"color: #D4D4D4\">  | Bytes | Cost (%CPU)| <\/span><span style=\"color: #569CD6\">Time<\/span><span style=\"color: #D4D4D4\">     |               <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-------------------------------------------------------------------------------------               <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">|   <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> | <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">STATEMENT<\/span><span style=\"color: #D4D4D4\">            |       |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |    <\/span><span style=\"color: #B5CEA8\">24<\/span><span style=\"color: #D4D4D4\"> |     <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |               <\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">|   <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |  <\/span><span style=\"color: #569CD6\">TABLE<\/span><span style=\"color: #D4D4D4\"> ACCESS <\/span><span style=\"color: #569CD6\">BY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">INDEX<\/span><span style=\"color: #D4D4D4\"> ROWID| T1    |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |    <\/span><span style=\"color: #B5CEA8\">24<\/span><span style=\"color: #D4D4D4\"> |     <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |               <\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">|*  <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\"> |   <\/span><span style=\"color: #569CD6\">INDEX<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">UNIQUE<\/span><span style=\"color: #D4D4D4\"> SCAN         | T1_PK |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |       |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |               <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-------------------------------------------------------------------------------------               <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">Predicate<\/span><span style=\"color: #D4D4D4\"> Information (identified <\/span><span style=\"color: #569CD6\">by<\/span><span style=\"color: #D4D4D4\"> operation id):                                                 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------------------------------------------------                                                 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\"> - access(<\/span><span style=\"color: #CE9178\">&quot;KEY&quot;<\/span><span style=\"color: #D4D4D4\">=<\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">)                                                                             <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">Statistics<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-----------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">  Requests <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\">\/<\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net roundtrips <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\">\/<\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">buffer<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">is<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> pinned count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">             <\/span><span style=\"color: #B5CEA8\">598<\/span><span style=\"color: #D4D4D4\">  bytes received via <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">           <\/span><span style=\"color: #B5CEA8\">83426<\/span><span style=\"color: #D4D4D4\">  bytes <\/span><span style=\"color: #569CD6\">sent<\/span><span style=\"color: #D4D4D4\"> via <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  calls <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">get<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">snapshot<\/span><span style=\"color: #D4D4D4\"> scn: kcmgss<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  calls <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> kcmgcs<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  consistent gets<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  consistent gets examination<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  consistent gets examination (fastpath)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  consistent gets <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> cache<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">cursor<\/span><span style=\"color: #D4D4D4\"> authentications<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">execute<\/span><span style=\"color: #D4D4D4\"> count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">index<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">fetch<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">by<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">key<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">           <\/span><span style=\"color: #B5CEA8\">24576<\/span><span style=\"color: #D4D4D4\">  logical <\/span><span style=\"color: #569CD6\">read<\/span><span style=\"color: #D4D4D4\"> bytes <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> cache<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">  non-idle wait count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  opened cursors cumulative<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  opened cursors current<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  parse <\/span><span style=\"color: #DCDCAA\">count<\/span><span style=\"color: #D4D4D4\"> (total)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">rows<\/span><span style=\"color: #D4D4D4\"> fetched via callback<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">session<\/span><span style=\"color: #D4D4D4\"> logical reads<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  sorts (memory)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #B5CEA8\">2011<\/span><span style=\"color: #D4D4D4\">  sorts (<\/span><span style=\"color: #569CD6\">rows<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">table<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">fetch<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">by<\/span><span style=\"color: #D4D4D4\"> rowid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">45<\/span><span style=\"color: #D4D4D4\">  user calls<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">2. Index-Organized Table<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Index-Organized Table t2<\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE TABLE t2 (\n   key    INTEGER            NOT NULL,\n   value  VARCHAR2(30 CHAR)  NOT NULL,\n   CONSTRAINT t2_pk PRIMARY KEY (key)\n)\nORGANIZATION INDEX\nSTORAGE (BUFFER_POOL KEEP);\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">CREATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">TABLE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">t2<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">INTEGER<\/span><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #569CD6\">NOT NULL<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">value<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">VARCHAR2<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">CHAR<\/span><span style=\"color: #D4D4D4\">)  <\/span><span style=\"color: #569CD6\">NOT NULL<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">CONSTRAINT<\/span><span style=\"color: #D4D4D4\"> t2_pk <\/span><span style=\"color: #569CD6\">PRIMARY KEY<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">ORGANIZATION <\/span><span style=\"color: #569CD6\">INDEX<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">STORAGE (BUFFER_POOL <\/span><span style=\"color: #569CD6\">KEEP<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>An index-organized table stores all its data within the index structure. This reduces the logical I\/Os by one when accessing a single row via the primary key. We also use the KEEP buffer pool to minimize physical I\/Os.<\/p>\n\n\n\n<p>Accessing a single row requires 2 consistent gets as shown below.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Query t2<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SET LINESIZE 100\nSET AUTOTRACE ON\nSELECT * FROM t2 WHERE key = 42;\n\n       KEY VALUE                         \n---------- ------------------------------\n        42 UKPBW05FQ1                    \n\nExplain Plan\n-----------------------------------------------------------\n\nPLAN_TABLE_OUTPUT                                                                                   \n----------------------------------------------------------------------------------------------------\nPlan hash value: 2827726509                                                                         \n                                                                                                    \n---------------------------------------------------------------------------                         \n| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                         \n---------------------------------------------------------------------------                         \n|   0 | SELECT STATEMENT  |       |     1 |    24 |     1   (0)| 00:00:01 |                         \n|*  1 |  INDEX UNIQUE SCAN| T2_PK |     1 |    24 |     1   (0)| 00:00:01 |                         \n---------------------------------------------------------------------------                         \n                                                                                                    \nPredicate Information (identified by operation id):                                                 \n---------------------------------------------------                                                 \n                                                                                                    \n   1 - access(&quot;KEY&quot;=42)                                                                             \n\nStatistics\n-----------------------------------------------------------\n              42  Requests to\/from client\n              42  SQL*Net roundtrips to\/from client\n               1  buffer is not pinned count\n             598  bytes received via SQL*Net from client\n           83426  bytes sent via SQL*Net to client\n               2  calls to get snapshot scn: kcmgss\n               2  calls to kcmgcs\n               2  consistent gets\n               2  consistent gets examination\n               2  consistent gets examination (fastpath)\n               2  consistent gets from cache\n               1  cursor authentications\n               2  execute count\n               1  index fetch by key\n           16384  logical read bytes from cache\n              42  non-idle wait count\n               2  opened cursors cumulative\n               1  opened cursors current\n               2  parse count (total)\n               2  session logical reads\n               1  sorts (memory)\n            2011  sorts (rows)\n              45  user calls\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> LINESIZE <\/span><span style=\"color: #B5CEA8\">100<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> AUTOTRACE <\/span><span style=\"color: #569CD6\">ON<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> t2 <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">KEY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">VALUE<\/span><span style=\"color: #D4D4D4\">                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------- ------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\"> UKPBW05FQ1                    <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Explain Plan<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-----------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">PLAN_TABLE_OUTPUT                                                                                   <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">----------------------------------------------------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Plan <\/span><span style=\"color: #569CD6\">hash<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">value<\/span><span style=\"color: #D4D4D4\">: <\/span><span style=\"color: #B5CEA8\">2827726509<\/span><span style=\"color: #D4D4D4\">                                                                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------------------------------------------------------------------------                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">| Id  | Operation         | <\/span><span style=\"color: #569CD6\">Name<\/span><span style=\"color: #D4D4D4\">  | <\/span><span style=\"color: #569CD6\">Rows<\/span><span style=\"color: #D4D4D4\">  | Bytes | Cost (%CPU)| <\/span><span style=\"color: #569CD6\">Time<\/span><span style=\"color: #D4D4D4\">     |                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------------------------------------------------------------------------                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">|   <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> | <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">STATEMENT<\/span><span style=\"color: #D4D4D4\">  |       |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |    <\/span><span style=\"color: #B5CEA8\">24<\/span><span style=\"color: #D4D4D4\"> |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |                         <\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">|*  <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |  <\/span><span style=\"color: #569CD6\">INDEX<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">UNIQUE<\/span><span style=\"color: #D4D4D4\"> SCAN| T2_PK |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |    <\/span><span style=\"color: #B5CEA8\">24<\/span><span style=\"color: #D4D4D4\"> |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------------------------------------------------------------------------                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">Predicate<\/span><span style=\"color: #D4D4D4\"> Information (identified <\/span><span style=\"color: #569CD6\">by<\/span><span style=\"color: #D4D4D4\"> operation id):                                                 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------------------------------------------------                                                 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> - access(<\/span><span style=\"color: #CE9178\">&quot;KEY&quot;<\/span><span style=\"color: #D4D4D4\">=<\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">)                                                                             <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">Statistics<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-----------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">  Requests <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\">\/<\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net roundtrips <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\">\/<\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">buffer<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">is<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> pinned count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">             <\/span><span style=\"color: #B5CEA8\">598<\/span><span style=\"color: #D4D4D4\">  bytes received via <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">           <\/span><span style=\"color: #B5CEA8\">83426<\/span><span style=\"color: #D4D4D4\">  bytes <\/span><span style=\"color: #569CD6\">sent<\/span><span style=\"color: #D4D4D4\"> via <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  calls <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">get<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">snapshot<\/span><span style=\"color: #D4D4D4\"> scn: kcmgss<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  calls <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> kcmgcs<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  consistent gets<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  consistent gets examination<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  consistent gets examination (fastpath)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  consistent gets <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> cache<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">cursor<\/span><span style=\"color: #D4D4D4\"> authentications<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">execute<\/span><span style=\"color: #D4D4D4\"> count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">index<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">fetch<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">by<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">key<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">           <\/span><span style=\"color: #B5CEA8\">16384<\/span><span style=\"color: #D4D4D4\">  logical <\/span><span style=\"color: #569CD6\">read<\/span><span style=\"color: #D4D4D4\"> bytes <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> cache<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">  non-idle wait count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  opened cursors cumulative<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  opened cursors current<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  parse <\/span><span style=\"color: #DCDCAA\">count<\/span><span style=\"color: #D4D4D4\"> (total)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">session<\/span><span style=\"color: #D4D4D4\"> logical reads<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  sorts (memory)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #B5CEA8\">2011<\/span><span style=\"color: #D4D4D4\">  sorts (<\/span><span style=\"color: #569CD6\">rows<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">45<\/span><span style=\"color: #D4D4D4\">  user calls<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">3. Single-Table Hash Cluster<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Single-Table Hash Cluster<\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE CLUSTER c3 (key INTEGER) \n   SIZE 256\n   SINGLE TABLE HASHKEYS 100000\n   STORAGE (BUFFER_POOL KEEP);\n\nCREATE TABLE t3 (\n   key    INTEGER            NOT NULL,\n   value  VARCHAR2(30 CHAR)  NOT NULL,\n   CONSTRAINT t3_pk PRIMARY KEY (key) -- to check uniqueness only\n)\nCLUSTER c3 (key);\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">CREATE<\/span><span style=\"color: #D4D4D4\"> CLUSTER c3 (<\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">INTEGER<\/span><span style=\"color: #D4D4D4\">) <\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">SIZE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">256<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">   SINGLE <\/span><span style=\"color: #569CD6\">TABLE<\/span><span style=\"color: #D4D4D4\"> HASHKEYS <\/span><span style=\"color: #B5CEA8\">100000<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   STORAGE (BUFFER_POOL <\/span><span style=\"color: #569CD6\">KEEP<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">CREATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">TABLE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">t3<\/span><span style=\"color: #D4D4D4\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">INTEGER<\/span><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #569CD6\">NOT NULL<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">value<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">VARCHAR2<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">CHAR<\/span><span style=\"color: #D4D4D4\">)  <\/span><span style=\"color: #569CD6\">NOT NULL<\/span><span style=\"color: #D4D4D4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">CONSTRAINT<\/span><span style=\"color: #D4D4D4\"> t3_pk <\/span><span style=\"color: #569CD6\">PRIMARY KEY<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #6A9955\">-- to check uniqueness only<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">CLUSTER c3 (<\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>A hash cluster is quite an old Oracle feature. I do not remember when it was introduced. It&#8217;s like it has always been around. The best option for primary key-based data retrieval, but a bit tricky to size. For sizing a hash cluster two parameters are important:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>HASHKEYS<\/li>\n<\/ul>\n\n\n\n<p>The <code>HASHKEYS<\/code>&nbsp;parameter (see line 3) defines the number of target buckets for the hash function. In this case, I chose 100,000. Without hash collisions, every key would be stored in its own bucket. But with this dataset, there are up to 5 keys which get stored in the same target bucket.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SIZE<\/li>\n<\/ul>\n\n\n\n<p>The <code>SIZE<\/code> parameter (see line 2) defines the number of bytes initially reserved for a target bucket of a hash function. Since I know that there are up to 5 rows within a bucket and I want a bucket to be stored completely in a single block, I chose a size large enough for 5 rows. This leads to 256 bytes so that I can store 32 buckets in a single 8K block.<\/p>\n\n\n\n<p>With these parameters, a cluster with 3,125 blocks will be created. It is probably a bit more. How much depends on the extent management configuration of the tablespace. This is optimal for our use case. But it is not optimal for full table scans, since we use 6-7 times more blocks than needed for a heap-organized table.<\/p>\n\n\n\n<p>Accessing a single row in a <strong>correctly sized single-table hash cluster<\/strong> requires just <strong>1 consistent get<\/strong> as shown below.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Query t3<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SET LINESIZE 100\nSET AUTOTRACE ON\nSELECT * FROM t3 WHERE key = 42;\n\n       KEY VALUE                         \n---------- ------------------------------\n        42 UKPBW05FQ1                    \n\nExplain Plan\n-----------------------------------------------------------\n\nPLAN_TABLE_OUTPUT                                                                                   \n----------------------------------------------------------------------------------------------------\nPlan hash value: 180373899                                                                          \n                                                                                                    \n--------------------------------------------------------------------------                          \n| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                          \n--------------------------------------------------------------------------                          \n|   0 | SELECT STATEMENT  |      |     1 |    24 |     1   (0)| 00:00:01 |                          \n|*  1 |  TABLE ACCESS HASH| T3   |     1 |    24 |     1   (0)| 00:00:01 |                          \n--------------------------------------------------------------------------                          \n                                                                                                    \nPredicate Information (identified by operation id):                                                 \n---------------------------------------------------                                                 \n                                                                                                    \n   1 - access(&quot;KEY&quot;=42)                                                                             \n\nStatistics\n-----------------------------------------------------------\n               1  DB time\n              42  Requests to\/from client\n              42  SQL*Net roundtrips to\/from client\n               1  buffer is not pinned count\n             598  bytes received via SQL*Net from client\n           83491  bytes sent via SQL*Net to client\n               2  calls to get snapshot scn: kcmgss\n               2  calls to kcmgcs\n               1  cluster key scan block gets\n               1  cluster key scans\n               1  consistent gets\n               1  consistent gets from cache\n               1  consistent gets pin\n               1  consistent gets pin (fastpath)\n               2  execute count\n            8192  logical read bytes from cache\n              42  non-idle wait count\n               2  opened cursors cumulative\n               1  opened cursors current\n               2  parse count (total)\n               1  session cursor cache hits\n               1  session logical reads\n               1  sorts (memory)\n            2011  sorts (rows)\n              45  user calls\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> LINESIZE <\/span><span style=\"color: #B5CEA8\">100<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> AUTOTRACE <\/span><span style=\"color: #569CD6\">ON<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> t3 <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">KEY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">VALUE<\/span><span style=\"color: #D4D4D4\">                         <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------- ------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\"> UKPBW05FQ1                    <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Explain Plan<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-----------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">PLAN_TABLE_OUTPUT                                                                                   <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">----------------------------------------------------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Plan <\/span><span style=\"color: #569CD6\">hash<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">value<\/span><span style=\"color: #D4D4D4\">: <\/span><span style=\"color: #B5CEA8\">180373899<\/span><span style=\"color: #D4D4D4\">                                                                          <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--------------------------------------------------------------------------                          <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">| Id  | Operation         | <\/span><span style=\"color: #569CD6\">Name<\/span><span style=\"color: #D4D4D4\"> | <\/span><span style=\"color: #569CD6\">Rows<\/span><span style=\"color: #D4D4D4\">  | Bytes | Cost (%CPU)| <\/span><span style=\"color: #569CD6\">Time<\/span><span style=\"color: #D4D4D4\">     |                          <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--------------------------------------------------------------------------                          <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">|   <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> | <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">STATEMENT<\/span><span style=\"color: #D4D4D4\">  |      |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |    <\/span><span style=\"color: #B5CEA8\">24<\/span><span style=\"color: #D4D4D4\"> |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |                          <\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">|*  <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |  <\/span><span style=\"color: #569CD6\">TABLE<\/span><span style=\"color: #D4D4D4\"> ACCESS <\/span><span style=\"color: #569CD6\">HASH<\/span><span style=\"color: #D4D4D4\">| T3   |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> |    <\/span><span style=\"color: #B5CEA8\">24<\/span><span style=\"color: #D4D4D4\"> |     <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">   (<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)| <\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">00<\/span><span style=\"color: #D4D4D4\">:<\/span><span style=\"color: #B5CEA8\">01<\/span><span style=\"color: #D4D4D4\"> |                          <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">--------------------------------------------------------------------------                          <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">Predicate<\/span><span style=\"color: #D4D4D4\"> Information (identified <\/span><span style=\"color: #569CD6\">by<\/span><span style=\"color: #D4D4D4\"> operation id):                                                 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------------------------------------------------                                                 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                                                                                    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\"> - access(<\/span><span style=\"color: #CE9178\">&quot;KEY&quot;<\/span><span style=\"color: #D4D4D4\">=<\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">)                                                                             <\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">Statistics<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">-----------------------------------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  DB <\/span><span style=\"color: #569CD6\">time<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">  Requests <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\">\/<\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net roundtrips <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\">\/<\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">buffer<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">is<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">not<\/span><span style=\"color: #D4D4D4\"> pinned count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">             <\/span><span style=\"color: #B5CEA8\">598<\/span><span style=\"color: #D4D4D4\">  bytes received via <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">           <\/span><span style=\"color: #B5CEA8\">83491<\/span><span style=\"color: #D4D4D4\">  bytes <\/span><span style=\"color: #569CD6\">sent<\/span><span style=\"color: #D4D4D4\"> via <\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">*Net <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> client<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  calls <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">get<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">snapshot<\/span><span style=\"color: #D4D4D4\"> scn: kcmgss<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  calls <\/span><span style=\"color: #569CD6\">to<\/span><span style=\"color: #D4D4D4\"> kcmgcs<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  cluster <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\"> scan <\/span><span style=\"color: #569CD6\">block<\/span><span style=\"color: #D4D4D4\"> gets<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  cluster <\/span><span style=\"color: #569CD6\">key<\/span><span style=\"color: #D4D4D4\"> scans<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  consistent gets<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  consistent gets <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> cache<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  consistent gets pin<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  consistent gets pin (fastpath)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">execute<\/span><span style=\"color: #D4D4D4\"> count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #B5CEA8\">8192<\/span><span style=\"color: #D4D4D4\">  logical <\/span><span style=\"color: #569CD6\">read<\/span><span style=\"color: #D4D4D4\"> bytes <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> cache<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">42<\/span><span style=\"color: #D4D4D4\">  non-idle wait count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  opened cursors cumulative<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  opened cursors current<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">  parse <\/span><span style=\"color: #DCDCAA\">count<\/span><span style=\"color: #D4D4D4\"> (total)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">session<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">cursor<\/span><span style=\"color: #D4D4D4\"> cache hits<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">session<\/span><span style=\"color: #D4D4D4\"> logical reads<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">               <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">  sorts (memory)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #B5CEA8\">2011<\/span><span style=\"color: #D4D4D4\">  sorts (<\/span><span style=\"color: #569CD6\">rows<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">              <\/span><span style=\"color: #B5CEA8\">45<\/span><span style=\"color: #D4D4D4\">  user calls<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>If you size the single-table hash cluster incorrectly, for example, by using <code>SIZE 64 HASHKEYS 500<\/code> you end up with more than 150 consistent gets to access a single row resulting in bad performance.<\/p>\n\n\n\n<p>Sizing a single-table hash cluster is really the key for best performance. However, for mixed workloads (PK access and other accesses to retrieve many rows) sizing becomes challenging and leads to a compromise. In such scenarios a heap-organized or index-organized table is easier to apply and may even be the better option.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Performance<\/h2>\n\n\n\n<p>Now let&#8217;s compare these four options using a PL\/SQL and a Java program reading the table fully via 100,000 queries. Not a smart way to do it, but it should show the performance impact of the different data structures.<\/p>\n\n\n<div class=\"accordion\"><div class=\"mfn-acc accordion_wrapper  toggle\"><br \/>\n<div class=\"question\"><div class=\"title\"><i class=\"icon-plus acc-icon-plus\" aria-hidden=\"true\"><\/i><i class=\"icon-minus acc-icon-minus\" aria-hidden=\"true\"><\/i>PL\/SQL procedure<\/div><div class=\"answer\"><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">PL\/SQL &#8211; 100,000 queries<\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE OR REPLACE PROCEDURE p (in_table_name VARCHAR2) IS\n   l_query VARCHAR2(1000 CHAR);\n   l_value VARCHAR2(30 CHAR);\n   l_start INTEGER;\n   l_end   INTEGER;\nBEGIN\n   l_start := dbms_utility.get_time();\n   l_query := 'SELECT value FROM ' || in_table_name || ' WHERE key = :key';\n   FOR i IN 1..100000 LOOP\n      EXECUTE IMMEDIATE l_query INTO l_value USING i;\n   END LOOP;\n   l_end := dbms_utility.get_time();\n   dbms_output.put_line('read 100000 rows from ' || in_table_name || ' in ' ||\n      to_char((l_end - l_start) \/ 100) || ' seconds.');\nEND p;\n\/\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">CREATE OR REPLACE<\/span><span style=\"color: #D4D4D4\"> PROCEDURE p (in_table_name <\/span><span style=\"color: #569CD6\">VARCHAR2<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">IS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #9CDCFE\">l_query<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">VARCHAR2<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">1000<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">CHAR<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #9CDCFE\">l_value<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">VARCHAR2<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">30<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">CHAR<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #9CDCFE\">l_start<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">INTEGER<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #9CDCFE\">l_end<\/span><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">INTEGER<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #9CDCFE\">l_start<\/span><span style=\"color: #D4D4D4\"> := <\/span><span style=\"color: #DCDCAA\">dbms_utility.<\/span><span style=\"color: #4EC9B0\">get_time<\/span><span style=\"color: #D4D4D4\">();<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #9CDCFE\">l_query<\/span><span style=\"color: #D4D4D4\"> := <\/span><span style=\"color: #CE9178\">&#39;SELECT value FROM &#39;<\/span><span style=\"color: #D4D4D4\"> || in_table_name || <\/span><span style=\"color: #CE9178\">&#39; WHERE key = :key&#39;<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #C586C0\">FOR<\/span><span style=\"color: #D4D4D4\"> i <\/span><span style=\"color: #569CD6\">IN<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">..<\/span><span style=\"color: #B5CEA8\">100000<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">LOOP<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #DCDCAA\">EXECUTE IMMEDIATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">l_query<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">INTO<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">l_value<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">USING<\/span><span style=\"color: #D4D4D4\"> i;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #C586C0\">END LOOP<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #9CDCFE\">l_end<\/span><span style=\"color: #D4D4D4\"> := <\/span><span style=\"color: #DCDCAA\">dbms_utility.<\/span><span style=\"color: #4EC9B0\">get_time<\/span><span style=\"color: #D4D4D4\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #DCDCAA\">dbms_output.<\/span><span style=\"color: #4EC9B0\">put_line<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #CE9178\">&#39;read 100000 rows from &#39;<\/span><span style=\"color: #D4D4D4\"> || in_table_name || <\/span><span style=\"color: #CE9178\">&#39; in &#39;<\/span><span style=\"color: #D4D4D4\"> ||<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #DCDCAA\">to_char<\/span><span style=\"color: #D4D4D4\">((<\/span><span style=\"color: #9CDCFE\">l_end<\/span><span style=\"color: #D4D4D4\"> - <\/span><span style=\"color: #9CDCFE\">l_start<\/span><span style=\"color: #D4D4D4\">) \/ <\/span><span style=\"color: #B5CEA8\">100<\/span><span style=\"color: #D4D4D4\">) || <\/span><span style=\"color: #CE9178\">&#39; seconds.&#39;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">END<\/span><span style=\"color: #D4D4D4\"> p;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n<p><\/div><\/div>\n<br \/>\n<div class=\"question\"><div class=\"title\"><i class=\"icon-plus acc-icon-plus\" aria-hidden=\"true\"><\/i><i class=\"icon-minus acc-icon-minus\" aria-hidden=\"true\"><\/i>Java program<\/div><div class=\"answer\"><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Java &#8211; 100,000 queries<\/span><span role=\"button\" tabindex=\"0\" data-code=\"import java.sql.Connection;\nimport java.sql.DriverManager;\nimport java.sql.PreparedStatement;\nimport java.sql.ResultSet;\nimport java.sql.SQLException;\nimport oracle.jdbc.driver.OracleDriver;\npublic class J {\n   private static boolean isRunningInDatabase() {\n      return System.getProperty(&quot;oracle.jserver.version&quot;) != null;\n   }\n   public static void m(String tableName) throws SQLException {\n      Connection conn;\n      if (isRunningInDatabase()) {\n         conn = new OracleDriver().defaultConnection();\n      } else {\n         conn = DriverManager.getConnection(\n            &quot;jdbc:oracle:thin:@\/\/localhost:1521\/odb.docker&quot;, &quot;tvdca&quot;, &quot;tvdca&quot;);\n      }\n      conn.setAutoCommit(false);\n      long start = System.currentTimeMillis();\n      String query = &quot;SELECT value FROM &quot; + tableName + &quot; WHERE key = ?&quot;;      \n      PreparedStatement ps = conn.prepareStatement(query);\n      for (long i = 1; i &lt;= 100000; i++) {\n         ps.setLong(1, i);\n         ResultSet rs = ps.executeQuery();\n         while (rs.next()) {\n            rs.getString(&quot;value&quot;);\n         }\n         rs.close();\n      }\n      ps.close();\n      if (!isRunningInDatabase()) {\n         conn.close();\n      }\n      long end = System.currentTimeMillis();\n      System.out.println(&quot;read 100000 rows from &quot; + tableName + &quot; in &quot; +\n         String.valueOf((double) (end-start)\/1000) + &quot; seconds.&quot;);      \n   }\n   public static void main(String&#091;&#093; args) throws SQLException {\n      m(args&#091;0&#093;);\n   }\n}\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">import<\/span><span style=\"color: #D4D4D4\"> java.sql.Connection;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">import<\/span><span style=\"color: #D4D4D4\"> java.sql.DriverManager;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">import<\/span><span style=\"color: #D4D4D4\"> java.sql.PreparedStatement;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">import<\/span><span style=\"color: #D4D4D4\"> java.sql.ResultSet;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">import<\/span><span style=\"color: #D4D4D4\"> java.sql.SQLException;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">import<\/span><span style=\"color: #D4D4D4\"> oracle.jdbc.driver.OracleDriver;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">public<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">class<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #4EC9B0\">J<\/span><span style=\"color: #D4D4D4\"> {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">private<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">static<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #4EC9B0\">boolean<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">isRunningInDatabase<\/span><span style=\"color: #D4D4D4\">() {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #C586C0\">return<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">System<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">getProperty<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #CE9178\">&quot;oracle.jserver.version&quot;<\/span><span style=\"color: #D4D4D4\">) != <\/span><span style=\"color: #569CD6\">null<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   }<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">public<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">static<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #4EC9B0\">void<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">m<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">tableName<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">throws<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #4EC9B0\">SQLException<\/span><span style=\"color: #D4D4D4\"> {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #4EC9B0\">Connection<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">conn<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #C586C0\">if<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #DCDCAA\">isRunningInDatabase<\/span><span style=\"color: #D4D4D4\">()) {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         conn = <\/span><span style=\"color: #C586C0\">new<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">OracleDriver<\/span><span style=\"color: #D4D4D4\">().<\/span><span style=\"color: #DCDCAA\">defaultConnection<\/span><span style=\"color: #D4D4D4\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      } <\/span><span style=\"color: #C586C0\">else<\/span><span style=\"color: #D4D4D4\"> {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         conn = <\/span><span style=\"color: #9CDCFE\">DriverManager<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">getConnection<\/span><span style=\"color: #D4D4D4\">(<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #CE9178\">&quot;jdbc:oracle:thin:@\/\/localhost:1521\/odb.docker&quot;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&quot;tvdca&quot;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&quot;tvdca&quot;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      }<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #9CDCFE\">conn<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">setAutoCommit<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #569CD6\">false<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #4EC9B0\">long<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">start<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #9CDCFE\">System<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">currentTimeMillis<\/span><span style=\"color: #D4D4D4\">();<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">query<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #CE9178\">&quot;SELECT value FROM &quot;<\/span><span style=\"color: #D4D4D4\"> + tableName + <\/span><span style=\"color: #CE9178\">&quot; WHERE key = ?&quot;<\/span><span style=\"color: #D4D4D4\">;      <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #4EC9B0\">PreparedStatement<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">ps<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #9CDCFE\">conn<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">prepareStatement<\/span><span style=\"color: #D4D4D4\">(query);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #C586C0\">for<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #4EC9B0\">long<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">i<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">; i &lt;= <\/span><span style=\"color: #B5CEA8\">100000<\/span><span style=\"color: #D4D4D4\">; i++) {<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">         <\/span><span style=\"color: #9CDCFE\">ps<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">setLong<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">1<\/span><span style=\"color: #D4D4D4\">, i);<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">         <\/span><span style=\"color: #4EC9B0\">ResultSet<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">rs<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #9CDCFE\">ps<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">executeQuery<\/span><span style=\"color: #D4D4D4\">();<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">         <\/span><span style=\"color: #C586C0\">while<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #9CDCFE\">rs<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">next<\/span><span style=\"color: #D4D4D4\">()) {<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #9CDCFE\">rs<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">getString<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #CE9178\">&quot;value&quot;<\/span><span style=\"color: #D4D4D4\">);<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">         }<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         <\/span><span style=\"color: #9CDCFE\">rs<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">close<\/span><span style=\"color: #D4D4D4\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      }<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #9CDCFE\">ps<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">close<\/span><span style=\"color: #D4D4D4\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #C586C0\">if<\/span><span style=\"color: #D4D4D4\"> (!<\/span><span style=\"color: #DCDCAA\">isRunningInDatabase<\/span><span style=\"color: #D4D4D4\">()) {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         <\/span><span style=\"color: #9CDCFE\">conn<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">close<\/span><span style=\"color: #D4D4D4\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      }<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #4EC9B0\">long<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">end<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #9CDCFE\">System<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">currentTimeMillis<\/span><span style=\"color: #D4D4D4\">();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #9CDCFE\">System<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #9CDCFE\">out<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">println<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #CE9178\">&quot;read 100000 rows from &quot;<\/span><span style=\"color: #D4D4D4\"> + tableName + <\/span><span style=\"color: #CE9178\">&quot; in &quot;<\/span><span style=\"color: #D4D4D4\"> +<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         <\/span><span style=\"color: #9CDCFE\">String<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">valueOf<\/span><span style=\"color: #D4D4D4\">((<\/span><span style=\"color: #4EC9B0\">double<\/span><span style=\"color: #D4D4D4\">) (end-start)\/<\/span><span style=\"color: #B5CEA8\">1000<\/span><span style=\"color: #D4D4D4\">) + <\/span><span style=\"color: #CE9178\">&quot; seconds.&quot;<\/span><span style=\"color: #D4D4D4\">);      <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   }<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">public<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">static<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #4EC9B0\">void<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">main<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\">[] <\/span><span style=\"color: #9CDCFE\">args<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #569CD6\">throws<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #4EC9B0\">SQLException<\/span><span style=\"color: #D4D4D4\"> {<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #DCDCAA\">m<\/span><span style=\"color: #D4D4D4\">(args[<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">]);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   }<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">}<\/span><\/span><\/code><\/pre><\/div>\n\n\n<p><\/div><\/div>\n<br \/>\n<\/div><\/div>\n\n\n\n\n<p>Both programs are doing the same work. They get a table name as a parameter and retrieve every row in the table via primary key access. The PL\/SQL procedure runs within the database and the Java program outside of the database. The Java program needs to do 100,000 network round trips. For the PL\/SQL program, these are just context switches between the PL\/SQL and SQL engine. Therefore, the PL\/SQL procedure calls are expected to be faster than the Java program executions.<\/p>\n\n\n\n<p>Each program has been called five times for every table. The slowest and the fastest runtimes have been ignored. The average of the remaining three runtimes is used for the following chart.<\/p>\n\n\n\n<p>The results look plausible for &#8220;t1 &#8211; heap-organized&#8221;, &#8220;t2 &#8211; index-organized&#8221; and &#8220;t3 &#8211; hash cluster&#8221;.&nbsp;But the runtimes for &#8220;t4 &#8211; memoptimized&#8221; are strange. For PL\/SQL and Java. This requires further analysis.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/Performance_Single_Row_Query.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1012\" height=\"612\" src=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/Performance_Single_Row_Query.png\" alt=\"\" class=\"wp-image-8463\" srcset=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/Performance_Single_Row_Query.png 1012w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/Performance_Single_Row_Query-300x181.png 300w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/Performance_Single_Row_Query-768x464.png 768w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/Performance_Single_Row_Query-241x146.png 241w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/Performance_Single_Row_Query-50x30.png 50w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/Performance_Single_Row_Query-124x75.png 124w\" sizes=\"auto, (max-width:767px) 480px, (max-width:1012px) 100vw, 1012px\" \/><\/a><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Analyzing PL\/SQL Runtime for &#8220;t4 &#8211; memoptimized&#8221;<\/h3>\n\n\n\n<p>Let&#8217;s execute the PL\/SQL procedure in a fresh session again.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Analyzing PL\/SQL Runtime for t4 &#8211; memoptimized<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SQL&gt; connect tvdca\/tvdca@odb\nConnected.\nSQL&gt; set serveroutput on\nSQL&gt; exec p('t4')\nread 100000 rows from t4 in 6.72 seconds.\n\n\nPL\/SQL procedure successfully completed.\n\nSQL&gt; SELECT n.name, s.sid, s.value\n  2    FROM v$sesstat s \n  3    JOIN v$statname n\n  4      ON n.statistic# = s.statistic#\n  5   WHERE n.name in ('consistent gets','memopt r lookups', 'memopt r hits')\n  6     AND s.value &gt; 0\n  7     AND s.sid = sys_context ('USERENV', 'SID')\n  8   ORDER BY s.value desc;\n\nNAME                                                                    SID      VALUE\n---------------------------------------------------------------- ---------- ----------\nconsistent gets                                                         273     300025\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">&gt; <\/span><span style=\"color: #569CD6\">connect<\/span><span style=\"color: #D4D4D4\"> tvdca\/tvdca@odb<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Connected.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">&gt; <\/span><span style=\"color: #569CD6\">set<\/span><span style=\"color: #D4D4D4\"> serveroutput <\/span><span style=\"color: #569CD6\">on<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">&gt; <\/span><span style=\"color: #569CD6\">exec<\/span><span style=\"color: #D4D4D4\"> p(<\/span><span style=\"color: #CE9178\">&#39;t4&#39;<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">read<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">100000<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">rows<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">from<\/span><span style=\"color: #D4D4D4\"> t4 <\/span><span style=\"color: #569CD6\">in<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">6<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #B5CEA8\">72<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">seconds<\/span><span style=\"color: #D4D4D4\">.<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">PL\/<\/span><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">procedure<\/span><span style=\"color: #D4D4D4\"> successfully completed.<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">SQL<\/span><span style=\"color: #D4D4D4\">&gt; <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> n.name, s.sid, s.value<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #B5CEA8\">2<\/span><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> v$sesstat s <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #B5CEA8\">3<\/span><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">JOIN<\/span><span style=\"color: #D4D4D4\"> v$statname n<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #B5CEA8\">4<\/span><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">ON<\/span><span style=\"color: #D4D4D4\"> n.statistic# = s.statistic#<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #B5CEA8\">5<\/span><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> n.name <\/span><span style=\"color: #569CD6\">in<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #CE9178\">&#39;consistent gets&#39;<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #CE9178\">&#39;memopt r lookups&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;memopt r hits&#39;<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #B5CEA8\">6<\/span><span style=\"color: #D4D4D4\">     <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> s.value &gt; <\/span><span style=\"color: #B5CEA8\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #B5CEA8\">7<\/span><span style=\"color: #D4D4D4\">     <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> s.sid = sys_context (<\/span><span style=\"color: #CE9178\">&#39;USERENV&#39;<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #CE9178\">&#39;SID&#39;<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #B5CEA8\">8<\/span><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> s.value <\/span><span style=\"color: #569CD6\">desc<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">NAME<\/span><span style=\"color: #D4D4D4\">                                                                    <\/span><span style=\"color: #569CD6\">SID<\/span><span style=\"color: #D4D4D4\">      <\/span><span style=\"color: #569CD6\">VALUE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #6A9955\">---------------------------------------------------------------- ---------- ----------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">consistent gets                                                         <\/span><span style=\"color: #B5CEA8\">273<\/span><span style=\"color: #D4D4D4\">     <\/span><span style=\"color: #B5CEA8\">300025<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>Line 21 is interesting. 300,025 <code>consistent gets<\/code>. These are 300,000 more than expected. And there are no values for the wait events <code>memopt r lookups<\/code> and <code>memopt r hits<\/code>. This means Oracle uses a conventional access path instead of the MemOptimized RowStore. It&#8217;s the same execution plan as for &#8220;t1 &#8211; heap-organized&#8221; and the execution times are similar as well. As mentioned in the Concept chapter in the beginning, the MemOptimized RowStore cannot be used from PL\/SQL.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Analyzing Java Runtime for &#8220;t4 &#8211; memoptimized&#8221;<\/h3>\n\n\n\n<p>To analyze the problem I&#8217;ve enabled SQL trace for the Java session. I was surprised by the runtime. Significantly faster with SQL trace enabled? The tkprof output revealed the reason. Here is an excerpt:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(234, 191, 191, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Tkprof Excerpt<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SQL ID: 03z4487kpgfv3 Plan Hash: 1143490106\n\nSELECT value \nFROM\n t4 WHERE key = :1 \n\n\ncall     count       cpu    elapsed       disk      query    current        rows\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\nParse        1      0.00       0.00          0          0          0           0\nExecute 100000      0.65       7.10          0          0          0           0\nFetch   100000      0.95       7.36          0     300000          0      100000\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\ntotal   200001      1.60      14.47          0     300000          0      100000\n\nMisses in library cache during parse: 1\nMisses in library cache during execute: 1\nOptimizer mode: ALL_ROWS\nParsing user id: 152  \nNumber of plan statistics captured: 1\n\nRows (1st) Rows (avg) Rows (max)  Row Source Operation\n---------- ---------- ----------  ---------------------------------------------------\n         1          1          1  TABLE ACCESS BY INDEX ROWID T4 (cr=3 pr=0 pw=0 time=599 us starts=1 cost=2 size=24 card=1)\n         1          1          1   INDEX UNIQUE SCAN T4_PK (cr=2 pr=0 pw=0 time=562 us starts=1 cost=1 size=0 card=1)(object id 87514)\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">SQL ID: 03z4487kpgfv3 Plan Hash: 1143490106<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">SELECT value <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">FROM<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> t4 WHERE key = :1 <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">call     count       cpu    elapsed       disk      query    current        rows<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Parse        1      0.00       0.00          0          0          0           0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Execute 100000      0.65       7.10          0          0          0           0<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">Fetch   100000      0.95       7.36          0     300000          0      100000<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">------- ------  -------- ---------- ---------- ---------- ----------  ----------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">total   200001      1.60      14.47          0     300000          0      100000<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Misses in library cache during parse: 1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Misses in library cache during execute: 1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Optimizer mode: ALL_ROWS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Parsing user id: 152  <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Number of plan statistics captured: 1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"><\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">Rows (1st) Rows (avg) Rows (max)  Row Source Operation<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">---------- ---------- ----------  ---------------------------------------------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">         1          1          1  TABLE ACCESS BY INDEX ROWID T4 (cr=3 pr=0 pw=0 time=599 us starts=1 cost=2 size=24 card=1)<\/span><\/span>\n<span class=\"line cbp-line-highlight\"><span style=\"color: #D4D4D4\">         1          1          1   INDEX UNIQUE SCAN T4_PK (cr=2 pr=0 pw=0 time=562 us starts=1 cost=1 size=0 card=1)(object id 87514)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p>See the highlighted lines 12 and 25. We are back to a conventional access path as soon as we enable SQL trace. The runtime is similar to &#8220;t1 &#8211; heap-organized&#8221; plus some SQL trace overhead. SQL trace is a dead end.<\/p>\n\n\n\n<p>Let&#8217;s try flame graphs. Luca Canali wrote an excellent blog post about <a href=\"https:\/\/db-blog.web.cern.ch\/blog\/luca-canali\/2014-06-flame-graphs-oracle\">flame graphs for Oracle<\/a>. I&#8217;ve followed Luca&#8217;s instructions to produce some flame graphs. The PNGs are shown below. You may open the SVG variant via the link in a new browser tab.<\/p>\n\n\n<div class=\"jq-tabs tabs_wrapper tabs_horizontal\"><ul><li><a href=\"#tab-69ff2144e9794-1\">t1 - heap-organized<\/a><\/li><li><a href=\"#tab-69ff2144e9794-2\">t4 - memoptimized<\/a><\/li><li><a href=\"#tab-69ff2144e9794-3\">t1 - heap-organized (marked)<\/a><\/li><li><a href=\"#tab-69ff2144e9794-4\">t4 - memoptimized (marked)<\/a><\/li><\/ul><div id=\"tab-69ff2144e9794-1\" ><\/p>\n<p><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1.svg\" target=\"_blank\" rel=\"noopener noreferrer\">Flame Graph &#8220;t1 &#8211; heap-organized&#8221; (SVG)<\/a><\/p>\n<p><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-8423\" src=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1.png\" alt=\"\" width=\"1200\" height=\"502\" srcset=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1.png 1200w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1-300x126.png 300w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1-768x321.png 768w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1-1024x428.png 1024w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1-260x109.png 260w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1-50x21.png 50w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1-150x63.png 150w\" sizes=\"auto, (max-width:767px) 480px, (max-width:1200px) 100vw, 1200px\" \/><\/a><\/p>\n<p><\/div><div id=\"tab-69ff2144e9794-2\" ><\/p>\n<p><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4.svg\" target=\"_blank\" rel=\"noopener noreferrer\">Flame Graph &#8220;t4 &#8211; memoptimized&#8221; (SVG)<\/a><\/p>\n<p><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4.png\"><img wpfc-lazyload-disable=\"true\" loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-8419\" src=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4.png\" alt=\"\" width=\"1200\" height=\"1270\" \/><\/a><\/p>\n<p><\/div><div id=\"tab-69ff2144e9794-3\" ><\/p>\n<p><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1.svg\" target=\"_blank\" rel=\"noopener noreferrer\">Flame Graph &#8220;t1 &#8211; heap-organized&#8221; (SVG)<\/a><\/p>\n<p><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1_marked.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-8450\" src=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1_marked.png\" alt=\"\" width=\"1200\" height=\"502\" srcset=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1_marked.png 1200w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1_marked-300x126.png 300w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1_marked-768x321.png 768w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1_marked-1024x428.png 1024w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1_marked-260x109.png 260w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1_marked-50x21.png 50w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t1_marked-150x63.png 150w\" sizes=\"auto, (max-width:767px) 480px, (max-width:1200px) 100vw, 1200px\" \/><\/a><\/p>\n<p><\/div><div id=\"tab-69ff2144e9794-4\" ><\/p>\n<p><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4.svg\" target=\"_blank\" rel=\"noopener noreferrer\">Flame Graph &#8220;t4 &#8211; memoptimized&#8221; (SVG)<\/a><\/p>\n<p><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4_marked.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-8451\" src=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4_marked.png\" alt=\"\" width=\"1200\" height=\"486\" srcset=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4_marked.png 1200w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4_marked-300x122.png 300w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4_marked-768x311.png 768w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4_marked-1024x415.png 1024w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4_marked-260x105.png 260w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4_marked-50x20.png 50w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2018\/06\/t4_marked-150x61.png 150w\" sizes=\"auto, (max-width:767px) 480px, (max-width:1200px) 100vw, 1200px\" \/><\/a><\/p>\n<p><\/div><\/div>\n\n\n\n<p>The average runtime of &#8220;t4 &#8211; memoptimized&#8221; was 77.33 seconds and the average runtime of &#8220;t1 &#8211; heap-organized&#8221; was 48.21 seconds. That&#8217;s a difference of about 30 seconds. How can we find the functions in &#8220;t4 &#8211; memoptimized&#8221; which are contributing the most to this difference?<\/p>\n\n\n\n<p>First, we assume the amount of sampled data is good enough to represent the load pattern. Second, we assume that we can calculate the runtime of a function based on the percentage shown in the flame graph. This allows the findings to be presented as follows:<\/p>\n\n\n\n<table id=\"tablepress-13\" class=\"tablepress tablepress-id-13\">\n<thead>\n<tr class=\"row-1\">\n\t<th class=\"column-1\">Function<\/th><th class=\"column-2\">t1 Percent<\/th><th class=\"column-3\">t4 Percent<\/th><th class=\"column-4\">t1 Time<\/th><th class=\"column-5\">t4 Time<\/th><th class=\"column-6\">Difference<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"row-striping row-hover\">\n<tr class=\"row-2\">\n\t<td class=\"column-1\">opitsk<\/td><td class=\"column-2\">99.53%<\/td><td class=\"column-3\">99.37%<\/td><td class=\"column-4\">47.98<\/td><td class=\"column-5\">76.84<\/td><td class=\"column-6\">-28.86<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<!-- #tablepress-13 from cache -->\n\n\n<p>Third, we are looking for functions on a reasonable level in the call stack. In this case, it is not helpful to state that the <code>opitsk<\/code> function is slower in &#8220;t4 &#8211; memoptimized&#8221;. Reasonable means, that the identified function are sampled in different call stacks. This is avoiding double counting.<\/p>\n\n\n\n<p>The following table lists 6 functions that account for more than two thirds of the total runtime of &#8220;t4 -memoptimized&#8221;. In total, they consume 36.66 seconds more than in &#8220;t1 &#8211; heap-organized&#8221;. In the tab pane above, there are flame graph variants named &#8220;marked&#8221;, highlighting these functions.<\/p>\n\n\n\n<table id=\"tablepress-14\" class=\"tablepress tablepress-id-14\">\n<thead>\n<tr class=\"row-1\">\n\t<th class=\"column-1\">Function<\/th><th class=\"column-2\">t1 Percent<\/th><th class=\"column-3\">t4 Percent<\/th><th class=\"column-4\">t1 Time<\/th><th class=\"column-5\">t4 Time<\/th><th class=\"column-6\">Difference<\/th><th class=\"column-7\">Notes<\/th>\n<\/tr>\n<\/thead>\n<tbody class=\"row-striping row-hover\">\n<tr class=\"row-2\">\n\t<td class=\"column-1\">opikndf2 (from optisk)<\/td><td class=\"column-2\">17.64%<\/td><td class=\"column-3\">30.22%<\/td><td class=\"column-4\">8.50<\/td><td class=\"column-5\">23.37<\/td><td class=\"column-6\">-14.86<\/td><td class=\"column-7\"><\/td>\n<\/tr>\n<tr class=\"row-3\">\n\t<td class=\"column-1\">kksumc (from SELECT FETCH:)<\/td><td class=\"column-2\">6.10%<\/td><td class=\"column-3\">19.18%<\/td><td class=\"column-4\">2.94<\/td><td class=\"column-5\">14.83<\/td><td class=\"column-6\">-11.89<\/td><td class=\"column-7\"><\/td>\n<\/tr>\n<tr class=\"row-4\">\n\t<td class=\"column-1\">kpoxihFetch<\/td><td class=\"column-2\">0.00%<\/td><td class=\"column-3\">4.86%<\/td><td class=\"column-4\">0.00<\/td><td class=\"column-5\">3.76<\/td><td class=\"column-6\">-3.76<\/td><td class=\"column-7\">Fetch from memoptimize buffer area, n\/a in t1<\/td>\n<\/tr>\n<tr class=\"row-5\">\n\t<td class=\"column-1\">ksupop<\/td><td class=\"column-2\">6.24%<\/td><td class=\"column-3\">7.40%<\/td><td class=\"column-4\">3.01<\/td><td class=\"column-5\">5.72<\/td><td class=\"column-6\">-2.71<\/td><td class=\"column-7\"><\/td>\n<\/tr>\n<tr class=\"row-6\">\n\t<td class=\"column-1\">ksupucg<\/td><td class=\"column-2\">3.00%<\/td><td class=\"column-3\">4.24%<\/td><td class=\"column-4\">1.45<\/td><td class=\"column-5\">3.28<\/td><td class=\"column-6\">-1.83<\/td><td class=\"column-7\"><\/td>\n<\/tr>\n<tr class=\"row-7\">\n\t<td class=\"column-1\">kpoxihLookup<\/td><td class=\"column-2\">0.00%<\/td><td class=\"column-3\">2.07%<\/td><td class=\"column-4\">0.00<\/td><td class=\"column-5\">1.60<\/td><td class=\"column-6\">-1.60<\/td><td class=\"column-7\">Hash index lookup, n\/a in t1<\/td>\n<\/tr>\n<\/tbody>\n<tfoot>\n<tr class=\"row-8\">\n\t<th class=\"column-1\">Total<\/th><th class=\"column-2\">32.98%<\/th><th class=\"column-3\">67.97%<\/th><th class=\"column-4\">15.90<\/th><th class=\"column-5\">52.56<\/th><th class=\"column-6\">-36.66<\/th><td class=\"column-7\"><\/td>\n<\/tr>\n<\/tfoot>\n<\/table>\n<!-- #tablepress-14 from cache -->\n\n\n<p>Someone with access to the source code of these functions could dig deeper, but I can&#8217;t. So I have to stop here. I don&#8217;t know how much my unsupported environment contributed to this bad performance. I just can hope it is a lot.<\/p>\n\n\n\n<p><em>Updated on 2018-06-19: After some research I found the root cause. OCI is a prerequisite for getting good performance out of the MemOptimized RowStore. The <a href=\"https:\/\/www.salvis.com\/blog\/2018\/06\/19\/memoptimized-rowstore-in-oracle-database-18c-with-oci\/\">succeeding blog post<\/a> gives you more details about that.<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>If you access Oracle Databases with #NoPlsql applications, the MemOptimized RowStore might be an interesting feature to improve the performance when querying single rows from single tables via the primary key. If you access Oracle Databases with #SmartDB applications, you cannot use this feature, since it does not work when the SQL query is called from PL\/SQL. That&#8217;s sad. However, it is disturbing that activating SQL trace or setting&nbsp;<code>STATISTICS_LEVEL = 'ALL'<\/code>&nbsp;deactivates the MemOptimized RowStore. I hope this will be fixed in a future release.<\/p>\n\n\n\n<p><em>Updated on 2018-06-19, added OCI as a prerequisite and <a href=\"https:\/\/www.salvis.com\/blog\/2018\/06\/19\/memoptimized-rowstore-in-oracle-database-18c-with-oci\/\">link<\/a> to succeeding blog post.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The MemOptimized RowStore introduced in Oracle Database 18c is designed to improve the performance of simple queries accessing data via primary key columns only. An example of such a query is SELECT value FROM t WHERE key = :key&nbsp;where key is the only primary key column of table t. This feature is<span class=\"excerpt-hellip\"> [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":9513,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[107,25,13,85],"class_list":["post-8330","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-java","tag-performance","tag-plsql","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>MemOptimized RowStore in Oracle Database 18c - Philipp Salvisberg&#039;s Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MemOptimized RowStore in Oracle Database 18c - Philipp Salvisberg&#039;s Blog\" \/>\n<meta property=\"og:description\" content=\"The MemOptimized RowStore introduced in Oracle Database 18c is designed to improve the performance of simple queries accessing data via primary key columns only. An example of such a query is SELECT value FROM t WHERE key = :key&nbsp;where key is the only primary key column of table t. This feature is [\u2026]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg&#039;s Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-09T23:40:11+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-08T01:10:34+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/02\/memoptimize_pool.png\" \/>\n\t<meta property=\"og:image:width\" content=\"414\" \/>\n\t<meta property=\"og:image:height\" content=\"537\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"13 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"MemOptimized RowStore in Oracle Database 18c\",\"datePublished\":\"2018-06-09T23:40:11+00:00\",\"dateModified\":\"2023-11-08T01:10:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/\"},\"wordCount\":2540,\"commentCount\":5,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/memoptimize_pool.png\",\"keywords\":[\"Java\",\"Performance\",\"PL\\\/SQL\",\"SQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/\",\"name\":\"MemOptimized RowStore in Oracle Database 18c - Philipp Salvisberg&#039;s Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/memoptimize_pool.png\",\"datePublished\":\"2018-06-09T23:40:11+00:00\",\"dateModified\":\"2023-11-08T01:10:34+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/memoptimize_pool.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/memoptimize_pool.png\",\"width\":414,\"height\":537},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2018\\\/06\\\/10\\\/memoptimized-rowstore-in-oracle-database-18c\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MemOptimized RowStore in Oracle Database 18c\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg&#039;s Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MemOptimized RowStore in Oracle Database 18c - Philipp Salvisberg&#039;s Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/","og_locale":"en_US","og_type":"article","og_title":"MemOptimized RowStore in Oracle Database 18c - Philipp Salvisberg&#039;s Blog","og_description":"The MemOptimized RowStore introduced in Oracle Database 18c is designed to improve the performance of simple queries accessing data via primary key columns only. An example of such a query is SELECT value FROM t WHERE key = :key&nbsp;where key is the only primary key column of table t. This feature is [\u2026]","og_url":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/","og_site_name":"Philipp Salvisberg&#039;s Blog","article_published_time":"2018-06-09T23:40:11+00:00","article_modified_time":"2023-11-08T01:10:34+00:00","og_image":[{"width":414,"height":537,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/02\/memoptimize_pool.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"13 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"MemOptimized RowStore in Oracle Database 18c","datePublished":"2018-06-09T23:40:11+00:00","dateModified":"2023-11-08T01:10:34+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/"},"wordCount":2540,"commentCount":5,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/02\/memoptimize_pool.png","keywords":["Java","Performance","PL\/SQL","SQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/","url":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/","name":"MemOptimized RowStore in Oracle Database 18c - Philipp Salvisberg&#039;s Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/02\/memoptimize_pool.png","datePublished":"2018-06-09T23:40:11+00:00","dateModified":"2023-11-08T01:10:34+00:00","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/02\/memoptimize_pool.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/02\/memoptimize_pool.png","width":414,"height":537},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2018\/06\/10\/memoptimized-rowstore-in-oracle-database-18c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MemOptimized RowStore in Oracle Database 18c"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg&#039;s Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/8330","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=8330"}],"version-history":[{"count":132,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/8330\/revisions"}],"predecessor-version":[{"id":12636,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/8330\/revisions\/12636"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/9513"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=8330"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=8330"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=8330"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}