Skip to content

Instantly share code, notes, and snippets.

@gkazior
Last active January 11, 2016 15:41
Show Gist options
  • Save gkazior/67fc759dca36b83d1ecd to your computer and use it in GitHub Desktop.
Save gkazior/67fc759dca36b83d1ecd to your computer and use it in GitHub Desktop.
Union all and function based indexes experiments
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment