What is the partition table for Oracle (author Chen)

Suppose we are now brewing a library, initially, we only have 10 books for everyone to read and buy. For 10 books, we might just need a bookshelf to hold it as a container for the 10 books, because anyone can easily take a glance at the title of the 10 book and pick out the right books for you to see.

    But as we get more and more books, we need bigger containers to preserve our science and education, it, history, humanities, and so on, and we all know that a common sense of life is that we must not be able to throw all types of books on a bookshelf container, the most scientific, Is the division, will be different books put to different places, so, if we in Xi ' an five intersection of Xinhua Bookstore, then only need to run to the four-floor it division, to find relevant it books.     In life is this like, in the database?     Current database business data and log data are growing at a geometric rate. In my previous social security industry, for example, social security logs are frequently dozens of g, in fact, almost covered from the system test run to run a year or two after the data, such a huge table, so that the logical backup of data, query, and even based on log table fallback has produced a huge efficiency problem.     How can I quickly and efficiently delete log information from three months ago, how can I quickly retrieve information about the current month, how to make full use of the physical environment of multiple disk space (bare devices), and increase the degree of parallelism balance I/O to improve the usability of the performance-enhancing database? The partitioning capabilities of the tables presented by Oracle after version 8.0 are a key practical technique.     in the 8.0.5 release, Oracle introduced the range partitioning technology, which is now becoming more and more powerful, including support for extended partitioning capabilities, interval partitions, foreign key partitioning, simulated column partitioning, and partition recommendations. So what is the benefit of partitioning? Why do we use partitions? Under what circumstances is it appropriate to use zoning? In the Gold Management group, there is a friend to ask a multi-user next 10W frequent table of the increase, deletion, modification of the table, many friends gave a change to become a partition table suggestions. In fact, this table can be set as a partition table, it is necessary to see the specific business use of the environment, multiple users under frequent additions and deletions, I think the use of global temporary table instead of the partition table, this is not a typical application of partitioned table environment, instead, should be the global temporary table application environment, and in a reference book, Found a suggestion of Daniel as proof: "The partition table is more suitable for large tables, at least more than 1 million records to consider the use of partitioned tables", such as social security system, using partitioned tables to solve the problem of log redundancy problem is a best use scenario.     This conclusion is very easy to understand, similar to the one we introduced in the first example, we open a bookstore, only 10 books, assuming that there will only be a few of the future, then why do we have to be divided into different areas? Wouldn't that be superfluous? Neither economic nor harmonious, only when we procured the booksVery large amount of time, we will consider using partitioning technology to solve the problem of finding efficiency.     Suppose now that the user wants to find a textbook on the data structure, we're looking for a needle in the 100W book. or in 10W it books to Find Fast? The answer is obvious. When we only have 10 books? Do we also create partitions? We will scan directly with the naked eye and get the book directly. Therefore, we need to select specific technical solutions in a specific environment, the aim is to pursue higher efficiency, higher performance is bound to be the user higher satisfaction, higher satisfaction is the inevitable result of better economic benefits, better economic benefits brought by the librarian is necessarily a better life.   So let's see what the benefits of partitioning capabilities Oracle provides? &NBSP;1) because the Oracle database can designate partitions as different table spaces, and different table spaces can point to different disk devices, in many cases the I/O speed of the disk device is the bottleneck of the system's data operation, and the partitioning is parallel, which optimizes the physical hardware resources. This shortens the effect of the time of execution. 2) partitioning, similar to the classification of books we put into different sections, at this time, we can only for one of the categories of books to operate, such as the search for such books, shelves, shelves, and even the book burned, which brings two particularly large advantages, first, we ignore the other partition data Second, we can easily delete the data in this area. 3) Improve the specific query speed, for example, we now only need to find the number of it books, because we can in the first consciousness, excluding other non-it books, will inevitably improve the performance of the query. 4) Save maintenance costs, traditional maintenance operations, such as rebuilding the index, and so on, because the existence of lock mechanism, it will affect other people's read and update delete operations, but because of the partition, we maintain it books, it will not affect other classified data, this time, Can reduce the various effects on other system data as a result of maintaining data. 5) From a security standpoint, we place different partitions in different table spaces, similar to the way we do not put all the eggs in a basket, which inevitably leads to security improvements.  oracle provides the following kinds of more common partitioning methods:  1) range partition (partition by range)      We can imagine the premise of the range zoning, if we now have 100W book, if divided into 10 partitions, can be compared to the average of each class to maintain around 10W, is the most ideal, so it looks neat and beautiful. Therefore, when we set up the scope partition, how to choose the basis of the range partitioning so that the data can be evenly distributed, is a need to focus on the issue.     In many cases, such as the log table in the Social security system mentioned above, using date classification is a better partitioning scheme, because the amount of data generated each month is roughly the same. Below, we create a month-based range partitioning table with a script (assuming that the data that is inserted is all 2013 data): DROP table PART_LOG_CHENZW purge; create table PART_LOG_CHENZW (        log_id number (PRIMARY key,       log_date date,      &NB Sp Log_desc VARCHAR2 (()) PARTITION by RANGE (log_date) ( partition part_log_01 VALUES less THAN (' 2013-01-01 ', ' Yyyy-mm-dd ') tablespace data, partition part_log_02 VALUES less THAN (to_date (' 2013-03-01 ', ' yyyy-mm-dd ')) Tablespace data, partition part_log_03 VALUES less THAN (to_date (' 2013-05-01 ', ' yyyy-mm-dd ')) tablespace DATA,  partition part_log_04 VALUES Less THAN (to_date (' 2013-07-01 ', ' yyyy-mm-dd ')] tablespace data, partition part _log_05 VALUES Less THAN (to_date (' 2013-09-01 ', ' yyyy-mm-dd ')) tablespace DATa, partition part_log_06 VALUES Less THAN (to_date (' 2013-10-01 ', ' yyyy-mm-dd ') tablespace data, partition part_log_07 VALUES less THAN (MAXVALUE) tablespace DATA);     The example above is a typical example of using a range partition, using partition by when creating a table The Range keyword indicates the policy for partitioning, where partition by LOG (log_date) refers to a field that uses Log_date as a partition, which, based on the size of the value, names 7 partitions to hold the data, and each partition can also specify a different table space. The seventh partition uses MaxValue to avoid a value not being delineated by the above range, which is similar to the default in the switch syntax, unlike this, which is not fall through.      Below, let's build 100,000 data to insert into the table and see what happens in the table. INSERT into part_log_chenzw    (log_id, Log_date, Log_desc)     SELECT level,      &NBSP ;    to_date (' 2013-01-01 ', ' yyyy-mm-dd ') +           numtodsinterval (Ceil (dbms_ RANDOM. VALUE (0, 365)), ' Day '),           level | | ' DESC '       from dual    CONNECT by level <= 100000;     First, we can check the log in 7 August As we go to the Xinhua bookstore just to find books on it, we know we should go there looking for data named PART_LOG_05 Partition: SELECT COUNT (1)From PART_LOG_CHENZW PARTITION (part_log_05); The following is the result of my native display: 06:50:40 chenzw> SELECT COUNT (1) from PART_LOG_CHENZW PARTITION (part_log_05);   COUNT (1)----------     16981    through the above results can be clearly seen, 16,981 of the data fell in the 7 August partition. By specifying the partition, we will focus all of our attention only on the data of this 1.6W and not on all 10W data, which can obviously bring about a performance improvement.      There's a problem with that, how do we know what kind of zoning is in the library? How can we know the right partition? In the Xinhua bookstore, often in the elevator when, there will be a sign, tell you, it books on the 4 floor, humanities and social science books on the 1 floor .... In fact, the Oracle database also provides this guide, which is the following two data dictionaries: Dba_part_tables and Dba_tab_partitions, let's turn over the contents of the two "signage": sql>  Select T.owner as "owner",       t.table_name as "table name",       t.partitioning_type as "Partition type",       t.subpartitioning_type as "sub-partition type",       t.partition_count as " Number of sub-partitions ",       t.partitioning_key_count as" columns in the partition key,       t.subpartitioning_ Key_count as "number of columns in sub-partition key",       t.status as "partitioned table State",       t.def_tabLespace_name as "default Tablespace"   from Dba_part_tables t where t.table_name in (' PART_LOG_CHENZW ');  owner   & nbsp: Table name               Partition type   sub-partition type sub-partition number the number of columns in the partition key the number of columns in the sub-partition key column in the partition table state default Tablespace----------- -------------- ------------------------------------------------------------------------------------------- ------CHENZW   PART_LOG_CHENZW    range     NONE     7         &NBS P;1              0                valid      DATA     By the above we can see that table PART_LOG_CHENZW now has 7 partitions, but we do not know what the 7 partitions are stored as data. Where do we go to find the data we want for 7 August? Sql> select T.table_owner as "owner",      t.table_name as "table name",      T.composite as " Whether to combine partitions ",      t.partition_name as" Partition name,      T.subpartition_count as "sub-partition number",    & nbsp T.high_value as "Partition Cap", &nbsp     T.high_value_length as "Partition upper limit Length",      t.partition_position as "Partition in table position",      T.tablespace_name as "table space"  from dba_tab_partitions twhere t.table_name in (' PART_LOG_CHENZW ')/  owner   Table name             whether to combine partition name         sub-partition limit                                  ,         &NB Sp                     The         partition upper-bound length partition the table space in which the table is located---- --- ---------------- ------------ ------------ ---------- ---------------------------------------------------------- ----------------------------------------------------------chenzw  part_log_chenzw  no           part_log_01           0 to_date (' 2013-01-01 00:00:00 ', ' Syyyy-mm-dd hh24:mi : SS ', ' Nls_calendar=gregoria            1             DATACHENZW &NBSP;PART_LOG_CHENZW  no           part_log_02           0 to_date (' 2013-03-01 00:00:00 ', ' syyy Y-mm-dd HH24:MI:SS ', ' Nls_calendar=gregoria           $           &NBSP ;  2 DATACHENZW  part_log_chenzw  no           part_log_03       &NBS P   0 to_date (' 2013-05-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregoria         &NBS P              3 DATACHENZW  part_log_chenzw  no           part_log_04           0 to_date (' 2013-07-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_ca Lendar=gregoria            4             DATACHENZW  p Art_log_chenzw  no           part_log_05           0 to_date (' 2013-09-01 00:00:0 0 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregoria                   &NB Sp    5 DATACHENZW  part_log_chenzw  no           part_log_06     &NBS P     0 to_date (' 2013-10-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregoria       &NBS P                6 DATACHENZW  part_log_chenzw  no           part_log_07           0 MAXVALUE                                        ,         &NB Sp                                  8 &N Bsp            7 data 7 rows selected     from the above results can be seen, if we want to find data for 7 August, You can locate the Part_log_05 partition based on the upper limit of the partition.      Finally, we can view the disk space information occupied by the partitioned table by querying the data dictionary user_segments, as follows: Select T.segment_name as "segment name",       t.partition_name as "Partition name",       t.segment_type as "partition type",       t.bytes/p Ower (1024, 2) | | ' M ' as "Partition size",       t.tablespace_name as "tablespace"   from user_segments t where t.segment_name I N (' PART_LOG_CHENZW ');     results are as follows: Segment name             Division name       partition Type           partition size   table space---------------------------------------------------------------PA RT_LOG_CHENZW  part_log_01  table PARTITION    .0625m     DATAPART_LOG_CHENZW  part_ log_02  table PARTITION    .5625m     DATAPART_LOG_CHENZW  part_log_03  table partItion    .5625m     DATAPART_LOG_CHENZW  part_log_04  table PARTITION    .5625m & nbsp   DATAPART_LOG_CHENZW  part_log_05  table PARTITION    .625m      datapart_log_ CHENZW  part_log_06  table PARTITION    .3125m     DATAPART_LOG_CHENZW  part_log_07 & nbsp Table PARTITION    .875m      data 7 rows selected 2) List partition (PARTITION by list)   &NB SP; Suppose we now have only four types of 10W books, 0 education, 1 it, 2 Humanities, 3 social sciences, and only these four cases, we can be partitioned by the type of books. Typically, if an item in your data can be enumerated, this column can be used as a partition field for the list partition. DROP table PART_BOOK_CHENZW purge; create table PART_BOOK_CHENZW (       book_id number (20) PRIMARY key,       book_date date,       book_type number (2) not null,  &nbsp ;    book_desc VARCHAR2 () PARTITION by LIST (Book_type) ( partition part_book_01 VALUES (0) tablespace Data, partition part_book_02VALUES (1) tablespace data, partition part_book_03 values (2) tablespace data, partition part_book_04 VALUES (3 ) tablespace data;  The following script is used to generate the appropriate data: INSERT into part_book_chenzw    (book_id, Book_date, Book_type,book_ DESC)     SELECT level,           to_date (' 2013-01-01 ', ' yyyy-mm-dd ') +  &nbs P        numtodsinterval (Ceil (dbms_random. VALUE (0, 365)), ' Day '),           mod (level,4),           leve L | | ' DESC '       from dual    CONNECT by Level <= 100000; 3) hash partition (partition by hash)   &NBS P Individuals feel that the more role of hash partitioning is to disperse the data, by evenly distributing the data to circumvent I/O bottlenecks, but this in peacetime production is really less used, so the proposal is only as understood. DROP table PART_BOOK_CHENZW purge; create table PART_BOOK_CHENZW (       book_id number (20) PRIMARY key,       book_date date,       book_type number (2) not null,  &nbsp ;    booK_desc VARCHAR2 ()) PARTITION by HASH (book_id) ( partition part_book_01 tablespace data, partition Part_book _02 tablespace data, partition part_book_03 tablespace data, partition PART_BOOK_04 TABLESPACE DATA);  INSERT into part_book_chenzw    (book_id, Book_date, Book_type,book_desc)     SELECT level,  & nbsp        to_date (' 2013-01-01 ', ' yyyy-mm-dd ') +           numtodsinterval (Ceil (Dbms_random. VALUE (0, 365)), ' Day '),           mod (level,4),           leve L | | ' DESC '       from dual    CONNECT to level <= 100000; 4) combo partition (partition by XXX subpartition by xxx)     If we now have 10W Monthly magazine offering to consumers to view and buy, how do we make zoning suitable? First we can partition the book according to the type, and then the partition of each partition for the month, so for a large number of books, we can according to the actual business needs, a lot of data through the partition into a number of small standard management, thus reducing the granularity of management, to achieve "fine management."      Here's an example of a combined partition:--Delete test table drop table PART_BOOK_CHENZW purge; --Create List-range combination partition create TABLE PART_BOOK_CHENZW (       book_id number () PRIMARY key,      &NBSP ; Book_date date,       book_type number (2) not null,       book_desc VARCHAR2 (20)) PARTITION by LIST (Book_type) subpartition by RANGE (book_date) subpartition TEMPLATE ( subpartition part_log_01 Values less THAN (to_date (' 2013-01-01 ', ' yyyy-mm-dd ')) tablespace data, subpartition part_log_02 VALUES less THAN ( To_date (' 2013-03-01 ', ' yyyy-mm-dd ')) tablespace data, subpartition part_log_03 VALUES less THAN (' To_date (' 2013-05-01 ', ' yyyy-mm-dd ')) tablespace data, subpartition part_log_04 VALUES less THAN (to_date (' 2013-07-01 ', ' Yyyy-mm-dd ') tablespace data, subpartition part_log_05 VALUES less THAN (to_date (' 2013-09-01 ', ' yyyy-mm-dd ')) Tablespace data, subpartition part_log_06 VALUES less THAN (to_date (' 2013-10-01 ', ' yyyy-mm-dd ')) tablespace DATA,& nbsp Subpartition part_log_07 VALUES less THAN (MAXVALUE) tablespace DATA) ( partition part_book_01 values (0) tablespace data, partition part_book_02 values (1) tablespace DATA,& nbsp PARTITION part_book_03 VALUES (2) tablespace data, partition part_book_04 values (3) tablespace DATA);-- Generate test data insert into part_book_chenzw    (book_id, Book_date, Book_type,book_desc)     SELECT level,& nbsp          to_date (' 2013-01-01 ', ' yyyy-mm-dd ') +           numtodsin Terval (Ceil (dbms_random. VALUE (0, 365)), ' Day '),           mod (level,4),           leve L | | ' DESC '       from dual    What is the difference between the combined partition above and the single range partition of the <= 100000; ? We look at the data dictionary by querying: Sql>select T.table_owner as "owner",      t.table_name as "table name",      T.compo SITE as "whether combined partition",      t.partition_name as "Partition name",      T.subpartition_count as "sub-partition number",      T.HIGH_VALUE as "Partition upper Limit",      t.high_value_length as "Partition upper limit Length",      t.partition_position as "Partition in table position",       T.tablespace_name as "table space"  from dba_tab_partitions twhere t.table_name in (' PART_BOOK_CHENZW ')/  owner   table name               whether to combine partition name           sub-partition number partition Upper-Limit partition upper-bound length partition in table where table space is located------------------------------------------------------------------------------------------ --------------chenzw  part_book_chenzw  yes          part_book_01     &NBSP ;     7 0                   1           &NBS P  1 DATACHENZW  part_book_chenzw  yes          part_book_02       &N Bsp   7 1                   1             &NBS P;2 DATACHENZW &nbsP PART_BOOK_CHENZW  yes          part_book_03           7 2   &N Bsp               1              3 DATACHENZW  part _BOOK_CHENZW  yes          part_book_04           7 3                   1             &NBSP;4 DATA Focusing on the "number of sub-partitions" of the above partition, we can see that each of the partitions under the list is divided into 7 partitions according to the date.

What is the partition table for Oracle (author Chen)