Development
Merry Christmas
C Рождеством Христовым! Славим Его!
Interval Search: Part 4. Dynamic Range Segmentation – interval quantization
Forums, mailing lists, and StackOverflow are all great resources for Oracle performance discussions, but I’ve long thought it would be useful to have a dedicated online chat/group specifically for Oracle performance specialists. A place to share news, articles, and discuss performance issues. To test the waters, I’ve created a group: https://t.me/ora_perf. If you’re interested, feel free to join! Let’s build a central hub for Oracle performance discussions.
Before diving into the main topic, let me address a frequent question I’ve received regarding the earlier parts of this series:
“You’re focusing on the rare case of date-only indexes (begin_date, end_date), but most real-world scenarios involve composite indexes with an ID field, like (id, begin_date, end_date).“
Yes, it’s true that in practice, composite indexes with an ID field are more common. And exactly such scenarios was the reason of this series. However, I intentionally started with a simplified case to focus on the date filtering mechanics. All the issues, observations, conclusions, and solutions discussed so far are equally applicable to composite indexes.
For example, many production databases have identifiers that reference tens or even hundreds of thousands of intervals. The addition of an ID-based access predicate may reduce the scanned volume for a single query, but the underlying date range filtering issues remain. These inefficiencies often go unnoticed because people don’t realize their simple queries are doing tens of LIOs when they could be doing just 3-5, with response times reduced from 100 microseconds to 2 microseconds.
Even if your queries always use an equality predicate on the ID field, you’ll still encounter challenges with huge queries with joins, such as:
select *
from IDs
join tab_with_history h
on IDs.id = h.id
and :dt between h.beg_date and h.end_date
Here, lookups for each ID against the composite index can become inefficient at scale compared to retrieving a pre-filtered slice for the target date.
To clarify, everything discussed in this series applies to composite indexes as well. The solutions can easily be extended to include ID fields by modifying just a few lines of code. Let’s now move to the main topic.
Dynamic Range Segmentation – Interval QuantizationIn the earlier parts, you may have noticed a skew in my test data, with many intervals of 30 days generated for every hour. This naturally leads to the idea of reducing scan volume by splitting long intervals into smaller sub-intervals.
What is Interval Quantization?Interval quantization is a known solution for this problem, but it often comes with drawbacks. Traditional quantization requires selecting a single fixed unit (e.g., 1 minute), which may not suit all scenarios. Using a small unit to cover all cases can lead to an explosion in the number of rows.
However, since Dynamic Range Segmentation (DRS) already handles short intervals efficiently, we can focus on quantizing only long intervals. For this example, we’ll:
- Leave intervals of up to 1 hour as-is, partitioning them into two categories: up to 15 minutes and up to 1 hour.
- Split longer intervals into sub-intervals of 1 day.
To simplify the splitting of long intervals, we’ll write a SQL Macro:
create or replace function split_interval_by_days(beg_date date, end_date date)
return varchar2 sql_macro
is
begin
return q'{
select/*+ no_decorrelate */
case
when n = 1
then beg_date
else trunc(beg_date)+n-1
end as sub_beg_date
,case
when n<=trunc(end_date)-trunc(beg_date)
then trunc(beg_date)+n -1/24/60/60
else end_date
end as sub_end_date
from (select/*+ no_merge */ level n
from dual
connect by level<=trunc(end_date)-trunc(beg_date)+1
)
}';
end;
/
Source on github: https://github.com/xtender/xt_scripts/blob/master/blog/1.interval_search/drs.v2/split_interval_by_days.sql
This macro returns sub-intervals for any given range:
SQL> select * from split_interval_by_days(sysdate-3, sysdate);
SUB_BEG_DATE SUB_END_DATE
------------------- -------------------
2024-12-17 02:30:34 2024-12-17 23:59:59
2024-12-18 00:00:00 2024-12-18 23:59:59
2024-12-19 00:00:00 2024-12-19 23:59:59
2024-12-20 00:00:00 2024-12-20 02:30:34
ODCIIndexCreate_pr
We’ll modify the partitioning structure:
partition by range(DURATION_MINUTES)
(
partition part_15_min values less than (15)
,partition part_1_hour values less than (60)
,partition part_1_day values less than (1440) --40*24*60
)
We’ll use the SQL Macro to populate the index table with split intervals:
-- Now populate the table.
stmt2 := q'[INSERT INTO {index_tab_name} ( beg_date, end_date, rid )
SELECT SUB_BEG_DATE as beg_date
,SUB_END_DATE as end_date
,P.rowid
FROM "{owner}"."{tab_name}" P
, split_interval_by_days(
to_date(substr(P.{col_name}, 1,19),'YYYY-MM-DD HH24:MI:SS')
,to_date(substr(P.{col_name},21,19),'YYYY-MM-DD HH24:MI:SS')
)
]';
ODCIIndexInsert_pr
procedure ODCIIndexInsert_pr(
ia sys.ODCIIndexInfo,
rid VARCHAR2,
newval VARCHAR2,
env sys.ODCIEnv
)
IS
BEGIN
-- Insert into auxiliary table
execute immediate
'INSERT INTO '|| get_index_tab_name(ia)||' (rid, beg_date, end_date)'
||'select
:rid, sub_beg_date, sub_end_date
from split_interval_by_days(:beg_date, :end_date)'
using rid,get_beg_date(newval),get_end_date(newval);
END;
ODCIIndexStart_Pr
Update the SQL statement to account for the new partitions:
stmt := q'{
select rid from {tab_name} partition (part_15_min) p1
where :cmpval between beg_date and end_date
and end_date < :cmpval+interval'15'minute
union all
select rid from {tab_name} partition (part_1_hour) p2
where :cmpval between beg_date and end_date
and end_date < :cmpval+1/24
union all
select rid from {tab_name} partition (part_1_day ) p3
where :cmpval between beg_date and end_date
and end_date < :cmpval+1
}';
SQL> select count(*) from test_table where DATE_IN_RANGE(virt_date_range,date'2012-02-01')=1;
COUNT(*)
----------
943
SQL> @last
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 17wncu9ftfzf6, child number 0
-------------------------------------
select count(*) from test_table where
DATE_IN_RANGE(virt_date_range,date'2012-02-01')=1
Plan hash value: 2131856123
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9218 | 1 |00:00:00.01 | 15 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 15 |
|* 2 | DOMAIN INDEX | TEST_RANGE_INDEX | 1 | | | 943 |00:00:00.01 | 15 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("XTENDER"."DATE_IN_RANGE"("VIRT_DATE_RANGE",TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))=1)
So, by applying quantization with Dynamic Range Segmentation, we reduced the number of logical reads from 30 (in the simpler version) to 15—a 2x improvement.
ConclusionIn this example, we used partitions for 15 minutes, 1 hour, and 1 day for simplicity. In practice, optimal values will depend on the actual data. While the number of rows in the index increases, the fixed maximum interval length ensures consistently efficient results.
All series:
Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index
In this part, I’ll show how to implement Dynamic Range Segmentation (DRS) explained in the previous part using a custom Domain Index, allowing you to apply this optimization with minimal changes to your existing tables.
1. Creating the Function and OperatorFirst, we create a function that will be used to define the operator for the domain index:
CREATE OR REPLACE FUNCTION F_DATE_IN_RANGE(date_range varchar2, cmpval date)
RETURN NUMBER deterministic
AS
BEGIN
-- simple concatenation: beg_date;end_date
-- in format YYYY-MM-DD HH24:MI:SS
if cmpval between to_date(substr(date_range, 1,19),'YYYY-MM-DD HH24:MI:SS')
and to_date(substr(date_range,21,19),'YYYY-MM-DD HH24:MI:SS')
then
return 1;
else
return 0;
end if;
END;
/
Next, we create the operator to use this function:
CREATE OPERATOR DATE_IN_RANGE BINDING(VARCHAR2, DATE)
RETURN NUMBER USING F_DATE_IN_RANGE;
/
idx_range_date_pkg Package
We define a package (idx_range_date_pkg) that contains the necessary procedures to manage the domain index. The full implementation is too lengthy to include here but is available on GitHub.
idx_range_date_type
The type idx_range_date_type implements the ODCI extensible indexing interface, which handles operations for the domain index.
The code is available on GitHub.
idx_range_date_type
Internal Data Segmentation:
The type and package create and maintain an internal table of segmented data. For example, the procedure ODCIIndexCreate_pr creates a partitioned table:
stmt1 := 'CREATE TABLE ' || get_index_tab_name(ia)
||q'[
(
beg_date date
,end_date date
,rid rowid
,DURATION_MINUTES number as ((end_date-beg_date)*24*60)
)
partition by range(DURATION_MINUTES)
(
partition part_15_min values less than (15)
,partition part_2_days values less than (2880) --2*24*60
,partition part_40_days values less than (57600) --40*24*60
,partition part_400_days values less than (576000) --400*24*60
,partition p_max values less than (maxvalue)
)
]';
Efficient Query Execution:
The procedure ODCIIndexStart_pr executes range queries against this internal table:
-- This statement returns the qualifying rows for the TRUE case.
stmt := q'{
select rid from {tab_name} partition (part_15_min) p1
where :cmpval between beg_date and end_date
and end_date<=:cmpval+interval'15'minute
union all
select rid from {tab_name} partition (part_2_days) p1
where :cmpval between beg_date and end_date
and end_date<=:cmpval+2
union all
select rid from {tab_name} partition (part_40_days) p1
where :cmpval between beg_date and end_date
and end_date<=:cmpval+40
union all
select rid from {tab_name} partition (part_400_days) p1
where :cmpval between beg_date and end_date
and end_date<=:cmpval+400
union all
select rid from {tab_name} partition (p_max) p1
where :cmpval between beg_date and end_date
}';
Returning Results:
The ODCIIndexFetch_pr procedure retrieves the list of qualifying ROWID values:
FETCH cur BULK COLLECT INTO rowids limit nrows;
Here is the corresponding function implementation:
MEMBER FUNCTION ODCIIndexFetch(
self in out idx_range_date_type,
nrows NUMBER,
rids OUT sys.ODCIRidList,
env sys.ODCIEnv
) RETURN NUMBER
IS
cnum number;
cur sys_refcursor;
BEGIN
idx_range_date_pkg.p_debug('Fetch: nrows='||nrows);
cnum:=self.curnum;
cur:=dbms_sql.to_refcursor(cnum);
idx_range_date_pkg.p_debug('Fetch: converted to refcursor');
idx_range_date_pkg.ODCIIndexFetch_pr(nrows,rids,env,cur);
self.curnum:=dbms_sql.to_cursor_number(cur);
RETURN ODCICONST.SUCCESS;
END;
INDEXTYPE
CREATE OR REPLACE INDEXTYPE idx_range_date_idxtype
FOR
DATE_IN_RANGE(VARCHAR2,DATE)
USING idx_range_date_type;
/
Now we created all the required objects, so it’s time to create the index.
5. Adding a Virtual Generated ColumnSince the ODCI interface only supports indexing a single column, we combine beg_date and end_date into a virtual generated column:
alter table test_table
add virt_date_range varchar2(39)
generated always as
(to_char(beg_date,'YYYY-MM-DD HH24:MI:SS')||';'||to_char(end_date,'YYYY-MM-DD HH24:MI:SS'))
/
We create the domain index on the virtual column:
CREATE INDEX test_range_index ON test_table (virt_date_range)
INDEXTYPE IS idx_range_date_idxtype
/
Let’s test the index with a query:
SQL> select count(*) from test_table where DATE_IN_RANGE(virt_date_range,date'2012-02-01')=1;
COUNT(*)
----------
943
Execution Plan:
SQL> @last
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID 17wncu9ftfzf6, child number 0
-------------------------------------
select count(*) from test_table where
DATE_IN_RANGE(virt_date_range,date'2012-02-01')=1
Plan hash value: 2131856123
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 9218 | 1 |00:00:00.01 | 30 |
| 1 | SORT AGGREGATE | | 1 | 1 | 40 | | 1 |00:00:00.01 | 30 |
|* 2 | DOMAIN INDEX | TEST_RANGE_INDEX | 1 | | | | 943 |00:00:00.01 | 30 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("XTENDER"."DATE_IN_RANGE"("VIRT_DATE_RANGE",TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))=1)
Results: Only 30 logical reads were needed for the same date 2012-02-01!
Using a custom domain index allows us to implement this method with minimal changes to existing tables. This method efficiently handles interval queries while requiring significantly fewer logical reads.
In the next part, I will demonstrate how to enhance the Dynamic Range Segmentation method by introducing interval quantization—splitting longer intervals into smaller sub-intervals represented as separate rows.
Interval Search: Part 2. Dynamic Range Segmentation – Simplified
In the previous part, I discussed the most efficient known methods for optimizing range queries. In this part, I’ll introduce a simple version of my custom approach, which I call Dynamic Range Segmentation (DRS).
As explained earlier, a significant issue with conventional approaches is the lack of both boundaries in the ACCESS predicates. This forces the database to scan all index entries either above or below the target value, depending on the order of the indexed columns.
Dynamic Range Segmentation solves this problem by segmenting data based on the interval length.
Let’s create a table partitioned by interval lengths with the following partitions:
- part_15_min: Intervals shorter than 15 minutes.
- part_2_days: Intervals between 15 minutes and 2 days.
- part_40_days: Intervals between 2 days and 40 days.
- part_400_days: Intervals between 40 days and 400 days.
- p_max: All remaining intervals
Here’s the DDL for the partitioned table:
create table Dynamic_Range_Segmentation(
beg_date date
,end_date date
,rid rowid
,DURATION_MINUTES number as ((end_date-beg_date)*24*60)
)
partition by range(DURATION_MINUTES)
(
partition part_15_min values less than (15)
,partition part_2_days values less than (2880) --2*24*60
,partition part_40_days values less than (57600) --40*24*60
,partition part_400_days values less than (576000) --400*24*60
,partition p_max values less than (maxvalue)
);
The DURATION_MINUTES column is a virtual generated column that computes the interval length in minutes as the difference between beg_date and end_date.
We will explore the nuances of selecting specific partition boundaries in future parts. For now, let’s focus on the approach itself.
We populate the partitioned table with the same test data and create a local index on (end_date, beg_date):
insert/*+append parallel(4) */ into Dynamic_Range_Segmentation(beg_date,end_date,rid)
select beg_date,end_date,rowid from test_table;
create index ix_drs on Dynamic_Range_Segmentation(end_date,beg_date) local;
call dbms_stats.gather_table_stats('','Dynamic_Range_Segmentation');
By segmenting the data, we can assert with certainty that if we are searching for records in the part_15_min partition, the qualifying records must satisfy the conditionend_date <= :dt + INTERVAL '15' MINUTE
because no intervals in this partition exceed 15 minutes in length. This additional boundary provides the much-needed second predicate.
Thus, we can optimize our query by addressing each partition individually, adding upper boundaries for all partitions except the last one (p_max):
select count(*),min(beg_date),max(end_date) from (
select * from Dynamic_Range_Segmentation partition (part_15_min) p1
where date'2012-02-01' between beg_date and end_date
and end_date<=date'2012-02-01'+interval'15'minute
union all
select * from Dynamic_Range_Segmentation partition (part_2_days) p1
where date'2012-02-01' between beg_date and end_date
and end_date<=date'2012-02-01'+2
union all
select * from Dynamic_Range_Segmentation partition (part_40_days) p1
where date'2012-02-01' between beg_date and end_date
and end_date<=date'2012-02-01'+40 union all
select * from Dynamic_Range_Segmentation partition (part_400_days) p1
where date'2012-02-01' between beg_date and end_date
and end_date<=date'2012-02-01'+400
union all
select * from Dynamic_Range_Segmentation partition (p_max) p1
where date'2012-02-01' between beg_date and end_date
);
Results:
COUNT(*) MIN(BEG_DATE) MAX(END_DATE)
---------- ------------------- -------------------
943 2011-01-03 00:00:00 2013-03-03 00:00:00
SQL> select * from table(dbms_xplan.display_cursor('','','all allstats last -alias -projection'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 204zu1xhdqcq3, child number 0
-------------------------------------
select count(*),min(beg_date),max(end_date) from ( select * from
Dynamic_Range_Segmentation partition (part_15_min) p1 where
date'2012-02-01' between beg_date and end_date and
end_date<=date'2012-02-01'+interval'15'minute union all select *
from Dynamic_Range_Segmentation partition (part_2_days) p1 where
date'2012-02-01' between beg_date and end_date and
end_date<=date'2012-02-01'+2 union all select * from
Dynamic_Range_Segmentation partition (part_40_days) p1 where
date'2012-02-01' between beg_date and end_date and
end_date<=date'2012-02-01'+40 union all select * from
Dynamic_Range_Segmentation partition (part_400_days) p1 where
date'2012-02-01' between beg_date and end_date and
end_date<=date'2012-02-01'+400 union all select * from
Dynamic_Range_Segmentation partition (p_max) p1 where
date'2012-02-01' between beg_date and end_date )
Plan hash value: 1181465968
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 24 (100)| | | | 1 |00:00:00.01 | 28 |
| 1 | SORT AGGREGATE | | 1 | 1 | 18 | | | | | 1 |00:00:00.01 | 28 |
| 2 | VIEW | | 1 | 1582 | 28476 | 24 (0)| 00:00:01 | | | 943 |00:00:00.01 | 28 |
| 3 | UNION-ALL | | 1 | | | | | | | 943 |00:00:00.01 | 28 |
| 4 | PARTITION RANGE SINGLE| | 1 | 4 | 64 | 3 (0)| 00:00:01 | 1 | 1 | 3 |00:00:00.01 | 3 |
|* 5 | INDEX RANGE SCAN | IX_DRS | 1 | 4 | 64 | 3 (0)| 00:00:01 | 1 | 1 | 3 |00:00:00.01 | 3 |
| 6 | PARTITION RANGE SINGLE| | 1 | 536 | 8576 | 7 (0)| 00:00:01 | 2 | 2 | 19 |00:00:00.01 | 7 |
|* 7 | INDEX RANGE SCAN | IX_DRS | 1 | 536 | 8576 | 7 (0)| 00:00:01 | 2 | 2 | 19 |00:00:00.01 | 7 |
| 8 | PARTITION RANGE SINGLE| | 1 | 929 | 14864 | 10 (0)| 00:00:01 | 3 | 3 | 890 |00:00:00.01 | 10 |
|* 9 | INDEX RANGE SCAN | IX_DRS | 1 | 929 | 14864 | 10 (0)| 00:00:01 | 3 | 3 | 890 |00:00:00.01 | 10 |
| 10 | PARTITION RANGE SINGLE| | 1 | 29 | 464 | 2 (0)| 00:00:01 | 4 | 4 | 17 |00:00:00.01 | 2 |
|* 11 | INDEX RANGE SCAN | IX_DRS | 1 | 29 | 464 | 2 (0)| 00:00:01 | 4 | 4 | 17 |00:00:00.01 | 2 |
| 12 | PARTITION RANGE SINGLE| | 1 | 84 | 1344 | 2 (0)| 00:00:01 | 5 | 5 | 14 |00:00:00.01 | 6 |
|* 13 | INDEX FAST FULL SCAN | IX_DRS | 1 | 84 | 1344 | 2 (0)| 00:00:01 | 5 | 5 | 14 |00:00:00.01 | 6 |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("END_DATE">=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE"<=TO_DATE(' 2012-02-01 00:15:00',
'syyyy-mm-dd hh24:mi:ss') AND "BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - access("END_DATE">=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE"<=TO_DATE(' 2012-02-03 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
9 - access("END_DATE">=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE"<=TO_DATE(' 2012-03-12 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
11 - access("END_DATE">=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE"<=TO_DATE(' 2013-03-07 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
13 - filter(("BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE">=TO_DATE(' 2012-02-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
This approach reduces logical reads (LIOs) to 28, compared to the 183 in the best case from the previous parts.
Simplifying with a SQL MacroTo avoid writing such large queries repeatedly, we can create a SQL Macro:
create or replace function DRS_by_date_macro(dt date)
return varchar2 sql_macro
is
begin
return q'{
select * from Dynamic_Range_Segmentation partition (part_15_min) p1
where dt between beg_date and end_date
and end_date<=dt+interval'15'minute
union all
select * from Dynamic_Range_Segmentation partition (part_2_days) p1
where dt between beg_date and end_date
and end_date<=dt+2
union all
select * from Dynamic_Range_Segmentation partition (part_40_days) p1
where dt between beg_date and end_date
and end_date<=dt+40
union all
select * from Dynamic_Range_Segmentation partition (part_400_days) p1
where dt between beg_date and end_date
and end_date<=dt+400
union all
select * from Dynamic_Range_Segmentation partition (p_max) p1
where dt between beg_date and end_date
}';
end;
/
With this macro, queries become concise:
SQL> select count(*),min(beg_date),max(end_date) from DRS_by_date_macro(date'2012-02-01');
COUNT(*) MIN(BEG_DATE) MAX(END_DATE)
---------- ------------------- -------------------
943 2011-01-03 00:00:00 2013-03-03 00:00:00
Execution plan:
SQL> select * from table(dbms_xplan.display_cursor('','','all allstats last -alias -projection'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7nmx3cnwrmd0c, child number 0
-------------------------------------
select count(*),min(beg_date),max(end_date) from
DRS_by_date_macro(date'2012-02-01')
Plan hash value: 1181465968
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows s| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 24 (100)| | | | 1 |00:00:00.01 | 28 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | | 1 |00:00:00.01 | 28 |
| 2 | VIEW | | 1 | 1582 | 24 (0)| 00:00:01 | | | 943 |00:00:00.01 | 28 |
| 3 | UNION-ALL | | 1 | | | | | | 943 |00:00:00.01 | 28 |
| 4 | PARTITION RANGE SINGLE| | 1 | 4 | 3 (0)| 00:00:01 | 1 | 1 | 3 |00:00:00.01 | 3 |
|* 5 | INDEX RANGE SCAN | IX_DRS | 1 | 4 | 3 (0)| 00:00:01 | 1 | 1 | 3 |00:00:00.01 | 3 |
| 6 | PARTITION RANGE SINGLE| | 1 | 536 | 7 (0)| 00:00:01 | 2 | 2 | 19 |00:00:00.01 | 7 |
|* 7 | INDEX RANGE SCAN | IX_DRS | 1 | 536 | 7 (0)| 00:00:01 | 2 | 2 | 19 |00:00:00.01 | 7 |
| 8 | PARTITION RANGE SINGLE| | 1 | 929 | 10 (0)| 00:00:01 | 3 | 3 | 890 |00:00:00.01 | 10 |
|* 9 | INDEX RANGE SCAN | IX_DRS | 1 | 929 | 10 (0)| 00:00:01 | 3 | 3 | 890 |00:00:00.01 | 10 |
| 10 | PARTITION RANGE SINGLE| | 1 | 29 | 2 (0)| 00:00:01 | 4 | 4 | 17 |00:00:00.01 | 2 |
|* 11 | INDEX RANGE SCAN | IX_DRS | 1 | 29 | 2 (0)| 00:00:01 | 4 | 4 | 17 |00:00:00.01 | 2 |
| 12 | PARTITION RANGE SINGLE| | 1 | 84 | 2 (0)| 00:00:01 | 5 | 5 | 14 |00:00:00.01 | 6 |
|* 13 | INDEX FAST FULL SCAN | IX_DRS | 1 | 84 | 2 (0)| 00:00:01 | 5 | 5 | 14 |00:00:00.01 | 6 |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("END_DATE">=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE"<=TO_DATE(' 2012-02-01 00:15:00',
'syyyy-mm-dd hh24:mi:ss') AND "BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - access("END_DATE">=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE"<=TO_DATE(' 2012-02-03 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
9 - access("END_DATE">=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE"<=TO_DATE(' 2012-03-12 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
11 - access("END_DATE">=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE"<=TO_DATE(' 2013-03-07 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
13 - filter(("BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE">=TO_DATE(' 2012-02-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
This approach can also be implemented in various ways, such as using materialized views, globally partitioned indexes, or other methods.
In the next part, I will demonstrate how to create a custom domain index to further optimize this method.
Interval Search: Optimizing Date Range Queries – Part 1
One of the most common and enduring challenges in database management is performing efficient interval searches, particularly for date intervals such as: WHERE :dt BETWEEN beg_date AND end_date.
In this series of articles, I will explore various strategies for optimizing such searches. We’ll delve into well-known standard approaches, analyze their limitations, and introduce my custom method—a method I promised to share several years ago, but I had postponed writing about it because the topic’s complexity seemed daunting, requiring a deep dive into the nuances of the data itself (e.g., open intervals, extreme values, data distribution, and skew). However, after receiving yet another question about it recently, I realized that I could no longer delay. Even if it means addressing some of the finer details in later parts, it’s time to start sharing this method in manageable steps.
Defining the ProblemIn many applications involving historical data, a common modeling approach is SCD (Slowly Changing Dimension) Type 2 (reference). This method often uses columns such as begin_date and end_date to represent the validity period of each record.
To find records that are valid at a specific point in time, queries often use predicates like:WHERE :dt BETWEEN beg_date AND end_date
The challenge lies in finding a universal and efficient method to execute such queries.
Solution ApproachesLet’s begin by creating a test table and generating sample data for evaluation:
create table test_table(
beg_date date
,end_date date
,padding varchar2(10)
);
declare
procedure p_insert(
start_date date
,end_date date
,step_minutes number
,duration_minutes number
) is
begin
insert/*+ append */ into test_table(beg_date,end_date,padding)
select
start_date + n * numtodsinterval(step_minutes,'minute')
,start_date + n * numtodsinterval(step_minutes,'minute') + numtodsinterval(duration_minutes,'minute')
,'0123456789'
from xmltable('0 to xs:integer(.)'
passing ceil( (end_date - start_date)*24*60/step_minutes)
columns n int path '.'
);
commit;
end;
begin
-- 5 min intervals every 5 minutes: 00:00-00:15, 00:05-00:20,etc:
--p_insert(date'2000-01-01',sysdate, 5, 5);
-- 5 min intervals every 5 minutes starting from 00:02 : 00:02-00:07, 00:07-00:12,etc
p_insert(date'2000-01-01'+interval'2'minute,sysdate, 5, 5);
-- 15 min intervals every 5 minutes: 00:00-00:15, 00:05-00:20,etc:
p_insert(date'2000-01-01',sysdate, 5, 15);
-- 30 min intervals every 15 minutes: 00:00-00:30, 00:15-00:45,etc:
p_insert(date'2000-01-01',sysdate, 15, 30);
-- 1 hour intervals every 15 minutes: 00:00-01:00, 00:15-01:15,etc:
p_insert(date'2000-01-01',sysdate, 15, 60);
-- 2 hour intervals every 20 minutes: 00:00-02:00, 00:20-02:00,etc:
p_insert(date'2000-01-01',sysdate, 20, 120);
-- 7 days intervals every 60 minutes:
p_insert(date'2000-01-01',sysdate, 60, 7*24*60);
-- 30 days intervals every 1 hour:
p_insert(date'2000-01-01',sysdate, 60, 30*24*60);
-- 120 days intervals every 7 days:
p_insert(date'2000-01-01',sysdate, 7*24*60, 120*24*60);
-- 400 days intervals every 30 days:
p_insert(date'2000-01-01',sysdate, 30*24*60, 400*24*60);
end;
/
We’ve got a table with 10mln rows with different date intervals:
SQL> select count(*),min(beg_date),max(end_date) from test_table;
COUNT(*) MIN(BEG_DATE) MAX(END_DATE)
---------- ------------------- -------------------
10723261 2000-01-01 00:00:00 2026-01-24 00:00:00
(beg_date, end_date)
The most straightforward approach is to create a composite index on (beg_date, end_date). However, even at first glance, it’s clear that this method has significant inefficiencies.
When we use a predicate like :dt BETWEEN beg_date AND end_date, it breaks down into two sub-predicates:
Access Predicate: beg_date <= :dt
This is used for index access since beg_date is the leading column in the index. However, the query will need to scan and evaluate all index entries that satisfy this condition.
Filter Predicate: :dt <= end_date
This acts as a filter on the results from the access predicate.
As the dataset grows, both beg_date and end_date values increase over time. Consequently, because the access predicate (beg_date <= :dt) is used to locate potential matches, the query will scan an ever-growing portion of the index.
(end_date, beg_date)
This is one of the most widely adopted approaches. By simply rearranging the order of columns in the index, placing end_date first, we can achieve significantly better performance in most cases.
Why? Queries tend to target data closer to the current time, and much less frequently target records from far in the past. By indexing on end_date first, the query engine can more effectively narrow down the relevant portion of the index.
Let’s create the indexes and assess their performance:
create index ix_beg_end on test_table(beg_date,end_date);
create index ix_end_beg on test_table(end_date,beg_date);
select segment_name,blocks,bytes/1024/1024 as mbytes
from user_segments
where segment_name in ('IX_BEG_END','IX_END_BEG','TEST_TABLE');
SEGMENT_NAME BLOCKS MBYTES
-------------------- ---------- ----------
IX_BEG_END 40960 320
IX_END_BEG 40832 319
TEST_TABLE 48128 376
Let’s query the records valid 100 days ago using the (beg_date, end_date) index:
SQL> select/*+ index(test_table (beg_date,end_date)) */ count(*),min(beg_date),max(end_date) from test_table where sysdate-100 between beg_date and end_date;
COUNT(*) MIN(BEG_DATE) MAX(END_DATE)
---------- ------------------- -------------------
935 2023-08-28 00:00:00 2025-09-26 00:00:00
SQL> select * from table(dbms_xplan.display_cursor('','','all allstats last'));
Plan hash value: 1056805589
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 40375 (100)| | 1 |00:00:00.79 | 40200 |
| 1 | SORT AGGREGATE | | 1 | 1 | 16 | | | 1 |00:00:00.79 | 40200 |
|* 2 | INDEX RANGE SCAN| IX_BEG_END | 1 | 28472 | 444K| 40375 (1)| 00:00:02 | 935 |00:00:00.79 | 40200 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("END_DATE">=SYSDATE@!-100 AND "BEG_DATE"<=SYSDATE@!-100)
filter("END_DATE">=SYSDATE@!-100)
As seen, the query required 40,200 logical reads, almost the entire index, which contains 40,960 blocks.
Now, let’s query the same data using the (end_date, beg_date) index:
SQL> select count(*),min(beg_date),max(end_date) from test_table where sysdate-100 between beg_date and end_date;
COUNT(*) MIN(BEG_DATE) MAX(END_DATE)
---------- ------------------- -------------------
935 2023-08-28 00:00:00 2025-09-26 00:00:00
SQL> select * from table(dbms_xplan.display_cursor('','','all allstats last'));
Plan hash value: 416972780
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 450 (100)| 1 |00:00:00.01 | 453 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 453 |
|* 2 | INDEX RANGE SCAN| IX_END_BEG | 1 | 28472 | 450 (1)| 935 |00:00:00.01 | 453 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("END_DATE">=SYSDATE@!-100 AND "END_DATE" IS NOT NULL)
filter("BEG_DATE"<=SYSDATE@!-100)
Using this index required only 453 logical reads, a dramatic improvement compared to the 40,200 reads with the first index.
Adding an Upper Bound forend_date
To illustrate the importance of having both upper and lower bounds for effective range queries, let’s further restrict the query with end_date < SYSDATE - 70:
SQL> select count(*),min(beg_date),max(end_date) from test_table where sysdate-100 between beg_date and end_date and end_date<sysdate-70;
COUNT(*) MIN(BEG_DATE) MAX(END_DATE)
---------- ------------------- -------------------
910 2023-08-28 00:00:00 2024-10-08 02:00:00
SQL> select * from table(dbms_xplan.display_cursor('','','all allstats last'));
Plan hash value: 3937277202
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 136 (100)| 1 |00:00:00.01 | 137 |
| 1 | SORT AGGREGATE | | 1 | | 1 |00:00:00.01 | 137 |
|* 2 | FILTER | | 1 | | 910 |00:00:00.01 | 137 |
|* 3 | INDEX RANGE SCAN| IX_END_BEG | 1 | 136 (0)| 910 |00:00:00.01 | 137 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SYSDATE@!-70>SYSDATE@!-100)
3 - access("END_DATE">=SYSDATE@!-100 AND "END_DATE"<SYSDATE@!-70 AND "BEG_DATE"<=SYSDATE@!-100)
filter("BEG_DATE"<=SYSDATE@!-100)
We retrieved nearly all required records (910 out of 935), but the number of logical I/O operations (LIO) dropped by more than threefold.
To illustrate the inherent limitations of our current indexing strategies, let’s simplify the scenario. Suppose we have a table of integer intervals (START, END) containing 10 million records: (0,1), (1,2), (2,3), …, (9999999, 10000000). When searching for a record where 5000000 BETWEEN START AND END, regardless of whether we use an index on (START, END) or (END, START), we would have to scan approximately half of the index. This clearly demonstrates that neither of these indexes can serve as a universal solution; under certain conditions, both indexes become inefficient.
Let’s illustrate this issue using our test table. We’ll select a date roughly in the middle of our dataset – date’2012-02-01′ – and examine the performance of both indexes.
First, we’ll test the query using the (beg_date, end_date) index:
SQL> select/*+ index(test_table (beg_date,end_date)) */ count(*),min(beg_date),max(end_date) from test_table where date'2012-02-01' between beg_date and end_date;
COUNT(*) MIN(BEG_DATE) MAX(END_DATE)
---------- ------------------- -------------------
943 2011-01-03 00:00:00 2013-03-03 00:00:00
SQL> select * from table(dbms_xplan.display_cursor('','','all allstats last -alias -projection'));
Plan hash value: 1056805589
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)|| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 19355 (100)|| 1 |00:00:00.45 | 19680 |
| 1 | SORT AGGREGATE | | 1 | 1 | || 1 |00:00:00.45 | 19680 |
|* 2 | INDEX RANGE SCAN| IX_BEG_END | 1 | 2783K| 19355 (1)|| 943 |00:00:00.45 | 19680 |
--------------------------------------------------------------------------------------------------------
The query required almost 20,000 LIO operations, a significant portion of the total index size. Next, we’ll perform the same query using the (end_date, beg_date) index:
select/*+ index(test_table (end_date,beg_date)) */ count(*),min(beg_date),max(end_date) from test_table where date'2012-02-01' between beg_date and end_date;
COUNT(*) MIN(BEG_DATE) MAX(END_DATE)
---------- ------------------- -------------------
943 2011-01-03 00:00:00 2013-03-03 00:00:00
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20929 (100)| 1 |00:00:00.38 | 20973 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.38 | 20973 |
|* 2 | INDEX RANGE SCAN| IX_END_BEG | 1 | 655K| 20929 (1)| 943 |00:00:00.38 | 20973 |
-------------------------------------------------------------------------------------------------------
Similarly, this query also required approximately 20,000 LIO operations, illustrating that both indices suffer from similar inefficiencies for this type of query.
The high number of logical reads in both cases highlights that neither index provides an efficient solution for queries with dates in the middle of the data range. The database engine must scan a large portion of the index to find the matching records, resulting in increased I/O and slower query performance, especially when the search value lies in the middle of the data range.
2. Partitioning + Composite IndexesThis approach is far less common but offers significant advantages. In the previous examples with composite indexes, the predicate on the second column of the index did not help reduce the number of scanned index entries. However, by partitioning the table on this second column, we can leverage partition pruning to exclude irrelevant partitions, significantly reducing the scan scope.
Example: Partitioned Table byEND_DATE
To demonstrate, let’s create a partitioned table using the same data as in the previous example, partitioned by END_DATE on a yearly interval:
create table test_table_part_1(
beg_date date
,end_date date
,rid rowid
)
partition by range(end_date) interval (numtoyminterval(1,'year'))
(
partition part_01 values less than (date'2000-01-01')
);
insert/*+append parallel(4) */ into test_table_part_1
select beg_date,end_date,rowid from test_table;
create index ix_tt_part_local on test_table_part_1(beg_date,end_date) local;
call dbms_stats.gather_table_stats('','test_table_part_1');
This results in 28 partitions:
SQL> select partition_name,partition_position,blevel,leaf_blocks,num_rows from user_ind_partitions where index_name='IX_TT_PART_LOCAL';
PARTITION_NAME PARTITION_POSITION BLEVEL LEAF_BLOCKS NUM_ROWS
-------------- ------------------ ---------- ----------- ----------
PART_01 1 0 0 0
SYS_P8333 2 2 1621 429547
SYS_P8341 3 2 1621 429304
SYS_P8348 4 2 1621 429304
SYS_P8353 5 2 1621 429304
SYS_P8355 6 2 1625 430480
SYS_P8332 7 2 1621 429304
SYS_P8335 8 2 1621 429305
SYS_P8331 9 2 1621 429305
SYS_P8336 10 2 1625 430480
SYS_P8338 11 2 1621 429304
SYS_P8340 12 2 1621 429304
SYS_P8343 13 2 1621 429304
SYS_P8345 14 2 1625 430481
SYS_P8347 15 2 1621 429305
SYS_P8352 16 2 1621 429304
SYS_P8350 17 2 1621 429304
SYS_P8351 18 2 1625 430480
SYS_P8334 19 2 1621 429305
SYS_P8337 20 2 1621 429304
SYS_P8339 21 2 1621 429305
SYS_P8342 22 2 1625 430480
SYS_P8344 23 2 1621 429304
SYS_P8346 24 2 1621 429304
SYS_P8349 25 2 1621 429305
SYS_P8354 26 2 1561 413443
SYS_P8356 27 1 2 391
SYS_P8357 28 0 1 1
Let’s test the same query for the same DATE '2012-02-01' using the partitioned table:
SQL> select/*+ index(t (beg_date,end_date)) */ count(*),min(beg_date),max(end_date) from test_table_part_1 t where date'2012-02-01' between beg_date and end_date;
COUNT(*) MIN(BEG_DATE) MAX(END_DATE)
---------- ------------------- -------------------
943 2011-01-03 00:00:00 2013-03-03 00:00:00
SQL> select * from table(dbms_xplan.display_cursor('','','all allstats last -alias -projection'));
Plan hash value: 1651658810
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10259 (100)| | | 1 |00:00:00.01 | 183 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | 1 |00:00:00.01 | 183 |
| 2 | PARTITION RANGE ITERATOR| | 1 | 2783K| 10259 (1)| 14 |1048575| 943 |00:00:00.01 | 183 |
|* 3 | INDEX RANGE SCAN | IX_TT_PART_LOCAL | 15 | 2783K| 10259 (1)| 14 |1048575| 943 |00:00:00.01 | 183 |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("END_DATE">=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "BEG_DATE"<=TO_DATE(' 2012-02-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
filter("END_DATE">=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
As shown, this approach reduced the number of logical reads (LIO) to just 183, compared to 20,000 in the earlier examples. Partitioning the table on END_DATE combined with a composite local index dramatically improves query performance by limiting the scan scope through partition pruning. Even in the worst-case scenario, the number of logical reads is orders of magnitude lower than with global composite indexes. This makes it a highly effective strategy for interval searches.
Next part: Interval Search: Part 2. Dynamic Range Segmentation – Simplified
An Appalachian Nativity
Christmas is almost upon us and during the preceding fast this year I've been drawn to the folk sounds of Appalachia. America has real culture, its just been hidden from us.
This beautiful - and really, I mean beautiful - folk carol And The Trees Do Moan is about as soulful as it gets. I don't know who these people are, but holy smokes are they good:
I will let the music speak for itself, except to say this is absolutely haunting.
God is with us.
Hear ye, even unto the uttermost ends of the earth: For God is with us.
Submit yourselves, ye mighty ones: For God is with us.
If again ye shall rise up in your might, again shall ye be overthrown:
For God is with us.
If any take counsel together, them shall the Lord destroy: For God is with us.
And the word which ye shall speak shall not abide in you: For God is with us.
For we fear not your terror, neither are we troubled: For God is with us.
But the Lord our God, He it is to Whom we will ascribe holiness, and
Him shall we fear: For God is with us.
And if I put my trust in Him, He shall be my sanctification: For God is with us.
I will set my hope on Him, and through Him shall I be saved: For God is with us.
Lo, I and the children whom God hath given me: For God is with us.
The people that walked in darkness have seen a great light: For God is with us.
And they that dwelt in the land of the shadow of death, on them hath
the light shined: For God is with us.
For unto us a Son is born, unto us a Child is given: For God is with us.
And the government shall be upon His shoulder: For God is with us.
And of His peace there shall be no end: For God is with us.
And his name shall be called the Angel of Great Council: For God is with us.
Wonderful, Counsellor: For God is with us.
The Mighty God, the Highest Power, the Prince of Peace: For God is with us.
The Father of the world to come: For God is with us.
Glory to the Father, and to the Son, and to the Holy Spirit: For God is with us.
Both now and ever, and unto ages of ages. Amen. For God is with us.
God is with us.
God is with us.
Underground Americana
Mainstream label music is almost entirely so awful that it drives me to despair to hear most popular music these days - let alone realize it is willingly consumed by anyone. Here I offer three bands that are both a complete "fuck you" to popular music in form and function, but tie into something deep within the American cultural dna that makes them something special:
1 Grave Pilgrim, The Bigotry of Purpose. Raw riffing rock, barked vocals. Tagline: chivalric violence and hideous cruelty. Best rock album of 2023 in my opinion. Best song, Rhiannon's Wake.
Their prior EP Molten Hands Reach West was just as good.
2 Panopticon, ...And Again, into the Light. Bluegrass meets atmospheric black metal on a journey through American geographies, old style anarchist complaints and the trials of life. Austin Lunn, the one man genius behind Panopticon, definitely uses music to exorcise his inner daemons (come to Divine Liturgy my friend to really get them out...). I have a lot of time for this guy. My favorite parts are the gravelly bluegrass ones, like....
Special mention for Panopticon's Kentucky. My forebears were coal miners.
3 Pan-Amerikan Native Front, Little Turtle's War. Indigenous metal meditating on past battles in the bloody unfolding of the American nation. We probably can stop screwing over natives in this country now.
Also, their split Immortal Ceremonies: someone must have thought "go hard or go home" on that one.
The Turning of Lot's Wife
The Turning of Lot's Wife
Scott Cairns
Genesis 19. 23-26
First of all, she had a name, and she had a history. She was Marah, and long before the breath of death's angel turned her to bitter dust, she had slipped from her mother's womb with remarkable ease, had moved in due time from infancy to womanhood with a manner of grace that came to be the sole blessing of her aging parents. She was beloved.
And like most daughters who are beloved by both a mother and a father, Marah moved about her city with unflinching compassion, tending to the dispossessed as if they were her own. And they became her own. In a city given to all species of excess, there were a great many in agony--abandoned men, abandoned women, abandoned children. Upon these she poured out her substance and her care.
Her first taste of despair was at the directive of the messengers, who announced without apparent sentiment what was to come, and what was to be done. With surprising banality, they stood and spoke. One coughed dryly into his fist and would not meet her eyes. And one took a sip from the cup she offered before he handed it back and the two disappeared into the night.
Unlike her husband--coward and sycophant--the woman remained faithful unto death. For even as the man fled the horrors of a city's conflagration, outrunning Marah and both girls as they all rushed into the desert, the woman stopped. She looked ahead briefly to the flat expanse, seeing her tall daughters, whose strong legs and churning arms were taking them safely to the hills; she saw, farther ahead, the old man whom she had served and comforted for twenty years. In the impossible interval where she stood, Marah saw that she could not turn her back on even one doomed child of the city, but must turn her back instead upon the saved.
Partitioning by node (instance_number)
Years ago, I had to solve the problem of cluster-wide contention for log table blocks during a massive number of inserts from numerous sessions.
Since the table was essentially insert-only and reads were extremely rare (only during investigations of issues), the obvious solution was for sessions from each cluster node to write to their own segments.
To implement this, we introduced an INSTANCE_ID column to the table and partitioned the data based on this column:
create table t_part(ID int, instance int default sys_context('userenv','instance'))
partition by list(instance) (
partition p1 values(1),
partition p2 values(2),
partition p3 values(3),
partition p4 values(4),
partition pX values(default)
);
Essentially, we created four partitions for instance numbers 1 to 4, along with a default partition (pX) to accommodate any unexpected increase in the number of nodes.
Since then, I have successfully applied this approach in numerous, more complex scenarios involving frequent read operations. As a result, I decided to document this technique to facilitate sharing and future reference.
While the provided example is simplified, real-world implementations often necessitate additional considerations. For instance, the table might be range-partitioned, enabling the use of composite partitioning:
create table t_log(ID int, tmstmp timestamp, instance int default sys_context('userenv','instance'))
partition by range(tmstmp) interval(interval'1'day)
subpartition by list(instance)
subpartition TEMPLATE (
subpartition sub_p1 values(1),
subpartition sub_p2 values(2),
subpartition sub_p3 values(3),
subpartition sub_p4 values(4),
subpartition sub_pX values(default)
)
(
partition p1 values less than(timestamp'2024-12-01 00:00:00')
);
Analects 7:6
Musk Ox
Musk Ox is a fantastic instrumental chamber folk project from up in Canada. I recently stumbled on these guys and their guitarist Nathanael Larochette from some collaborations he did with the (now defunct, but often fantastic) Oregon-based post metal/neofolk band Agalloch. Here's a neat documentary on the making of their album Woodfall.
Another cool project is the acoustic spin off that Nathanael did from the last Agalloch album.
His solo stuff is great. Music about trees and such.
AI not I
The notion that what we call AI is somehow approaching a form on consciousness remains an absurdity: fantastical thinking by people who really ought to spend a minimal amount of time at least reading up on philosophy of mind. Generative AI fits perfectly into John Searle's Chinese Room (the main variation is probability replaces rules, which reflects the one major innovation of NLP over decades).
I don't mean to suggest the technology is not extremely useful - it is, and will become more so. But: reality check.
A New Product for Podcast Listeners
We're excited to announce our first consumer product.
Introducing Listen Later
Launch Special
Sign up now and get free credits to convert multiple articles into audio and enjoy this unique service.
Why You Will Love Listen Later
Convenience Redefined: Transform articles into podcasts. Listen while you drive, exercise, or relax.
Exceptional Audio Quality: Enjoy natural, human-like AI narration. It's like listening to a storyteller, but powered by cutting-edge technology.
Seamless Experience: Using Listen Later is as easy as sending an email. There is no app to install and it's available on all devices and podcast apps.
Learn More at ListenLater.net
For the time being
Putting the decorations back into their cardboard boxes --
Some have got broken -- and carrying them up to the attic.
The holly and the mistletoe must be taken down and burnt,
And the children got ready for school. There are enough
Left-overs to do, warmed-up, for the rest of the week --
Not that we have much appetite, having drunk such a lot,
Stayed up so late, attempted -- quite unsuccessfully --
To love all of our relatives, and in general
Grossly overestimated our powers. Once again
As in previous years we have seen the actual Vision and failed
To do more than entertain it as an agreeable
Possibility, once again we have sent Him away,
Begging though to remain His disobedient servant,
The promising child who cannot keep His word for long.
The Christmas Feast is already a fading memory,
And already the mind begins to be vaguely aware
Of an unpleasant whiff of apprehension at the thought
Of Lent and Good Friday which cannot, after all, now
Be very far off. But, for the time being, here we all are,
Back in the moderate Aristotelian city
Of darning and the Eight-Fifteen, where Euclid's geometry
And Newton's mechanics would account for our experience,
And the kitchen table exists because I scrub it.
It seems to have shrunk during the holidays. The streets
Are much narrower than we remembered; we had forgotten
The office was as depressing as this. To those who have seen
The Child, however dimly, however incredulously,
The Time Being is, in a sense, the most trying time of all.
For the innocent children who whispered so excitedly
Outside the locked door where they knew the presents to be
Grew up when it opened. Now, recollecting that moment
We can repress the joy, but the guilt remains conscious;
Remembering the stable where for once in our lives
Everything became a You and nothing was an It.
And craving the sensation but ignoring the cause,
We look round for something, no matter what, to inhibit
Our self-reflection, and the obvious thing for that purpose
Would be some great suffering. So, once we have met the Son,
We are tempted ever after to pray to the Father;
"Lead us into temptation and evil for our sake."
They will come, all right, don't worry; probably in a form
That we do not expect, and certainly with a force
More dreadful than we can imagine. In the meantime
There are bills to be paid, machines to keep in repair,
Irregular verbs to learn, the Time Being to redeem
From insignificance. The happy morning is over,
The night of agony still to come; the time is noon:
When the Spirit must practice his scales of rejoicing
Without even a hostile audience, and the Soul endure
A silence that is neither for nor against her faith
That God's Will will be done, That, in spite of her prayers,
God will cheat no one, not even the world of its triumph."
My Coding Experience with an AI Assistant
AI is real and it is here. Its implications for knowledge workers will be enormous, including you.
There is no escaping AI in our line of work. Therefore, while building the new version of Gitora PL/SQL Editor, I’ve fully embraced it.
The last few months have been the most interesting, rewarding and productive period of my professional life. I’ve practically stopped using Google search and StackOverflow, two websites I’d consider essential to my work just a few months ago.
I’ve coded the Gitora PL/SQL debugger virtually without reading a single line of documentation about how to build debuggers in Java or the specifics of how to build a Java debugger for PL/SQL. I am not even sure if there is any documentation available about building a PL/SQL debugger on the Internet.
I just talked about these topics with ChatGPT and analyzed, used the code snippets it produced.
All CSS and almost all of the run-of-the-mill code I needed to write for the debugger and the Gitora AI Assistant for SQL and PL/SQL (oh the irony!) is either written by ChatGPT or GitHub CoPilot. Heck, I even had the the previous sentenced grammatically verified with ChatGPT. I got it right in my first attempt and ChatGPT congratulated me.
After using an AI Assistant for a few months, I cannot imagine coding without it. I view it an amazing junior pair programmer I am working with.
Although useful, ChatGPT and GitHub CoPilot are not as great as they can be while I am working with the Oracle database because they don't know the objects in the schema I am working in.
A context aware AI Assistant which knows about the tables, their relationships and other objects in the database would be immensely helpful. So I decided to build one. Gitora Editor 2.0 is my second iteration of a schema-aware AI Assistant.
If you think this is interesting and useful, you can download the Gitora Editor from this link. I'd appreciate any feedback. This whole thing is pretty new and I am interested in knowing how you are using the Gitora AI Assistant and how you think I can make it better.
Gitora AI Chat: Write SQL with AI
Introducing the Gitora AI Assistant: Write, debug and explain SQL and PL/SQL with the help of AI.
Gitora AI Assistant knows about your tables, their structure and their relationships so that it can give relevant answers.
To learn more and download click at this link.
PL/SQL Editor with AI Assistant
Introducing Gitora PL/SQL AI Assistant. It suggests PL/SQL code in real-time right from the Gitora Editor.
This was one of the MOST INTERESTING features I've ever worked on. The first iteration is a small step in the right direction. But the possibilities are ENDLESS.
I'd truly appreciate it if you could try it out and provide feedback so that I can improve it. Ideas to where to apply AI next are most welcome.
You can download the editor from this link: https://www.gitora.com/plsql_editor.html
Pages
