Union all and function based indexes experiments

union_all_fbi.sql

CREATE TABLE test_ExpV1 (value NUMBER(38));
CREATE TABLE test_ExpV2 (value NUMBER(38));

CREATE TABLE test_10r   (Id NUMBER(38) NOT NULL);
INSERT INTO  test_10r   SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 10;

INSERT INTO test_ExpV1
          SELECT TRUNC(dbms_random.value(1,  10000)) FROM dual CONNECT BY LEVEL <=  10000
UNION ALL SELECT 0                                   FROM dual CONNECT BY LEVEL <= 500000
UNION ALL SELECT -1                                  FROM dual CONNECT BY LEVEL <= 490000;

INSERT INTO test_ExpV2 SELECT * FROM test_ExpV1;

--DROP INDEX I_test_ExpV1;
--DROP INDEX I_test_ExpV2;

CREATE  INDEX I_test_ExpV1  ON test_ExpV1((DECODE(Value, -1, CAST(NULL AS NUMBER(38)), 0, CAST(NULL AS NUMBER(38)), Value)));
CREATE  INDEX I_test_ExpV2  ON test_ExpV2((DECODE(Value, -1, CAST(NULL AS NUMBER(38)), 0, CAST(NULL AS NUMBER(38)), Value)));

DECLARE
  PROCEDURE rStat(pv_TableName VARCHAR2) IS
  BEGIN
    dbms_stats.gather_table_stats
      (ownname          => USER
      ,tabname          => pv_TableName
      ,estimate_percent => null
      ,cascade          => true
      ,method_opt       => 'FOR ALL HIDDEN COLUMNS SIZE 254'
      );
  END;
BEGIN
  rStat('test_ExpV1');
  rStat('test_ExpV2');
  rStat('test_10r');
END;

COMMIT;