ORACLE SQL Tuning records a large table full table scan caused by a trim function

August 14, 2017, a city of Oracle related Scheduler ETL extraction speed is very slow, the average SQL statement execution time of about 9 seconds, if shown:

650) this.width=650; "Src=" http://img.blog.csdn.net/20170814133720856?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqvd3d3x3h1zv94aq==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Center "width=" "style=" border:0px;vertical-align:middle; "/>

The SQL statements involved in the scheduling process are as follows:

Select COUNT (*) from (select
RTrim (A.PID) PID,
A.item_type PTYPE,
"Feetype,
"Hisdid,
A.item_date Item_date,
NVL (a.wjw_id,a.item_id) item_id,
A.item_name Item_name,
NVL (a.numbers, 0) numbers,
NVL (a.price, 0) Price,
NVL (a.costs, 0) costs,
a.physician_id physician_id,
A.physician_name Physician_name,
a.dept_id dept_id,
A.deptname Deptname,
' 0 ' USAGE,
"Frequency_interval,
A.specification Use_method,
NVL (a.usage_days, 0) days_of_supply,
NVL (a.costs, 0) Eligible_amount,
"Self_amount,
"Physician_level,
"Physician_ap,
"Approvalnumber,
a.ID Prescriptionno,
"Costcategory,
"Item_name_hospital,
"' Forleave
From Claimdetailhospital_temp A
where trim (a.pid) = ' 42900500007915202 ');

Sqlplus Log on to the business user, execute and view the SQL execution plan as follows:

650) this.width=650; "Src=" http://img.blog.csdn.net/20170814135709632?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqvd3d3x3h1zv94aq==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Center "width=" "style=" border:0px;vertical-align:middle; "/>

Discovered by the SQL execution plan, the SQL statement performs a full table scan with the predicate: 2-filter (TRIM ("A"). PID ") = ' 42900500007915202 ')

To view the index on table claimdetailhospital_temp:

650) this.width=650; "Src=" http://img.blog.csdn.net/20170814134530136?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqvd3d3x3h1zv94aq==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Center "width=" "style=" border:0px;vertical-align:middle; "/>

650) this.width=650; "Src=" http://img.blog.csdn.net/20170814134345905?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqvd3d3x3h1zv94aq==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Center "width=" "style=" border:0px;vertical-align:middle; "/>

Thus, there is an index pid_index for the PID on the table claimdetailhospital_temp, but the DDL statement for the index Pid_index is:

Create INDEX Pid_index on claimdetailhospital_temp (PID);

Here, you can tell that the SQL statement performs a full table scan because the Where condition of SQL (a.pid) = ' 42900500007915202 ') uses the TRIM function on the query condition field PID to cause the parameter conversion,

A full table scan was performed to make index pid_index unusable. The processing method is simple, delete the index pid_index, create a trim-based function index:

Create INDEX ind_pid on claimdetailhospital_temp (Trim (PID));

After the successful creation of the function index, SQL performs a qualitative improvement and the execution speed is reduced from 9 seconds to 100 milliseconds:

650) this.width=650; "Src=" http://img.blog.csdn.net/20170814135532411?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqvd3d3x3h1zv94aq==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Center "width=" "style=" border:0px;vertical-align:middle; "/>

There is a question worth thinking about: developing or testing why not in the front of the application of the PID before and after the implementation of the white space function trim, rather than put on the database side to execute, which not only aggravate the workload of the database server,

It is also easy to cause the full table scan of large tables due to improper use of the function to reduce the execution speed of SQL; although, creating a trim-based function index on a PID field can improve SQL execution efficiency, but compared to normal indexes

A trim-based function index will significantly reduce the execution efficiency of DML statements, and the database can eliminate this unnecessary loss of performance if the trim-to-whitespace function is placed on the Web front-end.

This article is from the "Breeze ai ai" blog, please be sure to keep this source http://3596022.blog.51cto.com/3586022/1976249

ORACLE SQL Tuning records a large table full table scan caused by a trim function