Database Reference
In-Depth Information
vc2 varchar2(100),
n1 number
)
partition by range (d)
interval (NUMTODSINTERVAL(7, 'day'))
(
partition p_start values less than (to_date('01.01.2008','dd.mm.yyyy'))
);
The interval portioning algorithm created 53 partitions in addition to the initial one. The table is populated with
random dates and some padding for a year in the future. A small PL/SQL procedure executed every second by ten
scheduler jobs should bring the table to the attention of the access tracking algorithm:
CREATE PROCEDURE p
IS
CURSOR c
IS
SELECT d, vc1
FROM t1
WHERE d = TRUNC(to_date('23.07.2014', 'dd.mm.yyyy') - DBMS_RANDOM.value(0,128));
TYPE t IS TABLE OF c%rowtype INDEX BY pls_integer;
v_t t;
BEGIN
OPEN c;
LOOP
FETCH c bulk collect INTO v_t limit 500;
EXIT
WHEN v_t.count < 500;
END LOOP;
END;
Notice that the code deliberately leaves out half the data created to satisfy the “no access” criteria for the ILM
process. After a little while individual partitions of T1 appeared in
V$HEAT_MAP_SEGMENT
:
SQL> select object_name, subobject_name, segment_write, segment_read,
2 full_scan,lookup_scan
3 from v$heat_map_segment where object_name = 'T1'
OBJECT_NAM SUBOBJECT_ SEG SEG FUL LOO
---------- ---------- --- --- --- ---
T1 SYS_P623 NO NO YES YES
T1 SYS_P641 NO NO YES YES
[...]
T1 SYS_P635 NO NO YES YES
T1 SYS_P660 NO NO YES NO
53 rows selected.
Search WWH ::
Custom Search