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 Operator
First, 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;
/
2. Creating the 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.
3. Creating the 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.
Key Features of 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;
4. Creating the 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 Column
Since 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'))
/
6. Creating the Index
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
/
7. Testing the Index
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!
Summary
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.