Oracle Partitioning is a great feature.
Large tables can be partitioned into smaller pieces based on data ranges, values or dates, and queries that are designed to respect the partitioning scheme can automatically discount from consideration partitions that do not contain applicable data.
Partitions can be added or removed, moved, made read only, and so on.
That is only a very brief overview of the benefits of partitioning to large database tables, and anyone who had designed a data warehouse will understand why this feature is so powerful.
But it is also very expensive.
Oracle Partitioning is only available as an add-on cost to the already expensive Enterprise Edition license.
But what about if you want to realize some of these benefits without paying for the Partitioning option? Taking this even further could we implement some form of Partitioning with the massively less expensive Standard Edition?
Oracle Standard Edition 2 is dramatically less expensive that Enterprise Edition, but the DBA loses many features.
An SE2 database is limited to two sockets, even if it is a RAC database.
Dataguard is not supported with SE2, so the DBA would have to use an alternative such as Golden Gate, Shareplex or perhaps DB Visit. Storage array based replication is also an option here.
Oracle Advanced Compression is not supported on SE2, so the DBA would have to rely on storage compression such as is available from the Dell EMC VMAX, XtremIO or Unity arrays, as well as from many competing storage platforms.
Transparent Data Encryption is not support on SE2, so the DBA would have to rely on storage encryption which is available on most non-Oracle Sun storage platforms.
There are other limitations too, such as Parallel Query Option, and SE2 is not suitable for many larger workloads, but as shown above, many of the features the DBA loses are readily available from the infrastructure, and often much less expensive, and much simpler to manage.
But not partitioning.
However there was a time before Oracle has partitioning, and back then we relied on individual tables and views that presented a unified view to deliver a “roll-your-own” partitioning scheme. In the following post I am going to explore if those old approaches still work, and if it is feasible to implement such approaches in an SE2 database to deliver some of the benefits of partitioning, but without the cost of Enterprise Edition.
In this example I have a Swingbench database, and I checked the partitioning flag, so that the larger tables are split into 32 partitions by range. The DDL to create this table is as follows:
CREATE TABLE "SOE"."ORDERS" (
"ORDER_ID" NUMBER(12,0) CONSTRAINT "ORDER_ORDER_ID_NN" NOT NULL ENABLE,
"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE,
"ORDER_MODE" VARCHAR2(8),
"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE,
"ORDER_STATUS" NUMBER(2,0),
"ORDER_TOTAL" NUMBER(8,2),
"SALES_REP_ID"NUMBER(6,0),
"PROMOTION_ID" NUMBER(6,0),
"WAREHOUSE_ID" NUMBER(6,0),
"DELIVERY_TYPE" VARCHAR2(15),
"COST_OF_DELIVERY" NUMBER(6,0),
"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15),
"DELIVERY_ADDRESS_ID" NUMBER(12,0),
"CUSTOMER_CLASS" VARCHAR2(30),
"CARD_ID" NUMBER(12,0),
"INVOICE_ADDRESS_ID" NUMBER(12,0),
CONSTRAINT
"ORDER_PK" PRIMARY KEY ("ORDER_ID") USING INDEX REVERSE PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE
(
INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "SOE" ENABLE NOVALIDATE,
CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE,
CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE,
CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "SOE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE NOVALIDATE
)
PCTFREE 10 PCTUSED 40 INITRANS 16 MAXTRANS 255 STORAGE
(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "SOE"
PARTITION BY HASH ("ORDER_ID") (
PARTITION "SYS_P330" SEGMENT CREATION IMMEDIATE TABLESPACE "SOE" NOCOMPRESS ,
PARTITION "SYS_P331" SEGMENT CREATION IMMEDIATE TABLESPACE "SOE" NOCOMPRESS ,
some lines removed for clarity
PARTITION "SYS_P361" SEGMENT CREATION IMMEDIATE TABLESPACE "SOE" NOCOMPRESS )
;
So to implement this same approach without Partitioning, we would need 32 separate tables with identical columns, and a view that spans all of the partitions.
Below is the DDL for the first table.
The remaining 31 are created in identical fashion, although the DBA may elect to place different tables into different tablespaces, which may map to different ASM diskgroups or file systems.
Note I elected to use the same naming convention that Swingbench chose. In reality you would select something more meaningful.
CREATE TABLE "GCT"."MANPART_SYS_P330" (
"ORDER_ID" NUMBER(12,0) CONSTRAINT "ORDER_ORDER_ID_NN" NOT NULL ENABLE,
"ORDER_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE,
"ORDER_MODE" VARCHAR2(8),
"CUSTOMER_ID" NUMBER(12,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE,
"ORDER_STATUS" NUMBER(2,0),
"ORDER_TOTAL" NUMBER(8,2),
"SALES_REP_ID"NUMBER(6,0),
"PROMOTION_ID" NUMBER(6,0),
"WAREHOUSE_ID" NUMBER(6,0),
"DELIVERY_TYPE" VARCHAR2(15),
"COST_OF_DELIVERY" NUMBER(6,0),
"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15),
"DELIVERY_ADDRESS_ID" NUMBER(12,0),
"CUSTOMER_CLASS" VARCHAR2(30),
"CARD_ID" NUMBER(12,0),
"INVOICE_ADDRESS_ID" NUMBER(12,0),
CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) DEFERRABLE ENABLE NOVALIDATE,
CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) DEFERRABLE ENABLE NOVALIDATE
)
PCTFREE 10 PCTUSED 40 INITRANS 16 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
TABLESPACE "MANPART";
alter table GCT.MANPART_SYS_P330 add constraint "ORDER_P330_PK" PRIMARY KEY ("ORDER_ID")
USING INDEX REVERSE PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING STORAGE
(
INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "MANPART" ENABLE NOVALIDATE;
Once that is done, we create a view across all 32 tables:
create or replace view GCT.MANPART as ( select * from MANPART_SYS_P330 union all select * from MANPART_SYS_P331 union all select * from MANPART_SYS_P332 union all select * from MANPART_SYS_P333 union all select * from MANPART_SYS_P334 union all select * from MANPART_SYS_P335 union all select * from MANPART_SYS_P336 union all select * from MANPART_SYS_P337 union all select * from MANPART_SYS_P338 union all select * from MANPART_SYS_P339 union all select * from MANPART_SYS_P340 union all select * from MANPART_SYS_P341 union all select * from MANPART_SYS_P342 union all select * from MANPART_SYS_P343 union all select * from MANPART_SYS_P344 union all select * from MANPART_SYS_P345 union all select * from MANPART_SYS_P346 union all select * from MANPART_SYS_P347 union all select * from MANPART_SYS_P348 union all select * from MANPART_SYS_P349 union all select * from MANPART_SYS_P350 union all select * from MANPART_SYS_P351 union all select * from MANPART_SYS_P352 union all select * from MANPART_SYS_P353 union all select * from MANPART_SYS_P354 union all select * from MANPART_SYS_P355 union all select * from MANPART_SYS_P356 union all select * from MANPART_SYS_P357 union all select * from MANPART_SYS_P358 union all select * from MANPART_SYS_P359 union all select * from MANPART_SYS_P360 union all select * from MANPART_SYS_P361 );
The original ORDERS tables in Swingbench used a hash partition, which distributes the data somewhat evenly across all partitions.
We will use the same approach here, using the PL/SQL function ora_hash. If we apply the ora_hash function to a given integer value, it will generate a hash key within a given range. Specifying 31 means we get hash keys from 0 through 31, allowing us to distribute data across our 32 partitions.
We can see the results of the function with a simple SELECT statement:
select distinct ora_hash(order_id,31) from soe.orders order by 1; ORA_HASH(ORDER_ID,31) --------------------- 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 rows selected.
We could certainly carefully insert records into the right partitions by determining the hash of the ORDER_ID first, and we may want to do that for a bulk insert, but it is simpler and more effective to let the database do it for us.
We will create an insertion table, which I have called MANPARTI, into which we can insert our data, and then create a trigger to insert the new record into the correct “partition”.
CREATE TABLE "GCT"."MANPARTI" (
"ORDER_ID" NUMBER(12,0),
"ORDER_DATE" TIMESTAMP (6),
"ORDER_MODE" VARCHAR2(8),
"CUSTOMER_ID" NUMBER(12,0),
"ORDER_STATUS" NUMBER(2,0),
"ORDER_TOTAL" NUMBER(8,2),
"SALES_REP_ID"NUMBER(6,0),
"PROMOTION_ID" NUMBER(6,0),
"WAREHOUSE_ID" NUMBER(6,0),
"DELIVERY_TYPE" VARCHAR2(15),
"COST_OF_DELIVERY" NUMBER(6,0),
"WAIT_TILL_ALL_AVAILABLE" VARCHAR2(15),
"DELIVERY_ADDRESS_ID" NUMBER(12,0),
"CUSTOMER_CLASS" VARCHAR2(30),
"CARD_ID" NUMBER(12,0),
"INVOICE_ADDRESS_ID" NUMBER(12,0)
)
PCTFREE 10 PCTUSED 40 INITRANS 16 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
TABLESPACE "MANPART";
alter table gct.manparti add constraint "MANPARTI_UK" PRIMARY KEY ("ORDER_ID")
USING INDEX REVERSE PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING STORAGE
(
INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
)
TABLESPACE "MANPART" ENABLE NOVALIDATE;
The MANPARTI table does not need the same constraints as the 32 partition tables as the insert trigger will immediately try to insert the new record into one of them, and the whole transaction will fail in the event of a violation.
However the unique key will help with the clean up job which will be covered later on.
Next we need a trigger to insert records into the right partition:
DROP TRIGGER manpart_before_insert; CREATE OR REPLACE TRIGGER manpart_before_insert BEFORE INSERT ON manparti FOR EACH ROW DECLARE n_part integer; n_jobs integer; BEGIN -- find the partition id from the new record SELECT ora_hash(:new.order_id,32) INTO n_part FROM dual; if ( n_part=0 ) then insert into GCT.MANPART_SYS_P330 ( order_id, order_date, order_mode, customer_id, order_status, order_total, sales_rep_id, promotion_id, warehouse_id, delivery_type, cost_of_delivery, wait_till_all_available, delivery_address_id, customer_class, card_id, invoice_address_id ) values ( :new.order_id, :new.order_date, :new.order_mode, :new.customer_id, :new.order_status, :new.order_total, :new.sales_rep_id, :new.promotion_id, :new.warehouse_id, :new.delivery_type, :new.cost_of_delivery, :new.wait_till_all_available, :new.delivery_address_id, :new.customer_class, :new.card_id, :new.invoice_address_id ); end if; if ( n_part=1 ) then insert into GCT.MANPART_SYS_P331 ( order_id, order_date, order_mode, customer_id, order_status, order_total, sales_rep_id, promotion_id, warehouse_id, delivery_type, cost_of_delivery, wait_till_all_available, delivery_address_id, customer_class, card_id, invoice_address_id ) values ( :new.order_id, :new.order_date, :new.order_mode, :new.customer_id, :new.order_status, :new.order_total, :new.sales_rep_id, :new.promotion_id, :new.warehouse_id, :new.delivery_type, :new.cost_of_delivery, :new.wait_till_all_available, :new.delivery_address_id, :new.customer_class, :new.card_id, :new.invoice_address_id ); end if; if ( n_part=2 ) then insert into GCT.MANPART_SYS_P332 ( order_id, order_date, order_mode, customer_id, order_status, order_total, sales_rep_id, promotion_id, warehouse_id, delivery_type, cost_of_delivery, wait_till_all_available, delivery_address_id, customer_class, card_id, invoice_address_id ) values ( :new.order_id, :new.order_date, :new.order_mode, :new.customer_id, :new.order_status, :new.order_total, :new.sales_rep_id, :new.promotion_id, :new.warehouse_id, :new.delivery_type, :new.cost_of_delivery, :new.wait_till_all_available, :new.delivery_address_id, :new.customer_class, :new.card_id, :new.invoice_address_id ); end if; if ( n_part=3 ) then insert into GCT.MANPART_SYS_P333 ( order_id, order_date, order_mode, customer_id, order_status, order_total, sales_rep_id, promotion_id, warehouse_id, delivery_type, cost_of_delivery, wait_till_all_available, delivery_address_id, customer_class, card_id, invoice_address_id ) values ( :new.order_id, :new.order_date, :new.order_mode, :new.customer_id, :new.order_status, :new.order_total, :new.sales_rep_id, :new.promotion_id, :new.warehouse_id, :new.delivery_type, :new.cost_of_delivery, :new.wait_till_all_available, :new.delivery_address_id, :new.customer_class, :new.card_id, :new.invoice_address_id ); end if; some code removed to aid clarity if ( n_part=31 ) then insert into GCT.MANPART_SYS_P361 ( order_id, order_date, order_mode, customer_id, order_status, order_total, sales_rep_id, promotion_id, warehouse_id, delivery_type, cost_of_delivery, wait_till_all_available, delivery_address_id, customer_class, card_id, invoice_address_id ) values ( :new.order_id, :new.order_date, :new.order_mode, :new.customer_id, :new.order_status, :new.order_total, :new.sales_rep_id, :new.promotion_id, :new.warehouse_id, :new.delivery_type, :new.cost_of_delivery, :new.wait_till_all_available, :new.delivery_address_id, :new.customer_class, :new.card_id, :new.invoice_address_id ); end if; -- check the partition key is correct if( n_part >31 ) then raise_application_error(-20000, 'hash out of range'); end if; END; /
Obviously the problem with this approach, is that every insert will result in two new records – one is the insert table “MANPARTI” and a second in the right partition.
There are various ways to clean up the MANPARTI insert table, but I have used a DBMS job to do it:
exec dbms_scheduler.drop_job( job_name=> 'MANPARTI_CLEANUP' ) ;
begin
dbms_scheduler.create_job(
job_name => 'MANPARTI_CLEANUP'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin loop delete gct.manparti a where exists ( select 1 from manpart b where b.order_id = a.order_id ) and rownum <= 1000000; exit when SQL%ROWCOUNT sysdate
,repeat_interval => 'FREQ=MINUTELY;INTERVAL=5;'
,enabled => TRUE
,comments => 'MANPARTI clean up job'
);
end;
/
This job executes every five minutes, and deletes in bulk any records in MANPARTI where it finds a matching ORDER_ID in the MANPART view.
The code deletes 1 million records at a time, commits, and then loops until there are no records in MANPARTI that are not in the MANPART view. This is a simple job. If we were to try to implement this style solution on any scale we should consider using the DBMS_PARALLEL package to delete records using multiple concurrent jobs.
Okay so now we have 32 identical MANPART tables, a view that joins them all together, an insert table to allow us to easily insert data, and a clean up job to prune the records in the insert table.
My Swingbench table ORDERS has 4.2 billion records in it. I ran 32 concurrent shell jobs to insert that data into my DIY partitioned tables.
Now let’s see how things perform:
I run this parallel query to aggregate all values in my ORDERS table. I use 256 parallel query slaves as I am processing such a large data set.
col table_name for a25 col tablespace_name for a20 col partition_name for a20 col num_rows for 999,999,999,999 set linesize 132 set pagesize 999 break on customer_class on report compute sum label "Grand Total" of tot_ord on report compute sum label "Sub Total" of tot_ord on customer_class select /*+ parallel(a,256) */ a.customer_class, a.order_mode, count(*) orders, sum(a.order_total) tot_ord from soe.orders a group by a.customer_class, a.order_mode order by 1,2 /
Executing this query on my database yields the following:
CUSTOMER_CLASS ORDER_MO ORDERS TOT_ORD
------------------------------ -------- ---------- ----------
Business direct 57014880 2.8500E+11
online 57100013 2.8553E+11
28876225 1.4431E+11
****************************** ----------
Sub Total 7.1484E+11
Occasional direct 342351699 1.7115E+12
online 342148287 1.7109E+12
173366120 8.6646E+11
****************************** ----------
Sub Total 4.2889E+12
Prime direct 114039792 5.7005E+11
online 114184679 5.7107E+11
57798770 2.8889E+11
****************************** ----------
Sub Total 1.4300E+12
Regular direct 57090481 2.8541E+11
online 57175135 2.8588E+11
28915344 1.4453E+11
****************************** ----------
Sub Total 7.1583E+11
----------
Grand Total 7.1496E+12
12 rows selected.
Elapsed: 00:00:22.24
So about 22 seconds to process the data. The query plan is shown below:
Execution Plan
----------------------------------------------------------
Plan hash value: 2141852998
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 120 | 24756 (1)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 6 | 120 | 24756 (1)| 00:00:01 | | | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 6 | 120 | 24756 (1)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 6 | 120 | 24756 (1)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 6 | 120 | 24756 (1)| 00:00:01 | | | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 6 | 120 | 24756 (1)| 00:00:01 | | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 1429M| 26G| 24590 (1)| 00:00:01 | 1 | 32 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| ORDERS | 1429M| 26G| 24590 (1)| 00:00:01 | 1 | 32 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 256 because of table property
Statistics
----------------------------------------------------------
2526 recursive calls
0 db block gets
21264780 consistent gets
20880019 physical reads
0 redo size
1133 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
315 sorts (memory)
0 sorts (disk)
12 rows processed
Now let’s see how the optimizer handles our manually partitioned data. The query is run against the MANPART view using the same parallel query hint.
select /*+ parallel(a,256) */ a.customer_class, a.order_mode, count(*) orders, sum(a.order_total) tot_ord from gct.manpart a group by a.customer_class, a.order_mode order by 1,2 /
The results as expected are exactly the same – as all of the 4.2 billion records from ORDERS were inserted into the 32 tables that make up the MANPART view.
CUSTOMER_CLASS ORDER_MO ORDERS TOT_ORD
------------------------------ -------- ---------- ----------
Business direct 57014880 2.8500E+11
online 57100013 2.8553E+11
28876225 1.4431E+11
****************************** ----------
Sub Total 7.1484E+11
Occasional direct 342351699 1.7115E+12
online 342148287 1.7109E+12
173366120 8.6646E+11
****************************** ----------
Sub Total 4.2889E+12
Prime direct 114039792 5.7005E+11
online 114184679 5.7107E+11
57798770 2.8889E+11
****************************** ----------
Sub Total 1.4300E+12
Regular direct 57090481 2.8541E+11
online 57175135 2.8588E+11
28915344 1.4453E+11
****************************** ----------
Sub Total 7.1583E+11
----------
Grand Total 7.1496E+12
12 rows selected.
Elapsed: 00:00:21.28
And note that the query execution time is about the same – in this case 21.28 seconds. The explain plan is shown next
Execution Plan
----------------------------------------------------------
Plan hash value: 3571985678
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 174 | 24825 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 6 | 174 | 24825 (1)| 00:00:01 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 6 | 174 | 24825 (1)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 6 | 174 | 24825 (1)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 6 | 174 | 24825 (1)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 6 | 174 | 24825 (1)| 00:00:01 | Q1,00 | PCWP | |
| 7 | VIEW | MANPART | 1430M| 38G| 24659 (1)| 00:00:01 | Q1,00 | PCWP | |
| 8 | UNION-ALL | | | | | | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| MANPART_SYS_P330 | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWP | |
| 11 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 12 | TABLE ACCESS FULL| MANPART_SYS_P331 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 14 | TABLE ACCESS FULL| MANPART_SYS_P332 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWC | |
| 16 | TABLE ACCESS FULL| MANPART_SYS_P333 | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWP | |
| 17 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 18 | TABLE ACCESS FULL| MANPART_SYS_P334 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 19 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 20 | TABLE ACCESS FULL| MANPART_SYS_P335 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 21 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 22 | TABLE ACCESS FULL| MANPART_SYS_P336 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 23 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 24 | TABLE ACCESS FULL| MANPART_SYS_P337 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 25 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 26 | TABLE ACCESS FULL| MANPART_SYS_P338 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 27 | PX BLOCK ITERATOR | | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWC | |
| 28 | TABLE ACCESS FULL| MANPART_SYS_P339 | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWP | |
| 29 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 30 | TABLE ACCESS FULL| MANPART_SYS_P340 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 31 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 32 | TABLE ACCESS FULL| MANPART_SYS_P341 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 33 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 34 | TABLE ACCESS FULL| MANPART_SYS_P342 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 35 | PX BLOCK ITERATOR | | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWC | |
| 36 | TABLE ACCESS FULL| MANPART_SYS_P343 | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWP | |
| 37 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 38 | TABLE ACCESS FULL| MANPART_SYS_P344 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 39 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 40 | TABLE ACCESS FULL| MANPART_SYS_P345 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 41 | PX BLOCK ITERATOR | | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWC | |
| 42 | TABLE ACCESS FULL| MANPART_SYS_P346 | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWP | |
| 43 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 44 | TABLE ACCESS FULL| MANPART_SYS_P347 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 45 | PX BLOCK ITERATOR | | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWC | |
| 46 | TABLE ACCESS FULL| MANPART_SYS_P348 | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWP | |
| 47 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 48 | TABLE ACCESS FULL| MANPART_SYS_P349 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 49 | PX BLOCK ITERATOR | | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWC | |
| 50 | TABLE ACCESS FULL| MANPART_SYS_P350 | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWP | |
| 51 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 52 | TABLE ACCESS FULL| MANPART_SYS_P351 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 53 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 54 | TABLE ACCESS FULL| MANPART_SYS_P352 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 55 | PX BLOCK ITERATOR | | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWC | |
| 56 | TABLE ACCESS FULL| MANPART_SYS_P353 | 44M| 852M| 770 (1)| 00:00:01 | Q1,00 | PCWP | |
| 57 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 58 | TABLE ACCESS FULL| MANPART_SYS_P354 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 59 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 60 | TABLE ACCESS FULL| MANPART_SYS_P355 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 61 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 62 | TABLE ACCESS FULL| MANPART_SYS_P356 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 63 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 64 | TABLE ACCESS FULL| MANPART_SYS_P357 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 65 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 66 | TABLE ACCESS FULL| MANPART_SYS_P358 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 67 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 68 | TABLE ACCESS FULL| MANPART_SYS_P359 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 69 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 70 | TABLE ACCESS FULL| MANPART_SYS_P360 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
| 71 | PX BLOCK ITERATOR | | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWC | |
| 72 | TABLE ACCESS FULL| MANPART_SYS_P361 | 44M| 852M| 771 (1)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 256 because of table property
Statistics
----------------------------------------------------------
2112 recursive calls
0 db block gets
22026932 consistent gets
20883807 physical reads
0 redo size
1133 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
291 sorts (memory)
0 sorts (disk)
12 rows processed
The query plan cost is 24825 versus the 24756 for the partitioned data. Very similar.
This example aggregates all data, so every record has to be visited to calculate the result. What happens if we try select data where partition elimination might help with data that is partitioned?
First we will create a table with a set of ORDER_IDs that live just one partition, and we will sample 1% of orders that exist in that partition.
create table myorderid as ( select order_id from soe.orders partition (sys_p330) sample (0.01));
This gives us a static set of 4554 order ids.
Now let’s aggregate the top 10 orders in that set from our partitioned data:
select * from
(
select /*+ parallel(a,32) */
rank( ) over( order by sum(a.order_total) desc) as rank_metric,
a.order_id,
sum( a.order_total ),
count( a.order_total ),
a.card_id
from
myorderid b,
soe.orders a
where 1=1
and b.order_id = a.order_id
group by a.order_id, a.card_id
)
where rank_metric <= 10
/
Executing this query we get the following result:
RANK_METRIC ORDER_ID SUM(A.ORDER_TOTAL) COUNT(A.ORDER_TOTAL) CARD_ID
----------- ---------- ------------------ -------------------- ----------
1 668915948 9278 1 567761248
2 134721489 8784 1 523272193
3 394456939 8719 1 1480530184
4 691137250 8694 1 1305682262
5 400448004 8686 1 892768488
6 355846911 8665 1 749363249
7 722532427 8615 1 1117003621
8 343256329 8612 1 139969720
9 860903621 8456 1 989794878
10 902951205 8451 1 1419021526
10 rows selected.
Elapsed: 00:00:03.54
And examining the execution plan we can see that Oracle that a single partition would satisfy the query:
Execution Plan
----------------------------------------------------------
Plan hash value: 3503870416
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |I
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4554 | 289K| 164 (2)| 00:00:01 | | | |
|* 1 | VIEW | | 4554 | 289K| 164 (2)| 00:00:01 | | | |
|* 2 | WINDOW SORT PUSHED RANK | | 4554 | 137K| 164 (2)| 00:00:01 | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10002 | 4554 | 137K| 164 (2)| 00:00:01 | | | Q1,02 |
|* 5 | WINDOW CHILD PUSHED RANK | | 4554 | 137K| 164 (2)| 00:00:01 | | | Q1,02 |
| 6 | HASH GROUP BY | | 4554 | 137K| 164 (2)| 00:00:01 | | | Q1,02 |
| 7 | PX RECEIVE | | 4554 | 137K| 164 (2)| 00:00:01 | | | Q1,02 |
| 8 | PX SEND HASH | :TQ10001 | 4554 | 137K| 164 (2)| 00:00:01 | | | Q1,01 |
| 9 | HASH GROUP BY | | 4554 | 137K| 164 (2)| 00:00:01 | | | Q1,01 |
| 10 | NESTED LOOPS | | 4554 | 137K| 162 (0)| 00:00:01 | | | Q1,01 |
| 11 | NESTED LOOPS | | 4554 | 137K| 162 (0)| 00:00:01 | | | Q1,01 |
| 12 | PX RECEIVE | | | | | | | | Q1,01 |
| 13 | PX SEND ROUND-ROBIN | :TQ10000 | | | | | | | Q1,00 |
| 14 | PX SELECTOR | | | | | | | | Q1,00 |
| 15 | TABLE ACCESS FULL | MYORDERID | 4554 | 59202 | 4 (0)| 00:00:01 | | | Q1,00 |
|* 16 | INDEX UNIQUE SCAN | ORDER_PK | 1 | | 0 (0)| 00:00:01 | | | Q1,01 |
| 17 | TABLE ACCESS BY GLOBAL INDEX ROWID| ORDERS | 1 | 18 | 0 (0)| 00:00:01 | ROWID | ROWID | Q1,01 |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RANK_METRIC"<=10)
2 - filter(RANK() OVER ( ORDER BY SUM() DESC )<=10)
5 - filter(RANK() OVER ( ORDER BY SUM() DESC )<=10)
16 - access("B"."ORDER_ID"="A"."ORDER_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=7)
- Degree of Parallelism is 32 because of table property
Statistics
----------------------------------------------------------
642 recursive calls
10 db block gets
18498 consistent gets
6578 physical reads
0 redo size
1150 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
101 sorts (memory)
0 sorts (disk)
10 rows processed
Now let’s try that with our manually partitioned data:
select * from
(
select /*+ parallel(a,32) */
rank( ) over( order by sum(a.order_total) desc) as rank_metric,
a.order_id,
sum( a.order_total ),
count( a.order_total ),
a.card_id
from
myorderid b,
gct.manpart a
where 1=1
and b.order_id = a.order_id
group by a.order_id, a.card_id
)
where rank_metric <= 10
/
RANK_METRIC ORDER_ID SUM(A.ORDER_TOTAL) COUNT(A.ORDER_TOTAL) CARD_ID
----------- ---------- ------------------ -------------------- ----------
1 668915948 9278 1 567761248
2 134721489 8784 1 523272193
3 394456939 8719 1 1480530184
4 691137250 8694 1 1305682262
5 400448004 8686 1 892768488
6 355846911 8665 1 749363249
7 722532427 8615 1 1117003621
8 343256329 8612 1 139969720
9 860903621 8456 1 989794878
10 902951205 8451 1 1419021526
10 rows selected.
Elapsed: 00:00:28.33
The same result, but it took 28.33 seconds. A good deal slower.
Looking at the explain plan we can see why – Oracle has decided to full scan every single one of the 32 tables that comprise the MANPART view:
Execution Plan ---------------------------------------------------------- Plan hash value: 2088743733 ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distr ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 145K| 9248K| | 24683 (1)| 00:00:01 | | | |* 1 | VIEW | | 145K| 9248K| | 24683 (1)| 00:00:01 | | | |* 2 | WINDOW SORT PUSHED RANK | | 145K| 5691K| 7440K| 24683 (1)| 00:00:01 | | | | 3 | PX COORDINATOR | | | | | | | | | | 4 | PX SEND QC (RANDOM) | :TQ10002 | 145K| 5691K| | 24683 (1)| 00:00:01 | Q1,02 | P->S | QC (RAND |* 5 | WINDOW CHILD PUSHED RANK | | 145K| 5691K| | 24683 (1)| 00:00:01 | Q1,02 | PCWP | | 6 | HASH GROUP BY | | 145K| 5691K| 7440K| 24683 (1)| 00:00:01 | Q1,02 | PCWP | | 7 | PX RECEIVE | | 145K| 5691K| | 24683 (1)| 00:00:01 | Q1,02 | PCWP | | 8 | PX SEND HASH | :TQ10001 | 145K| 5691K| | 24683 (1)| 00:00:01 | Q1,01 | P->P | HASH | 9 | HASH GROUP BY | | 145K| 5691K| 7440K| 24683 (1)| 00:00:01 | Q1,01 | PCWP | |* 10 | HASH JOIN | | 145K| 5691K| | 24681 (1)| 00:00:01 | Q1,01 | PCWP | | 11 | PX RECEIVE | | 4554 | 59202 | | 4 (0)| 00:00:01 | Q1,01 | PCWP | | 12 | PX SEND BROADCAST | :TQ10000 | 4554 | 59202 | | 4 (0)| 00:00:01 | Q1,00 | S->P | BROADCAS | 13 | PX SELECTOR | | | | | | | Q1,00 | SCWC | | 14 | TABLE ACCESS FULL| MYORDERID | 4554 | 59202 | | 4 (0)| 00:00:01 | Q1,00 | SCWP | | 15 | VIEW | MANPART | 1430M| 35G| | 24662 (1)| 00:00:01 | Q1,01 | PCWP | | 16 | UNION-ALL | | | | | | | Q1,01 | PCWP | | 17 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 18 | TABLE ACCESS FULL| MANPART_SYS_P330 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 19 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 20 | TABLE ACCESS FULL| MANPART_SYS_P331 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 21 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 22 | TABLE ACCESS FULL| MANPART_SYS_P332 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 23 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 24 | TABLE ACCESS FULL| MANPART_SYS_P333 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 25 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 26 | TABLE ACCESS FULL| MANPART_SYS_P334 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 27 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 28 | TABLE ACCESS FULL| MANPART_SYS_P335 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 29 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 30 | TABLE ACCESS FULL| MANPART_SYS_P336 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 31 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 32 | TABLE ACCESS FULL| MANPART_SYS_P337 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 33 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 34 | TABLE ACCESS FULL| MANPART_SYS_P338 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 35 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 36 | TABLE ACCESS FULL| MANPART_SYS_P339 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 37 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 38 | TABLE ACCESS FULL| MANPART_SYS_P340 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 39 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 40 | TABLE ACCESS FULL| MANPART_SYS_P341 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 41 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 42 | TABLE ACCESS FULL| MANPART_SYS_P342 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 43 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 44 | TABLE ACCESS FULL| MANPART_SYS_P343 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 45 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 46 | TABLE ACCESS FULL| MANPART_SYS_P344 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 47 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 48 | TABLE ACCESS FULL| MANPART_SYS_P345 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 49 | PX BLOCK ITERATOR | | 44M| 766M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 50 | TABLE ACCESS FULL| MANPART_SYS_P346 | 44M| 766M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 51 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 52 | TABLE ACCESS FULL| MANPART_SYS_P347 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 53 | PX BLOCK ITERATOR | | 44M| 766M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 54 | TABLE ACCESS FULL| MANPART_SYS_P348 | 44M| 766M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 55 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 56 | TABLE ACCESS FULL| MANPART_SYS_P349 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 57 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 58 | TABLE ACCESS FULL| MANPART_SYS_P350 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 59 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 60 | TABLE ACCESS FULL| MANPART_SYS_P351 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 61 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 62 | TABLE ACCESS FULL| MANPART_SYS_P352 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 63 | PX BLOCK ITERATOR | | 44M| 766M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 64 | TABLE ACCESS FULL| MANPART_SYS_P353 | 44M| 766M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 65 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 66 | TABLE ACCESS FULL| MANPART_SYS_P354 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 67 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 68 | TABLE ACCESS FULL| MANPART_SYS_P355 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 69 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 70 | TABLE ACCESS FULL| MANPART_SYS_P356 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 71 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 72 | TABLE ACCESS FULL| MANPART_SYS_P357 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 73 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 74 | TABLE ACCESS FULL| MANPART_SYS_P358 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 75 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 76 | TABLE ACCESS FULL| MANPART_SYS_P359 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 77 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 78 | TABLE ACCESS FULL| MANPART_SYS_P360 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | | 79 | PX BLOCK ITERATOR | | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWC | | 80 | TABLE ACCESS FULL| MANPART_SYS_P361 | 44M| 767M| | 771 (1)| 00:00:01 | Q1,01 | PCWP | ---------------------------------------------------------------------------------------------------------------------------
But what if we push the join predicate into the our view?
In the next example we use the NO_MERGE and PUSH_PRED predicates to manipulate the optimization of the query.
select * from
(
select /*+ parallel(a,32) no_merge(a) push_pred(a) */
rank( ) over( order by sum(a.order_total) desc) as rank_metric,
a.order_id,
sum( a.order_total ),
count( a.order_total ),
a.card_id
from
myorderid b,
gct.manpart a
where 1=1
and b.order_id = a.order_id
group by a.order_id, a.card_id
)
where rank_metric <= 10
/
Executing this code yields:
RANK_METRIC ORDER_ID SUM(A.ORDER_TOTAL) COUNT(A.ORDER_TOTAL) CARD_ID
----------- ---------- ------------------ -------------------- ----------
1 668915948 9278 1 567761248
2 134721489 8784 1 523272193
3 394456939 8719 1 1480530184
4 691137250 8694 1 1305682262
5 400448004 8686 1 892768488
6 355846911 8665 1 749363249
7 722532427 8615 1 1117003621
8 343256329 8612 1 139969720
9 860903621 8456 1 989794878
10 902951205 8451 1 1419021526
10 rows selected.
Elapsed: 00:00:09.64
Still not as fast as the partitioned data, but massively faster than the previous query where Oracle full-scanned every one of the 32 tables under the MANPART view.
The execution plan is shown below:
Execution Plan
----------------------------------------------------------
Plan hash value: 4182342666
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 610 | 39650 | 41389 (1)| 00:00:02 |
|* 1 | VIEW | | 610 | 39650 | 41389 (1)| 00:00:02 |
|* 2 | WINDOW SORT PUSHED RANK | | 610 | 17080 | 41389 (1)| 00:00:02 |
| 3 | HASH GROUP BY | | 610 | 17080 | 41389 (1)| 00:00:02 |
| 4 | NESTED LOOPS | | 13789 | 377K| 41386 (1)| 00:00:02 |
| 5 | INDEX FAST FULL SCAN | MYORDERID_PK | 431 | 3017 | 2 (0)| 00:00:01 |
| 6 | VIEW | MANPART | 1 | 21 | 96 (0)| 00:00:01 |
| 7 | UNION ALL PUSHED PREDICATE | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P330 | 1 | 18 | 3 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | ORDER_P330_PK | 1 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P331 | 1 | 18 | 3 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | ORDER_P331_PK | 1 | | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P332 | 1 | 18 | 3 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | ORDER_P332_PK | 1 | | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P333 | 1 | 18 | 3 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | ORDER_P333_PK | 1 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P334 | 1 | 18 | 3 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | ORDER_P334_PK | 1 | | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P335 | 1 | 18 | 3 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | ORDER_P335_PK | 1 | | 2 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P336 | 1 | 18 | 3 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | ORDER_P336_PK | 1 | | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P337 | 1 | 18 | 3 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | ORDER_P337_PK | 1 | | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P338 | 1 | 18 | 3 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | ORDER_SYS_P338_PK | 1 | | 2 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P339 | 1 | 18 | 3 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | ORDER_SYS_P339_PK | 1 | | 2 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P340 | 1 | 18 | 3 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | ORDER_SYS_P340_PK | 1 | | 2 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P341 | 1 | 18 | 3 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | ORDER_SYS_P341_PK | 1 | | 2 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P342 | 1 | 18 | 3 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | ORDER_SYS_P342_PK | 1 | | 2 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P343 | 1 | 18 | 3 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | ORDER_SYS_P343_PK | 1 | | 2 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P344 | 1 | 18 | 3 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | ORDER_SYS_P344_PK | 1 | | 2 (0)| 00:00:01 |
| 38 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P345 | 1 | 18 | 3 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | ORDER_SYS_P345_PK | 1 | | 2 (0)| 00:00:01 |
| 40 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P346 | 1 | 18 | 3 (0)| 00:00:01 |
|* 41 | INDEX UNIQUE SCAN | ORDER_SYS_P346_PK | 1 | | 2 (0)| 00:00:01 |
| 42 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P347 | 1 | 18 | 3 (0)| 00:00:01 |
|* 43 | INDEX UNIQUE SCAN | ORDER_SYS_P347_PK | 1 | | 2 (0)| 00:00:01 |
| 44 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P348 | 1 | 18 | 3 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | ORDER_SYS_P348_PK | 1 | | 2 (0)| 00:00:01 |
| 46 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P349 | 1 | 18 | 3 (0)| 00:00:01 |
|* 47 | INDEX UNIQUE SCAN | ORDER_SYS_P349_PK | 1 | | 2 (0)| 00:00:01 |
| 48 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P350 | 1 | 18 | 3 (0)| 00:00:01 |
|* 49 | INDEX UNIQUE SCAN | ORDER_SYS_P350_PK | 1 | | 2 (0)| 00:00:01 |
| 50 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P351 | 1 | 18 | 3 (0)| 00:00:01 |
|* 51 | INDEX UNIQUE SCAN | ORDER_SYS_P351_PK | 1 | | 2 (0)| 00:00:01 |
| 52 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P352 | 1 | 18 | 3 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | ORDER_SYS_P352_PK | 1 | | 2 (0)| 00:00:01 |
| 54 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P353 | 1 | 18 | 3 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | ORDER_SYS_P353_PK | 1 | | 2 (0)| 00:00:01 |
| 56 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P354 | 1 | 18 | 3 (0)| 00:00:01 |
|* 57 | INDEX UNIQUE SCAN | ORDER_SYS_P354_PK | 1 | | 2 (0)| 00:00:01 |
| 58 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P355 | 1 | 18 | 3 (0)| 00:00:01 |
|* 59 | INDEX UNIQUE SCAN | ORDER_SYS_P355_PK | 1 | | 2 (0)| 00:00:01 |
| 60 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P356 | 1 | 18 | 3 (0)| 00:00:01 |
|* 61 | INDEX UNIQUE SCAN | ORDER_SYS_P356_PK | 1 | | 2 (0)| 00:00:01 |
| 62 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P357 | 1 | 18 | 3 (0)| 00:00:01 |
|* 63 | INDEX UNIQUE SCAN | ORDER_SYS_P357_PK | 1 | | 2 (0)| 00:00:01 |
| 64 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P358 | 1 | 18 | 3 (0)| 00:00:01 |
|* 65 | INDEX UNIQUE SCAN | ORDER_SYS_P358_PK | 1 | | 2 (0)| 00:00:01 |
| 66 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P359 | 1 | 18 | 3 (0)| 00:00:01 |
|* 67 | INDEX UNIQUE SCAN | ORDER_SYS_P359_PK | 1 | | 2 (0)| 00:00:01 |
| 68 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P360 | 1 | 18 | 3 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN | ORDER_SYS_P360_PK | 1 | | 2 (0)| 00:00:01 |
| 70 | TABLE ACCESS BY INDEX ROWID| MANPART_SYS_P361 | 1 | 18 | 3 (0)| 00:00:01 |
|* 71 | INDEX UNIQUE SCAN | ORDER_SYS_P361_PK | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RANK_METRIC"<=10)
2 - filter(RANK() OVER ( ORDER BY SUM("A"."ORDER_TOTAL") DESC )<=10)
9 - access("ORDER_ID"="B"."ORDER_ID")
11 - access("ORDER_ID"="B"."ORDER_ID")
13 - access("ORDER_ID"="B"."ORDER_ID")
15 - access("ORDER_ID"="B"."ORDER_ID")
17 - access("ORDER_ID"="B"."ORDER_ID")
19 - access("ORDER_ID"="B"."ORDER_ID")
21 - access("ORDER_ID"="B"."ORDER_ID")
23 - access("ORDER_ID"="B"."ORDER_ID")
25 - access("ORDER_ID"="B"."ORDER_ID")
27 - access("ORDER_ID"="B"."ORDER_ID")
29 - access("ORDER_ID"="B"."ORDER_ID")
31 - access("ORDER_ID"="B"."ORDER_ID")
33 - access("ORDER_ID"="B"."ORDER_ID")
35 - access("ORDER_ID"="B"."ORDER_ID")
37 - access("ORDER_ID"="B"."ORDER_ID")
39 - access("ORDER_ID"="B"."ORDER_ID")
41 - access("ORDER_ID"="B"."ORDER_ID")
43 - access("ORDER_ID"="B"."ORDER_ID")
45 - access("ORDER_ID"="B"."ORDER_ID")
47 - access("ORDER_ID"="B"."ORDER_ID")
49 - access("ORDER_ID"="B"."ORDER_ID")
51 - access("ORDER_ID"="B"."ORDER_ID")
53 - access("ORDER_ID"="B"."ORDER_ID")
55 - access("ORDER_ID"="B"."ORDER_ID")
57 - access("ORDER_ID"="B"."ORDER_ID")
59 - access("ORDER_ID"="B"."ORDER_ID")
61 - access("ORDER_ID"="B"."ORDER_ID")
63 - access("ORDER_ID"="B"."ORDER_ID")
65 - access("ORDER_ID"="B"."ORDER_ID")
67 - access("ORDER_ID"="B"."ORDER_ID")
69 - access("ORDER_ID"="B"."ORDER_ID")
71 - access("ORDER_ID"="B"."ORDER_ID")
Statistics
----------------------------------------------------------
25 recursive calls
0 db block gets
41856 consistent gets
7435 physical reads
0 redo size
1146 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
10 rows processed
So does the old technique of using joining table with identical columns into a single view still have relevance?
Yes and no.
It is entirely possible to use this technique to emulate range partitioning and hash partitioning.
These “partitions” can be moved, swapped, created and dropped. With the online table move and ability since 11g to make tables read-only, many of manageability features of partitioning can be implemented without the extra license.
When I have examined AWR data from large data warehouses I often see a majority of queries that are not able to leverage the partitioning keys of any partitioned data. Often that is because the data architect partitioned the data for manageability, not performance. In those cases a manual partition is no different from a real one for reasons of performance.
Indeed for small queries that can leverage indexes, or very large queries that need to inspect almost all rows, the performance delta between real partitions and manual ones is negligible.
But for OLAP operations that need to inspect a significant subset of a partitioned data sets the performance impact may required significantly more DBA effort to overcome.
Also note that the original question asked if we could use this approach in Standard Edition. The answer of course is yes, but SE2 also looses the parallel query option which we used here in our examples to access our 32 manual partitions in parallel.
Without the PQO, queries would become single threaded in the database.
That obstacle might be overcome by the application uses multiple sessions to inspect ranges of data, or perhaps by leveraging the DBMS_PARALLEL package as Christian Antognini explored in his blog post on the subject, although his examples focused mainly on updates to existing data.
Alternatively many workloads may be best suited to keeping the Enterprise Edition features but dropping the Partitioning option.
Certainly there are options, and numerous difficult challenges to overcome. Oracle Enterprise Edition continues to be the OLTP database of choice for organizations around the world, and for good reasons.
But there are also many emerging alternatives such as Enterprise DB and NuoDB as well as the cloud native options such as Amazon Redshift. Before we can consider moving critial data sets off Oracle we need to evaluate our dependence on its features, consider if we are leveraging them effectively and what alternatives we might reasonably embrace.
the gruffdba 