Author: Dev

In Oracle PL/SQL, creating an index is a common practice to improve the performance of queries by allowing the database engine to quickly locate and retrieve rows based on the indexed columns. An index is a database object that provides a fast access path to the data in a table. Syntax To create an index…(Continue Reading)

Create Bitmap Index example CREATE TABLESPACE tbs_01 DATAFILE ‘tbs_01.dat’ SIZE 20M ONLINE; CREATE TABLESPACE tbs_02 DATAFILE ‘tbs_02.dat’ SIZE 20M ONLINE; CREATE TABLESPACE tbs_03 DATAFILE ‘tbs_03.dat’ SIZE 20M ONLINE; — create table Hash-Partitioned Table CREATE TABLE test_course ( course_id NUMBER(9) PRIMARY KEY, course_name VARCHAR2(250), price NUMBER) PARTITION BY HASH (course_id) PARTITIONS 3 STORE IN (tbs_01, tbs_02,…(Continue Reading)

Create Index on a Hash-Partitioned Table example CREATE TABLESPACE tbs_01 DATAFILE ‘tbs_01.dat’ SIZE 20M ONLINE; CREATE TABLESPACE tbs_02 DATAFILE ‘tbs_02.dat’ SIZE 20M ONLINE; — create Hash-Partitioned Table CREATE TABLE test_course ( course_id NUMBER(6) PRIMARY KEY, course_name VARCHAR2(250)) PARTITION BY HASH (course_id) PARTITIONS 2 STORE IN (tbs_01, tbs_02); — create Index on a Hash-Partitioned Table CREATE…(Continue Reading)

Create Hash-Partitioned Global Index example — create Hash-Partitioned Global Index CREATE INDEX stu_last_name_idx ON STUDENTS_LIST (LAST_NAME) GLOBAL PARTITION BY HASH (LAST_NAME) PARTITIONS 2; Output: index STU_LAST_NAME_IDX created. Check indexes select INDEX_NAME, INDEX_TYPE, PARTITIONED from USER_INDEXES WHERE table_name=’STUDENTS_LIST’; Output: INDEX_NAME INDEX_TYPE PARTITIONED STUDENT_IDX NORMAL NO STU_LAST_NAME_IDX NORMAL YES

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)