Can partitioning be bypassed in oracle sql 12c?

Question

I have a table with about 25M rows in partitioned on a time key (month). Inside every partition are about 200K rows.

We use this table in a BI context, meaning that business users query on this table with queries that, apart from filter values, are always the same.

Mostly the end users query on a data range of 24 months, meaning that 24 partitions are needed to look into for data. We feel that if we query over the same table without partitions, the query is by far a lot faster than over a partitioned table. So it is our belief that partitioned tables aren't that interesting when looked into too many (in this case 24) partitions.

Is there some parameter (alter session ...) or hint we can use to not make the optimizer use the partitioning but scan over the table in another way?

You shoud make a local index for columns wich are used in filter. Every partition has record in catalogue. So oracle is watching for each partition by the key in the catalogue. Also compress old partition can reduce IO operation — MichalSv
Try disabling parallel scanning of partitions.select/*+ NO_PARALLEL*/ * from or decrease the level of parallelism select/*+ PARALLEL 2 */ * from. — Dmitry Demin
Do you have local indexes? If yes, then try to use GLOBAL indexes. If you query 24 partitions with local indexes, then it means Oracle has to scan 24 distinct indexes - which takes some time of course. — Wernfried Domscheit

Chris Saxon Chris Saxon · Accepted Answer · 2019-08-19T15:01:54

Each partition has its own segments. So the database physically stores the rows for each in separate locations. Assuming you need to access the table, you have to read all the relevant partitions.

So no, you can't "ignore" or bypass partitioning.

And there's no way to unpartition a table either. You'll have to recreate it, which is best done with dbms_redefinition.

Or you could just merge the partitions together:

create table t ( 
  c1, c2, c3, c4
) partition by range ( c1 ) (
  partition p0 values less than ( 10 ),
  partition p1 values less than ( 20 ),
  partition p2 values less than ( 30 ),
  partition p3 values less than ( 40 ),
  partition p4 values less than ( 50 )
) as
  select level c1, sysdate + level c2 ,
         round ( dbms_random.value ( 1, 100 ) ) c3,
         dbms_random.string ( 'a', 20 ) c4
  from   dual
  connect by level < 50;

alter table t 
  merge partitions p0, p1, p2, p3, p4
  into partition p4;

select partition_name 
from   user_tab_partitions
where  table_name = 'T';

PARTITION_NAME   
P4

Of course, query performance is just one reason for partitioning. There are many other reasons to use it, such as:

Fast & easy data archival via drop/truncate partition
Fast data loads with exchange partition
Stopping people changing old data by marking partitions as read only
Storing older data on slower (cheaper) disk
...

So it's worth checking that you're not making use of other partitioning features before wiping them out.

That said, ~200k rows/partition and 25M total does seem a little on the small side to be worth partitioning to me.

Technically, there is another option...

business users query on this table with queries that, apart from filter values, are always the same.

Are these aggregation (count, sum, avg, etc. ) queries? e.g.:

  select customer_id, count(*)
  from   ...
  where  ...
  group  by customer_id

If so materialized views (MVs) may be a good option to "bypass" partitioning.

create materialized view mv as
  select customer_id, count(*)
  from   ...
  where  ...
  group  by customer_id

You can partition (or not) these in a different way to the base tables. With added the advantage that if your queries typically process "many" rows but return "few", using the MV could be much faster.

Can partitioning be bypassed in oracle sql 12c?

1 Answers