Oracle exchange partition test, oracleexchange
Exchange partition provides a way to migrate data between a table and a table, or between a partition and a partition. Note that it is not in the form of converting a table into a partition or a non-partition, but only the number in the migration table
Data (mutual migration) is the most efficient (almost no I/O operations involved) because it is claimed to have changed the data dictionary ). Exchange partition applies to all partition formats. You can migrate data from a partition table to a non-partition table, or migrate data from a non-partition table to a partition table.
This provides a good idea for batch update of partition table data. Here I will conduct an experiment on data exchange between a common table and a partition table.
Note: partition tables cannot be directly exchanged with partition tables.
If you change the partition table, use the normal table for transition.
If you change to a common table, use the partition table for transition.
The INDEX must be a local index, not a global index.
1. Create a test table space
Create tablespace tbs1 datafile '/oracle_data/szdb/tbs1_01.dbf' size 10 m autoextend on;
Create tablespace tbs2 datafile '/oracle_data/szdb/tbs2_01.dbf' size 10 m autoextend on;
2. Create a test table
Drop table t_temp purge;
Drop table t_part_list purge;
Create table t_temp as select 1 id, t. owner, t. OBJECT_NAME, t. OBJECT_ID, t. OBJECT_TYPE from dba_objects t;
Create table t_part_list (id number, owner varchar2 (20), object_name varchar2 (40), object_id number, object_type varchar2 (20 ))
Partition by list (id)
(Partition p1 values (1) tablespace tbs1,
Partition p2 values (2) tablespace tbs2
);
3. View table data
SQL> select count (*) from t_part_list;
COUNT (*)
---------
0
SQL> select count (*) from t_temp;
COUNT (*)
----------
80905
4. Exchange data
Alter table t_part_list exchange partition p1 with table t_temp;
Or
Alter table t_part_list exchange partition p1 with table t_temp without validation; -- no longer verify Data Validity
5. View table data
SQL> select count (*) from t_part_list;
COUNT (*)
----------
80905
SQL> select count (*) from t_part_list partition (p1 );
COUNT (*)
----------
80905
SQL> select count (*) from t_temp;
COUNT (*)
----------
0
6. Note
When migrating data from a non-partitioned table to a partitioned table, a ora-14099 error may occur, although it can be addressed with without validation, but the data entering the partitioned table may not comply with the Partition Rules.
Therefore, without validation must be used with caution.
The table structures of the two tables involved in the exchange must be consistent unless the with validation clause is attached;
If the data is exchanged from a non-partition table to a partition table, the data in the non-partition table must comply with the rules of the specified partition in the Partition Table, unless the without validation clause is attached;
If you exchange data from a partition table to a partition table, the data in the exchanged partition must comply with the Partition Rules, unless the without validation clause is attached;
Global indexes or global index partitions that involve data changes are set to unusable unless the update indexes clause is attached.
Tip: Once the without validation clause is attached, it indicates that the data validity is no longer verified. Therefore, be careful when specifying this clause.
Oracle partition
Hello, the two upstairs are incorrect. partition by is grouped. First, you have to group the data by day, because the result of sum is grouped, the result of running the two upstairs is still
1 22 22
2 32 32
3 21 21
4 45 45
5 54 54
6 12 12
7 67
If you want to get the above results, you must first add a column to the raw data. We suppose you want to add a name column for it to make the original table:
Name day sal
Zh 1 22
Zh 2 32
Zh 3 21
Zh 4 45
Zh 5 54
Zh 6 12
Zh 7 67
On 1 23
On 2 46
To view the query results, we also added another person's name on
The corresponding query statement should be:
Select day, sal, sum (sal) over (partition by name order by name, day) sumsaltoyesterday from;
The final result is:
Day sal sumsaltoyesterday
1 23 23
2 46 69
1 22 22
2 32 54
3 21 75
4 45 120
5 54 174
6 12 186
7 67 253
In fact, the first two pieces of data are on, and the subsequent data is zh. To see the difference, add the name in the result column.
Use of oracle PARTITION
Create table test_ta (year number, month number, area varchar2 (20), region varchar2 (20), business varchar2 (20), income number );
Select *
From (select year,
Month,
Area,
Dense_rank () over (partition by year, month order by income desc) rank
From (select t. year, t. month, t. area, sum (income) income
From test_ta t
Group by t. year, t. month, t. area ))
Where rank = 1
-- Inmost subquery: calculates the total income of each region in each month.
-- Subquery on the second layer: groups each month and region, sorts the data in descending order of total income, and gives the sequence number (the sequence number is the same as the total income in March, depending on your needs, take one or both of them, and replace dense_rank () with row_number ())
-- Query at the outermost layer: query the records whose sorting sequence number is 1, that is, the region with the highest total monthly income
Hope to help you
