Author: Dev

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

Create Explicit Index example — create Explicit Index CREATE INDEX last_name_idx ON STUDENTS_LIST(LAST_NAME) TABLESPACE SYSTEM STORAGE (INITIAL 10K NEXT 10k PCTINCREASE 75); Output: index LAST_NAME_IDX created. Check indexes select INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME from USER_INDEXES WHERE table_name=’STUDENTS_LIST’; Output: INDEX_NAME INDEX_TYPE TABLESPACE_NAME STUDENT_IDX NORMAL SYSTEM LAST_NAME_IDX NORMAL SYSTEM