Dev.to

23ai - DBMS_SEARCH - pure SQL Sorcery | A promising alternative to global indexes.

{ Abhilash Kumar Bhattaram : Follow on LinkedIn } 23ai DBMS_SEARCH In simple language , the Oxford Dictionary for ubiquitous means omnipresent i.e.: "present or seeming to be present everywhere or in all places at once" What that mean for DBMS_SEARCH • It’s a cool Oracle package that helps you search like a boss across your entire database schema. • Instead of juggling 10 indexes and writing custom stuff, you just build one mega-index and you’re good to go! As good as it may sound it needs to be tested for all applications , the test case below shows I have avoided using a global index. The test case is from Oracle Sample Schemas Understanding the scenario of a typical partitioned table Understanding the use case Enter DBMS_SEARCH What is the new thing DBMS_SEARCH has added to my partitioned table Comapring results of my traditional query and my DBMS_SEARCH indexed query Comapring SQL Plan my traditional query and my DBMS_SEARCH indexed query

Apr 29, 2025 - 09:13
 0
23ai - DBMS_SEARCH - pure SQL Sorcery | A promising alternative to global indexes.

{ Abhilash Kumar Bhattaram : Follow on LinkedIn }

23ai DBMS_SEARCH

In simple language , the Oxford Dictionary for ubiquitous means omnipresent i.e.: "present or seeming to be present everywhere or in all places at once"

What that mean for DBMS_SEARCH

• It’s a cool Oracle package that helps you search like a boss across your entire database schema.
• Instead of juggling 10 indexes and writing custom stuff, you just build one mega-index and you’re good to go!

As good as it may sound it needs to be tested for all applications , the test case below shows I have avoided using a global index.

The test case is from Oracle Sample Schemas

Understanding the scenario of a typical partitioned table

Understanding the use case

Enter DBMS_SEARCH

What is the new thing DBMS_SEARCH has added to my partitioned table

Comapring results of my traditional query and my DBMS_SEARCH indexed query

Comapring SQL Plan my traditional query and my DBMS_SEARCH indexed query

Read More

Tags:

Previous Article

Streamline File Synchronization with FTP-Sync: A Guide for VS Code Users

Next Article

Remote vs. Hybrid vs. In-Office: How to Decide?

Related Posts

Fixing React Router Pagination Issue: How to Update URL Params with Buttons Instead of Links

Fixing React Router Pagination Issue: How to Update URL...

Apr 27, 2025  0

What Is Docker in Simple Terms in 2025?

What Is Docker in Simple Terms in 2025?

Apr 28, 2025  0

The sense of achievement while using AI

The sense of achievement while using AI

Apr 21, 2025  0