Difference between EDB and Oracle in partition pruning practices

Difference between EDB and Oracle in partition pruning practices

A problem occurred two days ago. To create a partition table in the EDB database, you need to use the partition Local index and partition pruning. However, you cannot use the partition pruning function when you view the execution plan.

Create a partition table:

CREATE TABLE test(  id bigint NOT NULL,  bag_id bigint,  bp_airline_code character varying(3),  bp_flight character varying(5),  bp_flight_suffix character varying(2),  bp_flight_date timestamp with time zone,  CONSTRAINT pk_test PRIMARY KEY (id))partition by range(bp_flight_date)(    partition part_20151101 VALUES LESS THAN('2015-NOV-02'),    partition part_20151102 VALUES LESS THAN('2015-NOV-03'),    partition part_20151103 VALUES LESS THAN('2015-NOV-04'));

Create a local index for the master table and three partitions:

CREATE INDEX test_idx_01 ON test (bag_id, bp_airline_code, bp_flight, bp_flight_suffix);CREATE INDEX test_part_20151101_idx ON test_part_20151101 (bag_id, bp_airline_code, bp_flight, bp_flight_suffix);CREATE INDEX test_part_20151102_idx ON test_part_20151102 (bag_id, bp_airline_code, bp_flight, bp_flight_suffix);CREATE INDEX test_part_20151103_idx ON test_part_20151103 (bag_id, bp_airline_code, bp_flight, bp_flight_suffix);

Run the following query statement with a partition key ,:

explain select * from testwhere bp_flight_date=to_date('2015-11-01', 'yyyy-mm-dd') and bag_id = 1

Partition pruning is not found:


I am a bit confused. I have clearly used the partition key as the query condition. Why are all partitions scanned here?

Analysis:
In fact, this question is simple and easy to say. It is troublesome to say, and it mainly involves understanding the details and principles.
In the preceding example, the partition rules are as follows:

partition by range(bp_flight_date)( partition part_20151101 VALUES LESS THAN('2015-NOV-02')...

Use the bp_flight_date field as the partition key with the condition less than ('2017-NOV-02 '). However, the query statement is as follows:

where bp_flight_date=to_date('2015-11-01', 'yyyy-mm-dd') and bag_id = 1

The to_date function is used for date values. It is not in a format similar to the "string" in the partition RULE. It is suspected that the two are not uniform. Due to some RULE, partition pruning cannot be used.

Next, modify the query conditions according to the conjecture:

explain select * from testwhere bp_flight_date='2015-11-01', 'yyyy-mm-dd' and bag_id = 1

View the execution plan:
VcHLt9bH + Lz0ssO1xMS/release/nS1NPQyc/release/fOqrLp0a/M9bz + release + Cgo8cD66zcnPw + release/release = "http://www.2cto.com/uploadfile/Collfiles/20151130/201511300932566.png" alt = "write image description" title = "\">


The execution plan shows that the partition feature is still used and the full table scan is not performed. Pstart and Pstop show the KEY, which indicates the partition KEY based on the function value.

Create a partition table like EDB without using the to_date function:
Oracle is different.

Summary:
1. The partition key in EDB is a date field, values less than ('2017-11-01 ') can use string format, but Oracle Reports an error in the ORA-01861, this creation method is not supported.
2. an EDB query statement that uses the partition key, if the format of the date condition is different from that in the partition rule, for example, the partition rule is '2017-NOV-01 'or '2017-11-01 ', however, if the query condition uses to_date ('1970-11-01 ', 'yyyy-mm-dd'), the partition pruning feature is not used, but all partitions are scanned. For Oracle, strict restrictions have been imposed when creating Partition Rules, so there is no inconsistency between the format of date Conditions and Partition Rules. The two methods are not good or bad. EDB is more free, but it requires human attention to correct writing. Oracle is rigorous, and the advantage is that it avoids human error, indirectly, it may also reflect the attitude of "community VS business" and "open source VS closed source" Towards a specific problem.