Complex oracle query requirements with memory tables

Use a memory table to handle complex oracle query requirements. during daily business processing, we often encounter some unconventional query requirements. We can use dynamic statements or other existing oracle functions to complete the query results, however, the efficiency is often unsatisfactory. suppose we have a customer order business table {order number, order

Use a memory table to handle complex oracle query requirements. during daily business processing, we often encounter some unconventional query requirements. We can use dynamic statements or other existing oracle functions to complete the query results, however, the efficiency is often unsatisfactory. suppose we have a customer order business table {order number, order

Use a memory table to handle complex oracle query requirements.

During daily business processing, we often encounter some unconventional query requirements. We can use dynamic statements or other existing oracle functions to complete the query results, however, the efficiency is often unsatisfactory.

Suppose we have a customer order business table {order number, order customer, order date, quantity, amount} that stores the order transaction details, the Order table stores a total of 500000 million records of transaction details in the last three months, of which the total number of customers is about. it is assumed that there is a separate index for date and customer in the order table.

It is now required to provide order details for multiple customers in any set for a certain period of time.

Select order number, order customer, order date, quantity, amount

From Order business table

Where order date between start date and end date

And order customer in (CUSTOMER 1, Customer 2, Customer 3 ...)

In the face of such requirements, we can ask the front-end program to return three parameters: start date, end date, and customer list (similar to Customer 1, Customer 2, Customer 3, Customer 4 ...)

1. Create a test table.

Create table t_order_cust (

O_id varchar2 (20 ),

O_customer varchar2 (20 ),

O_date date,

O_qty numeric (18, 2 ),

O_amount numeric (18, 2)

);

Create index ind_t_order_cust_01 on t_order_cust (o_customer );

Create index ind_t_order_cust_02 on t_order_cust (o_date );

2. Method 1: Use Dynamic statements to piece together.

For the above query, we can piece together the dynamic statement implementation, as shown in the following code.

Declare

V_beg_date date: = trunc (sysdate, 'month ');

V_end_date date: = trunk (sysdate );

V_cust_str varchar2 (1000): = '''customer 1 '', ''customer 2'', ''customer 3 ''... ';

V_ SQL _str varchar2 (2000 );

Begin

V_ SQL _str: = 'select * from t_order_cust

Where o_date between '| v_beg_date | 'and' | v_end_date |'

And o_customer in ('| v_cust_str | ')';

Execute immediate v_ SQL _str;

End;

We know that the customer index is optional at 1000 w/50 w = 20, while the date index is optional at 1000 w/(3*30) = 10 w, obviously, the efficiency of using date indexes is very poor. We can only choose to use the indexes on the customer. The advantage of using this processing method is that the indexes on the customer can be used, but the efficiency of using indexes in is relatively poor, in this way, the query execution tree needs to be re-established for each oracle query execution, which also requires a certain amount of additional overhead.

3. method 2 use like to query

In addition to the above method of splicing dynamic statements, we can imagine that the second method is to use the like function provided by oracle. the following code is used. in this method, the requirements for the customer list string are slightly different from those for the method.

Declare

V_beg_date date: = trunc (sysdate, 'month ');

V_end_date date: = trunk (sysdate );

V_cust_str varchar2 (1000): = 'customer 1, Customer 2, Customer 3... ';

Begin

Select * from t_order_cust

Where o_date between v_beg_date and v_end_date

And v_cust_str like '%' | o_customer | '% ';

End;

The advantage of this processing method is that the code writing is simple, but because the o_customer field of the customer index is spliced |, The ind_t_order_cust_01 of the customer index is invalid, instead, only inefficient date indexes can be used. this method is acceptable when the data size is small and does not affect the efficiency. However, when the data size is large, the disadvantage of this method will be fatal.

4. Method 3 use the instr function for processing

Declare

V_beg_date date: = trunc (sysdate, 'month ');

V_end_date date: = trunk (sysdate );

V_cust_str varchar2 (1000): = 'customer 1, Customer 2, Customer 3... ';

Begin

Select * from t_order_cust

Where o_date between v_beg_date and v_end_date

And instr (v_cust_str like, o_custome)> 0

End;

The advantages and disadvantages of this processing method are similar to the use of like. Similarly, because a function is used for o_customer, this index is unavailable, and function indexes are also not applicable to this situation.

5. Method 4 Use a memory table for processing

We know that the data types that oracle, sqlserver, and other relational databases are best at processing are collections rather than individual records. for the same 100 records, the difference in efficiency between one loop processing and batch processing will be geometric units.

Therefore, to improve query efficiency, we will consider converting a given customer string into a set or temporary table for processing. Oracle uses global temporary tables and complex data types to support this.

Here we will introduce how to process complex data types.

First, we define a complex type.

Create or replace type ctl. type_jax_varc2tab is table of varchar2 (2000 );

Then define a function to convert the given string into a nested memory table.

CREATEORREPLACEFUNCTIONF_jax_str2tab (p_strINVARCHAR2,
P_sepVarchar2Default','
)RETURNCtl. type_jax_varc2tabIS
/*************************************** ***************************
Ver1.0 Created by jaxzhang on 2009-06-08
Converts a string (1X2x3x4x5) to a memory table.
Create or replace type type_jax_varc2tab is table of varchar2 (2000 );
Test Case: SELECT * from table (f_jax_str2tab ('1*2*3*4*5 ','*'));
**************************************** **************************/
V_strVarchar2(2000 );
V_cntNUMBER;
V_numtab type_jax_varc2tab: = type_jax_varc2tab ();--Back memory table
BEGIN
SelectDecode (substr (p_str,-1), p_sep, p_str, p_str | p_sep)IntoV_strFromDual;
SelectLength (v_str)-length (REPLACE(V_str, p_sep ))IntoV_cntFromDual;

FORIIN1. v_cntLOOP
V_numtab.EXTEND;
V_numtab (I): = substr (v_str, 1, instr (v_str, p_sep)-1 );
V_str: = substr (v_str, instr (v_str, p_sep) + 1 );
ENDLOOP;

RETURNV_numtab;
EXCEPTION
WHENOTHERSTHEN
V_numtab.DELETE;
END;
The function above is to convert strings similar to 'customer 1, Customer 2, and customer 3' into the following form.

SELECT*FROMTABLE(F_jax_str2tab ('customer 1, Customer 2, customer 3 ',','));

COLUMN_VALUE

Customer 1

Customer 2

Customer 3

After the above memory table is obtained, we can associate it with the formal table t_order_cust in a way similar to a table or view to obtain the query result we need.

Select/* + ordered use_nl (a B )*/

FromTABLE(F_jax_str2tab ('customer 1, Customer 2, customer 3', '),

T_order_cust B

Where B. o_customer = a. column_value