Category: PL/SQL

Rename Index example — rename Index ALTER INDEX STUDENT_IDX RENAME TO STUDENT_IDX_2; Output: index STUDENT_IDX altered. Check index select INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME from USER_INDEXES WHERE table_name=’STUDENTS_LIST’; Output: INDEX_NAME INDEX_TYPE TABLESPACE_NAME STUDENT_IDX_2 NORMAL SYSTEM

Creating an index online in Oracle PL/SQL involves adding an index to a table while allowing concurrent DML (Data Manipulation Language) operations to be performed on the table. This is particularly useful in scenarios where the table is large, and you want to avoid locking the entire table during the index creation process, allowing other…(Continue Reading)

Create Index with Collect Statistics example — create Index with Collect Statistics CREATE INDEX stu_ln_idx ON STUDENTS_LIST(LAST_NAME) COMPUTE STATISTICS; Output: index STU_LN_IDX created. Check index select INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME from USER_INDEXES WHERE table_name=’STUDENTS_LIST’; Output: INDEX_NAME INDEX_TYPE TABLESPACE_NAME STU_LN_IDX NORMAL SYSTEM

Create Index Associated with a Constraint example — create Index Associated with a Constraint CREATE TABLE t_table ( c1 NUMBER PRIMARY KEY USING INDEX (create index t_idx on t_table (c1)), c2 NUMBER); — create Index Associated with a Constraint CREATE TABLE t2_table(t1 NUMBER, t2 NUMBER); CREATE INDEX t2_idx ON t2_table (t1, t2); ALTER TABLE t2_table…(Continue Reading)

Create Unique Explicit Index example — create Unique Explicit Index CREATE UNIQUE INDEX phone_unique_idx ON PHONE_BRANDS(NAME) TABLESPACE SYSTEM STORAGE (INITIAL 10K NEXT 10k PCTINCREASE 75); Output: unique index PHONE_UNIQUE_IDX created. Check indexes select INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME from USER_INDEXES WHERE table_name=’PHONE_BRANDS’; Output: INDEX_NAME INDEX_TYPE TABLESPACE_NAME PK_BRAND_ID NORMAL SYSTEM PHONE_UNIQUE_IDX NORMAL SYSTEM