Skip to content

Instantly share code, notes, and snippets.

@gkazior

gkazior/union_all_fbi_ext.sql

Last active January 12, 2016 10:31
Show Gist options
  • Save gkazior/fb4cf520d4eaab38a399 to your computer and use it in GitHub Desktop.
Save gkazior/fb4cf520d4eaab38a399 to your computer and use it in GitHub Desktop.
Download ZIP
Modification of union_all_fbi.sql - added query on PLSQL table
Raw
union_all_fbi_ext.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;
CREATE TYPE test_ITable IS TABLE OF NUMBER;
CREATE OR REPLACE PACKAGE test_ExpV AS
gt_Id test_ITable;
FUNCTION fiGetCount RETURN PLS_INTEGER;
FUNCTION fiGetId(pi_Idx PLS_INTEGER) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY test_ExpV AS
FUNCTION fiGetCount RETURN PLS_INTEGER IS BEGIN RETURN gt_Id.COUNT; END fiGetCount;
FUNCTION fiGetId(pi_Idx PLS_INTEGER) RETURN NUMBER IS BEGIN RETURN gt_Id(pi_Idx); END fiGetId;
END test_ExpV;
/
CREATE OR REPLACE FORCE VIEW test_ExpV_small
AS SELECT test_ExpV.fiGetId(ROWNUM) Id
FROM DUAL CONNECT BY LEVEL <= test_ExpV.fiGetCount
/
BEGIN
test_ExpV.gt_Id := test_ITable();
test_ExpV.gt_Id.EXTEND(3);
test_ExpV.gt_Id( 1 ) := 1;
test_ExpV.gt_Id( 2 ) := 2;
test_ExpV.gt_Id( 3 ) := 3;
END;
-- Got FULL SCAN HERE. PUSH_PRED does not work! However when I add BETWEEN predicate ... see the next plan!
EXPLAIN PLAN FOR
SELECT /*+ FIRST_ROWS PUSH_PRED(v) */ * FROM
(SELECT * FROM test_expv1 UNION ALL
SELECT * FROM test_expv2) v
INNER JOIN test_ExpV_small small
ON (DECODE (VALUE, -1, CAST (NULL AS NUMBER (38)), 0, CAST (NULL AS NUMBER (38)),VALUE)) = small.id;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
/*
Plan hash value: 1717111280
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 507K| 903 (2)| 00:00:11 |
| 1 | NESTED LOOPS | | 20000 | 507K| 903 (2)| 00:00:11 |
| 2 | VIEW | TEST_EXPV_SMALL | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | COUNT | | | | | |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 6 | VIEW | | 20000 | 253K| 901 (2)| 00:00:11 |
| 7 | UNION-ALL | | | | | |
| 8 | TABLE ACCESS FULL | TEST_EXPV1 | 1000K| 3906K| 451 (2)| 00:00:06 |
| 9 | TABLE ACCESS FULL | TEST_EXPV2 | 1000K| 3906K| 451 (2)| 00:00:06 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(LEVEL<="TEST_EXPV"."FIGETCOUNT"())
6 - filter("SMALL"."ID"=DECODE("VALUE",(-1),CAST(NULL AS NUMBER (38)),0,CAST(NULL AS
*/
-- ... when I add BETWEEN predicate ... I do not need PUSH_PRED! Isn't it strange?
EXPLAIN PLAN FOR
SELECT /*+ FIRST_ROWS PUSH_PRED(v) */ * FROM
(SELECT * FROM test_expv1 UNION ALL
SELECT * FROM test_expv2) v
INNER JOIN test_ExpV_small small
ON (DECODE (VALUE, -1, CAST (NULL AS NUMBER (38)), 0, CAST (NULL AS NUMBER (38)),VALUE)) = small.id;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
/*
Plan hash value: 1184350530
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 1300 | 995 (11)| 00:00:12 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 50 | 1300 | 995 (11)| 00:00:12 |
|* 3 | VIEW | TEST_EXPV_SMALL | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | COUNT | | | | | |
|* 5 | CONNECT BY WITHOUT FILTERING| | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 7 | VIEW | | 50 | 650 | 993 (11)| 00:00:12 |
| 8 | UNION-ALL | | | | | |
|* 9 | FILTER | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| TEST_EXPV1 | 2500 | 10000 | 4192 (1)| 00:00:51 |
|* 11 | INDEX RANGE SCAN | I_TEST_EXPV1 | 4500 | | 11 (0)| 00:00:01 |
|* 12 | FILTER | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| TEST_EXPV2 | 2500 | 10000 | 4192 (1)| 00:00:51 |
|* 14 | INDEX RANGE SCAN | I_TEST_EXPV2 | 4500 | | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
*/
-- Similar query without INNER JOIN which works nice!
EXPLAIN PLAN FOR
SELECT /*+ FIRST_ROWS a */ * FROM (
SELECT * FROM TEST_EXPV1
UNION ALL SELECT * FROM TEST_EXPV2
) U, (SELECT Id FROM test_ExpV_small WHERE Id BETWEEN :a AND :b) x
WHERE DECODE(Value, -1, CAST(NULL AS NUMBER(38)), 0, CAST(NULL AS NUMBER(38)), Value) = x.id
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
/*
Plan hash value: 1184350530
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 1300 | 995 (11)| 00:00:12 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 50 | 1300 | 995 (11)| 00:00:12 |
|* 3 | VIEW | TEST_EXPV_SMALL | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | COUNT | | | | | |
|* 5 | CONNECT BY WITHOUT FILTERING| | | | | |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 7 | VIEW | | 50 | 650 | 993 (11)| 00:00:12 |
| 8 | UNION-ALL | | | | | |
|* 9 | FILTER | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| TEST_EXPV1 | 2500 | 10000 | 4192 (1)| 00:00:51 |
|* 11 | INDEX RANGE SCAN | I_TEST_EXPV1 | 4500 | | 11 (0)| 00:00:01 |
|* 12 | FILTER | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| TEST_EXPV2 | 2500 | 10000 | 4192 (1)| 00:00:51 |
|* 14 | INDEX RANGE SCAN | I_TEST_EXPV2 | 4500 | | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
3 - filter("ID">=TO_NUMBER(:A) AND "ID"<=TO_NUMBER(:B))
5 - filter(LEVEL<="TEST_EXPV"."FIGETCOUNT"())
7 - filter("ID"=DECODE("VALUE",(-1),CAST(NULL AS NUMBER(38)),0,CAST(NULL AS
NUMBER(38)),"VALUE"))
9 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
11 - access(DECODE("VALUE",(-1),CAST(NULL AS NUMBER(38)),0,CAST(NULL AS
NUMBER(38)),"VALUE")>=TO_NUMBER(:A) AND DECODE("VALUE",(-1),CAST(NULL AS
NUMBER(38)),0,CAST(NULL AS NUMBER(38)),"VALUE")<=TO_NUMBER(:B))
12 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
14 - access(DECODE("VALUE",(-1),CAST(NULL AS NUMBER(38)),0,CAST(NULL AS
NUMBER(38)),"VALUE")>=TO_NUMBER(:A) AND DECODE("VALUE",(-1),CAST(NULL AS
NUMBER(38)),0,CAST(NULL AS NUMBER(38)),"VALUE")<=TO_NUMBER(:B))
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment