You Asked

Hallo Tom

I wonder if something like "partitioned plans" does exist - I never saw them.

In order to specify, what I mean by this, an example:

"autotrace traceonly explain" shows me the following execution plan when (equi-)joining two partitioned tables (both are IOT, in the example both are range-partitioned, no subpartitions):

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                   |   278K|    40M|       |  6406K  (4)| 07:45:14 |       |       |
|*  1 |  FILTER                    |                   |       |       |       |            |          |       |       |
|*  2 |   HASH JOIN                |                   |   278K|    40M|   585M|  6406K  (4)| 07:45:14 |       |       |
|   3 |    PARTITION RANGE ITERATOR|                   |    18M|   379M|       |   700K  (7)| 00:50:52 |   KEY |   KEY |
|*  4 |     INDEX FAST FULL SCAN   | SYS_IOT_TOP_51976 |    18M|   379M|       |   700K  (7)| 00:50:52 |   KEY |   KEY |
|   5 |    PARTITION RANGE ITERATOR|                   |    31M|  3928M|       |  5619K  (3)| 06:48:05 |   KEY |   KEY |
|*  6 |     INDEX FAST FULL SCAN   | SYS_IOT_TOP_69242 |    31M|  3928M|       |  5619K  (3)| 06:48:05 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------------------------

Partition-Pruning takes place, since in my where-clause I have specified for both tables ranges on the partition keys.

So, the optimizer knows, he has to join partition p1 to pn of table1 to partition q1 to qm of table2. In my example, it uses SYS_IOT_TOP_51976 (table1, partition p1 to pn) as build table and hash-joins this together to (table2, partition q1 to qm).

In my opinition, it could also decide to split this join into m*n joins.
With skew data in the partitions (for example, partition p2 has 10 rows, partition p3 100000) and indexes (for simplicity, say local indexes), some of these m*n joins could be better done via hash join, others via nested loops.

Does this occur ? As I said, In never saw it.
If yes, can you give an example ?

Thanks

and Tom said...

Not yet, but getting close perhaps. It would be, could be rather hugely expensive as far as optimization goes (the number of possible plans goes up rather quickly if you have to consider every possible way that way).

But the framework for it exists in current releases. for example, in 11gR2 - you can have indexes on some partitions - but not others - and the optimizer can in some cases take advantage of that:

ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('01-jan-2010','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('01-jan-2011','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte%ORA11GR2> insert into t
  2  select to_date('01-jun-2010','dd-mon-yyyy'), rownum, object_name
  3  from all_objects;

71923 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> create index t_idx on t(x) local unusable;

Index created.

ops$tkyte%ORA11GR2> alter index t_idx rebuild partition part2;

Index altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where x = 42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1468541238

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |     1 |    37 |     4   (0)| 00:00:01 |       |       |
|   1 |  VIEW                                | VW_TE_2 |     2 |    78 |     4   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                          |         |       |       |            |          |       |       |
|   3 |    PARTITION RANGE SINGLE            |         |     1 |    37 |     2   (0)| 00:00:01 |     2 |     2 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T       |     1 |    37 |     2   (0)| 00:00:01 |     2 |     2 |
|*  5 |      INDEX RANGE SCAN                | T_IDX   |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |
|   6 |    PARTITION RANGE OR                |         |     1 |    37 |     2   (0)| 00:00:01 |KEY(OR)|KEY(OR)|
|*  7 |     TABLE ACCESS FULL                | T       |     1 |    37 |     2   (0)| 00:00:01 |KEY(OR)|KEY(OR)|
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("X"=42)
   7 - filter("X"=42 AND ("T"."DT"<TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR
              "T"."DT">=TO_DATE(' 2011-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "T"."DT" IS NULL))

<b>but it doesn't appear to kick in for a join</b>

ops$tkyte%ORA11GR2> select * from t, (select 42 y from dual) d where t.x = d.y;

Execution Plan
----------------------------------------------------------
Plan hash value: 1848200259

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    37 |   139   (1)| 00:00:02 |       |       |
|   1 |  NESTED LOOPS        |      |     1 |    37 |   139   (1)| 00:00:02 |       |       |
|   2 |   FAST DUAL          |      |     1 |       |     2   (0)| 00:00:01 |       |       |
|   3 |   PARTITION RANGE ALL|      |     1 |    37 |   137   (1)| 00:00:02 |     1 |     3 |
|*  4 |    TABLE ACCESS FULL | T    |     1 |    37 |   137   (1)| 00:00:02 |     1 |     3 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T"."X"=42)

ops$tkyte%ORA11GR2> select * from t, (select 42 y from dual) d where t.dt = to_date('01-jun-2010') and t.x = d.y;

Execution Plan
----------------------------------------------------------
Plan hash value: 2578488247

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |     1 |    37 |     4   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                       |       |     1 |    37 |     4   (0)| 00:00:01 |       |       |
|   2 |   FAST DUAL                         |       |     1 |       |     2   (0)| 00:00:01 |       |       |
|   3 |   PARTITION RANGE SINGLE            |       |     1 |    37 |     2   (0)| 00:00:01 |     2 |     2 |
|*  4 |    TABLE ACCESS BY LOCAL INDEX ROWID| T     |     1 |    37 |     2   (0)| 00:00:01 |     2 |     2 |
|*  5 |     INDEX RANGE SCAN                | T_IDX |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T"."DT"=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - access("T"."X"=42)

ops$tkyte%ORA11GR2> set autotrace off

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thanks

Sokrates, October 06, 2010 - 7:27 am UTC

for this example and - since the framework now exists - waiting impatiently for further development of this topic in V12 and further

Partially doable in 10.2 ?

Hemant K Chitale, October 13, 2010 - 12:04 am UTC

Could be doable in 10.2 as well ? But I've tested this for only a single table query.

See http://hemantoracledba.blogspot.com/2010/08/creating-sparse-index.html

October 13, 2010 - 6:58 am UTC

the new thing in 11g is the ability to create a plan that uses the index where the index exists AND NOT use it where it doesn't - in the same plan.

In your case - the plan is always "use the index" and the query succeeds only if targeted against a partition that has the index.

Exactly the same as what the comment on your blog post pointed out.

Question on Oracle Magazine

Sokrates, February 28, 2011 - 1:28 am UTC

happy that you found this question interesting enough to publish it on Oracle Magazine March/April 2011 !

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

thanks

Partially doable in 10.2 ?

Question on Oracle Magazine

More to Explore

VLDB

VLDB