oracle中关于clob类型字段的查询效率问题

内存溢出，导出前需要查询的sql如下：

SELECT
    pid.id,pid.materials_id,pid.invoice_id,pid.inform_order_detail_id,pid.unit_price,pid.invoice_amt,pid.remark,pid.biz_status,pid.version,pid.is_deleted,pid.create_user,pid.gmt_create,pid.create_user_id,pid.modified_user,pid.gmt_modified,pid.materials_release_id,m.id m_id,m.item_id m_item_id,m.code m_code,m.unit m_unit,m.name m_name,m.short_code m_short_code,m.primary_uom m_primary_uom,m.key_component_flag m_key_component_flag,m.materials_type m_materials_type,m.classification m_classification,m.is_examine m_is_examine,m.item_status m_item_status,m.is_examine_item m_is_examine_item,m.purchase_type m_purchase_type,m.gmt_modified m_gmt_modified,m.modified_user m_modified_user,iiod.id iiod_id,iiod.invoice_inform_order_id iiod_invoice_inform_order_id,iiod.materials_id iiod_materials_id,iiod.invoice_qty iiod_invoice_qty,iiod.unit_price iiod_unit_price,iiod.dispense_num iiod_dispense_num,iiod.package_deal_code iiod_package_deal_code,iiod.current_differ_price iiod_current_differ_price,i.id i_id,i.code i_code,i.supplier_id i_supplier_id,i.invoice_val i_invoice_val,i.old_code i_old_code,i.invoice_tax_val i_invoice_tax_val,i.tax_amount i_tax_amount,i.vmi_type i_vmi_type,i.invoice_abstract i_invoice_abstract,i.general_ledger_date i_general_ledger_date,i.input_time i_input_time,i.is_hand_dispose i_is_hand_dispose,i.invoice_status i_invoice_status,i.organization_id i_organization_id,i.invoice_type i_invoice_type,s.id s_id,s.name s_name,s.code s_code,s.short_code s_short_code,s.TYPE s_type,s.main_product s_main_product,s.tax_reg_num s_tax_reg_num,iio.id iio_id,iio.code iio_code,iio.end_date iio_end_date,iio.department_id iio_department_id,iio.supplier_id iio_supplier_id,iio.drawer iio_drawer,iio.vmi_type iio_vmi_type,iio.invoice_audit_status iio_invoice_audit_status,iio.organization_id iio_organization_id,iio.inform_order_type iio_inform_order_type,it.id it_id,it.invoice_id it_invoice_id,it.tax_rate it_tax_rate,it.tax_amount it_tax_amount 
FROM
    p_invoice_detail pid
    LEFT JOIN s_materials m ON pid.materials_id = m.id
    LEFT JOIN p_invoice_inform_order_detail iiod ON pid.inform_order_detail_id = iiod.id
    LEFT JOIN p_invoice_inform_order iio ON iiod.invoice_inform_order_id = iio.id
    LEFT JOIN p_invoice i ON pid.invoice_id = i.id
    LEFT JOIN p_invoice_tax it ON i.id = it.invoice_id
    LEFT JOIN s_supplier s ON s.id = i.supplier_id 
WHERE
    i.is_deleted = 0

?

其中表p_invoice_detail 中大概34W条数据，p_invoice_inform_order_detail中大概14W条数据，其它表中数据不超1W条，于是博主各种折腾，建索引，调整连接顺序等，都不起作用，博主用的navicat，每次查询我的naviicat就罢工，报unknown internal error?(A70529121901)? bad? allocation，没办法，只好一个表一个表的排除，最后发现，左连接p_invoice_inform_order_detail 表后就会出现上述问题，其它表都没事，于是博主单独查询这张表发现也很慢：

?

?

?用了37.704s只查询出8300条记录（表中共有约14W条数据），但是同样的查询有34W条数据的表p_invoice_detail却用了12.640s就全部查询出来了，博主就奇怪了，于是一个字段一个字段的试，终于发现是表p_invoice_inform_order_detail中的dispose_num字段导致的，后来发现该字段为clob类型，问题定位就好说了，上网查询发现将clob字段转为string类型再查询出来会提高效率，于是照做：将最开始的iiod.dispense_num修改为dbms_lob.substr(iiod.dispense_num,dbms_lob.getlength(iiod.dispense_num),1) dispense_num：

SELECT
    pid.id, dbms_lob.substr(iiod.dispense_num,1) dispense_num,
    iiod.package_deal_code iiod_package_deal_code,it.tax_amount it_tax_amount 
FROM
    p_invoice_detail pid
    LEFT JOIN s_materials m ON pid.materials_id = m.id
    LEFT JOIN p_invoice_inform_order_detail iiod ON pid.inform_order_detail_id = iiod.id
    LEFT JOIN p_invoice_inform_order iio ON iiod.invoice_inform_order_id = iio.id
    LEFT JOIN p_invoice i ON pid.invoice_id = i.id
    LEFT JOIN p_invoice_tax it ON i.id = it.invoice_id
    LEFT JOIN s_supplier s ON s.id = i.supplier_id 
WHERE
    i.is_deleted = 0

?

修改后查询，发现还是很慢，于是将其它不需要的字段去掉，调整为：

SELECT
  i.id,i.gmt_create,i.code,i.invoice_tax_val,m.code materials_code,m.name materials_name,s.code supplier_code,s.name supplier_name,it.tax_rate,i.tax_amount,iiod.current_differ_price,i.invoice_val,pid.unit_price * pid.invoice_amt total_money,dbms_lob.substr(iiod.dispense_num,1) dispense_num,decode(i.invoice_type,‘1‘,‘供应商平台‘,‘2‘,‘发票平台‘)
            
FROM
    p_invoice_detail pid
    LEFT JOIN p_invoice i ON pid.invoice_id = i.id
    LEFT JOIN s_materials m ON pid.materials_id = m.id
    LEFT JOIN p_invoice_inform_order_detail iiod ON pid.inform_order_detail_id = iiod.id
    LEFT JOIN p_invoice_inform_order iio ON iiod.invoice_inform_order_id = iio.id
    LEFT JOIN p_invoice_tax it ON i.id = it.invoice_id
    LEFT JOIN s_supplier s ON s.id = i.supplier_id 
WHERE
    i.is_deleted = 0

?

这次总算不报错了，虽然查询单速度还是有点慢，但还能接受：

（编辑：安卓应用网_ASP源码网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!