Table Creation and Partitioning Strategies
1. Range Partitioning (Date-based):
CREATE TABLE orders (
order_id NUMBER,
order_date DATE,
customer_id NUMBER,
amount NUMBER
)
PARTITION BY RANGE (order_date) (
PARTITION p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
PARTITION p3 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);
2. List Partitioning (Category-based):
CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(100),
department VARCHAR2(50)
)
PARTITION BY LIST (department) (
PARTITION p_sales VALUES ('SALES'),
PARTITION p_hr VALUES ('HR'),
PARTITION p_it VALUES ('IT'),
PARTITION p_finance VALUES ('FINANCE')
);
3. Hash Partitioning:
CREATE TABLE customers (
cust_id NUMBER,
cust_name VARCHAR2(100),
region VARCHAR2(50)
)
PARTITION BY HASH (cust_id)
PARTITIONS 4;
4. Composite Range-Hash Partitioning:
CREATE TABLE transactions (
trans_id NUMBER,
trans_date DATE,
product_id NUMBER,
amount NUMBER
)
PARTITION BY RANGE (trans_date)
SUBPARTITION BY HASH (product_id)
SUBPARTITIONS 4 (
PARTITION p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);
5. Interval Partitioning:
CREATE TABLE sales_interval (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
PARTITION p_start VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);
6. Reference Partitioning:
CREATE TABLE master_table (
master_id NUMBER PRIMARY KEY,
creation_date DATE
);
CREATE TABLE detail_table (
detail_id NUMBER,
master_id NUMBER,
description VARCHAR2(100),
FOREIGN KEY (master_id) REFERENCES master_table(master_id)
)
PARTITION BY REFERENCE (master_table);
7. System Partitioning:
CREATE TABLE system_partitioned_table (
id NUMBER,
data VARCHAR2(100)
)
PARTITION BY SYSTEM (
PARTITION p1,
PARTITION p2
);
8. Virtual Column-Based Partitioning:
CREATE TABLE employees_vcol (
emp_id NUMBER,
emp_name VARCHAR2(100),
hire_date DATE,
year_hired AS (EXTRACT(YEAR FROM hire_date))
)
PARTITION BY RANGE (year_hired) (
PARTITION p_before_2020 VALUES LESS THAN (2020),
PARTITION p_2020_onwards VALUES LESS THAN (2025)
);
9. Creating a Partitioned Index:
CREATE INDEX idx_order_date ON orders(order_date)
LOCAL;
10. Creating a Global Non-Partitioned Index:
CREATE INDEX idx_global_customer ON customers(customer_id)
GLOBAL;
Partition Management
11. Splitting a Partition:
ALTER TABLE orders
SPLIT PARTITION p2 AT (TO_DATE('2024-07-01', 'YYYY-MM-DD'))
INTO (PARTITION p2a, PARTITION p2b);
12. Merging Partitions:
ALTER TABLE orders
MERGE PARTITIONS p2a, p2b INTO PARTITION p2;
13. Dropping a Partition:
ALTER TABLE orders
DROP PARTITION p3;
14. Adding a New Partition:
ALTER TABLE orders
ADD PARTITION p4 VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD'));
15. Truncating a Partition:
ALTER TABLE orders
TRUNCATE PARTITION p2;
16. Exchanging a Partition:
CREATE TABLE temp_orders AS SELECT * FROM orders WHERE 1=0;
ALTER TABLE orders
EXCHANGE PARTITION p2 WITH TABLE temp_orders;
17. Coalescing Partitions:
ALTER TABLE customers
COALESCE PARTITION;
18. Moving a Partition:
ALTER TABLE orders
MOVE PARTITION p2 TO tablespace new_tablespace;
19. Renaming a Partition:
ALTER TABLE orders
RENAME PARTITION p2 TO p2_renamed;
20. Checking Partition Status:
SELECT partition_name, high_value, tablespace_name
FROM user_tab_partitions
WHERE table_name = 'ORDERS';
21. Checking Subpartition Information:
SELECT subpartition_name, high_value, partition_name
FROM user_tab_subpartitions
WHERE table_name = 'TRANSACTIONS';
22. Checking Partitioned Index Information:
SELECT index_name, partition_name, high_value
FROM user_ind_partitions
WHERE index_name = 'IDX_ORDER_DATE';
23. Checking Global Partitioned Indexes:
SELECT index_name, table_name, partitioned
FROM user_indexes
WHERE partitioned = 'YES';
24. Rebuilding a Partitioned Index:
ALTER INDEX idx_order_date
REBUILD PARTITION p1;
25. Dropping a Partitioned Index:
DROP INDEX idx_order_date;
Performance Tuning and Optimization
26. Gathering Statistics for a Partitioned Table:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'ORDERS', PARTNAME => 'P1');
END;
27. Gathering Global Statistics:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'ORDERS');
END;
28. Parallel Query on a Partitioned Table:
SELECT /*+ PARALLEL(ORDERS, 4) */ COUNT(*)
FROM orders;
29. Parallel Index Creation:
CREATE INDEX idx_parallel ON orders(order_date)
LOCAL PARALLEL 4;
30. Moving Partitions to Different Tablespaces:
ALTER TABLE orders
MOVE PARTITION p1 TO tablespace ts_orders_2023;
31. Checking Partition Pruning:
EXPLAIN PLAN FOR
SELECT * FROM orders
WHERE order_date = TO_DATE('2024-05-01', 'YYYY-MM-DD');
32. Loading Data into Specific Partition:
INSERT INTO orders PARTITION (p1)
SELECT * FROM external_table
WHERE order_date < TO_DATE('2024-01-01', 'YYYY-MM-DD');
33. Bulk Load into Partitioned Table:
INSERT /*+ APPEND */ INTO orders
SELECT * FROM external_table;
34. Creating a Partitioned Table Using Parallel DDL:
CREATE TABLE large_table (
id NUMBER,
name VARCHAR2(100),
creation_date DATE
)
PARTITION BY RANGE (creation_date)
PARALLEL 4;
35. Direct Path Insert into Partitioned Table:
INSERT /*+ APPEND */ INTO orders
SELECT * FROM temp_orders;
Advanced Partitioning Techniques
36. Composite List-Range Partitioning:
CREATE TABLE composite_list_range (
id NUMBER,
category VARCHAR2(50),
sale_date DATE
)
PARTITION BY LIST (category)
SUBPARTITION BY RANGE (sale_date) (
PARTITION electronics VALUES ('ELECTRONICS') (
SUBPARTITION s1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
SUBPARTITION s2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
),
PARTITION clothing VALUES ('CLOTHING') (
SUBPARTITION s3 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
SUBPARTITION s4 VALUES LESS THAN (TO_DATE('2024-01-01
', 'YYYY-MM-DD'))
)
);
37. Composite Hash-Range Partitioning:
CREATE TABLE composite_hash_range (
id NUMBER,
region VARCHAR2(50),
sale_date DATE
)
PARTITION BY HASH (region)
SUBPARTITION BY RANGE (sale_date)
SUBPARTITIONS 4 (
PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);
38. Adding a Subpartition:
ALTER TABLE composite_list_range
ADD SUBPARTITION s5 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
INTO PARTITION electronics;
39. Dropping a Subpartition:
ALTER TABLE composite_list_range
DROP SUBPARTITION s5;
40. Merging Subpartitions:
ALTER TABLE composite_list_range
MERGE SUBPARTITIONS s1, s2 INTO SUBPARTITION s12;
41. Splitting a Subpartition:
ALTER TABLE composite_list_range
SPLIT SUBPARTITION s12 AT (TO_DATE('2024-07-01', 'YYYY-MM-DD'))
INTO (SUBPARTITION s12a, SUBPARTITION s12b);
42. Global Index on Composite Partitioned Table:
CREATE INDEX idx_global ON composite_list_range(id)
GLOBAL;
43. Local Index on Composite Partitioned Table:
CREATE INDEX idx_local ON composite_list_range(id)
LOCAL;
44. Dropping a Composite Partitioned Table:
DROP TABLE composite_list_range;
45. Creating Interval-Reference Partitioning:
CREATE TABLE master_interval_ref (
master_id NUMBER,
creation_date DATE
)
PARTITION BY RANGE (creation_date)
INTERVAL (NUMTOYMINTERVAL(1, 'YEAR')) (
PARTITION p0 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);
CREATE TABLE detail_interval_ref (
detail_id NUMBER,
master_id NUMBER,
description VARCHAR2(100),
FOREIGN KEY (master_id) REFERENCES master_interval_ref(master_id)
)
PARTITION BY REFERENCE (master_interval_ref);
46. Creating Interval-List Partitioning:
CREATE TABLE interval_list (
id NUMBER,
category VARCHAR2(50),
sale_date DATE
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
SUBPARTITION BY LIST (category) (
PARTITION p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
SUBPARTITIONS (
SUBPARTITION electronics VALUES ('ELECTRONICS'),
SUBPARTITION clothing VALUES ('CLOTHING')
)
);
Partitioning for Large-Scale Data Management
47. Managing Large Data Volumes with Interval Partitioning:
CREATE TABLE large_sales (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (ts_2024, ts_2025);
48. Partitioning a Historical Data Table:
CREATE TABLE historical_data (
id NUMBER,
event_date DATE,
description VARCHAR2(255)
)
PARTITION BY RANGE (event_date) (
PARTITION p_old VALUES LESS THAN (TO_DATE('2000-01-01', 'YYYY-MM-DD')),
PARTITION p_recent VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')),
PARTITION p_current VALUES LESS THAN (MAXVALUE)
);
49. Partitioning a Table Based on Multiple Columns:
CREATE TABLE multi_column_partition (
id NUMBER,
region VARCHAR2(50),
sale_date DATE
)
PARTITION BY RANGE (sale_date, region) (
PARTITION p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'REGION1'),
PARTITION p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'), 'REGION2')
);
50. Partitioning for Archive Data:
CREATE TABLE archived_data (
archive_id NUMBER,
archive_date DATE,
data BLOB
)
PARTITION BY RANGE (archive_date)
SUBPARTITION BY HASH (archive_id)
SUBPARTITIONS 4;
Queries for Partition Management and Performance Monitoring
51. Finding the Largest Partition in a Table:
SELECT partition_name, blocks
FROM user_tab_partitions
WHERE table_name = 'ORDERS'
ORDER BY blocks DESC;
52. Analyzing Partition Access in Execution Plans:
EXPLAIN PLAN FOR
SELECT * FROM orders
WHERE order_date = TO_DATE('2024-05-01', 'YYYY-MM-DD');
53. Counting Rows in Each Partition:
SELECT partition_name, COUNT(*)
FROM orders
PARTITION BY partition_name
GROUP BY partition_name;
54. Monitoring Partition Growth Over Time:
SELECT partition_name, SUM(bytes)/1024/1024 MB
FROM dba_segments
WHERE segment_name = 'ORDERS'
GROUP BY partition_name
ORDER BY MB DESC;
55. Identifying Underutilized Partitions:
SELECT partition_name, num_rows
FROM dba_tab_partitions
WHERE table_name = 'ORDERS'
AND num_rows < 1000;
56. Checking Partitioned Table Size:
SELECT partition_name, SUM(bytes)/1024/1024 MB
FROM user_segments
WHERE segment_name = 'ORDERS'
GROUP BY partition_name
ORDER BY MB DESC;
57. Moving Partitions to Compress Data:
ALTER TABLE orders
MOVE PARTITION p1
COMPRESS FOR OLTP;
58. Implementing Partition-wise Join for Performance:
SELECT /*+ USE_NL(p1 p2) */ *
FROM orders p1, customers p2
WHERE p1.customer_id = p2.customer_id
AND p1.order_date BETWEEN p2.start_date AND p2.end_date;
59. Rebuilding Partitions for Performance Optimization:
ALTER TABLE orders
REBUILD PARTITION p1;
60. Checking I/O Usage by Partition:
SELECT partition_name, SUM(blocks)
FROM dba_segments
WHERE segment_name = 'ORDERS'
GROUP BY partition_name
ORDER BY SUM(blocks) DESC;
Advanced Management and Optimization
61. Automating Partition Creation for Future Dates:
BEGIN
FOR i IN 1..12 LOOP
EXECUTE IMMEDIATE 'ALTER TABLE sales ADD PARTITION p' || TO_CHAR(SYSDATE + INTERVAL '1' MONTH * i, 'YYYYMM') ||
' VALUES LESS THAN (TO_DATE(''' || TO_CHAR(SYSDATE + INTERVAL '1' MONTH * i, 'YYYY-MM-DD') || ''', ''YYYY-MM-DD''))';
END LOOP;
END;
62. Partitioning Large Tables by Fiscal Year:
CREATE TABLE fiscal_sales (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_date) (
PARTITION fy2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
PARTITION fy2024 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
PARTITION fy2025 VALUES LESS THAN (TO_DATE('2025-07-01', 'YYYY-MM-DD'))
);
63. Implementing Partition-wise Aggregation:
SELECT partition_name, SUM(amount)
FROM orders
PARTITION BY partition_name
GROUP BY partition_name;
64. Partitioning by Week Using Interval Partitioning:
CREATE TABLE weekly_sales (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTODSINTERVAL(7, 'DAY')) (
PARTITION p_initial VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);
65. Rolling Window Partitioning:
CREATE TABLE rolling_window (
id NUMBER,
log_date DATE,
data VARCHAR2(255)
)
PARTITION BY
RANGE (log_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
PARTITION p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);
66. Handling Historical Data with Partition Exchange:
CREATE TABLE history_sales
AS SELECT * FROM sales
WHERE 1=0;
ALTER TABLE sales
EXCHANGE PARTITION p1 WITH TABLE history_sales;
67. Data Aging with Partitioning:
CREATE TABLE aged_data (
data_id NUMBER,
entry_date DATE,
data_value VARCHAR2(255)
)
PARTITION BY RANGE (entry_date) (
PARTITION recent VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION archive VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD'))
);
68. Optimizing Data Retrieval with Partition Pruning:
SELECT *
FROM orders
WHERE order_date >= TO_DATE('2024-07-01', 'YYYY-MM-DD');
69. Implementing Data Retention Policies with Partitioning:
ALTER TABLE orders
DROP PARTITION p1;
70. Partition-wise Data Purging:
ALTER TABLE orders
TRUNCATE PARTITION p1;
Monitoring and Maintenance
71. Analyzing Table Partitions for Space Usage:
SELECT partition_name, SUM(bytes)/1024/1024 MB
FROM dba_segments
WHERE segment_name = 'ORDERS'
GROUP BY partition_name
ORDER BY MB DESC;
72. Monitoring Partition Growth Patterns:
SELECT partition_name, COUNT(*)
FROM orders
PARTITION BY partition_name
GROUP BY partition_name
ORDER BY COUNT(*) DESC;
73. Tracking Partition Usage Over Time:
SELECT partition_name, SUM(blocks)
FROM dba_segments
WHERE segment_name = 'ORDERS'
GROUP BY partition_name
ORDER BY SUM(blocks) DESC;
74. Checking Partitioned Table Segment Details:
SELECT partition_name, segment_name, tablespace_name, blocks
FROM dba_segments
WHERE segment_name = 'ORDERS';
75. Partition-Wise Compression Status:
SELECT partition_name, compression
FROM user_tab_partitions
WHERE table_name = 'ORDERS';
76. Listing All Tables with Partitioning:
SELECT table_name, partitioned
FROM user_tables
WHERE partitioned = 'YES';
77. Querying Partition Key Values:
SELECT partition_name, high_value
FROM user_tab_partitions
WHERE table_name = 'ORDERS';
78. Checking Partition Statistics:
SELECT partition_name, num_rows, blocks
FROM user_tab_partitions
WHERE table_name = 'ORDERS';
79. Listing All Partitions for a Table:
SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'ORDERS';
80. Analyzing I/O Distribution Across Partitions:
SELECT partition_name, SUM(blocks)
FROM dba_segments
WHERE segment_name = 'ORDERS'
GROUP BY partition_name
ORDER BY SUM(blocks) DESC;
Best Practices and Guidelines
81. Ensuring Balanced Partitioning:
SELECT partition_name, COUNT(*)
FROM orders
PARTITION BY partition_name
GROUP BY partition_name
HAVING COUNT(*) > 1000000;
82. Archiving Old Data Using Partitions:
ALTER TABLE orders
DROP PARTITION p_old;
83. Reducing Fragmentation with Partition Management:
ALTER TABLE orders
COALESCE PARTITION;
84. Implementing Sliding Window Partitioning:
CREATE TABLE sliding_window (
id NUMBER,
entry_date DATE,
data_value VARCHAR2(255)
)
PARTITION BY RANGE (entry_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
PARTITION p_initial VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);
85. Maintaining Partitioned Tables Efficiently:
ALTER TABLE orders
TRUNCATE PARTITION p_old;
86. Partition Key Selection Best Practices:
CREATE TABLE optimized_orders (
order_id NUMBER,
order_date DATE,
customer_id NUMBER,
amount NUMBER
)
PARTITION BY RANGE (order_date) (
PARTITION p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
PARTITION p3 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);
87. Monitoring Partition-Wise Storage Utilization:
SELECT partition_name, SUM(bytes)/1024/1024 MB
FROM dba_segments
WHERE segment_name = 'ORDERS'
GROUP BY partition_name
ORDER BY MB DESC;
88. Optimizing Partitioning for Large Tables:
ALTER TABLE orders
MOVE PARTITION p1 COMPRESS FOR OLTP;
89. Ensuring Efficient Partition Pruning:
EXPLAIN PLAN FOR
SELECT * FROM orders
WHERE order_date BETWEEN TO_DATE('2024-07-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD');
90. Implementing Partition-Wise Backups:
BACKUP PARTITION orders PARTITION p1;
Miscellaneous Queries
91. Adding a Comment to a Partition:
COMMENT ON PARTITION p1
IS 'Orders for the first quarter of 2024';
92. Creating a Range-Interval Partitioned Table:
CREATE TABLE dynamic_sales (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
PARTITION p_start VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);
93. Monitoring Partition-Level Compression:
SELECT partition_name, compression
FROM user_tab_partitions
WHERE table_name = 'ORDERS';
94. Listing Partitions with High Data Volume:
SELECT partition_name, COUNT(*)
FROM orders
PARTITION BY partition_name
GROUP BY partition_name
HAVING COUNT(*) > 1000000;
95. Implementing Partition-wise Data Encryption:
ALTER TABLE orders
MODIFY PARTITION p1 ENCRYPT USING 'AES256';
96. Implementing Partition-Wise Sharding:
CREATE SHARDING TABLE sharded_orders (
order_id NUMBER,
order_date DATE,
amount NUMBER
)
PARTITION BY RANGE (order_date)
SHARD (
PARTITION p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);
97. Setting a Default Partition:
ALTER TABLE orders
ADD PARTITION default_partition VALUES LESS THAN (MAXVALUE);
98. Partitioning by Year and Region:
CREATE TABLE yearly_regional_sales (
sale_id NUMBER,
sale_date DATE,
region VARCHAR2(50),
amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region) (
PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) (
SUBPARTITION region1 VALUES ('REGION1'),
SUBPARTITION region2 VALUES ('REGION2')
),
PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')) (
SUBPARTITION region1 VALUES ('REGION1'),
SUBPARTITION region2 VALUES ('REGION2')
)
);
99. Setting Up Partitioned Audit Logs:
CREATE TABLE audit_logs (
log_id NUMBER,
log_date DATE,
action VARCHAR2(255)
)
PARTITION BY RANGE (log_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
PARTITION p_initial VALUES LESS THAN (TO_DATE('2024-01-01
', 'YYYY-MM-DD'))
);
100. Partition-Wise Export:
EXPDP scott/tiger TABLES=orders PARTITION_OPTIONS=DEPT;
No comments:
Post a Comment