How Do I Know If I Am A Will Beneficiary

Partitioned Tables - Command 'EXCHANGE'

SQL_10gR2> CREATE TABLE AS data_1 level 2 SELECT id, timestamp'2000-11-02 9:00:00 'date

level data_2 CREATE TABLE AS SELECT level 2 id, timestamp'2001-9-10 13:00:00 'date 3 FROM dual 4 CONNECT BY level


What we did was create 2 tables with different dates in each of them.

Now create only the structure of the partitioned table where we will load the data:

 
 SQL_10gR2> CREATE TABLE test 
 2 (id, date) PARTITION BY RANGE 
 3 (date) 
 4 (5 <= 100000 ;  Table created.  Elapsed: 00:00:01.06  SQL_10gR2> year_2000 PARTITION VALUES LESS THAN (timestamp'2000-12-02 00:00:00 '), 
 6 year_2001 PARTITION VALUES LESS THAN (timestamp'2001-10-10 00:00:00') 
 7) 
 <= 100000 ;  Table created. 

8 AS 9 SELECT 1, timestamp'2000-11-02 9:00:00 '

11 10 FROM dual WHERE 1 = 0;

 We will make an alter to change the data dictionary and relate each of the 2 tables with the respective partition create table TEST ... 
 
 
 SQL_10gR2> ALTER TABLE test 2 
 
 
 year_2000 
 EXCHANGE PARTITION 3 with table 4 
 data_1 
 
 WITHOUT VALIDATION; 
 
 Table altered. 

Elapsed: 00:00:00.03

  
  
 SQL_10gR2> ALTER TABLE test  2    EXCHANGE PARTITION 
  year_2001 
 3  WITH table datos_2  4    WITHOUT VALIDATION 
  ; 
  
 Table altered. 
     Elapsed: 00:00:00.02 
  
  
 SQL_10gR2> SELECT count(*)  2  FROM test ;   
 COUNT(*) 
 ----------    200000   
 1 row selected. 
  
 SQL_10gR2> SELECT count(*) 
 2  FROM datos_1 ;    COUNT(*) 
 ---------- 
        0 
  
 1 row selected. 
  
 SQL_10gR2> SELECT count(*) 
 2  FROM datos_2 ; 
  
 COUNT(*) 
 
 0 
 
 ---------- 1 row selected. 
 
 
 As we can see with Partition Exchange does not take us almost nothing to load the data into the partitioned table because we're not loading the data, simply adjust the data dictionary. 
 
 may notice that I added the sentence WITHOUT VALIDATION. What is this? WITHOUT VALIDATION usually a fast operation because it only modifies the data dictionary. If the partitioned table or placed in the Exchange Partition has a primary key or unique constraint enabled, then the Partition Exchange VALIDATION WITH done as to maintain the integrity of the constraints. Let 
 
 rerun the 2 previous alter without judging WITHOUT VALIDATION ... 
 
 
 SQL_10gR2> ALTER TABLE test 2 
 
 
 year_2000 
 EXCHANGE PARTITION 3 with table data_1; 
 

Table altered.


Elapsed: 00:00:01.00


SQL_10gR2> ALTER TABLE test 2

 
 
 year_2001  EXCHANGE PARTITION 3 with table data_2;  
 Table altered. 
 
 
 Elapsed: 00:00:01.05 
   
 If I run these alter with Trace, the Trace report I show, among other rulings, the following ... 
 
   select 1 from 
 "data_1" where  TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1 
  
 call     count       cpu    elapsed       disk      query    current        rows 
 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
 Parse        1      0.00       0.00          0          0          0           0  Execute      1      0.00       0.00          0          1          0           0  Fetch        1      0.04       0.04          0         65          0           0 
 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- 

total 3 0.04 0.04 0 66 0 0

  
 Misses in library cache during parse: 1 
  Misses in library cache during execute: 1 
 Optimizer mode: ALL_ROWS 
 Parsing user id: 81     (recursive depth: 1) 
  
 Rows     Row Source Operation 
 -------  --------------------------------------------------- 
     0  TABLE ACCESS FULL DATOS_1 (cr=65 pr=0 pw=0 time=44582 us) 
  
  
 select 1  from  "DATOS_2" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1 
  
 call     count       cpu    elapsed       disk      query    current        rows 
 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
 Parse        1      0.00       0.00          0          0          0           0 
 Execute       1      0.00       0.00          0          1          0           0 
 Fetch        1      0.04       0.04          0         65          0           0 
 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
  
 total        3      0.04       0.04          0         66          0           0 
  
  
 Misses in library cache during parse: 1 
 Misses in library cache during execute: 1 
 Optimizer mode: ALL_ROWS 
 Parsing user id: 81     (recursive depth: 1) 
  
 Rows     Row Source Operation 
 -------  --------------------------------------------------- 
     0  TABLE ACCESS FULL DATOS_2 (cr=65 pr=0 pw=0 time=46957 us) 
  
  
 Antes que nothing, we note that the alternative is implemented in more time, right? From the consultations we observed the Trace, we see that it is conducting a FULL SCAN of the tables and that is running a function in the WHERE of each query. Imagine if we have to make this kind of processes in environments with large volumes of data and wherein the system is saturated by the I / O to disk.   What happens if we do not have the data separated by year in different tables, and instead have all the data in one table? Well, as exemplified by the table TEST just to load, could do the following ... 
 
 
 SQL_10gR2> CREATE TABLE test_2 
 2 (id, date) 
  3  PARTITION BY RANGE ( fecha ) 
 4  ( 
 5      PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ), 
 6      PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' ) 
 7  ) 
 8  AS 

9 SELECT *
10 FROM test ;

Table created.

 Elapsed: 00:00:05.04 
  
 SQL_10gR2> DROP TABLE test ; 
  
 Table dropped. 
  
 SQL_10gR2> ALTER TABLE test_2 RENAME TO test ; 
  
 Table altered. 
  
 SQL_10gR2> SELECT count(*) 
 2  FROM test ; 
  
 COUNT(*) 
 ---------- 
   200000 
  
 1 row selected.