Example 1: Creating a simple table index
This statement names the index (EMP_EMPLOYEE_ID) and names the
column to serve as the index key (EMPLOYEE_ID).
The UNIQUE argument causes SQL to return an error message if a
user tries to store an identification number that is already
assigned.
SQL> CREATE UNIQUE INDEX EMP_EMPLOYEE_ID ON EMPLOYEES
cont> (EMPLOYEE_ID);
Example 2: Creating an index with descending index segments
This statement names the index (EMP_EMPLOYEE_ID) and names
the column to serve as the descending index key (EMPLOYEE_ID
DESCENDING).
The DESCENDING keyword causes the keys to be sorted in descending
order. If you do not specify DESCENDING or ASCENDING, SQL sorts
the keys in ascending order.
SQL> CREATE UNIQUE INDEX EMP_EMPLOYEE_ID ON EMPLOYEES
cont> (EMPLOYEE_ID DESCENDING);
Example 3: Creating a multisegmented index
SQL> CREATE INDEX EMP_FULL_NAME ON EMPLOYEES
cont> (LAST_NAME,
cont> FIRST_NAME,
cont> MIDDLE_INITIAL);
This statement names three columns to be used in the index EMP_
FULL_NAME. SQL concatenates these three columns to make the
multisegmented index.
Example 4: Creating a compressed numeric index
SQL> CREATE INDEX YEAR1_IND ON DEGREES
cont> (YEAR_GIVEN ASCENDING MAPPING VALUES 1950 TO 1970);
This statement creates ascending index segments for the YEAR_
GIVEN column in the DEGREES table, compressing the year values.
Example 5: Creating a truncated text index
SQL> CREATE INDEX COL_NAME_IND ON COLLEGES
cont> (COLLEGE_NAME SIZE IS 20);
This statement creates a compressed index, COL_NAME_IND, on the
COLLEGES table so that the number of octets from the COLLEGE_NAME
column that are used as a key cannot exceed 20 octets.
Example 6: Creating an index in a uniform storage area with
thresholds.
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ADD STORAGE AREA UNIFORM1 PAGE FORMAT IS UNIFORM;
SQL> ALTER DATABASE FILENAME mf_personnel
cont> ADD STORAGE AREA UNIFORM2 PAGE FORMAT IS UNIFORM;
SQL> ATTACH 'FILENAME mf_personnel';
SQL> CREATE UNIQUE INDEX EMP_THRESHOLDS ON EMPLOYEES (EMPLOYEE_ID)
cont> TYPE IS SORTED
cont> STORE USING (EMPLOYEE_ID)
cont> IN RDB$SYSTEM (THRESHOLDS ARE (60,75,90))
cont> WITH LIMIT OF ('00200')
cont> IN UNIFORM1 (THRESHOLD IS (65))
cont> WITH LIMIT OF ('00400')
cont> OTHERWISE IN UNIFORM2
cont> (THRESHOLD OF (90));
%RDB-W-META_WARN, metadata successfully updated with the reported warning
-RDMS-W-IDXCOLEXIST, an index with this column list already exists
SQL> --
SQL> SHOW INDEX EMP_THRESHOLDS
Indexes on table EMPLOYEES:
EMP_THRESHOLDS with column EMPLOYEE_ID
No Duplicates allowed
Type is Sorted
Key suffix compression is DISABLED
Node size 430
Store clause: STORE USING (EMPLOYEE_ID)
IN RDB$SYSTEM (THRESHOLDS ARE (60,75,90))
WITH LIMIT OF ('00200')
IN UNIFORM1 (THRESHOLD IS (65))
WITH LIMIT OF ('00400')
OTHERWISE IN UNIFORM2
(THRESHOLD OF (90))
This statement uses the STORE clause to partition the index into
different uniform page format storage areas and apply thresholds.
In Examples 7 and 8, the table COLOURS in the database MIA_CHAR_
SET is defined as:
SQL> CREATE TABLE COLOURS
cont> (ENGLISH MCS_DOM,
cont> FRENCH MCS_DOM,
cont> JAPANESE KANJI_DOM,
cont> ROMAJI DEC_KANJI_DOM,
cont> KATAKANA KATAKANA_DOM,
cont> HINDI HINDI_DOM,
cont> GREEK GREEK_DOM,
cont> ARABIC ARABIC_DOM,
cont> RUSSIAN RUSSIAN_DOM);
Example 7: Creating a simple table index using the octets
character length, which is the default
SQL> SET CHARACTER LENGTH 'OCTETS';
SQL> CREATE INDEX COLOUR_INDEX ON COLOURS (JAPANESE SIZE IS 4)
cont> TYPE IS SORTED;
SQL> SHOW INDEX COLOUR_INDEX;
Indexes on table COLOURS:
COLOUR_INDEX with column JAPANESE
size of index key is 4 octets
Duplicates are allowed
Type is Sorted
The previous statement creates a compressed index key of 4
octets.
Example 8: Creating an index using the CHARACTERS character
length
SQL> SET CHARACTER LENGTH 'CHARACTERS';
SQL> CREATE INDEX COLOUR_INDEX_2 ON COLOURS (JAPANESE SIZE IS 4)
cont> TYPE IS SORTED;
SQL> SHOW INDEX COLOUR_INDEX_2;
Indexes on table COLOURS:
COLOUR_INDEX_2 with column JAPANESE
size of index key is 4 characters
Duplicates are allowed
Type is Sorted
The previous statement creates a compressed index key of 4
characters.
Example 9: Creating an index that enables compression
The following example shows how to create an index and enable
compression with a minimum run length of 2:
SQL> CREATE INDEX EMP_NDX ON EMPLOYEES
cont> (EMPLOYEE_ID SIZE IS 4)
cont> ENABLE COMPRESSION (MINIMUM RUN LENGTH 2);
SQL> SHOW INDEX EMP_NDX;
Indexes on table EMPLOYEES:
EMP_NDX with column EMPLOYEE_ID
size of index key is 4
Duplicates are allowed
Type is Sorted
Compression is ENABLED (Minimum run length 2)
Example 10: Using the Index Attributes Clause
SQL> CREATE UNIQUE INDEX JOB_JOB_CODE
cont> ON JOBS (
cont> JOB_CODE
cont> ASC)
cont> TYPE IS SORTED
cont> THRESHOLDS ARE (75,83,90)
cont> ENABLE COMPRESSION
cont> NOLOGGING
cont> COMMENT IS 'Used for translation of job codes';
%RDB-W-META_WARN, metadata successfully updated with the reported warning
-RDMS-W-DATACMIT, unjournaled changes made; database may not be recoverable
SQL> -- SQL returned this message because the NOLOGGING attribute
SQL> -- was set.
Example 11: Creating an Index and Displaying the Default Node
Size
SQL> -- Create a simple table upon which to define
SQL> -- some indexes
SQL>
SQL> CREATE TABLE TEST_INDEX_TABLE
cont> (A CHAR(70),
cont> B INTEGER);
SQL>
SQL> -- Default value is 430 bytes
SQL>
SQL> CREATE UNIQUE INDEX TEST_INDEX_DEF
cont> ON TEST_INDEX_TABLE (A, B)
cont> TYPE IS SORTED
cont> USAGE UPDATE;
SQL>
SQL> SHOW TABLE (INDEX) TEST_INDEX_TABLE
Information for table TEST_INDEX_TABLE
TEST_INDEX_DEF with column A
and column B
No Duplicates allowed
Type is Sorted
Compression is DISABLED
Node size 430
Percent fill 70
Example 12: Naming Partitions
SQL> -- Alter mf_personnel database to add three slots
SQL> -- for storage areas and then add three storage areas.
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> RESERVE 3 STORAGE AREAS;
%RDMS-W-DOFULLBCK, full database backup should be done to ensure future recovery
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> ADD STORAGE AREA WAGE_LOW;
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> ADD STORAGE AREA WAGE_MID;
SQL> ALTER DATABASE FILENAME MF_PERSONNEL
cont> ADD STORAGE AREA WAGE_HIGH;
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> -- Create an index on the JOBS table and name the partitions
SQL> CREATE INDEX WAGE_CLASS_IDX ON JOBS (WAGE_CLASS)
cont> TYPE IS SORTED
cont> STORE USING (WAGE_CLASS)
cont> IN WAGE_LOW (PARTITION WAGE_LOW) WITH LIMIT OF ('1')
cont> IN WAGE_MID (PARTITION WAGE_MID) WITH LIMIT OF ('3')
cont> OTHERWISE IN WAGE_HIGH (PARTITION WAGE_HIGH);
Example 13: Creating a Large Index Partitioned Across Many
Storage Areas
First, create the database definition:
SQL> CREATE INDEX ... MAINTENANCE IS ENABLED DEFERRED ...;
Next submit batch jobs to build each partition in parallel. For
example, each batch job would execute a script similar to the
following:
ATTACH 'filename testdatabase';
SET FLAGS 'index_stats';
ALTER INDEX TRANSACTIONS_INDEX BUILD PARTITION PART_1;
COMMIT;
Finally, after the batch jobs have completed, the database
administrator must make the index active for query usage by
changing the maintenance mode to ENABLED IMMEDIATE. A BUILD
ALL PARTITIONS clause could be added in case any step failed
(possibly due to resource limitations or a failed node).
SQL> SET FLAGS 'index_stats';
SQL> SET TRANSLATION READ WRITE RESERVING...FOR EXCLUSIVE WRITES;
SQL> ALTER INDEX ... BUILD ALL PARTITIONS;
SQL> ALTER INDEX ... MAINTENANCE IS ENABLED IMMEDIATE;
SQL> COMMIT;
This scheme has several advantages over issuing a CREATE INDEX
statement directly:
o The build actions can be run in parallel, which allows
better resource usage (read and sort fewer rows), and reduced
execution time for the index creation.
o The partitions being processed are relatively small when
compared to the full index and, therefore, smaller quantities
of data will be processed. This will result in smaller .ruj
files and less AIJ file space for these transactions.
o Each build partition runs in a separate transaction, can
easily be repeated if a step fails, and does not require
repeating the entire CREATE INDEX statement.
o If any steps have failed, they will also be repeated by the
BUILD ALL PARTITIONS clause included in the script.