oracle 混合分区表,Oracle 19c 新特性：混合分区表Hybrid partitioned tables强体验

PDB1@ZRP>select * from hybrid_test partition (sales_2019);

REGION TIME_ID AMOUNT

------ ------------------- ----------

EAST 2019-01-01 00:00:00 1032

EAST 2019-01-02 00:00:00 2371

PDB1@ZRP>select * from hybrid_test partition (sales_2015);

no rows selected

PDB1@ZRP>select * from hybrid_test partition (sales_2016);

REGION TIME_ID AMOUNT

------ ------------------- ----------

EAST 2016-01-01 00:00:00 6000

EAST 2016-01-02 00:00:00 3000

EAST 2016-01-03 00:00:00 9012

EAST 2016-01-04 00:00:00 2450

EAST 2016-01-05 00:00:00 6709

SOUTH 2016-01-01 00:00:00 4000

SOUTH 2016-01-02 00:00:00 2120

SOUTH 2016-01-03 00:00:00 6300

SOUTH 2016-01-04 00:00:00 3850

SOUTH 2016-01-05 00:00:00 2090

WEST 2016-01-01 00:00:00 2467

WEST 2016-01-02 00:00:00 2140

WEST 2016-01-03 00:00:00 5300

WEST 2016-01-04 00:00:00 2470

WEST 2016-01-05 00:00:00 4080

NORTH 2016-01-01 00:00:00 2600

NORTH 2016-01-02 00:00:00 1300

NORTH 2016-01-03 00:00:00 1250

NORTH 2016-01-04 00:00:00 4350

NORTH 2016-01-05 00:00:00 3190

20 rows selected.

PDB1@ZRP>select * from hybrid_test partition (sales_2017);

REGION TIME_ID AMOUNT

------ ------------------- ----------

EAST 2017-01-01 00:00:00 8000

EAST 2017-01-02 00:00:00 7000

EAST 2017-01-03 00:00:00 6500

EAST 2017-01-04 00:00:00 3450

EAST 2017-01-05 00:00:00 9000

SOUTH 2017-01-01 00:00:00 2000

SOUTH 2017-01-02 00:00:00 3120

SOUTH 2017-01-03 00:00:00 2300

SOUTH 2017-01-04 00:00:00 5850

SOUTH 2017-01-05 00:00:00 1900

WEST 2017-01-01 00:00:00 3400

WEST 2017-01-02 00:00:00 2400

WEST 2017-01-03 00:00:00 5900

WEST 2017-01-04 00:00:00 5450

WEST 2017-01-05 00:00:00 1780

NORTH 2017-01-01 00:00:00 2000

NORTH 2017-01-02 00:00:00 1000

NORTH 2017-01-03 00:00:00 3000

NORTH 2017-01-04 00:00:00 2350

NORTH 2017-01-05 00:00:00 2190

20 rows selected.

PDB1@ZRP>select * from hybrid_test partition (sales_2018);

REGION TIME_ID AMOUNT

------ ------------------- ----------

EAST 2018-01-01 00:00:00 2100

EAST 2018-01-02 00:00:00 7800

EAST 2018-01-03 00:00:00 6900

EAST 2018-01-04 00:00:00 9450

EAST 2018-01-05 00:00:00 9700

SOUTH 2018-01-01 00:00:00 1300

SOUTH 2018-01-02 00:00:00 2120

SOUTH 2018-01-03 00:00:00 6300

SOUTH 2018-01-04 00:00:00 2850

SOUTH 2018-01-05 00:00:00 7900

WEST 2018-01-01 00:00:00 3800

WEST 2018-01-02 00:00:00 2600

WEST 2018-01-03 00:00:00 5200

WEST 2018-01-04 00:00:00 5250

WEST 2018-01-05 00:00:00 2980

NORTH 2018-01-01 00:00:00 2120

NORTH 2018-01-02 00:00:00 1230

NORTH 2018-01-03 00:00:00 3500

NORTH 2018-01-04 00:00:00 2050

NORTH 2018-01-05 00:00:00 1060

20 rows selected.

PDB1@ZRP>select /*+ gather_plan_statistics */ * from hybrid_test where time_id=to_date('20160102','yyyymmdd');

REGION TIME_ID AMOUNT

------ ------------------- ----------

EAST 2016-01-02 00:00:00 3000

SOUTH 2016-01-02 00:00:00 2120

WEST 2016-01-02 00:00:00 2140

NORTH 2016-01-02 00:00:00 1300

PDB1@ZRP>select * from table(dbms_xplan.display_cursor(format=>'IOSTATS PARTITION LAST')) ;

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID 71z2djd7chmxh, child number 0

-------------------------------------

select /*+ gather_plan_statistics */ * from hybrid_test where

time_id=to_date('20160102','yyyymmdd')

Plan hash value: 2383463387

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter((SYS_OP_XTNN("HYBRID_TEST"."TIME_ID","HYBRID_TEST"."REGION") AND "TIME_ID"=TO_DATE('

2016-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

21 rows selected.

PDB1@ZRP>