Oracle partitioning, indexing, testing (1)

Source: Internet
Author: User

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;

 


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.