{ Abhilash Kumar Bhattaram : Follow on LinkedIn }

23ai DBMS_SEARCH

Before jumping into this new feature in 23ai ,
let us understand what Oracle Documentation says about DBMS_SEARCH here

"The DBMS_SEARCH PL/SQL package provides procedures and functions to create, manage, and query a ubiquitous search index."

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's 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.

Understanding the scenario of a typical partitioned table

The test case is from Oracle Sample Schemas for SH.COSTS table

The partition table that will be used in the blog is of 128 MB is size including all partitions

FREE> @seg SH.COSTS

              SEG_MB|OWNER               |SEGMENT_NAME                  |SEG_PART_NAME                 |SEGMENT_TYPE        |SEG_TABLESPACE_NAME           |              BLOCKS|              HDRFIL|              HDRBLK
--------------------|--------------------|------------------------------|------------------------------|--------------------|------------------------------|--------------------|--------------------|--------------------
                   8|SH                  |COSTS                         |COSTS_Q4_2001                 |TABLE PARTITION     |USERS                         |                1024|                  15|               20369
                   8|SH                  |COSTS                         |COSTS_Q3_2001                 |TABLE PARTITION     |USERS                         |                1024|                  15|               44177
                   8|SH                  |COSTS                         |COSTS_Q2_2001                 |TABLE PARTITION     |USERS                         |                1024|                  15|               21393
                   8|SH                  |COSTS                         |COSTS_Q1_2001                 |TABLE PARTITION     |USERS                         |                1024|                  15|               22417
                   8|SH                  |COSTS                         |COSTS_Q4_2000                 |TABLE PARTITION     |USERS                         |                1024|                  15|               23441
                   8|SH                  |COSTS                         |COSTS_Q3_2000                 |TABLE PARTITION     |USERS                         |                1024|                  15|               24465
                   8|SH                  |COSTS                         |COSTS_Q2_2000                 |TABLE PARTITION     |USERS                         |                1024|                  15|               33937
                   8|SH                  |COSTS                         |COSTS_Q1_2000                 |TABLE PARTITION     |USERS                         |                1024|                  15|               34961
                   8|SH                  |COSTS                         |COSTS_Q4_1999                 |TABLE PARTITION     |USERS                         |                1024|                  15|               35985
                   8|SH                  |COSTS                         |COSTS_Q3_1999                 |TABLE PARTITION     |USERS                         |                1024|                  15|               41105
                   8|SH                  |COSTS                         |COSTS_Q2_1999                 |TABLE PARTITION     |USERS                         |                1024|                  15|               42129
                   8|SH                  |COSTS                         |COSTS_Q1_1999                 |TABLE PARTITION     |USERS                         |                1024|                  15|               43153
                   8|SH                  |COSTS                         |COSTS_Q4_1998                 |TABLE PARTITION     |USERS                         |                1024|                  15|               37009
                   8|SH                  |COSTS                         |COSTS_Q3_1998                 |TABLE PARTITION     |USERS                         |                1024|                  15|               38033
                   8|SH                  |COSTS                         |COSTS_Q2_1998                 |TABLE PARTITION     |USERS                         |                1024|                  15|               39057
                   8|SH                  |COSTS                         |COSTS_Q1_1998                 |TABLE PARTITION     |USERS                         |                1024|                  15|               40081
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q4_2001                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46786
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q3_2001                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46778
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q2_2001                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46770
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q1_2001                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46762
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q4_2000                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46754
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q3_2000                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46746
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q2_2000                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46738
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q1_2000                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46730
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q4_1999                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46722
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q3_1999                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46714
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q2_1999                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46706
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q1_1999                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46698
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q4_1998                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46690
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q3_1998                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46682
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q2_1998                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46674
                   0|SH                  |COSTS_TIME_BIX                |COSTS_Q1_1998                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46666
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q4_2001                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46946
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q3_2001                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46938
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q2_2001                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46930
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q1_2001                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46922
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q4_2000                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46914
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q3_2000                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46906
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q2_2000                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46898
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q1_2000                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46890
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q4_1999                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46882
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q3_1999                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46874
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q2_1999                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46866
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q1_1999                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46858
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q4_1998                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46850
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q3_1998                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46842
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q2_1998                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46834
                   0|SH                  |COSTS_PROD_BIX                |COSTS_Q1_1998                 |INDEX PARTITION     |USERS                         |                   8|                  15|               46826
--------------------|                    |                              |                              |                    |                              |                    |                    |
                 128|                    |                              |                              |                    |                              |                    |                    |

48 rows selected.

Elapsed: 00:00:00.08
FREE>

The partition table has BITMAP indexes on PROD_ID and TIME_ID.

FREE> @tab_ind SH COSTS

Elapsed: 00:00:00.00
### Table Columns

OWNER                         |TABLE_NAME                         |COLUMN_NAME                             |DATA_TYPE |         DATA_LENGTH|LAST_ANALYZED    |HISTOGRAM
------------------------------|-----------------------------------|----------------------------------------|----------|--------------------|-----------------|---------------
SH                            |COSTS                              |PROD_ID                                 |NUMBER    |                  22|14-APR-2025 11:27|NONE
SH                            |COSTS                              |TIME_ID                                 |DATE      |                   7|14-APR-2025 11:27|NONE
SH                            |COSTS                              |PROMO_ID                                |NUMBER    |                  22|14-APR-2025 11:27|NONE
SH                            |COSTS                              |CHANNEL_ID                              |NUMBER    |                  22|14-APR-2025 11:27|NONE
SH                            |COSTS                              |UNIT_COST                               |NUMBER    |                  22|14-APR-2025 11:27|NONE
SH                            |COSTS                              |UNIT_PRICE                              |NUMBER    |                  22|14-APR-2025 11:27|NONE

6 rows selected.

Elapsed: 00:00:00.06
### Index Tablespaces

TABLE_OWNER         |TABLE_NAME                         |INDEX_OWNER         |INDEX_NAME          |TABLESPACE_NAME     |            NUM_ROWS|STATUS         |INDEX_TYPE
--------------------|-----------------------------------|--------------------|--------------------|--------------------|--------------------|---------------|----------------------------------------
SH                  |COSTS                              |SH                  |COSTS_PROD_BIX      |                    |                1068|N/A            |BITMAP
SH                  |COSTS                              |SH                  |COSTS_TIME_BIX      |                    |                1460|N/A            |BITMAP

Elapsed: 00:00:00.01
### Indexed Columns

INDEX_OWNER         |TABLE_NAME                         |INDEX_NAME          |COLUMN_NAME
--------------------|-----------------------------------|--------------------|----------------------------------------
SH                  |COSTS                              |COSTS_PROD_BIX      |PROD_ID
SH                  |COSTS                              |COSTS_TIME_BIX      |TIME_ID

Understanding the use case

We are going to do a test case on PROMO_ID which does not have any indexes , in a conventional case we would need a global index for PRMO_ID as these records would be across partitions.

In this case PROMO_ID has only 3 distinct values across several partitions making it a classic example for the need of a global index.

FREE> select count(*) from SH.COSTS;

            COUNT(*)
--------------------
               82112

1 row selected.

Elapsed: 00:00:00.00
FREE> select distinct(PROMO_ID) from SH.COSTS;

            PROMO_ID
--------------------
                 999
                 350
                 351

3 rows selected.

Elapsed: 00:00:00.01
FREE>

Enter DBMS_SEARCH

Now we will try to index PROMO_ID in the new way , enter DBMS_SEARCH - a new way to index.

Our intention is to fetch the records whose PROMO_ID is 999 across partitions using the new index approach using DBMS_SEARCH.

Before we get started lets get a measure of which data we are trying to run the tests on , which is as below - 78425 records, a suficient record count to get some resonable test results.

FREE> select count(*) from SH.COSTS where PROMO_ID=999;

            COUNT(*)
--------------------
               78425

1 row selected.

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

Now as per documentation if is use the DBMS_SEARCH , I cannot create a NUMBER index as it is not supported by Oracle Text , so I essentially made Oracle to decide it's own default to create the Index using DBMS_SEARCH.

Note that I am using the phrase 'SH.PROMO_ID' which means I am interested in the PROMO_ID column ( you can use your own naming convention .

FREE> BEGIN DBMS_SEARCH.CREATE_INDEX('SH.PROMO_ID','USERS','NUMBER'); END;
  2  /
BEGIN DBMS_SEARCH.CREATE_INDEX('SH.PROMO_ID','USERS','NUMBER'); END;
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-11391: Unsupported value for the datatype parameter.
ORA-06512: at "CTXSYS.DRUE", line 192
ORA-06512: at "CTXSYS.DBMS_SEARCH", line 594
ORA-06512: at line 1


Elapsed: 00:00:00.08
FREE> BEGIN DBMS_SEARCH.CREATE_INDEX('SH.PROMO_ID','USERS'); END;
  2  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.45
FREE>

What Oracle does is "it creates a SH.PROMO_ID table with JSON columns in " this is where the sorcery magic happens ,

Note that

FREE> select owner,object_name,object_type from dba_objects where object_name='PROMO_ID';

OWNER                         |OBJECT_NAME                        |OBJECT_TYPE
------------------------------|-----------------------------------|-----------------------
SH                            |PROMO_ID                           |TABLE PARTITION
SH                            |PROMO_ID                           |TABLE
SH                            |PROMO_ID                           |INDEX PARTITION
SH                            |PROMO_ID                           |INDEX


FREE> @tab_ind SH PROMO_ID

Elapsed: 00:00:00.00
### Table Columns

OWNER                         |TABLE_NAME                         |COLUMN_NAME                             |DATA_TYPE |         DATA_LENGTH|LAST_ANALYZED    |HISTOGRAM
------------------------------|-----------------------------------|----------------------------------------|----------|--------------------|-----------------|---------------
SH                            |PROMO_ID                           |METADATA                                |JSON      |                8200|                 |NONE
SH                            |PROMO_ID                           |DATA                                    |JSON      |                8200|                 |NONE
SH                            |PROMO_ID                           |OWNER                                   |VARCHAR2  |                 128|                 |NONE
SH                            |PROMO_ID                           |SOURCE                                  |VARCHAR2  |                 128|                 |NONE
SH                            |PROMO_ID                           |KEY                                     |VARCHAR2  |                1024|                 |NONE

Elapsed: 00:00:00.05
### Index Tablespaces

TABLE_OWNER         |TABLE_NAME                         |INDEX_OWNER         |INDEX_NAME          |TABLESPACE_NAME     |            NUM_ROWS|STATUS         |INDEX_TYPE
--------------------|-----------------------------------|--------------------|--------------------|--------------------|--------------------|---------------|----------------------------------------
SH                  |PROMO_ID                           |SH                  |DR$PROMO_ID$BK      |                    |                   0|N/A            |FUNCTION-BASED NORMAL
SH                  |PROMO_ID                           |SH                  |PROMO_ID            |                    |                    |VALID          |DOMAIN
SH                  |PROMO_ID                           |SH                  |SYS_IL0000074190C000|                    |                    |N/A            |LOB
                    |                                   |                    |01$$                |                    |                    |               |

SH                  |PROMO_ID                           |SH                  |SYS_IL0000074190C000|                    |                    |N/A            |LOB
                    |                                   |                    |02$$                |                    |                    |               |


Elapsed: 00:00:00.01
### Indexed Columns

INDEX_OWNER         |TABLE_NAME                         |INDEX_NAME          |COLUMN_NAME
--------------------|-----------------------------------|--------------------|----------------------------------------
SH                  |PROMO_ID                           |PROMO_ID            |DATA
SH                  |PROMO_ID                           |DR$PROMO_ID$BK      |KEY

Elapsed: 00:00:00.01
### Index Created Timestamp

OWNER                         |OBJECT_NAME                        |SUBOBJECT_NAME                |OBJECT_TYPE            |LAST_DDL_TIME    |CREATED
------------------------------|-----------------------------------|------------------------------|-----------------------|-----------------|-----------------
SH                            |SYS_IL0000074190C00002$$           |SYS_IL_P452                   |INDEX PARTITION        |29-APR-2025 11:53|29-APR-2025 11:53
SH                            |PROMO_ID                           |SYS_P448                      |INDEX PARTITION        |29-APR-2025 11:53|29-APR-2025 11:53
SH                            |PROMO_ID                           |SYS_P448                      |TABLE PARTITION        |29-APR-2025 11:53|29-APR-2025 11:53
SH                            |PROMO_ID                           |                              |TABLE                  |29-APR-2025 11:53|29-APR-2025 11:53
SH                            |PROMO_ID                           |                              |INDEX                  |29-APR-2025 11:53|29-APR-2025 11:53
SH                            |SYS_IL0000074190C00002$$           |                              |INDEX                  |29-APR-2025 11:53|29-APR-2025 11:53
SH                            |SYS_IL0000074190C00001$$           |SYS_IL_P450                   |INDEX PARTITION        |29-APR-2025 11:53|29-APR-2025 11:53
SH                            |SYS_IL0000074190C00001$$           |                              |INDEX                  |29-APR-2025 11:53|29-APR-2025 11:53
SH                            |DR$PROMO_ID$BK                     |                              |INDEX                  |29-APR-2025 11:53|29-APR-2025 11:53
SH                            |DR$PROMO_ID$BK                     |SYS_P448                      |INDEX PARTITION        |29-APR-2025 11:53|29-APR-2025 11:53

10 rows selected.

Elapsed: 00:00:00.19
### Table Segments Sizes

                  MB|TABLESPACE_NAME          |OWNER                    |SEGMENT_NAME                            |SEGMENT_TYPE        |          PARTITIONS
--------------------|-------------------------|-------------------------|----------------------------------------|--------------------|--------------------
--------------------|                         |                         |                                        |                    |
                    |                         |                         |                                        |                    |

no rows selected

Elapsed: 00:00:00.43
### Index Segments Sizes

                  MB|TABLESPACE_NAME          |OWNER                    |SEGMENT_NAME                            |SEGMENT_TYPE        |          PARTITIONS
--------------------|-------------------------|-------------------------|----------------------------------------|--------------------|--------------------
--------------------|                         |                         |                                        |                    |
                    |                         |                         |                                        |                    |

no rows selected

Elapsed: 00:00:00.26
FREE> 


REE> select index_name,index_type,owner,ityp_owner,table_name from dba_indexes where index_name='PROMO_ID';

INDEX_NAME        |INDEX_TYPE                 |OWNER             |ITYP_OWNER                              |TABLE_NAME
------------------|---------------------------|------------------|----------------------------------------|------------------
PROMO_ID          |DOMAIN                     |SH                |CTXSYS                                  |PROMO_ID

Elapsed: 00:00:00.01
FREE>

Now what is important to see here is the PROMO_ID column in the SH.PROMO_ID table is a Domain Index which is an Oracle Text Based Index.

You could read more about Oracle Text Indexes here.

https://docs.oracle.com/en/database/oracle/oracle-database/19/addci/building-domain-indexes.html#GUID-E370B5E4-BAC0-49C6-B17D-830B3A507FB4

I see now that that the Index Table is empty , simply because I have not associated anny columns to it.

FREE> SELECT json_serialize(DBMS_SEARCH.GET_DOCUMENT('SH.PROMO_ID',metadata)) FROM SH.PROMO_ID;

no rows selected

Uff .. Basically this is an Index now without a column --- now need to add sources

Now I would need to add columns to this DBMS_SEARCH index usinf DBMS_SEARCH.ADD_SOURCE

BEGIN  DBMS_SEARCH.ADD_SOURCE('SH.PROMO_ID', 'SH.COSTS'); END ;

FREE> BEGIN  DBMS_SEARCH.ADD_SOURCE('SH.PROMO_ID', 'SH.COSTS'); END ;
  2  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:32.13
FREE> 

NOTE : You can continue to add more sources of PROMO_ID here which DBMS_SEARCH supports to have multiple sources , but we will stick to just one table in our example.

You can see it took 32 seconds to add this column , now I would like to compare all the Indexed coulumns to my actual table SH.COSTS and the indexed table SH.PROMO_ID

FREE> SELECT json_serialize(DBMS_SEARCH.GET_DOCUMENT('SH.PROMO_ID',metadata)) FROM SH.PROMO_ID;

JSON_SERIALIZE(DBMS_SEARCH.GET_DOCUMENT('SH.PROMO_ID',METADATA))
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
.
.
.


{"SH":{"COSTS":{"PROD_ID":22,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":18.04,"UNIT_PRICE":26.05}}}
{"SH":{"COSTS":{"PROD_ID":114,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":15.56,"UNIT_PRICE":20.56}}}
{"SH":{"COSTS":{"PROD_ID":118,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":15.29,"UNIT_PRICE":17.37}}}
{"SH":{"COSTS":{"PROD_ID":23,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":18.52,"UNIT_PRICE":23.27}}}
{"SH":{"COSTS":{"PROD_ID":117,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":8.17,"UNIT_PRICE":9.7}}}
{"SH":{"COSTS":{"PROD_ID":18,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":1224.1,"UNIT_PRICE":1499.63}}}
{"SH":{"COSTS":{"PROD_ID":24,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":41.25,"UNIT_PRICE":63.92}}}
{"SH":{"COSTS":{"PROD_ID":114,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":18.11,"UNIT_PRICE":20.97}}}
{"SH":{"COSTS":{"PROD_ID":39,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":31.79,"UNIT_PRICE":36.95}}}
{"SH":{"COSTS":{"PROD_ID":44,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":38.19,"UNIT_PRICE":47.96}}}
{"SH":{"COSTS":{"PROD_ID":113,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":20.16,"UNIT_PRICE":24.72}}}
{"SH":{"COSTS":{"PROD_ID":119,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":6.08,"UNIT_PRICE":7.46}}}
{"SH":{"COSTS":{"PROD_ID":122,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":16.78,"UNIT_PRICE":20.23}}}
{"SH":{"COSTS":{"PROD_ID":124,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":16.82,"UNIT_PRICE":18.41}}}
{"SH":{"COSTS":{"PROD_ID":129,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":153.32,"UNIT_PRICE":199.52}}}
{"SH":{"COSTS":{"PROD_ID":129,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":165.11,"UNIT_PRICE":193.65}}}
{"SH":{"COSTS":{"PROD_ID":36,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":29.34,"UNIT_PRICE":48.86}}}

82112 rows selected.

Elapsed: 00:00:58.42
FREE> 

FREE> select count(*) from sh.costs;

            COUNT(*)
--------------------
               82112

Elapsed: 00:00:00.00
FREE>

I see the counts are matching 82112 in both cases,
Lets get to the real use case now.

Runing SQL's on the Indxed table to get API like output.

Lets first compare the 78425 records in both SH.COSTS and SH.PROMO_ID

FREE> select count(*) from SH.COSTS where PROMO_ID=999;

            COUNT(*)
--------------------
               78425

Elapsed: 00:00:00.00
FREE> 
FREE> select count(JSON_SERIALIZE(METADATA)) from SH.PROMO_ID where JSON_TEXTCONTAINS (data, '$.SH.COSTS', '999');

COUNT(JSON_SERIALIZE(METADATA))
-------------------------------
                          78425

They match !!!

Now lets do the real thing I started out to do , query the PROMO_ID using the the DBMS_SEARCH and without creating a global index.

But I have to do a JSON _SERIALIZE to get the required results.

FREE> SELECT json_serialize(DBMS_SEARCH.GET_DOCUMENT('SH.PROMO_ID',metadata)) FROM SH.PROMO_ID WHERE JSON_TEXTCONTAINS (data, '$.SH.COSTS', '999');

JSON_SERIALIZE(DBMS_SEARCH.GET_DOCUMENT('SH.PROMO_ID',METADATA))
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
.
.
.
.

{"SH":{"COSTS":{"PROD_ID":27,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":37.1,"UNIT_PRICE":47.14}}}
{"SH":{"COSTS":{"PROD_ID":119,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":6.08,"UNIT_PRICE":7.46}}}
{"SH":{"COSTS":{"PROD_ID":22,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":21.63,"UNIT_PRICE":24.23}}}
{"SH":{"COSTS":{"PROD_ID":23,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":19.89,"UNIT_PRICE":21.64}}}
{"SH":{"COSTS":{"PROD_ID":114,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":15.72,"UNIT_PRICE":20.79}}}
{"SH":{"COSTS":{"PROD_ID":119,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":6.35,"UNIT_PRICE":7.39}}}
{"SH":{"COSTS":{"PROD_ID":22,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":18.04,"UNIT_PRICE":26.05}}}
{"SH":{"COSTS":{"PROD_ID":114,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":15.56,"UNIT_PRICE":20.56}}}
{"SH":{"COSTS":{"PROD_ID":118,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":15.29,"UNIT_PRICE":17.37}}}
{"SH":{"COSTS":{"PROD_ID":23,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":18.52,"UNIT_PRICE":23.27}}}
{"SH":{"COSTS":{"PROD_ID":117,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":8.17,"UNIT_PRICE":9.7}}}
{"SH":{"COSTS":{"PROD_ID":18,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":1224.1,"UNIT_PRICE":1499.63}}}
{"SH":{"COSTS":{"PROD_ID":24,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":41.25,"UNIT_PRICE":63.92}}}
{"SH":{"COSTS":{"PROD_ID":114,"TIME_ID":"2001-03-21T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":18.11,"UNIT_PRICE":20.97}}}
{"SH":{"COSTS":{"PROD_ID":39,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":31.79,"UNIT_PRICE":36.95}}}
{"SH":{"COSTS":{"PROD_ID":44,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":38.19,"UNIT_PRICE":47.96}}}
{"SH":{"COSTS":{"PROD_ID":113,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":20.16,"UNIT_PRICE":24.72}}}
{"SH":{"COSTS":{"PROD_ID":119,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":6.08,"UNIT_PRICE":7.46}}}
{"SH":{"COSTS":{"PROD_ID":122,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":16.78,"UNIT_PRICE":20.23}}}
{"SH":{"COSTS":{"PROD_ID":124,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":16.82,"UNIT_PRICE":18.41}}}
{"SH":{"COSTS":{"PROD_ID":129,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":153.32,"UNIT_PRICE":199.52}}}
{"SH":{"COSTS":{"PROD_ID":129,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":165.11,"UNIT_PRICE":193.65}}}
{"SH":{"COSTS":{"PROD_ID":36,"TIME_ID":"2001-03-22T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":29.34,"UNIT_PRICE":48.86}}}

78425 rows selected.

Elapsed: 00:01:09.68
FREE>

Comapring SQL Plan my traditional query without Global Index 

FREE> set autotrace traceonly explain statistics
FREE> select PROD_ID,TIME_ID,PROMO_ID,CHANNEL_ID,UNIT_COST,UNIT_PRICE   from SH.COSTS where PROMO_ID=999;

78425 rows selected.

Elapsed: 00:00:04.60

Execution Plan
----------------------------------------------------------
Plan hash value: 1918128980

---------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       | 27371 |   694K|   135   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|       | 27371 |   694K|   135   (0)| 00:00:01 |     1 |    28 |
|*  2 |   TABLE ACCESS FULL | COSTS | 27371 |   694K|   135   (0)| 00:00:01 |     1 |    28 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("PROMO_ID"=999)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5435  consistent gets
          0  physical reads
          0  redo size
    3243706  bytes sent via SQL*Net to client
     130808  bytes received via SQL*Net from client
       5230  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      78425  rows processed

Comapring SQL Plan my traditional query with Global Index

Note that here in traditional approach global indexes are not applicable in partitioned tables.

FREE> create index SH.PROMO_ID_GLOBAL_IDX on SH.COSTS(PROMO_ID) tablespace users;

Index created.

Elapsed: 00:00:00.07

FREE> create bitmap index SH.PROMO_ID_GLOBAL_BI_IDX on SH.COSTS(PROMO_ID) tablespace users;
create bitmap index SH.PROMO_ID_GLOBAL_BI_IDX on SH.COSTS(PROMO_ID) tablespace users
                                                    *
ERROR at line 1:
ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables
Help: https://docs.oracle.com/error-help/db/ora-25122/

As you can see below this index is not effective even after forcing the index

FREE> set autotrace traceonly explain statistics
FREE> select  /*+ INDEX(SH.COSTS PROMO_ID_GLOBAL_IDX) */  PROD_ID,TIME_ID,PROMO_ID,CHANNEL_ID,UNIT_COST,UNIT_PRICE   from SH.COSTS where PROMO_ID=999;

78425 rows selected.

Elapsed: 00:00:04.86

Execution Plan
----------------------------------------------------------
Plan hash value: 1918128980

---------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       | 27371 |   694K|   135   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|       | 27371 |   694K|   135   (0)| 00:00:01 |     1 |    28 |
|*  2 |   TABLE ACCESS FULL | COSTS | 27371 |   694K|   135   (0)| 00:00:01 |     1 |    28 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("PROMO_ID"=999)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         N -  INDEX(SH.COSTS PROMO_ID_GLOBAL_IDX)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5435  consistent gets
          0  physical reads
          0  redo size
    3243706  bytes sent via SQL*Net to client
     130808  bytes received via SQL*Net from client
       5230  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      78425  rows processed

My tuning advisor also gave up and does not give me any support the global index for this scenario is as good as useless.

FREE> col sql_text for a120
FREE> select sql_id,sql_text from v$sql where sql_text like 'select PROD_ID,TIME_ID%';

SQL_ID       |SQL_TEXT
-------------|------------------------------------------------------------------------------------------------------------------------
0rtgyw0c0cf3r|select PROD_ID,TIME_ID,PROMO_ID,CHANNEL_ID,UNIT_COST,UNIT_PRICE   from SH.COSTS where PROMO_ID=999

Elapsed: 00:00:00.01
FREE> @$ORACLE_HOME/rdbms/admin/sqltrpt.sql

15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Elapsed: 00:00:00.00

SQL_ID       |   ELAPSED|SQL_TEXT_FRAGMENT
-------------|----------|-------------------------------------------------------
2uv6x0nar5jcf|    132.64|SELECT json_serialize(DBMS_SEARCH.GET_DOCUMENT('SH.PROM
47mxw5k83ku7f|     43.15|SELECT CASE WHEN EXISTS(SELECT 1 FROM SYS.ALL_VIEWS WHE
3hytgqy08gr0q|     39.36|call CTXSYS.DBMS_SEARCH.SYNC_INDEX ( :0,:1,:2 )
f46syq6p0qp8u|     37.55|SELECT CASE WHEN EXISTS(SELECT 1 FROM SYS.ALL_TABLES WH
69pf116c5zqyw|     37.41|SELECT COLUMN_NAME FROM SYS.ALL_TAB_COLS C WHERE C.OWNE
029v581kymzhz|     30.99|SELECT CASE WHEN EXISTS(SELECT 1 FROM SYS.ALL_VIEWS WHE
28ybx76zb0twn|     11.45|SELECT   JSON_OBJECT('SH' VALUE     JSON_OBJECT('COSTS'
bg0ucs4vg0zab|     10.89|SELECT value FROM v$parameter where lower(name) = '_qa_
5nm0hr6mnb6tg|      8.73|SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=FA
5pcscknhqvr5u|      4.96|SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TR
g9gjht126476b|      4.21|SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TR
b39m8n96gxk7c|      4.03|call dbms_autotask_prvt.run_autotask ( :0,:1 )
3h29b2cy0mhhr|      3.61|SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=FA
ampw9ddqufjd3|      3.07|begin /*KAPI:capture*/ dbms_auto_index_internal.capture
fwy9s3s1pvs24|      2.33|select crid, nvl(dml_rid, crid) brid, kdid, nvl(oper, 0
Elapsed: 00:00:00.02

15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID       |   ELAPSED|SQL_TEXT_FRAGMENT
-------------|----------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2uv6x0nar5jcf|     69.85|SELECT json_serialize(DBMS_SEARCH.GET_DOCUMENT('SH.PROM
2uv6x0nar5jcf|     69.85|SELECT json_serialize(DBMS_SEARCH.GET_DOCUMENT('SH.PROM
47mxw5k83ku7f|     34.59|SELECT CASE WHEN EXISTS(SELECT 1 FROM SYS.ALL_VIEWS WHE
47mxw5k83ku7f|     34.59|SELECT CASE WHEN EXISTS(SELECT 1 FROM SYS.ALL_VIEWS WHE
3hytgqy08gr0q|     30.58|call CTXSYS.DBMS_SEARCH.SYNC_INDEX ( :0,:1,:2 )
3hytgqy08gr0q|     30.58|call CTXSYS.DBMS_SEARCH.SYNC_INDEX ( :0,:1,:2 )
f46syq6p0qp8u|     28.68|SELECT CASE WHEN EXISTS(SELECT 1 FROM SYS.ALL_TABLES WH
f46syq6p0qp8u|     28.68|SELECT CASE WHEN EXISTS(SELECT 1 FROM SYS.ALL_TABLES WH
69pf116c5zqyw|     27.31|SELECT COLUMN_NAME FROM SYS.ALL_TAB_COLS C WHERE C.OWNE
69pf116c5zqyw|     27.31|SELECT COLUMN_NAME FROM SYS.ALL_TAB_COLS C WHERE C.OWNE
029v581kymzhz|     24.22|SELECT CASE WHEN EXISTS(SELECT 1 FROM SYS.ALL_VIEWS WHE
029v581kymzhz|     24.22|SELECT CASE WHEN EXISTS(SELECT 1 FROM SYS.ALL_VIEWS WHE
28ybx76zb0twn|      9.90|SELECT   JSON_OBJECT('SH' VALUE     JSON_OBJECT('COSTS'
28ybx76zb0twn|      9.90|SELECT   JSON_OBJECT('SH' VALUE     JSON_OBJECT('COSTS'
5nm0hr6mnb6tg|      8.73|SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=FA
Elapsed: 00:00:00.07

Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 0rtgyw0c0cf3r

Sql Id specified: 0rtgyw0c0cf3r
Elapsed: 00:00:00.00

Tune the sql
~~~~~~~~~~~~
Elapsed: 00:00:00.00
Elapsed: 00:00:00.42

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_77
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 04/29/2025 15:10:20
Completed at       : 04/29/2025 15:10:20

-------------------------------------------------------------------------------
Schema Name   : SYS
Container Name: FREEPDB1
SQL ID        : 0rtgyw0c0cf3r
SQL Text      : select PROD_ID,TIME_ID,PROMO_ID,CHANNEL_ID,UNIT_COST,UNIT_PRIC
                E   from SH.COSTS where PROMO_ID=999

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.

-------------------------------------------------------------------------------

Elapsed: 00:00:00.08
Elapsed: 00:00:00.00

Comapring SQL Plan results of my query using DBMS_SEARCH indexed query

Now you can see the SQL cost is far less but here we are querying the SH.PROMO_ID , which is the the new qay to query where we can off load certain reports of the table , the DBMS_SEARCH indexes arew asyncrhronous and keeps updating as transactions flow in SH.COSTS.

FREE> set autotrace traceonly explain statistics
FREE> SELECT json_serialize(DBMS_SEARCH.GET_DOCUMENT('SH.PROMO_ID',metadata)) FROM SH.PROMO_ID WHERE JSON_TEXTCONTAINS (data, '$.SH.COSTS', '999');

78425 rows selected.

Elapsed: 00:00:54.08

Execution Plan
----------------------------------------------------------
Plan hash value: 1909689119

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |          |    35 |   280K|    12   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST ALL                |          |    35 |   280K|    12   (0)| 00:00:01 |     1 |     2 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PROMO_ID |    35 |   280K|    12   (0)| 00:00:01 |     1 |     2 |
|*  3 |    DOMAIN INDEX                    | PROMO_ID |       |       |     4   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("PROMO_ID"."DATA" /*+ LOB_BY_VALUE */ ,'(999) INPATH (/SH/COSTS)')>0)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
    2352929  recursive calls
          0  db block gets
    9340569  consistent gets
          0  physical reads
        704  redo size
   11443201  bytes sent via SQL*Net to client
     130808  bytes received via SQL*Net from client
       5230  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      78425  rows processed

FREE>

Understanding the SQL cost differences.

You can see from the original plan that the cost is without using the global index

Image description

You can see from the original plan that the cost is with using the global index

Image description

and from the new plan usnog DBMS_SEARCH the cost is much lewer

Image description

So whats the bottomline ?

Application Development needs to keep refining itself to ensure API calls can happen from the database , though these features are available it needs to be made application fit by redesigning the use cases and make use of what the 23ai features provide.

While this may or may not help certain appliactions needs to be seen , but it does look very promising.

Well Done Oracle !!!