당신은 주제를 찾고 있습니까 “ora-02243 invalid alter index or alter materialized view option – Indexed Views (Materialized Views) in SQL Server and Performance Considerations“? 다음 카테고리의 웹사이트 https://ppa.charoenmotorcycles.com 에서 귀하의 모든 질문에 답변해 드립니다: https://ppa.charoenmotorcycles.com/blog/. 바로 아래에서 답을 찾을 수 있습니다. 작성자 KeepLearningIT 이(가) 작성한 기사에는 조회수 13,883회 및 좋아요 203개 개의 좋아요가 있습니다.
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option. Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement. Action: Specify only legal options.If the index is local index on the partition, you can disable it as follows. alter table MSDBA. TEST_INDEX modify partition TINDEXP1 unusable local indexes; Or you can allow for unusable indexes as follows.To disable an index, you run an ALTER INDEX command: ALTER INDEX index_name ON table_name DISABLE; You can replace the index_name with the name of your index, and the table_name with the name of the table that the index is created on.
ora-02243 invalid alter index or alter materialized view option 주제에 대한 동영상 보기
여기에서 이 주제에 대한 비디오를 시청하십시오. 주의 깊게 살펴보고 읽고 있는 내용에 대한 피드백을 제공하세요!
d여기에서 Indexed Views (Materialized Views) in SQL Server and Performance Considerations – ora-02243 invalid alter index or alter materialized view option 주제에 대한 세부정보를 참조하세요
In this video, we cover what indexed-views are in SQL Server, how we can create them, and how they can improve query performance.
0:00 – Intro
0:30 – Querying a Normal Database View
1:08 – Querying an Indexed View
2:57 – Demonstration
3:08 – The WITH SCHEMABINDING attribute
3:26 – Official Documentation for Indexed Views
5:28 – Enabling Statistics IO to monitor the performance
5:47 – The Execution Plan
6:22 – Creating a Clustered Index on a View
6:37 – Indexed Views on Non-enterprise Editions
6:56 – The NOEXPAND table hint
8:18 – Creating Non-clustered Indexes on a View
9:05 – Indexed Views and the GROUP BY clause
9:28 – The COUNT_BIG function
10:26 – Query Performance Comparison
The complete course:
Database Views play a fundamental part in Database Development, whether it’s for reporting, productivity, or security, you’ll hardly come across a database that doesn’t contain views. In the course, I will teach you the basic and advanced implementations of Database Views.
Are you ready to join in?
Link to the course (87% Discount):
https://www.udemy.com/course/sql-window-functions-for-advanced-querying/?couponCode=2DACC232A601B93D76D2
#sqlServer #IndexedView #MaterializedView
ora-02243 invalid alter index or alter materialized view option 주제에 대한 자세한 내용은 여기를 참조하세요.
invalid ALTER INDEX or ALTER MATERIALIZED VIEW option …
ORA-02243: inval ALTER INDEX or ALTER MATERIALIZED VIEW option. ; Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an …
Source: doyensys.com
Date Published: 9/9/2021
View: 3980
How to Resolve ORA-02243 – ALTER INDEX – Ed Chen Logic
How to Resolve ORA-02243: inval ALTER INDEX or ALTER MATERIALIZED VIEW option ; ORA-02243 means that there’s a syntax error in your ALTER INDEX …
Source: logic.edchen.org
Date Published: 11/12/2021
View: 976
ORA-02243:invalid ALTER INDEX or ALTER MATERIALIZES …
When I run the DDL script,got the error:ORA-02243:inval ALTER INDEX or ALTER MATERIALIZES VIEW option I checked the metalink, got nothing.
Source: community.oracle.com
Date Published: 1/16/2021
View: 7731
Oracle 12cR1 ORA-02243 invalid ALTER INDEX … – ora_excel
Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW …
Source: www.oraexcel.com
Date Published: 8/26/2021
View: 5640
ORA-02243: the alter index or alter materialized view option is …
ORA-02243: the alter index or alter materialized view option is inval · The function is currently val · The signature of the current function …
Source: topic.alibabacloud.com
Date Published: 4/1/2022
View: 7731
SQL & PL/SQL » alter index problem – OraFAQ Forum
(ORA-02243: inval ALTER INDEX or ALTER MATERIALIZED VIEW option) can you please help me how to disable a index .
Source: www.orafaq.com
Date Published: 2/21/2022
View: 5540
How to solve Error Message ORA-02243 in Oracle 11g
An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW …
Source: turfybot.free.fr
Date Published: 7/3/2021
View: 5530
[20160816]Can index be disabled?.txt – Fear Cat
ERROR at line 1: ORA-02243: inval ALTER INDEX or ALTER MATERIALIZED VIEW option. SCOTT@test01p> alter index IF_DEPTX_DNAME disable; Index altered.
Source: blog.fearcat.in
Date Published: 3/6/2022
View: 3695
‘Re: Diable unique index’ – MARC
SQL> alter index q_x disable; alter index q_x disable * ERROR at line 1: ORA-02243: inval ALTER INDEX or ALTER MATERIALIZED VIEW option SQL> create …
Source: marc.info
Date Published: 10/24/2021
View: 5228
주제와 관련된 이미지 ora-02243 invalid alter index or alter materialized view option
주제와 관련된 더 많은 사진을 참조하십시오 Indexed Views (Materialized Views) in SQL Server and Performance Considerations. 댓글에서 더 많은 관련 이미지를 보거나 필요한 경우 더 많은 관련 기사를 볼 수 있습니다.
주제에 대한 기사 평가 ora-02243 invalid alter index or alter materialized view option
- Author: KeepLearningIT
- Views: 조회수 13,883회
- Likes: 좋아요 203개
- Date Published: 2020. 8. 6.
- Video Url link: https://www.youtube.com/watch?v=qDWPdgglbvA
How to disable index on MATERIALIZED VIEW IN Oracle?
If the index is local index on the partition, you can disable it as follows. alter table MSDBA. TEST_INDEX modify partition TINDEXP1 unusable local indexes; Or you can allow for unusable indexes as follows.
Can we disable index in Oracle?
To disable an index, you run an ALTER INDEX command: ALTER INDEX index_name ON table_name DISABLE; You can replace the index_name with the name of your index, and the table_name with the name of the table that the index is created on.
Can we alter index in Oracle?
Use the ALTER INDEX statement to change or rebuild an existing index. The index must be in your own schema or you must have ALTER ANY INDEX system privilege. To execute the MONITORING USAGE clause, the index must be in your own schema.
How do I create an unusable partition index?
Make an index or index partition unusable by specifying the UNUSABLE keyword. The following example makes index emp_email_uk unusable: hr@PROD> ALTER INDEX emp_email_uk UNUSABLE; Index altered.
Can materialized view have index?
A materialized view can be partitioned, and you can define a materialized view on a partitioned table. You can also define one or more indexes on the materialized view. Unlike indexes, materialized views can be accessed directly using a SELECT statement.
How do I manually refresh a materialized view in Oracle?
- Query Rewrite Method 1: Text Match Rewrite.
- Query Rewrite Method 2: Join Back.
- Query Rewrite Method 3: Aggregate Computability.
- Query Rewrite Method 4: Aggregate Rollup.
- Query Rewrite Method 5: Rollup Using a Dimension.
- Query Rewrite Method 6: When Materialized Views Have Only a Subset of Data.
What happens when you disable an index?
Disabling an index prevents user access to the index, and for clustered indexes to the underlying table data. The index definition remains in metadata, and index statistics are kept on nonclustered indexes. Disabling a clustered index on a view or a nonclustered index physically deletes the index data.
How do I turn off indexing?
To do it, first open the Start menu and type PC and click on This PC when it appears in the search results. Next, right click a drive and click Properties. On the General tab, click to uncheck Allow files on this drive to have contents indexed in addition to file properties at the bottom. After, click Apply.
How many indexes can be created on a table in Oracle?
You can also create an index for a cluster. You can create a composite index on multiple columns up to a maximum of 32 columns. A composite index key cannot exceed roughly one-half (minus some overhead) of the available space in the data block.
How do I change the index in tablespace?
To change the tablespace of an index, you must own the index and have CREATE privilege on the new tablespace. All indexes in the current database in a tablespace can be moved by using the ALL IN TABLESPACE form, which will lock all indexes to be moved and then move each one.
How do I update an index in SQL?
Using SQL Server Management Studio
Right-click the index that you want to modify and then click Properties. In the Index Properties dialog box, make the desired changes. For example, you can add or remove a column from the index key, or change the setting of an index option.
How do I change the tablespace of an index in Oracle?
We can move the indexes & tables to separate tablespaces using the below query. ALTER TABLE [TABLE_NAME] MOVE TABLESPACE TEST_TBL; ALTER INDEX [INDEX_NAME] REBUILD TABLESPACE TEST_TBL; Above queries are possible to move 1 to 2 indexes & tables, but in application schema, there might be 1000’s of tables & indexes.
What causes unusable index in Oracle?
Indexes can become invalid or unusable whenever a DBA tasks shifts the ROWID values, thereby requiring an index rebuild. These DBA tasks that shift table ROWID’s include: Table partition maintenance – Alter commands (move, split or truncate partition) will shift ROWID’s, making the index invalid and unusable.
How do I rebuild a partitioned index in Oracle?
- Rebuild partition index by specifying partition name. ALTER INDEX sales_IDX REBUILD PARTITION sales_Q4 TABLESPACE users;
- Rebuild the Global index as normal. …
- Check the index is local or global: …
- Check the partition index.
- Rebuild the partition local index with the script.
How do you know if an index is unusable?
- SELECT OWNER, INDEX_NAME, NULL AS PARTITION_NAME, NULL AS SUBPARTITION_NAME, TABLESPACE_NAME.
- FROM DBA_INDEXES.
- WHERE STATUS = ‘UNUSABLE’
- UNION ALL — Index partitions:
- SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME, NULL AS SUBPARTITION_NAME, TABLESPACE_NAME.
How do I turn off indexing?
Right-click the index you want to disable and select Disable. In the Disable Indexes dialog box, verify that the correct index is in the Indexes to disable grid and click OK.
How do you drop an index?
- MS Access: DROP INDEX index_name ON table_name;
- SQL Server: DROP INDEX table_name.index_name;
- DB2/Oracle: DROP INDEX index_name;
- MySQL: ALTER TABLE table_name. DROP INDEX index_name;
How do I disable referential integrity constraints in Oracle?
There are multiple ways to disable constraints in Oracle. constraint_name; Another way to enable and disable constraints in Oracle would be to either use a plsql block or write a script. Execute Immediate ‘alter table ‘||:tab_name||’ disable constraint ‘||tabCons(numCount);
What is unusable index in Oracle?
Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as ‘unusable’ with an ALTER INDEX command. A direct path load against a table or partition will also leave its indexes unusable.
Oracle disable partitioned index tips
Oracle disable partitioned index
Question: I tried to disable a partitioned index and got this message:
alter index myindex disable;
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
How do I disable a partitioned index?
Answer: Oracle does not allow you to disable an index unless it is a function-based index:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option.
Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
Action: Specify only legal options.
First, ask yourself WHY you wish to make your index disabled? If ANY DML is attempted against the indexed table (if the index is unique) during the period that the index is disabled, it will abort.
To disable a partitioned index, follow these steps:
1 – Get the name of the index partition:
select
index_name,
status
from
dba_ind_partitions
where
partition_name = ‘IDXPAR’;
2 – Disable the local index on the partition:
alter table
mytab_partitoned
modify partition
type13
unusable local indexes;
3 – allow for unusable indexes:
alter session set skip_unusable_indexes = true;
Also see: skip_unusable_indexes tips, Oracle disable index tips
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option in Oracle 12C
Details of error are as follows.
ALTER INDEX MSDBA.TEST_INDEX DISABLE
Error at line 1
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option.
Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
Action: Specify only legal option
ORA-02243
The ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option error is related with the function-based index, because Oracle doesn’t allow to disable index.
invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
To solve this problem you may drop the index as follows.
DROP INDEX MSDBA.TEST_INDEX;
If the index is local index on the partition, you can disable it as follows.
alter table MSDBA.TEST_INDEX modify partition TINDEXP1 unusable local indexes;
Or you can allow for unusable indexes as follows.
alter session set skip_unusable_indexes = true;
Or you can rebuild the index as follows to solve this ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option error.
ALTER INDEX MSDBA.TEST_INDEX REBUILD;
How to Resolve ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
ORA-02243
The root cause of ORA-02243 may vary in different operations, so the case in this post may not fit your situation.
Tried to move an index to another tablespace by using REBUILD clause of ALTER INDEX statement, but it failed with ORA-02243.
SQL> alter index emp_emp_id_pk tablespace example rebuild online;
alter index emp_emp_id_pk tablespace example rebuild online
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
ORA-02243 means that there’s a syntax error in your ALTER INDEX statement, sometimes, it’s an order problem among clauses.
In the above, we used both TABLESPACE and REBUILD clauses in the statement.
Solutions
This is a syntactical error, because declaring TABLESPACE clause first before other actions is invalid. We should use REBUILD clause first, then do the rest. For example:
SQL> alter index emp_emp_id_pk rebuild online tablespace example;
Index altered.
Or move ONLINE to the last.
SQL> alter index emp_emp_id_pk rebuild tablespace example online;
Index altered.
It’s also acceptable.
ORA-02243:invalid ALTER INDEX or ALTER MATERIALIZES VIEW option
ORA-02243:invalid ALTER INDEX or ALTER MATERIALIZES VIEW option
I checked the metalink, got nothing.Anyone can help? Thanks!
Hi, Friend,I am changing all the index to another tablespace of one of my schema.When I run the DDL script,got the error:
Oracle 12cR1 ORA-02243 invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
Database: 12c Release 1
Error code: ORA-02243
Description: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
Action: Specify only legal options.
Database: 10g Release 1
Error code: ORA-02243
Description: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
Action: Specify only legal options.
Database: 10g Release 2
Error code: ORA-02243
Description: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
Action: Specify only legal options.
Database: 11g Release 1
Error code: ORA-02243
Description: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
Action: Specify only legal options.
Database: 11g Release 2
Error code: ORA-02243
Description: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
Action: Specify only legal options.
ORA-02243: the alter index or alter materialized view option is invalid
In the morning, a friend in the forum encountered the following error when trying to invalidate the index:
ORA-02243: the alter index or alter materialized view option is invalid
The statement used is as follows:
Alter index index_name disable;
When I first saw it, I thought it was because of constraints, so I could not disable the index, because I remember that I had an impression in the previous primary key learning process. When deleting a unique index, if there is a corresponding primary key constraint, it cannot be deleted. The related learning stickers are as follows:
Http://blog.csdn.net/wh62592855/archive/2009/10/24/4724232.aspx
However, after reading the answers from other friends, I flipped through the document. It seems that this is not the reason. The following is an excerpt from the document:
Enable clause
ENABLE Applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true:
The function is currently valid
The signature of the current function matches the signature of the function when the index was created
The function is currently marked DETERMINISTIC
Restriction on enabling function-based indexes you cannot specify any other clses ALTER INDEX In the same statement ENABLE .
Disable clause
DISABLE Applies only to a function-based index. this clause lets you disable the use of a function-based index. you might want to do so, for example, while working on the body of the function. afterward you can either rebuild the index or specify another ALTER INDEX Statement with ENABLE Keyword.
Unusable clause
Specify UNUSABLE To mark the index or index partition (s) or index subpartition (s) UNUSABLE . An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE , The other partitions of the index are still valid. you can execute statements that require the index if the statements do not access the unusable partition. you can also split or rename the unusable partition before rebuilding it.
Restriction on marking indexes unusable you cannot specify this clause for an index on a temporary table.
As you can see, disable and enable are only valid for function indexes, but not for other indexes. You can choose to use unusable.
Let’s take a look at the experiment below.
SQL> Create Table T1 (ID number, name varchar2 (20);
table created.
SQL> Create index idx1 on T1 (ID);
index created.
SQL> Create index idx2 on T1 (upper (name);
index created.
SQL> alter index idx1 disable;
alter index idx1 disable
*
error at line 1:
ORA-02243: Invalid alter index or alter materialized view optionSQL> alter index idx2 disable;
index altered.
SQL> alter index idx1 unusable;
index altered.
SQL> select index_name, status, funcidx_status from user_indexes where index_name
in (‘idx1 ‘, ‘idx2 ‘);index_name status funcidx _
—————————— ——–
idx1 unusable
idx2 valid disabled
SQL » alter index problem
Index can not be disabled; drop it instead.
[EDIT]Did you, perhaps, mix terms “index” and “constraint”? If there’s, for example, a primary key constraint and index on this column, you could disable primary key constraint, but index should be dropped. [Updated on: Wed, 24 May 2006 06:32] Report message to a moderator
ORA-02243 invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
This is an Oracle Database Server Message. This can be generated by the Oracle Database when running any Oracle program.
An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
How to deepen your knowledge about Oracle?
There is no magic formula nor quick fix. You can train yourself with books and get an advantage over your competitors by developing a set of skills highly sought throughout the world. Niels Bohr saw an expert as “a person who has found out by his own painful experience all the mistakes that one can make in a very narrow field”. So why not you? Be an expert!
Here are some books about Oracle you should know for a start.
Oracle Database 11g The Complete Reference (Oracle Press) The Definitive Guide to Oracle Database 11g.
Get full details on the powerful features of Oracle Database 11g from this thoroughly updated Oracle Press guide. Oracle Database 11g: The Complete Reference explains how to use all the new features and tools, execute powerful SQL queries, construct PL/SQL and SQL*Plus statements, and work with large objects and object-relational databases. Learn how to implement the latest security measures, tune database performance, and deploy grid computing techniques. An invaluable cross-referenced appendix containing Oracle commands, keywords, features, and functions is also included. Author: Kevin Loney
Oracle Database 11g SQL (Oracle Press) Write powerful SQL statements and PL/SQL programs.
Learn to access Oracle databases through SQL statements and construct PL/SQL programs with guidance from Oracle expert, Jason Price. Published by Oracle Press, Oracle Database 11g SQL explains how to retrieve and modify database information, use SQL Plus and SQL Developer, work with database objects, write PL/SQL programs, and much more. Inside, you’ll find in-depth coverage of the very latest SQL features and tools, performance optimization techniques, advanced queries, Java support, and XML. This book contains everything you need to master SQL. Author: Jason Price
Oracle Database 11g Release 2 Performance Tuning Tips & Techniques (Oracle Press) Implement Proven Database Optimization Solutions.
Systematically identify and eliminate database performance problems with help from Oracle Certified Master Richard Niemiec. Filled with real-world case studies and best practices, Oracle Database 11g Release 2 Performance Tuning Tips & Techniques details the latest monitoring, troubleshooting, and optimization methods. Find out how to find and fix bottlenecks, configure storage devices, execute effective queries, and develop bug-free SQL and PL/SQL code. Testing, reporting, and security enhancements are also covered in this Oracle Press guide. Author: Richard J. Niemiec
OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047) (Oracle Press) A Fully Integrated Study System for OCA Exam 1Z0-047.
Prepare for the Oracle Certified Associate SQL Certified Expert exam with help from this exclusive Oracle Press guide. In each chapter, you’ll find challenging exercises, practice questions, and a two-minute drill to highlight what you’ve learned. This authoritative guide will help you pass the test and serve as your essential on-the-job reference. Get complete coverage of all objectives for exam 1Z0-047. Author: Steve O’Hearn
from:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option in Oracle 12C
Details of error are as follows.
ALTER INDEX MSDBA.TEST_INDEX DISABLE
Error at line 1
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option.
Cause: An option other than INITRANS, MAXTRANS,or STORAGE is specified in an ALTER INDEX statement or in the USING INDEX clause of an ALTER MATERIALIZED VIEW statement.
Action: Specify only legal option
ORA-02243
The ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option error is related with the function-based index, because Oracle doesn’t allow to disable index.
invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
To solve this problem you may drop the index as follows.
DROP INDEX MSDBA.TEST_INDEX;
If the index is local index on the partition, you can disable it as follows.
alter table MSDBA.TEST_INDEX modify partition TINDEXP1 unusable local indexes;
Or you can allow for unusable indexes as follows.
alter session set skip_unusable_indexes = true;
Or you can rebuild the index as follows to solve this ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option error.
ALTER INDEX MSDBA.TEST_INDEX REBUILD;
How to Rename, Drop, Enable, and Disable an Index in Oracle
Step 1: How to Rename an Index
To rename an index in Oracle SQL, you use the ALTER INDEX command:
ALTER INDEX index_name RENAME TO new_index_name;
You can replace the index_name with the current name of the index, and the new_index_name with the new name for your index.
So, if you want to rename an index from idx_cust_fname to idx_cust_fullname, your command would look like this:
ALTER INDEX idx_cust_fname RENAME TO idx_cust_fullname;
Your index will now be renamed, assuming you have the correct privileges.
ALTER INDEX
331/479
ALTER INDEX
Purpose
Use the ALTER INDEX statement to change or rebuild an existing index.
See Also: CREATE INDEX for information on creating an index
Prerequisites
The index must be in your own schema or you must have ALTER ANY INDEX system privilege.
To execute the MONITORING USAGE clause, the index must be in your own schema.
To modify a domain index, you must have EXECUTE object privilege on the indextype of the index.
Schema object privileges are granted on the parent index, not on individual index partitions or subpartitions.
You must have tablespace quota to modify, rebuild, or split an index partition or to modify or rebuild an index subpartition.
Syntax
alter_index::=
(deallocate_unused_clause ::=, allocate_extent_clause ::=, shrink_clause::=, parallel_clause ::=, physical_attributes_clause ::=, logging_clause::=, rebuild_clause ::=, alter_index_partitioning ::=)
deallocate_unused_clause ::=
(size_clause::=)
allocate_extent_clause ::=
(size_clause::=)
shrink_clause::=
parallel_clause ::=
physical_attributes_clause ::=
(storage_clause::=)
logging_clause::=
rebuild_clause ::=
(parallel_clause ::=, physical_attributes_clause ::=, key_compression::=, logging_clause::=)
key_compression::=
alter_index_partitioning ::=
(modify_index_default_attrs ::=, add_hash_index_partition ::=, modify_index_partition ::=, rename_index_partition::=, drop_index_partition ::=, split_index_partition::=, coalesce_index_partition ::=, modify_index_subpartition::=)
modify_index_default_attrs ::=
(physical_attributes_clause ::=, logging_clause::=)
add_hash_index_partition ::=
(parallel_clause ::=)
coalesce_index_partition ::=
(parallel_clause ::=)
modify_index_partition ::=
(deallocate_unused_clause ::=, allocate_extent_clause ::=, physical_attributes_clause ::=, logging_clause::=, key_compression::=)
rename_index_partition::=
drop_index_partition ::=
split_index_partition::=
(parallel_clause ::=)
index_partition_description::=
(segment_attributes_clause::=, key_compression::=)
segment_attributes_clause::=
(physical_attributes_clause ::=, logging_clause::=)
modify_index_subpartition::=
(allocate_extent_clause ::=, deallocate_unused_clause ::=)
Semantics
schema
Specify the schema containing the index. If you omit schema , Oracle Database assumes the index is in your own schema.
index
Specify the name of the index to be altered.
Restrictions on Modifying Indexes The modification of indexes is subject to the following restrictions:
If index is a domain index, you can specify only the PARAMETERS clause, the RENAME clause, the rebuild_clause (with or without the PARAMETERS clause), the parallel_clause , or the UNUSABLE clause. No other clauses are valid.
You cannot alter or rename a domain index that is marked LOADING or FAILED . If an index is marked FAILED , the only clause you can specify is REBUILD .
See Also: Oracle Data Cartridge Developer’s Guide LOADING and FAILED states of domain indexes for information on theandstates of domain indexes
deallocate_unused_clause
Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the index and make the freed space available for other segments in the tablespace.
If index is range-partitioned or hash-partitioned, then Oracle Database deallocates unused space from each index partition. If index is a local index on a composite-partitioned table, Oracle Database deallocates unused space from each index subpartition.
Restrictions on Deallocating Space Deallocation of space is subject to the following restrictions:
You cannot specify this clause for an index on a temporary table.
You cannot specify this clause and also specify the rebuild_clause .
Please refer to deallocate_unused_clause for a full description of this clause.
KEEP integer The KEEP clause lets you specify the number of bytes above the high water mark that the index will have after deallocation. If the number of remaining extents is less than MINEXTENTS , then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL , then INITIAL is set to the value of the current initial extent. If you omit KEEP , all unused space is freed.
Please refer to ALTER TABLE for a complete description of this clause.
allocate_extent_clause
The allocate_extent_clause lets you explicitly allocate a new extent for the index. For a local index on a hash-partitioned table, Oracle Database allocates a new extent for each partition of the index.
Restriction on Allocating Extents You cannot specify this clause for an index on a temporary table or for a range-partitioned or composite-partitioned index.
Please refer to allocate_extent_clause for a full description of this clause.
shrink_clause
Use this clause to compact the index segments. Specifying ALTER INDEX … SHRINK SPACE COMPACT is equivalent to specifying ALTER INDEX … COALESCE .
For complete information on this clause, please refer to shrink_clause in the documentation on CREATE TABLE .
Restriction on Shrinking Index Segments You cannot specify this clause for for a bitmap join index or for a function-based index.
parallel_clause
Use the PARALLEL clause to change the default degree of parallelism for queries and DML on the index.
Restriction on Parallelizing Indexes You cannot specify this clause for an index on a temporary table.
For complete information on this clause, please refer to parallel_clause in the documentation on CREATE TABLE .
physical_attributes_clause
Use the physical_attributes_clause to change the values of parameters for a nonpartitioned index, all partitions and subpartitions of a partitioned index, a specified partition, or all subpartitions of a specified partition.
Restrictions on Index Physical Attributes Index physical attributes are subject to the following restrictions:
You cannot specify this clause for an index on a temporary table.
You cannot specify the PCTUSED parameter at all when altering an index.
You can specify the PCTFREE parameter only as part of the rebuild_clause , the modify_index_default_attrs clause, or the split_partition_clause .
storage_clause
Use the storage_clause to change the storage parameters for a nonpartitioned index, index partition, or all partitions of a partitioned index, or default values of these parameters for a partitioned index. Please refer to storage_clause for complete information on this clause.
logging_clause
Use the logging_clause to change the logging attribute of the index. If you also specify the REBUILD clause, then this new setting affects the rebuild operation. If you specify a different value for logging in the REBUILD clause, then Oracle Database uses the last logging value specified as the logging attribute of the index and of the rebuild operation.
An index segment can have logging attributes different from those of the base table and different from those of other index segments for the same base table.
Restriction on Index Logging You cannot specify this clause for an index on a temporary table.
See Also: logging_clause for a full description of this clause
Oracle Database Concepts and the Oracle Data Warehousing Guide for more information about LOGGING and parallel DML
RECOVERABLE | UNRECOVERABLE
These keywords are deprecated and have been replaced with LOGGING and NOLOGGING , respectively. Although RECOVERABLE and UNRECOVERABLE are supported for backward compatibility, Oracle strongly recommends that you use the LOGGING and NOLOGGING keywords.
RECOVERABLE is not a valid keyword for creating partitioned tables or LOB storage characteristics. UNRECOVERABLE is not a valid keyword for creating partitioned or index-organized tables. Also, it can be specified only with the AS subquery clause of CREATE INDEX .
rebuild_clause
Use the rebuild_clause to re-create an existing index or one of its partitions or subpartitions. If index is marked UNUSABLE , a successful rebuild will mark it USABLE . For a function-based index, this clause also enables the index. If the function on which the index is based does not exist, the rebuild statement will fail.
Note: When you rebuild the secondary index of an index-organized table, Oracle Database preserves the primary key columns contained in the logical rowid when the index was created. Therefore, if the index was created with the COMPATIBLE initialization parameter set to less than 10.0.0, the rebuilt index will contain the index key and any of the primary key columns of the table that are not also in the index key. If the index was created with the COMPATIBLE initialization parameter set to 10.0.0 or greater, then the rebuilt index will contain the index key and all the primary key columns of the table, including those also in the index key. When you rebuild the secondary index of an index-organized table, Oracle Database preserves the primary key columns contained in the logical rowid when the index was created. Therefore, if the index was created with theinitialization parameter set to less than 10.0.0, the rebuilt index will contain the index key and any of the primary key columns of the table that are not also in the index key. If the index was created with theinitialization parameter set to 10.0.0 or greater, then the rebuilt index will contain the index key and all the primary key columns of the table, including those also in the index key.
Restrictions on Rebuilding Indexes The rebuilding of indexes is subject to the following restrictions:
You cannot rebuild an index on a temporary table.
You cannot rebuild a bitmap index that is marked INVALID . Instead, you must drop and then re-create it.
You cannot rebuild an entire partitioned index. You must rebuild each partition or subpartition, as described for the PARTITION clause.
You cannot specify the deallocate_unused_clause in the same statement as the rebuild_clause .
You cannot change the value of the PCTFREE parameter for the index as a whole ( ALTER INDEX ) or for a partition ( ALTER INDEX … MODIFY PARTITION ). You can specify PCTFREE in all other forms of the ALTER INDEX statement.
For a domain index: You can specify only the PARAMETERS clause (either for the index or for a partition of the index) or the parallel_clause . No other rebuild clauses are valid. You can rebuild an index only if the index is not marked IN_PROGRESS . You can rebuild an index partition only if the index is not marked IN_PROGRESS or FAILED and the partition is not marked IN_PROGRESS .
You cannot rebuild a local index, but you can rebuild a partition of a local index ( ALTER INDEX … REBUILD PARTITION ).
For a local index on a hash partition or subpartition, the only parameter you can specify is TABLESPACE .
PARTITION Clause
Use the PARTITION clause to rebuild one partition of an index. You can also use this clause to move an index partition to another tablespace or to change a create-time physical attribute.
The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database Administrator’s Guide for a discussion of these restrictions.
Restriction on Rebuilding Partitions You cannot specify this clause for a local index on a composite-partitioned table. Instead, use the REBUILD SUBPARTITION clause.
SUBPARTITION Clause
Use the SUBPARTITION clause to rebuild one subpartition of an index. You can also use this clause to move an index subpartition to another tablespace. If you do not specify TABLESPACE , the subpartition is rebuilt in the same tablespace.
The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database Administrator’s Guide for a discussion of these restrictions.
Restrictions on Modifying Index Subpartitions The modification of index subpartitions is subject to the following restrictions:
The only parameters you can specify for a subpartition are TABLESPACE and the parallel_clause .
You cannot rebuild the subpartition of a list partition.
REVERSE | NOREVERSE
Indicate whether the bytes of the index block are stored in reverse order:
REVERSE stores the bytes of the index block in reverse order and excludes the rowid when the index is rebuilt.
NOREVERSE stores the bytes of the index block without reversing the order when the index is rebuilt. Rebuilding a REVERSE index without the NOREVERSE keyword produces a rebuilt, reverse-keyed index.
Restrictions on Reverse Indexes Reverse indexes are subject to the following restrictions:
You cannot reverse a bitmap index or an index-organized table.
You cannot specify REVERSE or NOREVERSE for a partition or subpartition.
parallel_clause
Use the parallel_clause to parallelize the rebuilding of the index.
TABLESPACE Clause
Specify the tablespace where the rebuilt index, index partition, or index subpartition will be stored. The default is the default tablespace where the index or partition resided before you rebuilt it.
key_compression
Specify COMPRESS to enable key compression, which eliminates repeated occurrence of key column values. Use integer to specify the prefix length (number of prefix columns to compress).
For unique indexes, the range of valid prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.
For nonunique indexes, the range of valid prefix length values is from 1 to the number of key columns. The default prefix length is number of key columns.
Oracle Database compresses only nonpartitioned indexes that are nonunique or unique indexes of at least two columns.
Specify NOCOMPRESS to disable key compression. This is the default.
Restriction on Key Compression You cannot specify COMPRESS for a bitmap index.
ONLINE Clause
Specify ONLINE to allow DML operations on the table or partition during rebuilding of the index.
Restrictions on Online Indexes Online indexes are subject to the following restrictions:
Parallel DML is not supported during online index building. If you specify ONLINE and then issue parallel DML statements, Oracle Database returns an error.
You cannot specify ONLINE for a bitmap join index or a cluster index.
For a nonunique secondary index on an index-organized table, the number of index key columns plus the number of primary key columns that are included in the logical rowid in the index-organized table cannot exceed 32. The logical rowid excludes columns that are part of the index key.
COMPUTE STATISTICS Clause
This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors.
Restriction on COMPUTE STATISTICS Clause You cannot specify this clause for a domain index.
logging_clause
Specify whether the ALTER INDEX … REBUILD operation will be logged.
Please refer to the logging_clause for a full description of this clause.
PARAMETERS Clause
The PARAMETERS clause applies only to domain indexes. This clause specifies the parameter string that is passed uninterpreted to the appropriate ODCI indextype routine. The maximum length of the parameter string is 1000 characters.
If you are altering or rebuilding an entire index, then the string must refer to index-level parameters. If you are rebuilding a partition of the index, then the string must refer to partition-level parameters.
If index is marked UNUSABLE , then modifying the parameters alone does not make it USABLE . You must also rebuild the UNUSABLE index to make it usable.
If you have installed Oracle Text, then you can rebuild your Oracle Text domain indexes using parameters specific to that product. For more information on those parameters, please refer to Oracle Text Reference.
Restrictions on the PARAMETERS Clause The PARAMETERS clause is subject to the following restrictions:
You can specify this clause only for a domain index.
You can modify index partitions only if index is not marked IN_PROGRESS or FAILED , no index partitions are marked IN_PROGRESS , and the partition being modified is not marked FAILED .
See Also: Oracle Data Cartridge Developer’s Guide for more information on indextype routines
CREATE INDEX for more information on domain indexes
ENABLE Clause
ENABLE applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true:
The function is currently valid
The signature of the current function matches the signature of the function when the index was created
The function is currently marked as DETERMINISTIC
Restriction on Enabling Function-based Indexes You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE .
DISABLE Clause
DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.
UNUSABLE Clause
Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE . An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE , the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.
Restriction on Marking Indexes Unusable You cannot specify this clause for an index on a temporary table.
RENAME Clause
Use this clause to rename an index. The new_index_name is a single identifier and does not include the schema name.
Restriction on Renaming Indexes For a domain index, neither index nor any partitions of index can be marked IN_PROGRESS or FAILED .
COALESCE Clause
Specify COALESCE to instruct Oracle Database to merge the contents of index blocks where possible to free blocks for reuse.
Restrictions on Coalescing Index Blocks Coalescing of index blocks is subject to the following restrictions:
You cannot specify this clause for an index on a temporary table.
Do not specify this clause for the primary key index of an index-organized table. Instead use the COALESCE clause of ALTER TABLE .
See Also: Oracle Database Administrator’s Guide for more information on space management and coalescing indexes
COALESCE Clause for information on coalescing the space of an index-organized table
shrink_clause for an alternative method of compacting index segments
MONITORING USAGE | NOMONITORING USAGE
Use this clause to determine whether Oracle Database should monitor index use.
Specify MONITORING USAGE to begin monitoring the index. Oracle Database first clears existing information on index use, and then monitors the index for use until a subsequent ALTER INDEX … NOMONITORING USAGE statement is executed.
To terminate monitoring of the index, specify NOMONITORING USAGE .
To see whether the index has been used since this ALTER INDEX … NOMONITORING USAGE statement was issued, query the USED column of the V$OBJECT_USAGE dynamic performance view.
See Also: Oracle Database Reference for information on the data dictionary and dynamic performance views
UPDATE BLOCK REFERENCES Clause
The UPDATE BLOCK REFERENCES clause is valid only for normal and domain indexes on index-organized tables. Specify this clause to update all the stale guess data block addresses stored as part of the index row with the correct database address for the corresponding block identified by the primary key.
For a domain index, Oracle Database executes the ODCIIndexAlter routine with the alter_option parameter set to AlterIndexUpdBlockRefs . This routine enables the cartridge code to update the stale guess data block addresses in the index.
Restriction on UPDATE BLOCK REFERENCES You cannot combine this clause with any other clause of ALTER INDEX .
alter_index_partitioning
The partitioning clauses of the ALTER INDEX statement are valid only for partitioned indexes.
The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Please refer to Oracle Database Administrator’s Guide for a discussion of these restrictions.
Restrictions on Modifying Index Partitions Modifying index partitions is subject to the following restrictions:
You cannot specify any of these clauses for an index on a temporary table.
You can combine several operations on the base index into one ALTER INDEX statement (except RENAME and REBUILD ), but you cannot combine partition operations with other partition operations or with operations on the base index.
modify_index_default_attrs
Specify new values for the default attributes of a partitioned index.
Restriction on Modifying Partition Default Attributes The only attribute you can specify for a hash-partitioned global index or for an index on a hash-partitioned or composite-partitioned table is TABLESPACE .
TABLESPACE Specify the default tablespace for new partitions of an index or subpartitions of an index partition.
logging_clause Specify the default logging attribute of a partitioned index or an index partition.
Please refer to logging_clause for a full description of this clause.
FOR PARTITION Use the FOR PARTITION clause to specify the default attributes for the subpartitions of a partition of a local index on a composite-partitioned table.
Restriction on FOR PARTITION You cannot specify FOR PARTITION for a list partition.
add_hash_index_partition
Use this clause to add a partition to a global hash-partitioned index. Oracle Database adds hash partitions and populates them with index entries rehashed from an existing hash partition of the index, as determined by the hash function. If you omit the partition name, then Oracle Database assigns a name of the form SYS_P n . If you omit the TABLESPACE clause, then Oracle Database places the partition in the tablespace specified for the index. If no tablespace is specified for the index, then Oracle Database places the partition in the default tablespace of the user, if one has been specified, or in the system default tablespace.
modify_index_partition
Use the modify_index_partition clause to modify the real physical attributes, logging attribute, or storage characteristics of index partition partition or its subpartitions. For a hash-partitioned global index, the only subclause of this clause you can specify is UNUSABLE .
COALESCE Specify this clause to merge the contents of index partition blocks where possible to free blocks for reuse.
UPDATE BLOCK REFERENCES The UPDATE BLOCK REFERENCES clause is valid only for normal indexes on index-organized tables. Use this clause to update all stale guess data block addresses stored in the secondary index partition.
Restrictions on UPDATE BLOCK REFERENCES This clause is subject to the following restrictions:
You cannot specify the physical_attributes_clause for an index on a hash-partitioned table.
You cannot specify UPDATE BLOCK REFERENCES with any other clause in ALTER INDEX .
Note: If the index is a local index on a composite-partitioned table, the changes you specify here will override any attributes specified earlier for the subpartitions of index, as well as establish default values of attributes for future subpartitions of that partition. To change the default attributes of the partition without overriding the attributes of subpartitions, use ALTER TABLE … MODIFY DEFAULT ATTRIBUTES OF PARTITION . If the index is a local index on a composite-partitioned table, the changes you specify here will override any attributes specified earlier for the subpartitions of index, as well as establish default values of attributes for future subpartitions of that partition. To change the default attributes of the partition without overriding the attributes of subpartitions, use…
UNUSABLE Clause This clause has the same function for index partitions that it has for the index as a whole. Please refer to “UNUSABLE Clause”.
key_compression This clause is relevant for composite-partitioned indexes. Use this clause to change the compression attribute for the partition and every subpartition in that partition. Oracle Database marks each index subpartition in the partition UNUSABLE and you must then rebuild these subpartitions. Key compression must already have been specified for the table before you can specify key compression for a partition. You can specify this clause only at the partition level. You cannot change the compression attribute for an individual subpartition.
You can use this clause for noncomposite index partitions. However, it is more efficient to use the rebuild_clause for noncomposite partitions, which lets you rebuild and set the compression attribute in one step.
rename_index_partition
Use the rename_index_partition clauses to rename index partition or subpartition to new_name .
Restrictions on Renaming Index Partitions Renaming index partitions is subject to the following restrictions:
You cannot rename the subpartition of a list partition.
For a partition of a domain index, index cannot be marked IN_PROGRESS or FAILED , none of the partitions can be marked IN_PROGRESS , and the partition you are renaming cannot be marked FAILED .
drop_index_partition
Use the drop_index_partition clause to remove a partition and the data in it from a partitioned global index. When you drop a partition of a global index, Oracle Database marks the next index partition UNUSABLE . You cannot drop the highest partition of a global index.
split_index_partition
Use the split_index_partition clause to split a partition of a global range-partitioned index into two partitions, adding a new partition to the index. This clause is not valid for hash-partitioned global indexes. Instead, use the add_hash_index_partition clause.
Splitting a partition marked UNUSABLE results in two partitions, both marked UNUSABLE . You must rebuild the partitions before you can use them.
Splitting a usable partition results in two partitions populated with index data. Both new partitions are usable.
AT Clause Specify the new noninclusive upper bound for split_partition_1 . The value_list must evaluate to less than the presplit partition bound for partition_name_old and greater than the partition bound for the next lowest partition (if there is one).
INTO Clause Specify (optionally) the name and physical attributes of each of the two partitions resulting from the split.
coalesce_index_partition
This clause is valid only for hash-partitioned global indexes. Oracle Database reduces by one the number of index partitions. Oracle Database selects the partition to coalesce based on the requirements of the hash function. Use this clause if you want to distribute index entries of a selected partition into one of the remaining partitions and then remove the selected partition.
modify_index_subpartition
Use the modify_index_subpartition clause to mark UNUSABLE or allocate or deallocate storage for a subpartition of a local index on a composite-partitioned table. All other attributes of such a subpartition are inherited from partition-level default attributes.
Examples
Storing Index Blocks in Reverse Order: Example The following statement rebuilds index ord_customer_ix (created in “Creating an Index: Example”) so that the bytes of the index block are stored in reverse order:
ALTER INDEX ord_customer_ix REBUILD REVERSE;
Rebuilding an Index in Parallel: Example The following statement causes the index to be rebuilt from the existing index by using parallel execution processes to scan the old and to build the new index:
ALTER INDEX ord_customer_ix REBUILD PARALLEL;
Modifying Real Index Attributes: Example The following statement alters the oe.cust_lname_ix index so that future data blocks within this index use 5 initial transaction entries and an incremental extent of 100 kilobytes:
/* Unless you change the default tablespace of sample user oe, or specify different tablespace storage for the index, this example fails because the default tablespace originally assigned to oe is locally managed. */ ALTER INDEX oe.cust_lname_ix INITRANS 5 STORAGE (NEXT 100K);
If the oe.cust_lname_ix index were partitioned, this statement would also alter the default attributes of future partitions of the index. Partitions added in the future would then use 5 initial transaction entries and an incremental extent of 100K.
Enabling Parallel Queries: Example The following statement sets the parallel attributes for index upper_ix (created in “Creating a Function-Based Index: Example”) so that scans on the index will be parallelized:
ALTER INDEX upper_ix PARALLEL;
Renaming an Index: Example The following statement renames an index:
ALTER INDEX upper_ix RENAME TO upper_name_ix;
Marking an Index Unusable: Examples The following statements use the cost_ix index, which was created in “Creating a Range-Partitioned Global Index: Example”. Partition p1 of that index was dropped in “Dropping an Index Partition: Example”. The first statement marks index partition p2 as UNUSABLE :
ALTER INDEX cost_ix MODIFY PARTITION p2 UNUSABLE;
The next statement marks the entire index cost_ix as UNUSABLE :
ALTER INDEX cost_ix UNUSABLE;
Rebuilding Unusable Index Partitions: Example The following statements rebuild partitions p2 and p3 of the cost_ix index, making the index once more usable: The rebuilding of partition p3 will not be logged:
ALTER INDEX cost_ix REBUILD PARTITION p2; ALTER INDEX cost_ix REBUILD PARTITION p3 NOLOGGING;
Changing MAXEXTENTS: Example The following statement changes the maximum number of extents for partition p3 and changes the logging attribute:
/* This example will fail if the tablespace in which partition p3 resides is locally managed. */ ALTER INDEX cost_ix MODIFY PARTITION p3 STORAGE(MAXEXTENTS 30) LOGGING;
Renaming an Index Partition: Example The following statement renames an index partition of the cost_ix index (created in “Creating a Range-Partitioned Global Index: Example”):
ALTER INDEX cost_ix RENAME PARTITION p3 TO p3_Q3;
Splitting a Partition: Example The following statement splits partition p2 of index cost_ix (created in “Creating a Range-Partitioned Global Index: Example”) into p2a and p2b :
ALTER INDEX cost_ix SPLIT PARTITION p2 AT (1500) INTO ( PARTITION p2a TABLESPACE tbs_01 LOGGING, PARTITION p2b TABLESPACE tbs_02);
Dropping an Index Partition: Example The following statement drops index partition p1 from the cost_ix index:
ALTER INDEX cost_ix DROP PARTITION p1;
Modifying Default Attributes: Example The following statement alters the default attributes of local partitioned index prod_idx , which was created in “Creating an Index on a Hash-Partitioned Table: Example”. Partitions added in the future will use 5 initial transaction entries and an incremental extent of 100K:
ALTER INDEX prod_idx MODIFY DEFAULT ATTRIBUTES INITRANS 5 STORAGE (NEXT 100K);
키워드에 대한 정보 ora-02243 invalid alter index or alter materialized view option
다음은 Bing에서 ora-02243 invalid alter index or alter materialized view option 주제에 대한 검색 결과입니다. 필요한 경우 더 읽을 수 있습니다.
이 기사는 인터넷의 다양한 출처에서 편집되었습니다. 이 기사가 유용했기를 바랍니다. 이 기사가 유용하다고 생각되면 공유하십시오. 매우 감사합니다!
사람들이 주제에 대해 자주 검색하는 키워드 Indexed Views (Materialized Views) in SQL Server and Performance Considerations
- SQL Server
- SQL
- Database Views
- Views
- Microsoft
- Databases
- Indexes
- Clustered Indexes
- Udemy
- Free Course
- Materialized View
- Performance
- non clustered index
- sql server views
- indexed views
- materialized views
- indexed views in sql server
- indexed view
- indexed views in sql
- materialised views in oracle
- materialised views
- materialised views in sql server
- database views
- view database mysql
Indexed #Views #(Materialized #Views) #in #SQL #Server #and #Performance #Considerations
YouTube에서 ora-02243 invalid alter index or alter materialized view option 주제의 다른 동영상 보기
주제에 대한 기사를 시청해 주셔서 감사합니다 Indexed Views (Materialized Views) in SQL Server and Performance Considerations | ora-02243 invalid alter index or alter materialized view option, 이 기사가 유용하다고 생각되면 공유하십시오, 매우 감사합니다.
