Author: Dev

Create Range-Partitioned Global Index example — create Range-Partitioned Global Index CREATE INDEX amount_idx ON orders (amount) GLOBAL PARTITION BY RANGE (amount) (PARTITION p1 VALUES LESS THAN (500), PARTITION p2 VALUES LESS THAN (1500), PARTITION p3 VALUES LESS THAN (MAXVALUE)); Output: index AMOUNT_IDX created. Check indexes select INDEX_NAME, INDEX_TYPE, PARTITIONED from USER_INDEXES WHERE table_name=’ORDERS’; Output: INDEX_NAME…(Continue Reading)

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)