Category Archives: No-Copy or Copy Options

Machine Learning- Data Management-1

Posted on by Julianne prior

Just like with graph algorithms, Oracle 23c has more than 30 algorithms ready to use for machine learning models.
Did anyone ask for AI? Machine learning is AI, and of course, there is more AI beyond just machine learning, but these are the interesting pieceswe can perform in the Oracle Database.

We even discussed that the Oracle Database statistic collection is now leveraging machine learning algorithms to make real-time statistics more efficient and predict information about data changes and growth.
Machine learning is there to answer additional questions and has the ability to analyze large
volumes of data.
What questions are being asked of your data? Have you been able to answer the business needs with reports and queries? If not, it is time to investigate implementing graph and machine learning and using the data for business value.

Machine learning can automatically discover patterns and create actionable information. Using the PL/SQL package DBMS_DATA_MINING, machine learning models can be built quickly.
You will need to gain some understanding of the types of algorithms such as classification, which can be used for predictions about customers.

In Autonomous Database, Oracle AutoML is available and can help you select the right types of algorithms. AutoML helps nonexpert users leverage machine learning in the Oracle Database.

Here is the process for AutoML:
• Auto algorithm selection: Finds the best algorithm from in-database algorithms
• Adaptive sampling: Identifies and adjusts the right sample size
• Auto feature selection: Improves accuracy and performance
• Auto model tuning: Improves models with automated tuning

AutoML provides a quick way to get started with machine learning and test the built-in algorithms and build models without completely understanding all of the science. However, with the different skill sets, the packages are available.
For example, let’s take our customer list, and based on their begin date as a customer, figure out if they are at risk of not purchasing additional items or leaving as a customer:

SQL> create or replace view vw_customer_longterm asselect c.customer_id, c.customer_name, decode(to_char(customer_begin_ date,’YYYY’,’2023′,1,0) cust_date_value,decode(nvl(o.order_id,0),0,0,1) order_value from customer c left outer join orders o on o.customer_id=c.customer_id; View created.


SQL> declarev_setlst dbms_data_mining.setting_list; beginv_setlst(dbms_data_mining.algo_name) := dbms_data_mining.algo_support_ vector_machines;v_setlst(dbms_data_mining.prep_auto) :=dbms_data_mining.prep_auto_on; dbms_data_mining.create_model2(model_name =>’FIND_LONGTERM’, mining_function => ‘CLASSIFICATION’,data_query => ‘select * frommmtest.customer_longterm’, set_list => v_setlst,case_id_column_name => ‘CUSTOMER_ID’, target_column_name => ‘CUST_DATE_VALUE’); end;/\

SQL> create table customer_longterm as select customer_id,prediction(FIND_LONGTERM usering*) cust_likely_to_leave, prediction_details(FIND_LONGTERM using *) PDfrom vw_customer_longterm;

Machine learning can confirm observations and find new patterns. Discovery and predictive relationships are ways to start harnessing the data by pulling information out of the data.

Graph- Data Management

Posted on by Julianne prior

Relational data seems like enough to make connections in data; however, there are connections that can require recursive queries and multiple joins that might be difficult to do with straight SQL.

Even if we look at the previous example of tables and compare customers that are connected by location, when they became customers, and the items they ordered, traversing through a graph structure might be easier.
Oracle 23c has native support for property graph data structures, and you can use a GRAPH_TABLE function and MATCH clause to write the queries.

There are more than 60 built-in algorithms for graph analytics.

Here are a couple of areas for these analytics:
• Detecting components and communities
• Evaluating structures
• Predicting links
• Ranking and walking
• Path-finding
• Machine learning

Now, you can start thinking of the questions you can ask of the data, such as making product recommendations, identifying dependencies and relationships, and detecting fraud.

You can even think of the operational data where this can help with workflows and dependencies of assets and systems.


Let’s look at a quick example using the same tables of customers and orders. We can have the customer table be a vertex, and items are also a vertex. Orders are the edges because this will show the connections. The graph will show items the customers have ordered and allow us to see additional related orders.

SQL> create property graph cust_orders vertex tables(customer key (customer_id) properties (customer_name, city,state), items key (item_id) properties (item_name, item_price) edge tables ( orderssource key (item_id) references items (item_id)destination key (customer_id) references customer (customer_id) properties (item_id,customer_id,order_id, order_date));

After we create the graph, we can query it for matches and count the items ordered:

SQL> select item_name, count(1) from graph_table (cust_orders match (src)-[is orders] -> (dst)
columns (dst.item_name)) group by item_name;

The () are for the vertices customer or items, and [] are for the edges that are the orders.
The match clause specifies the pattern from the graph_table, which is the path pattern for the graph. As you can see, we are using the same data without moving it to another graph database or using a separate system; this allows us to perform the analytics or use the graph algorithms against the data.

These simple examples might not give you the big picture of the data possibilities, but knowing you have different ways of viewing, connecting, and analyzing the data with queries on existing data is going to be useful. This allows you to leverage the data assets and use a single source of truth with all of the other security, performance, and reliability of the database that has been configured.