Author: Dev

Create Composite Index example — create Composite Index CREATE UNIQUE INDEX orders_unique_index ON ORDERS (ORDER_ID, COURSE_ID, STUDENT_ID) ; Output: unique index ORDERS_UNIQUE_INDEX created. Check indexes select INDEX_NAME, INDEX_TYPE, UNIQUENESS from USER_INDEXES WHERE table_name=’ORDERS’; Output: INDEX_NAME INDEX_TYPE UNIQUENESS PK_ORDER_ID NORMAL UNIQUE ORDERS_UNIQUE_INDEX NORMAL UNIQUE

Create Function-Based Index example — create Index Function-Based CREATE INDEX CITY_IDX ON STUDENTS (UPPER(CITY)); CREATE INDEX PRICE_IDX ON COURSE(PRICE – DISCOUNT); Output: index CITY_IDX created. index PRICE_IDX created. Check indexes select INDEX_NAME, INDEX_TYPE, FUNCIDX_STATUS from USER_INDEXES WHERE INDEX_NAME IN (SELECT OBJECT_NAME FROM (SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE=’INDEX’ ORDER BY CREATED DESC) WHERE ROWNUM <…(Continue Reading)

Create Cluster Index example — create Cluster CREATE CLUSTER course_cluster (course NUMBER(9)) SIZE 512 STORAGE (initial 100K next 50K); — create Index on Cluster CREATE INDEX idx_course_cluster ON CLUSTER course_cluster; Output: cluster COURSE_CLUSTER created. index IDX_COURSE_CLUSTER created. Check cluster SELECT CLUSTER_NAME, TABLESPACE_NAME, CLUSTER_TYPE FROM USER_CLUSTERS; Output: CLUSTER_NAME TABLESPACE_NAME CLUSTER_TYPE COURSE_CLUSTER SYSTEM INDEX Check index SELECT…(Continue Reading)

Create Index in NOLOGGING Mode example CREATE TABLE ORDERS (ORDER_ID NUMBER NOT NULL ENABLE, COURSE_ID NUMBER NOT NULL ENABLE, STUDENT_ID NUMBER NOT NULL ENABLE, ORDER_DATE DATE, CONSTRAINT PK_ORDER_ID PRIMARY KEY (ORDER_ID) ); — create Index in NOLOGGING mode CREATE INDEX orders_idx_nologging ON orders (student_id) NOLOGGING; Output: table ORDERS created. index ORDERS_IDX_NOLOGGING created. Check indexes select…(Continue Reading)

Compressing an Index example CREATE TABLE ORDERS (ORDER_ID NUMBER NOT NULL ENABLE, COURSE_ID NUMBER NOT NULL ENABLE, STUDENT_ID NUMBER NOT NULL ENABLE, ORDER_DATE DATE, CONSTRAINT PK_ORDER_ID PRIMARY KEY (ORDER_ID) ); — create Index Key Compression CREATE INDEX student_orders_idx ON ORDERS (student_id, order_id) COMPRESS 1; Output: table ORDERS created. index STUDENT_ORDERS_IDX created. Check indexes select INDEX_NAME,…(Continue Reading)