Oracle partitioning, indexing, testing (1)

Oracle partition, index, test (1) ----- insert only this time. See how to insert Fast Preparation

SQL code -- data file alter system set db_create_file_dest = 'd: \ toby \ oracle \ data; -- tablespace create tablespace ts_sales_200901 datafile size 5 M autoextend on; create tablespace upload datafile size 5 M autoextend on; create tablespace ts_sales_200903 datafile size 5 M autoextend on; create tablespace ts_sales_200904 datafile size 5 M autoextend on; create tablespace ts_sales_200905 datafile size 5 M Oextend on; create tablespace ts_sales_200906 datafile size 5 M autoextend on; create tablespace ts_sales_200907 datafile size 5 M autoextend on; create tablespace ts_sales_200908 datafile size 5 M autoextend on; create tablespace ts_sales_200909 datafile size 5 M autoextend on; create tablespace ts_sales_200910 datafile size 5 M autoextend on; create tablespace ts_sales_200911 datafile size 5 M Utoextend on; create tablespace ts_sales_200912 datafile size 5 M autoextend on; create tablespace ts_sales_201001 datafile size 5 M autoextend on; create tablespace ts_sales_201002 datafile size 5 M autoextend on; create table city (city_id number (10), city nvarchar2 (30), primary key (city_id); create table employee (EMPLOYEE_ID number (10), FIRST_NAME nvarchar2 (30 ), LAST_NAME nvarchar2 (30), MAN AGER_ID number (10), primary key (EMPLOYEE_ID); --- insert city -- id FROM 1 to 24 insert into city select rownum, city from hr. LOCATIONS; -- insert employee -- id FROM 100 to 206 insert into employee select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID from hr. EMPLOYEES; --- create table sales_data (sales_date date, city_id number (10), employee_id number (10), sales_type nvarchar2 (30), sales_amount number (10) pa Rtition by range (sales_date) (partition sales_200901 values less than (to_date ('2017-02-01 ', 'yyyy-MM-DD') tablespace ts_sales_200901, partition sales_200902 values less than (to_date ('2017-03-01 ', 'yyyy-MM-DD') tablespace ts_sales_200902, partition sales_200903 values less than (to_date ', 'yyyy-MM-DD ') tablespace ts_sales_200903, partition sales_200904 values less than (to_date ('100 2009 ', 'yyyy-MM-DD') tablespace ts_sales_200904, partition sales_200905 values less than (to_date ('2017-06-01 ', 'yyyy-MM-DD') tablespace ts_sales_200905, partition sales_200906 values less than (to_date ('2017-07-01 ', 'yyyy-MM-DD') tablespace ts_sales_200906, partition sales_200907 values less than (to_date ('2017-08-01 ', 'yyyy-MM-DD ') tablespace ts_sales_200907, partition sales_200908 values S than (to_date ('2017-09-01 ', 'yyyy-MM-DD') tablespace ts_sales_200908, partition sales_200909 values less than (to_date ('2017-10-01 ', 'yyyy-MM-DD ') tablespace ts_sales_200909, partition sales_200910 values less than (to_date ('2017-11-01', 'yyyy-MM-DD ') tablespace ts_sales_200910, partition sales_200911 values less than (to_date ('2017-12-01 ', 'yyyy-MM-DD') tablespace ts_sales_200911, partition sal Es_200912 values less than (to_date ('1970-01-01 ', 'yyyy-MM-DD') tablespace ts_sales_200912, partition sales_201001 values less than (to_date ('1970-02-01 ', 'yyyy-MM-DD ') tablespace ts_sales_201001, partition sales_201002 values less than (to_date ('2017-03-01', 'yyyy-MM-DD ') tablespace ts_sales_201002 ); -- partition indexes are placed in the corresponding tablespace create index index_sales_data_partition on sales_data (sales_date) local (partiti On sales_200901 tablespace partition, partition tablespace partition, partition sales_200905 tablespace partition, partition tablespace partition, Partition sales_200909 tablespace partition, partition sales_200910 tablespace partition, partition sales_200911 tablespace partition, partition sales_200912 tablespace partition, partition sales_201001 tablespace partition, partition sales_201002 tablespace partition ); --- bitmap create bitmap index index_sales_data_sales_type on sales_data (sales_type) local ;-- Create bitmap index index_sales_data_city on sales_data (city. city_id) from sales_data, city where sales_data.city_id = city. city_id local; --- create table sales_data1 (sales_date date, city_id number (10), employee_id number (10), sales_type nvarchar2 (30), sales_amount number (10) without creating a bitmap )) partition by range (sales_date) (partition sales_200901 values less than (to_date ('2017-02-01 ', 'yyyy-MM-D D ') tablespace ts_sales_200901, partition sales_200902 values less than (to_date ('2017-03-01', 'yyyy-MM-DD ') tablespace ts_sales_200902, partition sales_200903 values less than (to_date ('1970-04-01 ', 'yyyy-MM-DD') tablespace ts_sales_200903, partition sales_200904 values less ', 'yyyy-MM-DD ') tablespace ts_sales_200904, partition sales_200905 values less than (to_date ('20 2009 ', 'yyyy-MM-DD') tablespace ts_sales_200905, partition sales_200906 values less than (to_date ('2017-07-01 ', 'yyyy-MM-DD') tablespace ts_sales_200906, partition sales_200907 values less than (to_date ('2017-08-01 ', 'yyyy-MM-DD') tablespace ts_sales_200907, partition sales_200908 values less than (to_date ('2017-09-01 ', 'yyyy-MM-DD ') tablespace ts_sales_200908, partition sales_200909 values le Ss than (to_date ('2017-10-01 ', 'yyyy-MM-DD') tablespace ts_sales_200909, partition sales_200910 values less than (to_date ('2017-11-01 ', 'yyyy-MM-DD ') tablespace ts_sales_200910, partition sales_200911 values less than (to_date ('2017-12-01', 'yyyy-MM-DD ') tablespace ts_sales_200911, partition sales_200912 values less than (to_date ('1970-01-01 ', 'yyyy-MM-DD') tablespace ts_sales_200912, partition sa Les_201001 values less than (to_date ('1970-02-01 ', 'yyyy-MM-DD') tablespace ts_sales_201001, partition sales_201002 values less than (to_date ('1970-03-01 ', 'yyyy-MM-DD ') tablespace ts_sales_201002); -- partition index placed in the corresponding tablespace create index partition on sales_data1 (sales_date) local (partition sales_200901 tablespace ts_sales_200901, partition sales_200902 tablespace partition Ion partition tablespace partition, partition tablespace partition, partition sales_200905 tablespace partition, partition tablespace partition, partition sales_200910 tablespace partition, Partition sales_200911 tablespace partition, partition sales_200912 tablespace partition, partition sales_201001 tablespace partition, partition sales_201002 tablespace partition); -- create table partition (sales_date date, city_id number (10 ), employee_id number (10), sales_type nvarchar2 (30), sales_amount number (10); -- Identical non-partitioned table 2 create table sales_data3 (s Ales_date date, city_id number (10), employee_id number (10), sales_type nvarchar2 (30), sales_amount number (10 )); set serveroutput on --- insert data of 10 million declare maxrecords constant int: = 10000000; sales_date int: = 0; sales number: = 0; I INT: = 1; city_id int: = 0; employee_id int: = 0; sales_date1 nvarchar2 (20): = ''; sales_month int: = 0; begin for I IN 1 .. maxrecords loop CITY_ID: = ABS (MOD (DBMS_RANDOM.RANDOM, 24 )); Employee_id: = ABS (MOD (DBMS_RANDOM.RANDOM, 106) + 100; SALES_DATE: = ABS (MOD (DBMS_RANDOM.RANDOM, 28); sales_month: = ABS (MOD (DBMS_RANDOM.RANDOM, 5); -- make sure that it is not 0 while city_id = 0 or SALES_DATE = 0 or sales_month = 0 loop CITY_ID: = ABS (MOD (DBMS_RANDOM.RANDOM, 24); sales_month: = ABS (MOD (DBMS_RANDOM.RANDOM, 5); SALES_DATE: = ABS (MOD (DBMS_RANDOM.RANDOM, 28); end loop; sales: = ABS (MOD (DBMS_RANDOM.RANDOM, 100000 )); sales _ Date1: = '1970-0' | to_char (sales_month) | '-' | to_char (sales_date); insert into SALES_DATA3 VALUES (to_date (sales_date1, 'yyyy-MM-DD '), city_id, employee_id, 'toby', sales); end loop; dbms_output.put_line ('done! '); Commit; end;

 

Currently, SALES_DATA3 has 10 million data records distributed between and. there are three SALES_DATA partitions with bitmap SALES_DATA1 partitions, and no bitmap SALES_DATA2 is run three times in total to reduce the probability of taking the minimum time for normal insertion.

SQL code -- insert into SALES_DATA1 select * from SALES_DATA3 where sales_date <to_date ('2017-02-1 ', 'yyyy-mm-dd') in 167.218, 137.045, 135.247, and 135.247 seconds '); use nologging to try the SQL code -- 203.28, 189.064, 167.993, minimum 167.993 seconds alter table SALES_DATA1 nologging; insert into SALES_DATA1 select * from SALES_DATA3 where sales_date <to_date ('2017-02-1 ', 'yyyy-mm-dd'); alter table SALES_DATA1 logging; more time than logging... theoretically it should be short. at least it seems that nolog is not obvious. Try the appendSql code again -- 81.867, 89.569, 84.574 min 81.867 seconds insert/* + append */into SALES_DATA1 select * from SALES_DATA3 where sales_date <to_date ('2017-02-1 ', 'yyyy-mm-dd '); try other append + nologgingSql code -- 68.559, 84.127, 84.907 min 68.559 seconds insert/* + append */into SALES_DATA1 nologging select * from SALES_DATA3 where sales_date <to_date ('2017-02-1 ', 'yyyy-mm-dd '); it seems that nologging is useful in append + nologging. when computer spam is parallel, the SQL code alter table SALES_DATA3 parallel; alter table SALES_DATA1 parallel; alter table SALES_DATA1 nologging; insert/* + append */into SALES_DATA1 select/* + parallel (t, 2) */* from SALES_DATA3 t where sales_date <to_date ('2017-02-1 ', 'yyyy-mm-dd'); similarly, bitmap has a significant impact on insert, which may be several times different. SQL code alter index into modify partition sales_200903 unusable; alter index INDEX_SALES_DATA_CITY modify partition sales_200903 unusable; alter index ready modify partition sales_200903 unusable; ----- insert statement ----- re-build the statement using the PARALLEL alter index ready rebuild partition sales_200903 nologging parallel; alter index ready rebuild partition sales_200903 NOLOGGING PARALLEL; alter index INDEX_SALES_DATA_SALES_TYPE rebuild partition sales_200903 nologging parallel;