Oracle experiment table with nullable validfrom/to dates and randomly generated data

oracle-test1.sql

CREATE TABLE x_nitest (
  oid       VARCHAR2(36 CHAR) NOT NULL,
  validFrom TIMESTAMP,
  validTo   TIMESTAMP,

  PRIMARY KEY (oid)
);
CREATE INDEX x_nitest_from ON x_nitest(validFrom);
CREATE INDEX x_nitest_to ON x_nitest(validTo);

-- returns random timestamp between today and +-offset days
-- in 30% returns NULL
CREATE OR REPLACE FUNCTION rndts(offset IN NUMBER)
  RETURN TIMESTAMP
  IS
  l_ran_time TIMESTAMP;
BEGIN
  IF (dbms_random.value(0, 1)) < 0.3
  THEN
    RETURN NULL;
  END IF;

  SELECT SYSDATE + dbms_random.value(0, offset)
  INTO l_ran_time
  FROM dual;
  RETURN l_ran_time;
END;
/

BEGIN
  FOR v_LoopCounter IN 1..100000
    LOOP
      INSERT INTO x_nitest
      -- converts GUID to UUID string with hyphens
      VALUES (regexp_replace(
                      rawtohex(sys_guid()),
                      '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})',
                      '\1-\2-\3-\4-\5'),
              rndts(-1000),
              rndts(400));
    END LOOP;
  COMMIT;
END;

SELECT *
FROM x_nitest
;

-- this does not use index, too much data is scanned anyway
SELECT count(*)
FROM x_nitest
WHERE validFrom IS NOT NULL AND validTo IS NOT NULL
;

-- this uses index and both filters are low-cost + UNION ALL is used for OR
-- but ORDER is typically 10x more costly than both filters together (tested on ~10-2% of total table rows returned)
-- SELECT count(*)
select *
FROM x_nitest
WHERE validTo > sysdate + 100 AND validTo <= SYSDATE + 150
      OR validFrom > sysdate - 100 AND validFrom <= sysdate - 50
ORDER BY oid
;