Oracle learning: partitioned tables and Indexes

When to use Oracle partitions: 1. Tables with large data volumes, such as tables larger than 2 GB. On the one hand, a 2 GB file is an upper limit for 32-bit OS, and the backup time is long. 2,

When to use Oracle partitions: 1. Tables with large data volumes, such as tables larger than 2 GB. On the one hand, a 2 GB file is an upper limit for 32-bit OS, and the backup time is long. 2,

When to use Oracle partitioning:
1. Large Data Tables, such as tables larger than 2 GB. On the one hand, a 2 GB file is an upper limit for 32-bit OS, and the backup time is long.
2. tables that contain historical data, such as the latest data, are placed in the latest partition. Typical Example: In a historical table, only the data of the current month can be modified, and only the read-only data of other months can be modified.
ORACLE only supports the following partitions: tables, indexes on tables, materialized views, and indexes on materialized views.
Partitions are transparent to SQL and DML (applications do not have to know that they have already been partitioned), but DDL can manage different partitions.
Different partitions must have the same logical attributes, such as common table names, column names, data types, and constraints;
However, they can have different physical attributes, such as pctfree, pctused, and tablespaces.
Partition independence: other partitions are still available even if some partitions are unavailable.
A maximum of 64000 partitions can be divided, but tables with LONG or long raw Columns cannot, but tables with CLOB or BLOB columns can.
You do not need the to_date function, for example:
Alter session set nls_date_format = 'Mm/dd/yyyy ';
Create table sales_range
(Salesman_id NUMBER (5 ),
Salesman_name VARCHAR2 (30 ),
Sales_amount NUMBER (10 ),
Sales_date DATE)
Partition by range (sales_date)
(
PARTITION sales_jan2000 values less than ('2017/123 '),
PARTITION sales_feb2000 values less than ('2014/1/123 '),
PARTITION sales_mar2000 values less than ('2017/123 '),
PARTITION sales_apr2000 values less than ('2017/123 ')
);
Partition Key: a maximum of 16 columns, which can be nullable
Non-Partitioned Tables can have partitioned or non-partitioned indexes;
Partitioned Tables can have partitioned or non-partitioned indexes;
Partitioning Method:
Range Partitioning
List Partitioning
Hash Partitioning
Composite Partitioning
Composite Partitioning: combination, and range-hash and range-list composite partitioning
Range Partitioning:
Each partition has a values less than clause, indicating that the partition is smaller THAN (<) a certain upper limit, and greater THAN or equal to (> =) the value of values less than in the previous partition.
MAXVALUE defines the highest partition, which represents a virtual infinite value. This partition contains null values.
Create table sales_range
(Salesman_id NUMBER (5 ),
Salesman_name VARCHAR2 (30 ),
Sales_amount NUMBER (10 ),
Sales_date DATE)
Partition by range (sales_date)
(
PARTITION sales_jan2000 values less than (TO_DATE ('2014/1/123', 'dd/MM/yyyy ')),
PARTITION sales_feb2000 values less than (TO_DATE ('2017/123456', 'dd/MM/YYYY ')),
PARTITION sales_mar2000 values less than (TO_DATE ('2014/1/123', 'dd/MM/yyyy ')),
PARTITION sales_apr2000 values less than (TO_DATE ('2017/123456', 'dd/MM/YYYY ')),
PARTITION sales_2000 values less than (MAXVALUE)
);
Insert data:
Insert into sales_range values (, 3, to_date ('21-04-2000 ', 'dd-MM-YYYY '));
Insert into sales_range values (1, 2, 3, sysdate );
Select data:
Select * from sales_range;
Select * from sales_range partition (sales_apr2000 );
Select * from sales_range partition (sales_mar2000 );
Select * from sales_range partition (sales_2000 );
Partition by multiple columns:
Create table sales_range1
(Salesman_id NUMBER (5 ),
Salesman_name VARCHAR2 (30 ),
Sales_amount NUMBER (10 ),
Sales_date DATE)
Partition by range (sales_date, sales_amount)
(
PARTITION sales_jan2000 values less than (TO_DATE ('2014/1/123', 'dd/MM/YYYY '), 01/02 ),
PARTITION sales_feb2000 values less than (TO_DATE ('2017/123456', 'dd/MM/YYYY '), 01/03 ),
PARTITION sales_mar2000 values less than (TO_DATE ('2014/1/123', 'dd/MM/YYYY '), 01/04 ),
PARTITION sales_apr2000 values less than (TO_DATE ('2017/123456', 'dd/MM/YYYY '), 01/05 ),
PARTITION sales_2000 values less than (MAXVALUE, MAXVALUE)
);
Insert into sales_range1 values (500, 21/01, TO_DATE ('2014/1/123', 'dd/MM/yyyy '));
Insert into sales_range1 values (2,3, 1500, sysdate );