How To Create Oracle Domain Index: Definition And 5 Examples

What is domain index in Oracle? oracle domain index is a specialized type of index in Oracle that is used to retrieve text, spatial, or user-defined data speedily.  this specialized type of index provides a way to retrieve structure data from a table. Compared to Other traditional b-tree or bitmap indexes, they are used to retrieve data from databases with different purposes. Domain index design for retrieving data set that is huge like JSON text, XML file, or very complex set of data.

In this detailed article about Oracle domain index, we will explain what is domain indexes, How to use domain index, various types, how to create domain index types, the architecture of domain index, and provide real-world examples of Oracle databases.

What is domain index in Oracle?

Oracle domain index is designed for complex data structures that need to improve performance. These are different from B-tree and Bitmap indexes. Modern databases handle complex data types therefore improving the performance is critical for those kinds of data types.

For example, Geographical data, unstructured data, text files, or JSON files contain a lot of data, and handling that kind of data using B-tree and Bitmap index is not suit for this. Therefore oracle has a Domain index with different architecture for these kinds of big data.

Oracle provides a lot of ways to create an index. If you would like read on below article for types of created index by Oracle.  Indexes improve the performance of queries and creating an index in a database is not enough to improve the performance, but you have to be smart in selecting the index type on a database.

Create Oracle Domain Index

Spatial Indexes

Spatial domain indexes are used to improve the performance of geographical data. Think about such like location information, position restated data. Those kinds of tables contain a lot of data related to geography. Our regular indexes can’t handle those kinds of data as usual.  

Domain Index Creation for Spatial Data

-- 
-- Use index type as INDEXTYPE IS MDSYS.SPATIAL_INDEX:
CREATE INDEX spatial_idx ON spatial_table (column_name) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
--

Text Indexes

Text domain indexes towards big text data and allows improved performance on big files like JSON, or non-structured text and big descriptions.

Domain Index Creation for Text Data

--  
-- Use end syntax for INDEXTYPE IS CTXSYS.CONTEXT:
CREATE INDEX text_idx ON my_table (column_name) INDEXTYPE IS CTXSYS.CONTEXT;
--

XML Indexes

XML domain indexes are specialized for XML files. Normally XML files contain a lot of data in a single file. Searching data in a lot of XML files is not easy therefore Domain XML indexes are helpful in those kinds of situations.

Domain Index Creation for XML Data

-- 
--To create a domain index for XML data, you would use:
CREATE INDEX xml_idx ON xml_table (xml_column) INDEXTYPE IS XDB.XMLIndex;
--

Domain index in Oracle with Example 

1. Spatial Domain Index for Geographic Data:

Storing geospatial data and retrieving data using INDEXTYPE IS MDSYS.SPATIAL_INDEX.

• Index Creation:

--
CREATE INDEX spatial_idx ON geographical_table(location_column) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
--

• Search Query:

--
SELECT * FROM geographical_table 
WHERE SDO_FILTER(location_column, 
SDO_GEOMETRY(POINT(37.7749, -122.4194, NULL), 8307), 'querytype=WINDOW') = 'TRUE';
--

2. Text Domain Index for Full-Text Search:

Enabling efficient full-text search and filter table row by using INDEXTYPE IS CTXSYS.CONTEXT.

• Index Creation:

--
CREATE INDEX text_idx ON document_info (text_column) INDEXTYPE IS CTXSYS.CONTEXT;
--

• Search Query:

--
SELECT * FROM document_info WHERE CONTAINS(text_column, 'Oracle AND database', 1) > 0;
--

3. XML Domain Index for XML Documents:

XML documents efficiently improve by adding INDEXTYPE IS XDB.XMLIndex into index.

• Index Creation:

--
CREATE INDEX xml_idx ON xml_data_file(xml_content_column) INDEXTYPE IS XDB.XMLIndex; 
--

• Search Query:

--
SELECT * FROM xml_data_file WHERE XMLExists('/bookstore/book[price>30]' PASSING xml_content_column); 
--

4. Multi-Column Text Domain Index for Combined Text Searches:

Combining two columns and filter rows by using bot column values.

• Index Creation:

--
CREATE INDEX multi_text_idx ON text_table(text_column, text_column_description) INDEXTYPE IS CTXSYS.CONTEXT;
--

• Search Query:

--
SELECT * FROM text_table 
WHERE CONTAINS(text_column, 'Oracle', 1) > 0 
AND CONTAINS(text_column_description, 'performance', 2) > 0;
--

5. Context Domain Index for Mixed Data Types:

We are indexing a table with a mix of data types, including spatial, text, and XML data.

• Index Creation:

--
CREATE INDEX mixed_data_idx ON mixed_data(data_column) INDEXTYPE IS CTXSYS.CONTEXT; 
--

• Search Query:

--sq
SELECT * FROM mixed_data 
WHERE CONTAINS(data_column, 'Oracle AND (database OR spatial)') > 0; 
--

Drop DOMAIN Index Oracle

-- Drop a Bitmap Index 
DROP INDEX customer_gendor_idx;
--

Conclusion

In conclusion, domain indexes in Oracle enable you to improve the performance of complex data types. If you have big text, XML files, BLOB-type columns, or any other huge content on your file this is one of the options for improving the performance of your database.

 Here we discussed spatial, text, and XML data. In this article, we discussed how to create a domain index, real examples in Oracle, and where to use them. Share your ideas and any topic would you like to read use this to improve query performance and enhance your database management.