Oracle Data Mining uses Nested data types/tables to store some of its data. Oracle Data Mining creates a number of tables/objects that contain nested data when it is preparing data for input to the data mining algorithms and when outputting certain results from the algorithms. In Oracle 11.2g there are two nested data types used and in Oracle 12.1c we get an additional two nested data types. These are setup when you install the Oracle Data Miner Repository. If you log into SQL*Plus or SQL Developer you can describe them like any other table or object.
DM_NESTED_NUMERICALS
DM_NESTED_CATEGORICALS
The following two Nested data types are only available in 12.1c
DM_NESTED_BINARY_DOUBLES
DM_NESTED_BINARY_FLOATS
These Nested data types are used by Oracle Data Miner in preparing data for input to the data mining algorithms and for producing the some of the outputs from the algorithms.
Creating your own Nested Tables
To create your own Nested Data Types and Nested Tables you need to performs steps that are similar to what is illustrated in the following steps. These steps show you how to define a data type, how to create a nested table, how to insert data into the nested table and how to select the data from the nested table.
1. Set up the Object Type
Create a Type object that will defines the structure of the data. In these examples we want to capture the products and quantity purchased by a customer.
create type CUST_ORDER as object
(product_id varchar2(6),
quantity_sold number(6));
/
2. Create a Type as a Table
Now you need to create a Type as a table.
create type cust_orders_type as table of CUST_ORDER;
/
3. Create the table using the Nested Data
Now you can create the nested table.
create table customer_orders_nested (
cust_id number(6) primary key,
order_date date,
sales_person varchar2(30),
c_order CUST_ORDERS_TYPE)
NESTED TABLE c_order STORE AS c_order_table;
4. Insert a Record and Query
This insert statement shows you how to insert one record into the nested column.
insert into customer_orders_nested
values (1, sysdate, 'BT', CUST_ORDERS_TYPE(cust_order('P1', 2)) );
When we select the data from the table we get
select * from customer_orders_nested;
CUST_ID ORDER_DAT SALES_PERSON
---------- --------- ------------------------------
C_ORDER(PRODUCT_ID, QUANTITY_SOLD)
-----------------------------------------------------
1 19-SEP-13 BT
CUST_ORDERS_TYPE(CUST_ORDER('P1', 2))
It can be a bit difficult to read the data in the nested column so we can convert the nested column into a table to display the results in a better way
select cust_id, order_date, sales_person, product_id, quantity_sold
from customer_orders_nested, table(c_order)
CUST_ID ORDER_DAT SALES_PERSON PRODUC QUANTITY_SOLD
---------- --------- ------------------------------ ------ -------------
1 19-SEP-13 BT P1 2
5. Insert many Nested Data items & Query
To insert many entries into the nested column you can do this
insert into customer_orders_nested
values (2, sysdate, 'BT2', CUST_ORDERS_TYPE(CUST_ORDER('P2', 2), CUST_ORDER('P3',3)));
When we do a Select * we get
CUST_ID ORDER_DAT SALES_PERSON
---------- --------- ------------------------------
C_ORDER(PRODUCT_ID, QUANTITY_SOLD)
-------------------------------------------------------------
1 19-SEP-13 BT
CUST_ORDERS_TYPE(CUST_ORDER2('P1', 2))
2 19-SEP-13 BT2
CUST_ORDERS_TYPE(CUST_ORDER2('P2', 2), CUST_ORDER2('P3', 3))
Again it is not easy to ready the data in the nested column, so if we convert it to a table again we now get a row being displayed for each entry in the nested column.
select cust_id, order_date, sales_person, product_id, quantity_sold
from customer_orders_nested, table(c_order);
CUST_ID ORDER_DAT SALES_PERSON PRODUC QUANTITY_SOLD
---------- --------- ------------------------------ ------ -------------
1 19-SEP-13 BT P1 2
2 19-SEP-13 BT2 P2 2
2 19-SEP-13 BT2 P3 3
No comments:
Post a Comment