Thanks for the question, Alan.
Asked: September 27, 2016 - 7:02 pm UTC
Last updated: October 07, 2016 - 4:11 am UTC
Version: 11G R2
Viewed 1000+ times
You Asked
I am attempting to push the Oracle optimizer toward a partition-pruning path unsuccessfully.
Any thoughts/ideas appreciated
CREATE TABLE small_table (p_key.....value1...value2....value120.)
-- 40k Records
indexes on each value, and one on (value1,p_key) combination
CREATE TABLE largest_table(....small_p_key
..shared_key....
CONSTRAINT..FOREIGN KEY(small_p_key)
REFERENCES small_table(p_key)
)
PARTITION BY small_p_key....
-- 100mm records, each small_p_key around 2K records
-- Index on shared_key
CREATE TABLE large table (...shared_key...)
-- 20mm records
-- Index on shared_key
The majority of the accesslooks like:
CREATE TABLE randomname
SELECT...
FROM small_table a1
JOIN largest_table a2
ON a1.small_p_key = a2.p_key
JOIN large_table a3
ON a3.shared_key = a2.shared_key
WHERE a1.value* BETWEEN 1 and 24 -- diff values can be used..
The resulting number of records from largest_table tends to be between 100 and 20,000
The optimizer is not using parition pruning when choosing the query path.
Instead it sees the 100mm size and chooses to route through large_table first and index scan largest_table.
Queries take exceedingly long to execute. The path is choosing shared_key index scan on largest_table.
If the stats on largest_table are manipulated to set the count to 1mm, the optimizer then routes from small_table to largest, prunes, and index scans large_table.
Queries are sub-second. This is not a long term solution.
In an attempt to influence the optimizer, I tried a recommended Reference strategy:
-----------------------------------------------------------------------------------
CREATE TABLE small_table (p_key.....)
PARTITION BY RANGE (p_key)......
(About 40K rows)
CREATE TABLE largest_table (..small_p_key.)
PARTITION BY REFERENCE (small_p_key)
hoping that the optimizer could then see the path from small_table (grab 5-10 rows), to largest_table (prune to a few thousand rows), to large_table (index lookup the rows).
however, that was not what happened.
Additional notes:
11g R2
I cannot use Profiles/Baselines as each query is different (table name).
I cannot use hints as the table aliases a1, a2...are re-used for different in the same scope where I would supply the hints
I cannot drop/invisible the shared_key index on largest_table as other workloads require it.
How can I ..convince...the optimizer to use the pruning path with the correct stats are in place on all the tables?
and Connor said...
Looks fine to me ... I see a bloom filter being to used to isolate partitions as I would expect
SQL> drop table small_table cascade constraints purge;
Table dropped.
SQL> CREATE TABLE small_table (p_key int primary key, val1 int, val2 int);
Table created.
SQL> insert /*+ APPEND */ into small_table
2 select rownum, mod(rownum,100), trunc(rownum/100)
3 from dual
4 connect by level <= 40000;
40000 rows created.
SQL>
SQL> create index small_table_ix1 on small_table ( val1,p_key );
Index created.
SQL> create index small_table_ix2 on small_table ( val2,p_key );
Index created.
SQL>
SQL> drop table large_table cascade constraints purge;
Table dropped.
SQL>
SQL> CREATE TABLE large_table (p_key int, blah2 int
2 );
Table created.
SQL>
SQL> insert /*+ APPEND */ into large_table
2 select mod(rownum,40000)+1, rownum
3 from
4 ( select 1 from dual connect by level <= 2000 ),
5 ( select 1 from dual connect by level <= 1000 );
2000000 rows created.
SQL>
SQL> alter table large_table add CONSTRAINT large_table_fk FOREIGN KEY(p_key) REFERENCES small_table(p_key) ;
Table altered.
SQL> create index large_table_ix on large_table ( p_key );
Index created.
SQL>
SQL>
SQL> drop table largest_table cascade constraints purge;
Table dropped.
SQL>
SQL> CREATE TABLE largest_table(
2 p_key int, blah int
3 )
4 --
5 PARTITION BY range ( p_key )
6 interval ( 2000 )
7 ( partition p1 values less than ( 0 )
8 )
9 /
Table created.
SQL>
SQL> insert /*+ APPEND */ into largest_table
2 select mod(rownum,40000)+1, rownum
3 from
4 ( select 1 from dual connect by level <= 10000 ),
5 ( select 1 from dual connect by level <= 10000 );
100000000 rows created.
SQL> alter table largest_table add CONSTRAINT largest_table_fk FOREIGN KEY(p_key) REFERENCES small_table(p_key) ;
Table altered.
SQL> create index largest_table_ix on largest_table ( p_key ) local;
Index created.
SELECT *
FROM small_table a1
JOIN largest_table a2
ON a1.p_key = a2.p_key
JOIN large_table a3
ON a3.p_key = a2.p_key
WHERE a1.val1 = 10;
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49M| 1546M| | 43521 (3)| 00:00:02 | | |
|* 1 | HASH JOIN | | 49M| 1546M| 33M| 43521 (3)| 00:00:02 | | |
|* 2 | HASH JOIN | | 991K| 21M| | 38908 (2)| 00:00:02 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 400 | 4800 | | 19 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS FULL | SMALL_TABLE | 400 | 4800 | | 19 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE JOIN-FILTER| | 100M| 1049M| | 38628 (2)| 00:00:02 |:BF0000|:BF0000|
| 6 | TABLE ACCESS FULL | LARGEST_TABLE | 100M| 1049M| | 38628 (2)| 00:00:02 |:BF0000|:BF0000|
| 7 | TABLE ACCESS FULL | LARGE_TABLE | 2000K| 19M| | 755 (2)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A3"."P_KEY"="A2"."P_KEY")
2 - access("A1"."P_KEY"="A2"."P_KEY")
4 - filter("A1"."VAL1"=10)
Note
-----
- this is an adaptive plan
25 rows selected.
SELECT *
FROM small_table a1
JOIN largest_table a2
ON a1.p_key = a2.p_key
JOIN large_table a3
ON a3.p_key = a2.p_key
WHERE a1.val1 BETWEEN 1 and 24;
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1239M| 38G| | 86427 (5)| 00:00:04 | | |
|* 1 | HASH JOIN | | 1239M| 38G| 41M| 86427 (5)| 00:00:04 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 2000K| 19M| | 755 (2)| 00:00:01 | | |
| 3 | TABLE ACCESS FULL | LARGE_TABLE | 2000K| 19M| | 755 (2)| 00:00:01 | | |
|* 4 | HASH JOIN | | 25M| 548M| | 38908 (2)| 00:00:02 | | |
| 5 | PART JOIN FILTER CREATE| :BF0001 | 10093 | 118K| | 19 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS FULL | SMALL_TABLE | 10093 | 118K| | 19 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE AND | | 100M| 1049M| | 38628 (2)| 00:00:02 |KEY(AP)|KEY(AP)|
| 8 | TABLE ACCESS FULL | LARGEST_TABLE | 100M| 1049M| | 38628 (2)| 00:00:02 |KEY(AP)|KEY(AP)|
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A3"."P_KEY"="A2"."P_KEY")
4 - access("A1"."P_KEY"="A2"."P_KEY")
6 - filter("A1"."VAL1"<=24 AND "A1"."VAL1">=1)
22 rows selected.