Problems creating a local index on a partitioned table

I have a list of the EMP tables, divided into P1, P2, p3 three districts,

To create a local index on this table, what is the difference between the following two ways:

Mode one:
CREATE INDEX lhq_idx on EMP (DEPTNO) Local
(PARTITION p1 tablespace users,
PARTITION P2 tablespace users
PARTITION P3 tablespace Users
);

Mode two:

CREATE INDEX LHQ_IDX on EMP (deptno) local tablspace users;

The first is to create a partitioned index on the partitioned table.
The second is to create a global index on the partitioned table.
That
CREATE INDEX LHQ_IDX on EMP (deptno) local tablspace users; <=> (equivalent to)
CREATE INDEX LHQ_IDX on EMP (DEPTNO) global partition by range (DEPTNO);

I feel that the two ways are the same, but the first one can specify the table space,

Create INDEX IDX_LHQ on LHQ (DEPTNO) to view the results of user_segments



Create INDEX Lhq_idx on LHQ (DEPTNO) global; View the results of user_segments

A summary of the table spaces, partitioned tables, and indexes of Oracle

The first time I ran a database test last week, I came across a lot of questions.

Table Space:
Oracle's UNDOTBS01.DBF file is too big a solution
1. Prohibit undo tablespace automatic growth
ALTER DATABASE datafile ' FULL_PATH\UNDOTBS01.DBF ' autoextend off;
2.--Create a new small space for the undo Tablespace
Create undo tablespace undotBS2 DataFile ' Full_path\undotbs02. DBF ' size 100m;

--Set the new table space for the system Undo_tablespace
alter system set UNDO_TABLESPACE=UNDOTBS2;

--Drop the old table space
Drop tablespace undotbs1 including contents;

--View all table spaces
SELECT * FROM Dba_tablespaces

--Create a table space
Create Tablespace HRPM0
DataFile '/oradata/misdb/hrpm0. DBF ' size 5m autoextend on next 10m MaxSize Unlimited

--Remove Tablespace
DROP tablespace data01 including CONTENTS and datafiles;

--Modify Table space size
ALTER DATABASE datafile '/PATH/NADDATE05.DBF ' resize 100M


Partition table:

When the amount of data in a table increases, the query data slows down, and the application's performance drops, you should consider partitioning the table. After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple table spaces (physical files) so that when querying the data, it does not scan the entire table every time.
Some of the following table partitions are available in Oracle:
Range Partitioning: This type of partition uses a set of values for a column, usually the column as a partitioning key.
Example 1: Suppose you have a customer table with data 200000 rows, we partition the table through customer_id, each partition stores 100000 rows, and we save each partition in a separate tablespace so that the data file can span multiple physical disks. Here is the code to create the tables and partitions, as follows:
CREATE TABLE CUSTOMER
(
customer_id number not NULL PRIMARY KEY,
First_Name VARCHAR2 () not NULL,
Last_Name VARCHAR2 () not NULL,
PHONE VARCHAR2 not NULL,
EMAIL VARCHAR2 (80),
STATUS CHAR (1)
)
PARTITION by RANGE (customer_id)
(
PARTITION cus_part1 VALUES less THAN (100000) tablespace cus_ts01,
PARTITION cus_part2 VALUES less THAN (200000) tablespace CUS_TS02
)
Note: When you create a table for partitioning, the table space must first exist, and it is recommended that different partitions be placed in different table spaces.
Example 2: Assuming there is a order_activities table, every 6 months to clean up the order, we can partition the table by month, the partition code is as follows:
CREATE TABLE order_activities
(
ORDER_ID Number (7) is not NULL,
Order_date DATE,
Total_amount number,
CUSTOTMER_ID Number (7),
PAID CHAR (1)
)
PARTITION by RANGE (order_date)
(
PARTITION ord_act_part01 VALUES Less THAN (to_date (' 01-may-2003 ', ' dd-mon-yyyy ')) tablespace ord_ts01,
PARTITION ord_act_part02 VALUES Less THAN (to_date (' 01-jun-2003 ', ' dd-mon-yyyy ')) tablespace ORD_TS02,
PARTITION ord_act_part02 VALUES Less THAN (to_date (' 01-jul-2003 ', ' dd-mon-yyyy ')) tablespace ord_ts03
)

Second, the list partition: This partition is characterized by a column of only a few values, based on this feature we can use the list partition.
Example 1:
CREATE TABLE Problem_tickets
(
PROBLEM_ID Number (7) is not NULL PRIMARY KEY,
DESCRIPTION VARCHAR2 (2000),
CUSTOMER_ID Number (7) is not NULL,
Date_entered DATE not NULL,
STATUS VARCHAR2 (20)
)
PARTITION by LIST (STATUS)
(
PARTITION prob_active VALUES (' ACTIVE ') tablespace prob_ts01,
PARTITION prob_inactive VALUES (' INACTIVE ') tablespace PROB_TS02
)

Hash partitioning: This type of partition uses a hashing algorithm on the column value to determine which partition to put the row into. A hash partition is recommended when the value of a column does not have an appropriate condition. Take a look at the following examples:
Example 1:
CREATE TABLE hash_table
(
COL Number (8),
INF VARCHAR2 (100)
)
PARTITION by HASH (COL)
(
PARTITION PART01 tablespace hash_ts01,
PARTITION PART02 tablespace HASH_TS02,
PARTITION PART03 tablespace Hash_ts03
)

Composite Range List Partitioning: This partition is based on a range partition and a list partition, the table is first scoped by a column, then the list is partitioned by a column, and the partitions in the partition are called sub partitions.
Example 1:
CREATE TABLE SALES
(
product_id VARCHAR2 (5),
Sales_date DATE,
Sales_cost Number (10),
STATUS VARCHAR2 (20)
)
PARTITION by RANGE (sales_date)
Subpartition by LIST (STATUS)
(
PARTITION P1 VALUES Less THAN (to_date (' 2003-01-01 ', ' yyyy-mm-dd ')) tablespace p1_ts
(
Subpartition p1sub1 VALUES (' ACTIVE ') tablespace subp1_ts1,
Subpartition p1sub2 VALUES (' INACTIVE ') tablespace subp1_ts2
),
PARTITION P2 VALUES Less THAN (to_date (' 2003-03-01 ', ' yyyy-mm-dd ')) tablespace p2_ts
(
Subpartition p2sub1 VALUES (' ACTIVE ') tablespace subp2_ts1,