英文:
Rebuilding an index, without knowing the index name (SQL)
问题
"如何在不知道索引名称的情况下，重新构建特定约束的索引的正确方法，因为在不同环境之间索引名称可能会不同。
在这种情况下，偏好不知道索引名称（现有条件），也不重命名它。
尝试进行强制转换、字符串处理等...并收到以下错误消息
ORA-00953: 缺少或无效的索引名称
ALTER INDEX (select index_name from user_constraints where constraint_name = 'constraint123') REBUILD"
英文:
What is the correct way to rebuild an index of a specific constraint, without knowing the index name, since it might different between environments.
In this case, prefer not knowing the index name (existing condition), and not renaming it first.
ALTER INDEX (select index_name from user_constraints where constraint_name = upper('constraint123')) REBUILD;
I tried casting, string, as is... and getting the following error
ORA-00953: missing or invalid index name
ALTER INDEX (select index_name from user_constraints where constraint_name = 'constraint123') REBUILD
答案1
得分: 0
在任何时候，如果您不知道对象名称（表名、列名等），您必须使用动态SQL，并且这需要使用PL/SQL。以下是如何执行它：
CREATE OR REPLACE PROCEDURE p_rebuild_constraint_index (in_constraint_name IN varchar2)
AS
var_index_name varchar2(128);
BEGIN
SELECT MAX(index_name)
INTO var_index_name
FROM user_constraints
WHERE constraint_name = in_constraint_name;
IF var_index_name IS NOT NULL
THEN
EXECUTE IMMEDIATE 'ALTER INDEX "'||var_index_name||'" REBUILD';
END IF;
END;
然后简单地调用它：
BEGIN p_rebuild_constraint_index('CONSTRAINT123'); END;
英文:
Any time you don't know an object name (table name, column name, etc..) you have to use dynamic SQL, and that requires PL/SQL. Here's how you would do it:
CREATE OR REPLACE PROCEDURE p_rebuild_constraint_index (in_constraint_name IN varchar2)
AS
var_index_name varchar2(128);
BEGIN
SELECT MAX(index_name)
INTO var_index_name
FROM user_constraints
WHERE constraint_name = in_constraint_name;
IF var_index_name IS NOT NULL
THEN
EXECUTE IMMEDIATE 'ALTER INDEX "'||var_index_name||'" REBUILD';
END IF;
END;
Then simply call it:
BEGIN p_rebuild_constraint_index('CONSTRAINT123'); END;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论