ALTER INDEX idx_lo_ordkey MODIFY PARTITION p45 unusable;
Marking the indexes unusable on these 3 partition means that the indexes won’t be maintained during any DML operations on these partitions. Since data only changes in these most recent partitions, we still get all of the benefits we would have gotten if the index had been dropped altogether.
If the date range in our query is changed so that it only accesses older data, then the Optimizer picks our original index driven plans:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
The PARTITION_SALES table is range partitioned by month on LO_ORDERDATE. In order to ensure that the query performs efficiently in Oracle Database 11g a local index has been created on the LO_ORDERKEY column. Below is the execution plan for the statement in 11g:
After upgrading to Oracle Database 12c, I only have enough memory to accommodate the latest 3 months of data in the IM column store. The following query shows the latest 3 months populated into the IM column store:
Rather than dropping the index on the LO_ORDERKEY column completely, I used the following command to mark the index unusable on the 3 latest partitions:
ALTER INDEX idx_lo_ordkey MODIFY PARTITION p45 unusable;
Marking the indexes unusable on these 3 partition means that the indexes won’t be maintained during any DML operations on these partitions. Since data only changes in these most recent partitions, we still get all of the benefits we would have gotten if the index had been dropped altogether.
If the date range in our query is changed so that it only accesses older data, then the Optimizer picks our original index driven plans:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
The PARTITION_SALES table is range partitioned by month on LO_ORDERDATE. In order to ensure that the query performs efficiently in Oracle Database 11g a local index has been created on the LO_ORDERKEY column. Below is the execution plan for the statement in 11g:
After upgrading to Oracle Database 12c, I only have enough memory to accommodate the latest 3 months of data in the IM column store. The following query shows the latest 3 months populated into the IM column store:
Rather than dropping the index on the LO_ORDERKEY column completely, I used the following command to mark the index unusable on the 3 latest partitions:
ALTER INDEX idx_lo_ordkey MODIFY PARTITION p45 unusable;
Marking the indexes unusable on these 3 partition means that the indexes won’t be maintained during any DML operations on these partitions. Since data only changes in these most recent partitions, we still get all of the benefits we would have gotten if the index had been dropped altogether.
If the date range in our query is changed so that it only accesses older data, then the Optimizer picks our original index driven plans:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
ALTER INDEX idx_lo_ordkey MODIFY PARTITION p45 unusable;
Marking the indexes unusable on these 3 partition means that the indexes won’t be maintained during any DML operations on these partitions. Since data only changes in these most recent partitions, we still get all of the benefits we would have gotten if the index had been dropped altogether.
If the date range in our query is changed so that it only accesses older data, then the Optimizer picks our original index driven plans:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
The PARTITION_SALES table is range partitioned by month on LO_ORDERDATE. In order to ensure that the query performs efficiently in Oracle Database 11g a local index has been created on the LO_ORDERKEY column. Below is the execution plan for the statement in 11g:
After upgrading to Oracle Database 12c, I only have enough memory to accommodate the latest 3 months of data in the IM column store. The following query shows the latest 3 months populated into the IM column store:
Rather than dropping the index on the LO_ORDERKEY column completely, I used the following command to mark the index unusable on the 3 latest partitions:
ALTER INDEX idx_lo_ordkey MODIFY PARTITION p45 unusable;
Marking the indexes unusable on these 3 partition means that the indexes won’t be maintained during any DML operations on these partitions. Since data only changes in these most recent partitions, we still get all of the benefits we would have gotten if the index had been dropped altogether.
If the date range in our query is changed so that it only accesses older data, then the Optimizer picks our original index driven plans:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
ALTER INDEX idx_lo_ordkey MODIFY PARTITION p45 unusable;
Marking the indexes unusable on these 3 partition means that the indexes won’t be maintained during any DML operations on these partitions. Since data only changes in these most recent partitions, we still get all of the benefits we would have gotten if the index had been dropped altogether.
If the date range in our query is changed so that it only accesses older data, then the Optimizer picks our original index driven plans:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
The PARTITION_SALES table is range partitioned by month on LO_ORDERDATE. In order to ensure that the query performs efficiently in Oracle Database 11g a local index has been created on the LO_ORDERKEY column. Below is the execution plan for the statement in 11g:
After upgrading to Oracle Database 12c, I only have enough memory to accommodate the latest 3 months of data in the IM column store. The following query shows the latest 3 months populated into the IM column store:
Rather than dropping the index on the LO_ORDERKEY column completely, I used the following command to mark the index unusable on the 3 latest partitions:
ALTER INDEX idx_lo_ordkey MODIFY PARTITION p45 unusable;
Marking the indexes unusable on these 3 partition means that the indexes won’t be maintained during any DML operations on these partitions. Since data only changes in these most recent partitions, we still get all of the benefits we would have gotten if the index had been dropped altogether.
If the date range in our query is changed so that it only accesses older data, then the Optimizer picks our original index driven plans:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
I’m back on the road this month, meeting with customers to discuss their initial impressions and experiences with Oracle Database In-Memory. During one such discussion, I got asked a very peculiar question. The question was, “Do I really have to drop all of my reporting indexes if I use Database In-Memory?”
I have to admit I was a little taken aback by this question. After all, I thought most folks would be delighted to have an opportunity to give up the majority of their indexes, not just because of the space savings and DML performance benefits but also the maintenance nightmare that indexes can sometimes become.
Assuming this was a trick question, I deployed the standard stalling technique of answering a question with a question, “Can you tell me a little more about your situation?”
To which the system architect explained that they were in production with Oracle Database In-Memory on a 2 node RAC cluster running on commodity servers and a crap IO subsystem (his words, not mine). They had a snowflake schema, and had enough memory to accommodate all of their dimension tables but only the last 3 months of data in their two fact tables. Following my guidelines, they had kept their primary key indexes but dropped the rest of their indexes. He assured me that the performance of most of their queries had improved 100X and their ETL jobs were finishing 2X faster without the indexes but there were some queries that accessed more than just the last 3 months worth of data in the fact table and their performance had gotten worse, a lot worse.
It was in that moment that I realized that our guidance on dropping all reporting indexes with Database In-Memory had been based on an assumption that was not always true. The assumption I had been working under was; all of your performance critical data resides in memory or you have a good IO sub-system (engineered system etc.)
Luckily for me, there was a solution I could offer to the system architect, to restore the performance of his queries that access more than 3 months worth of data from his fact table.
The solution was to take advantage of the table expansion optimizer transformation in conjunction with partially usable local indexes.
If you aren’t familiar with the table expansion transformation, it was introduced in Oracle Database 11g Release 2 to allow the optimizer to generate a plan that uses indexes on only some of the partitions in a table, but not all of them. Hence, DBAs can configure a table so that an index is only created on the partitions that are predominately read-only, and will not suffer the overhead of index maintenance on the active portions of the data.
This same technique can be use with Database In-Memory. In this case the index is created only on the partitions in the fact table that are not in the In-Memory column store (IM column store). For the most current partitions that are in-memory, the index is marked unusable. Therefore it doesn’t have to be maintained during DML.
Let’s look at an example of table expansion in action with Oracle Database In-Memory. Consider the following query:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
ALTER INDEX idx_lo_ordkey MODIFY PARTITION p45 unusable;
Marking the indexes unusable on these 3 partition means that the indexes won’t be maintained during any DML operations on these partitions. Since data only changes in these most recent partitions, we still get all of the benefits we would have gotten if the index had been dropped altogether.
If the date range in our query is changed so that it only accesses older data, then the Optimizer picks our original index driven plans:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
The PARTITION_SALES table is range partitioned by month on LO_ORDERDATE. In order to ensure that the query performs efficiently in Oracle Database 11g a local index has been created on the LO_ORDERKEY column. Below is the execution plan for the statement in 11g:
After upgrading to Oracle Database 12c, I only have enough memory to accommodate the latest 3 months of data in the IM column store. The following query shows the latest 3 months populated into the IM column store:
Rather than dropping the index on the LO_ORDERKEY column completely, I used the following command to mark the index unusable on the 3 latest partitions:
ALTER INDEX idx_lo_ordkey MODIFY PARTITION p45 unusable;
Marking the indexes unusable on these 3 partition means that the indexes won’t be maintained during any DML operations on these partitions. Since data only changes in these most recent partitions, we still get all of the benefits we would have gotten if the index had been dropped altogether.
If the date range in our query is changed so that it only accesses older data, then the Optimizer picks our original index driven plans:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
If however the date range in our query is only for 1 month and that partition is in- memory, then the plan becomes a full table scan via the IM column store:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-04-01’,‘YYYY-MM-DD’) AND To_date(‘2015-04-30’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
And when the date range spans both the data in the IM column store and on disk (as in the original query above), the Optimizer takes advantage of the table expansion transformation to automatically rewrite the query into a UNION ALL query.
The transformed or rewritten UNION ALL query is made up of two branches, one branch accessing the older data on disk and the second branch accessing the 3 latest partitions in-memory. In this example the transformed query would be as follows:
SELECT Count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2011-09-01’,‘YYYY-MM-DD’) AND To_date(‘2015-02-28’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
UNION ALL
SELECT count(*)
FROM partition_sales s
WHERE s.lo_orderdate BETWEEN
To_date(‘2015-03-01’,‘YYYY-MM-DD’) AND To_date(‘2015-05-31’,‘YYYY-MM-DD’)
AND s.lo_orderkey = 5819909;
Transforming or rewriting the query to a UNION ALL enables the optimizer to get the best execution plan for all of the partitions in the table, with the first branch using the index access and the second branch using the In-Memory column store.
So, as you can see there maybe situations where it is not advantageous to drop all of your reporting indexes completely but in those situations remember a combination of the table expansion transformation and partially useable indexes will give you a very similar benefit.
Original publish date: June 26, 2015