在大数据量的系统中,分区表是很常见的,分区有多种类型,可以根据业务来选择自己需要的分区,不过为了数据的兼容性,需要考虑对于分
在大数据量的系统中,分区表是很常见的,分区有多种类型,可以根据业务来选择自己需要的分区,不过为了数据的兼容性,需要考虑对于分区表设定一个默认的表分区,如果数据在插入表分区的时候,,没有符合条件的分区,就会插入默认的表分区中。
这个可以根据自己的需要来设定,如果确实对数据有严格的要求,甚至可以要求不加入默认的分区。
以下的脚本会去"扫描"分区的信息,如果检测到没有默认的分区,就会生成对应的脚本。
可以根据自己的情况来决定是否需要加入分区。
sqlplus -s $1/$2@$SH_DB_SID set head off
set pages 100
set linesize 200
/* bad performance
select name,Count(*)from user_part_key_columns where object_type='TABLE' and name in
(
select table_name from user_tables where partitioned='YES'
minus
(select distinct table_name
from user_tab_partitions where partition_name like '%MAX%'
)
)
group by name;
*/
select 'alter table '||table_name||' add partition PMAXVALUE values less than ('||
decode(col_count, 1, 'MAXVALUE',
2,'MAXVALUE,MAXVALUE',
3,'MAXVALUE,MAXVALUE,MAXVALUE',
4,'MAXVALUE,MAXVALUE,MAXVALUE,MAXVALUE','partition out of range')||');'
from
(
(
select t1.table_name,count(t2.name) col_count from user_tables t1,user_part_key_columns t2
where t1.partitioned='YES'
and t1.table_name=t2.name
and t2.object_type='TABLE'
group by t1.table_name
)
minus
(
select t2.name table_name,count(t2.name)col_count
from user_tab_partitions t1,user_part_key_columns t2
where t1.partition_name like '%MAX%'
and t1.table_name=t2.name
and t2.object_type='TABLE'
group by t2.name
)
)
/
EOF
exit
简单运行一下,可以看到有一些分区表是灭有默认的分区的。可以根据自己的情况来定夺。
[ora11g@rac1 dbm_lite]$ ksh getmaxpar.sh system Oracle
alter table LOGMNRC_GSBA add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNRC_GSII add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNRC_GTCS add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNRC_GTLO add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNRP_CTAS_PART_MAP add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNR_ATTRCOL$ add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNR_ATTRIBUTE$ add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNR_CCOL$ add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNR_CDEF$ add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNR_COL$ add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNR_COLTYPE$ add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNR_DICTIONARY$ add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNR_DICTSTATE$ add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNR_ENC$ add partition PMAXVALUE values less than (MAXVALUE);
alter table LOGMNR_ICOL$ add partition PMAXVALUE values less than (MAXVALUE);
浅谈Oracle分区表之范围分区
Oracle分区表迁移
Oracle分区表使用实例
Oracle分区表 (Partition Table) 的创建及管理
本文永久更新链接地址:

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

The steps to build a MySQL database include: 1. Create a database and table, 2. Insert data, and 3. Conduct queries. First, use the CREATEDATABASE and CREATETABLE statements to create the database and table, then use the INSERTINTO statement to insert the data, and finally use the SELECT statement to query the data.

MySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.

MySQL is suitable for beginners because: 1) easy to install and configure, 2) rich learning resources, 3) intuitive SQL syntax, 4) powerful tool support. Nevertheless, beginners need to overcome challenges such as database design, query optimization, security management, and data backup.

Yes,SQLisaprogramminglanguagespecializedfordatamanagement.1)It'sdeclarative,focusingonwhattoachieveratherthanhow.2)SQLisessentialforquerying,inserting,updating,anddeletingdatainrelationaldatabases.3)Whileuser-friendly,itrequiresoptimizationtoavoidper

ACID attributes include atomicity, consistency, isolation and durability, and are the cornerstone of database design. 1. Atomicity ensures that the transaction is either completely successful or completely failed. 2. Consistency ensures that the database remains consistent before and after a transaction. 3. Isolation ensures that transactions do not interfere with each other. 4. Persistence ensures that data is permanently saved after transaction submission.

MySQL is not only a database management system (DBMS) but also closely related to programming languages. 1) As a DBMS, MySQL is used to store, organize and retrieve data, and optimizing indexes can improve query performance. 2) Combining SQL with programming languages, embedded in Python, using ORM tools such as SQLAlchemy can simplify operations. 3) Performance optimization includes indexing, querying, caching, library and table division and transaction management.

MySQL uses SQL commands to manage data. 1. Basic commands include SELECT, INSERT, UPDATE and DELETE. 2. Advanced usage involves JOIN, subquery and aggregate functions. 3. Common errors include syntax, logic and performance issues. 4. Optimization tips include using indexes, avoiding SELECT* and using LIMIT.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

WebStorm Mac version
Useful JavaScript development tools

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SublimeText3 English version
Recommended: Win version, supports code prompts!

Zend Studio 13.0.1
Powerful PHP integrated development environment