Timestamp with the Date variable bound to the automatic partitioning of Oracle

Long time no update blog, in fact, the work encountered a lot of problems in Google can find the answer, there is no need to record down. Today I would like to talk about the actual system encountered in the Oracle database problems, we hope to have a little bit of help on the good.

Let me first describe the scenario I encountered: Our database is using Oracle 11g, and I think we immediately have a basic understanding of its auto-partitioning (Interval), which is a great feature that eliminates the need to build a large range of code in the form. It also eliminates the hassle of later partitioning the database. Of course, the use of the job can also be done to expand the partition, but since Oracle provides such a good tool, why not with their own, in fact, I am too lazy to write. So I'm talking about our specific problem, we have a partition table like this:

CREATE TABLE TICKET (tct_id number, tct_date DATE, Tct_code VARCHAR2 (5), Tct_departure VARCHAR2 (5), Tct_destination VARCHAR2 (5)) partition by range (tct_date) interval (numtodsinterval (1, ' Day ')) (partition PT _tct_day_01 values less than (to_date (' 2014-07-27 ', ' yyyy-mm-dd ') nocompress);

Look very complicated, in fact, is a simple automatic partition table, the partition key is tct_date (booking date). Then why use automatic partition table, in fact, because there are too many data, such as 1 billion, with a table even if there is a better index, the query speed will be down. This partition table generates a partition every day, so as long as a local index is built on the table, a prefix is built here:

CREATE INDEX idx_combine on TICKET (Tct_date, Tct_code, Tct_departure, tct_destination) LOCAL

On the implementation of a very good query speed, in the case of 1 billion data, the query speed can basically be controlled within 10ms, which is basically the limit value. Our project has been running for more than 1 years before I took over, the first speed as expected, the speed of the query is basically within 10MS, the project manager suddenly told me that our system is very slow to insert, let me tune it.

Receiving the task, with our DBA to AWR, soon discovered that the system bottleneck appeared on a query statement:

SELECT * from TICKET T WHERE t.date=:D 1 and t.tct_code=:D 2 and t.tct_depature=:D 3 and t.tct_destination=:D 4;

At first glance feel very good, the variable binding, but the speed of the query soared to near 200ms, while the physical reading is very high, the logical reading is not very low, and the DB sequence file read frequency is very high, this shows that two points:

1. This query statement is likely to have a full table sweep, no partition elimination;

2. This query statement reads a large number of index data, it is possible to traverse the local index of all partitions;

The SQL AWR report that synthesizes this SQL can basically draw a conclusion that partition elimination is not implemented .

But this statement clearly took the partitioning conditions, is a prefix index, then why Oracle to do the full table sweep, this is very confusing, I carried out a bit of analysis:

1. The reason why this query is efficient is because there is no partition elimination, traversing all the indexes, which is why the initial program efficiency is good, but with more and more data, the program is more and more slow;

2. But why is it that there are zoning conditions here, but not zoning? The first reason that comes into my mind is that the values of the partitioning conditions are not matched;

3. Follow the above thinking, I looked at the source code of the project, found that the problem is now a statement, this error is very hidden, it is very difficult to find:

Ps.settimestamp (1, New Timestamp (Date.gettime ()));

Book_flt_date in the database is a date type, the statement is actually injected into SQL java.sql.Date type of value, that is, to book_flt_date assignment, this place to replace it with timestamp, OK, the problem appears , because the timestamp is to be accurate to milliseconds, and date is only accurate to the second, which causes the two to match, the result is unable to partition elimination;

4. How to solve this problem? I did a little bit of makeover:

Ps.setdate (1, New Java.sql.Date (Date.gettime ()));

Here the use of java.sql.Date, problem solving, query statements immediately into the 10ms, it seems to be obediently go partition conditions!

Can only sigh dikes destroyed in the nest, and then the line and careful. In fact, this reflects a previous programmer to java.util.Date and java.sql.Date the difference is not very clear, in addition to the relationship between Timestap and Date is also very vague, In fact, it is entirely possible to use the OJDBC oracle.sql.Date instead. This small blind spot, the result is also very serious, to after the thing or to eliminate these blind spots more.