Oracle Partition Trim Not Working When Outer Join Is Used
I have one table which is a list separated by a numeric column (row_id),
TABLEA (ROW_ID NUMERIC(38), TB_KEY NUMERIC(38), ROW_DATA VARCHAR(20));
Section clipping works when I query from a table without joins:
SELECT A.* FROM TABLEA A
WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);
Section clipping fails when I leave outer join in TableB
SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);
Section cropping works when I change the left outer join to inner join
SELECT A.* FROM TABLEA A
INNER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);
Section clipping works when I leave the outer join in TableB and don't use the IN clause
SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE ROW_ID = 123;
Section clipping works when I left outer join in TableB and used static values for IN clause
SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE ROW_ID IN (123, 345);
Can someone explain to me why the remaining outer join will cause the section to fail when I query a column that is sectioned using an IN clause with the result from a subquery?
source to share
The answer for Oracle 11g is YES, partition pruning works great.
There are three main access patterns in your setup: table list partitioned
TABLEA
, lets you skip all of them. Note that I am using the simplest possible statements to illustrate the behavior.
Keyed Access in Equal Predicate or IN List
The simplest case is to use a literal in an equal predicate per section key:
SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE A.ROW_ID = 123;
This leads to the following execution plan
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 4 (0)| 00:00:01 | | |
|* 1 | HASH JOIN OUTER | | 1 | 51 | 4 (0)| 00:00:01 | | |
| 2 | PARTITION LIST SINGLE| | 1 | 38 | 2 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | TABLEA | 1 | 38 | 2 (0)| 00:00:01 | 2 | 2 |
| 4 | TABLE ACCESS FULL | TABLEB | 1 | 13 | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."TB_KET"="B"."TB_KEY"(+))
3 - filter("A"."ROW_ID"=123)
Access only to the corresponding section from TABLEA (here section # 2) - see columns Pstart
and Pstop
.
A bit complex, but similar, takes place in IN LIST
SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE ROW_ID IN (123, 345);
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 4 (0)| 00:00:01 | | |
|* 1 | HASH JOIN OUTER | | 1 | 51 | 4 (0)| 00:00:01 | | |
| 2 | PARTITION LIST INLIST| | 1 | 38 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 3 | TABLE ACCESS FULL | TABLEA | 1 | 38 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 4 | TABLE ACCESS FULL | TABLEB | 1 | 13 | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."TB_KET"="B"."TB_KEY"(+))
3 - filter("A"."ROW_ID"=123 OR "A"."ROW_ID"=345)
In this case, you can access more sections, but only those sections that contain keys from are considered IN LIST
.
The same is true for access using a bind variable.
Key accessed from table with NESTED LOOPS
More complex is the case when two tables are joined. When using a nested loop join for each key from TABLEB
Access to TABLEA
. This means that only one section is available for each key, which contains the key.
SELECT A.* FROM TABLEA A
WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 4 (25)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 1 | 60 | 4 (25)| 00:00:01 | | |
| 2 | SORT UNIQUE | | 1 | 22 | 2 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS FULL | TABLEB | 1 | 22 | 2 (0)| 00:00:01 | | |
| 4 | PARTITION LIST ITERATOR| | 100K| 3710K| 1 (0)| 00:00:01 | KEY | KEY |
|* 5 | TABLE ACCESS FULL | TABLEA | 100K| 3710K| 1 (0)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------
Sections KEY
are split again - KEY
so only sections with key from are available TABLEB
, but from the nature of nested loops, one section can be accessed multiple times (for different keys).
Keyed access from table using HASH JOIN
Usage HASH JOIN
is the most difficult case where partitioning must take place before starting the connection. This is how Bloom Filter works .
How it works? After scanning TABLEB
Oracle knows all the corresponding keys from it, these keys can be matched with the corresponding sections and Bloom Filter (BF)
these sections are created (operations 3 and 2). BF is sent to TABLEA
and used to trim sections on it (steps 4 and 5).
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 5859K| 5 (20)| 00:00:01 | | |
|* 1 | HASH JOIN RIGHT SEMI | | 100K| 5859K| 5 (20)| 00:00:01 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 1 | 22 | 2 (0)| 00:00:01 | | |
|* 3 | TABLE ACCESS FULL | TABLEB | 1 | 22 | 2 (0)| 00:00:01 | | |
| 4 | PARTITION LIST JOIN-FILTER| | 100K| 3710K| 2 (0)| 00:00:01 |:BF0000|:BF0000|
| 5 | TABLE ACCESS FULL | TABLEA | 100K| 3710K| 2 (0)| 00:00:01 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ROW_ID"="ID")
3 - filter("DT_COL"=SYSDATE@!)
Cm. Pstart
, Pstop
:BFnnnn
As a sign of a flower filter.
source to share
Trimming sections can work with subquery LEFT OUTER JOIN
and IN
. You are probably seeing a very specific problem that requires a more specific test.
Example circuit
drop table tableb purge;
drop table tablea purge;
create table tablea (row_id numeric(38),tb_key numeric(38),row_data varchar(20))
partition by list(row_id)
(partition p1 values (1),partition p123 values(123),partition p345 values(345));
create table tableb (id numeric(38), dt_col date, tb_key numeric(38));
begin
dbms_stats.gather_table_stats(user, 'TABLEA');
dbms_stats.gather_table_stats(user, 'TABLEB');
end;
/
Inquiries
--Partition pruning works when i query from table with no joins:
explain plan for
SELECT A.* FROM TABLEA A
WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);
select * from table(dbms_xplan.display);
--Partition Pruning fails when I do left outer join to TableB
explain plan for
SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KEY = B.TB_KEY
WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);
select * from table(dbms_xplan.display);
--Partition Pruning works when I change left outer join to inner join
explain plan for
SELECT A.* FROM TABLEA A
INNER JOIN TABLEB B ON A.TB_KEY = B.TB_KEY
WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);
select * from table(dbms_xplan.display);
--Partition Pruning works when I do left outer join to TableB and do not use IN clause
explain plan for
SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KEY = B.TB_KEY
WHERE ROW_ID = 123;
select * from table(dbms_xplan.display);
--Partition Pruning works when I do left outer join to TableB and use static values for IN clause
explain plan for
SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KEY = B.TB_KEY
WHERE ROW_ID IN (123, 345);
select * from table(dbms_xplan.display);
Output
The complete execution plan is not displayed here to save space. The only important columns are Pstart
and Pstop
, which implies the use of section clipping.
Execution plans look like one of the following:
... -----------------
... | Pstart| Pstop |
... -----------------
...
... | KEY | KEY |
... | KEY | KEY |
...
... -----------------
OR
... -----------------
... | Pstart| Pstop |
... -----------------
...
... | 2 | 2 |
... | 2 | 2 |
...
... -----------------
OR
... -----------------
... | Pstart| Pstop |
... -----------------
...
... |KEY(I) |KEY(I) |
... |KEY(I) |KEY(I) |
...
... -----------------
How does it help?
Little. Although you have provided much more information than a typical question, even more information is required to resolve this issue.
At least now you know that the problem is not caused by a general limitation in partitioning. This is a very specific issue, most likely related to optimizer statistics. Troubleshooting such problems can be time consuming. I would suggest starting with the example data above and adding more features and data until the section clipping is gone.
Post a new test case here, changing the question and someone should resolve it.
source to share