Sunday 8 September 2024
Unveiling DBMS_SEARCH in Oracle 23 AI: The New Era of Database Search
In the world of database management, efficiently locating data is paramount. With the advent of Oracle 23 AI, Oracle has introduced a game-changing feature: `DBMS_SEARCH`. This new feature, designed to simplify and accelerate search operations within Oracle databases, is making waves in the industry. In this blog post, we will dive deep into what `DBMS_SEARCH` is, how it works, and its benefits through a real-world example.
Imagine a global e-commerce company, "ShopEasy," with millions of users, transactions, products, and reviews stored in its Oracle 23ai database. As the business grows, so does the need to quickly retrieve data that powers search functionality on their platform. Users may want to find products based on specific keywords, filter by categories, or even search through product reviews to see what others are saying. The traditional approach would involve writing complex SQL queries and potentially creating multiple indexes. However, with Oracle 23ai's `DBMS_SEARCH` package, things have become much more streamlined.
### What is `DBMS_SEARCH`?
`DBMS_SEARCH` is a new built-in package in Oracle 23 AI that allows you to perform powerful and flexible full-text searches directly within the database. It provides a simplified, high-performance method for searching across multiple tables and columns, offering a unified search interface similar to popular search engines like Elasticsearch but directly integrated within Oracle.
### How Does `DBMS_SEARCH` Work?
Under the hood, `DBMS_SEARCH` leverages AI-powered indexing and advanced algorithms to understand the context of data better. It automatically indexes the specified columns, understands the relationships between tables, and optimizes search queries to provide near-instantaneous results. The package comes with several built-in functions, such as `DBMS_SEARCH.CREATE_INDEX`, `DBMS_SEARCH.SEARCH`, and `DBMS_SEARCH.DROP_INDEX`, allowing developers to define search contexts, execute searches, and manage indexes effortlessly.
For example, to set up a search index across the `PRODUCTS` and `REVIEWS` tables, you can use:
```sql
BEGIN
DBMS_SEARCH.CREATE_INDEX(
index_name => 'product_review_index',
tables => 'PRODUCTS, REVIEWS',
columns => 'product_name, review_text'
);
END;
/
```
With this setup, the `DBMS_SEARCH.SEARCH` function can be used to search for "wireless headphones" across both tables, returning relevant product names and reviews in a single, optimized query:
```sql
SELECT * FROM TABLE(DBMS_SEARCH.SEARCH('product_review_index', 'wireless headphones'));
```
### Benefits of `DBMS_SEARCH`
The benefits of `DBMS_SEARCH` are manifold:
1. **Unified Search Across Multiple Tables**: Unlike traditional methods where each table would require separate indexing and querying, `DBMS_SEARCH` allows for a single, cohesive search across multiple tables and columns.
2. **AI-Powered Optimization**: By leveraging AI, Oracle 23ai intelligently optimizes search queries, reducing latency and boosting performance. This is especially useful for real-time applications like e-commerce, where quick search results are crucial.
3. **Simplified Development and Maintenance**: Developers can set up and manage search contexts with just a few lines of code. There's no need for external search engines or additional infrastructure.
4. **Enhanced User Experience**: For "ShopEasy," `DBMS_SEARCH` directly translates to a better user experience. Users can search for products, filter by reviews, or find popular items effortlessly, driving more engagement and sales.
### A Real-World Impact
For "ShopEasy," implementing `DBMS_SEARCH` led to a 50% reduction in search query latency and simplified their search infrastructure. The customers could instantly find products matching their queries, read reviews, and make purchases, boosting customer satisfaction and revenue.
### Conclusion
`DBMS_SEARCH` in Oracle 23 AI represents a significant leap forward in database search capabilities, bringing the power of AI-driven search directly into the hands of developers. Whether you’re running an e-commerce platform, a financial service, or a healthcare application, `DBMS_SEARCH` offers an efficient, high-performance, and unified way to handle search operations. With its ease of use, powerful performance, and integration with Oracle's robust database ecosystem, `DBMS_SEARCH` is set to become a must-have tool for modern database management.
Saturday 25 May 2024
Vector index creation in Oracle 23ai
As you are aware there is AI embedded in Oracle 23ai powered by Vector Search, you can create vector indexes in Oracle 23ai, To allow vector index creation, you must enable a new memory area stored in the SGA called the Vector Pool. The Vector Pool is a memory allocated in SGA to store Hierarchical Navigable Small World (HNSW) vector indexes and all associated metadata. It is also used to speed up Inverted Flat File (IVF) index creation as well as DML operations on base tables with IVF indexes.
To size the Vector Pool, use the VECTOR_MEMORY_SIZE initialization parameter. You can dynamically modify this parameter at the following levels:
1) At the CDB level VECTOR_MEMORY_SIZE specifies the current size of the Vector Pool. Reducing the parameter value will fail if there is current vector usage.
2) At the PDB level VECTOR_MEMORY_SIZE specifies the maximum Vector Pool usage allowed by a PDB. Reducing the parameter value will be allowed even if current vector usage exceeds the new quota.
3) You can change the value of a parameter in a parameter file in the following ways:
3a) By editing an initialization parameter file. In most cases, the new value takes effect the next time you start an instance of the database.
3b) By issuing an ALTER SYSTEM SET ... SCOPE=SPFILE statement to update a server parameter file.
3c) By issuing an ALTER SYSTEM RESET statement to clear an initialization parameter value and set it back to its default value.
Lets see a demo how you can do it :
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
SQL> show con_name
CON_NAME
------------------------------
FREEPDB1
SQL> show user
USER is "SYS"
SQL> show parameter vector_memory_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
vector_memory_size big integer 0
SQL> SELECT ISPDB_MODIFIABLE FROM V$SYSTEM_PARAMETER WHERE NAME='vector_memory_size';
ISPDB
-----
TRUE
SQL> ALTER SYSTEM SET vector_memory_size=1G SCOPE=BOTH;
System altered.
SQL> show parameter vector_memory_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
vector_memory_size big integer 1G
V$VECTOR_MEMORY_POOL, is the view you can query to monitor the Vector Pool.
Reference : https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/size-vector-pool.html
New Ping command in Oracle 23ai database
Oracle 23ai offers a very useful command Ping. Lets go deep and understand what it is :
Firstly to make the confusion clear it is just like tnsping, so if you want to test connectivity and see the network reachability for listener, database and so on this is very helpful. As per Oracle officially ping utility : Pings the database or database network listener to check availability. The PING command will fail with an error if either the database or the network listener are unavailable. It indicates that either the network listener or the database needs to be restarted, or that the database connection is not usable and needs to be recreated.
PING command is executed without a connect identifier, then a round-trip message is sent through the network listener to the currently connected database. When it is executed with a connect identifier, then a round-trip message is sent to the specified network listener to verify if it is able to handle the database connections.When i fired just ping it did this to me :
SQL> ping
Ok (1.398 msec)
So it tests the current connection reachability.
Now when i give a wrong name which means i dont have an entry in respective tnsnames.ora it hangs for a moment for obvious reasons and returns this info :
SQL> ping a
>> Hangs here for a while (few seconds)
SP2-1679: Connect identifier (a) was not found.
Help: https://docs.oracle.com/error-help/db/sp2-1679/
Cause: Connect identifier passed was not found in tnsnames.ora.
Action: Check the connect identifier used in the PING command.
Now lets try to actually ping something whose entry exists in the tnsnames.ora i.e :
SQL> ping FREEPDB1
Local Net Naming configuration file: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
Attempting to contact: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB1)))
Ok (20.312 msec)
SQL> !cat /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
FREE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREE)
)
)
LISTENER_FREE =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
FREEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREEPDB1)
)
)
#Happy learning, thanks
Oracle 23ai helpful feature of error details for Oracle users
I was testing the new 23ai free VM shared by Oracle, and i observed very helpful feature of 23ai which shares us the url of the error to read more about it for knowledge. Here is how it works :
So here the table t does not exists, and we get Help page to see about the error and its details and action page
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-00942: table or view "SYS"."T" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
SQL> show errordetails
errordetails ON
SQL> set errordetails off
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-00942: table or view "SYS"."T" does not exist
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-00942: table or view "SYS"."T" does not exist
# Further the verbose page shows more details like Cause: as well.
SQL> set errordetails verbose
SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-00942: table or view "SYS"."T" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
Cause: The specified table or view did not exist, or a synonym
pointed to a table or view that did not exist.
To find existing user tables and views, query the
ALL_TABLES and ALL_VIEWS data dictionary views. Certain
privileges may be required to access the table. If an
application returned this message, then the table that the
application tried to access did not exist in the database, or
the application did not have access to it.
Action: Check each of the following:
- The spelling of the table or view name is correct.
- The referenced table or view name does exist.
- The synonym points to an existing table or view.
If the table or view does exist, ensure that the correct access
privileges are granted to the database user requiring access
to the table. Otherwise, create the table.
Also, if you are attempting to access a table or view in another
schema, make sure that the correct schema is referenced and that
access to the object is granted.
Params: 1) object_name: The table or view name specified as
SCHEMA.OBJECT_NAME, if one is provided.
Otherwise, it is blank.
How to fix ORA-01017 in 23ai Oracle
As you know Oracle has announced 23ai as a big announcement with so many features more than 300 for developers and Oracle users, here is the post on How to fix ORA-01017 in 23ai Oracle for the VM which Oracle provides to developer for free .
[oracle@localhost ~]$ ps -ef | grep smon
oracle 1738 1 0 17:27 ? 00:00:00 db_smon_FREE
oracle 4398 4334 0 17:37 pts/3 00:00:00 grep --color=auto smon
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [FREE] ? FREE
The Oracle base remains unchanged with value /opt/oracle
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Sat May 25 17:37:30 2024
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/
[oracle@localhost ~]$ sqlplus sys@localhost:1521/FREEPDB1 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Sat May 25 17:38:24 2024
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
So the solution is, the credentials must be used for FREEPDB1 as mentioned in the Oracle portal . Here is the link for more info to get started with 23ai https://www.oracle.com/database/free/get-started/
Subscribe to:
Posts (Atom)