- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我一直在努力改进我的程序,所以我开始将它们从使用 ||
字符串连接更改为使用绑定(bind)变量。当我将绑定(bind)变量添加到以下过程时,我发现我需要将聚合函数添加到 where 子句,以便搜索数字在逗号分隔的数字列表中的位置。
添加聚合函数 string_to_table_num
和 string_to_table_varchar2
并更新过程的其他部分以使用绑定(bind)变量后,我尝试向我的 ORDS 端点发出请求。该请求用了 10 多分钟才完成。我认为这是由于聚合函数和 APEX 的组合造成的,因为原始查询的运行时间不到 500 毫秒。为了提高速度,我尝试了几种不同的功能,但我没有任何运气。
如果您有建议,我也会采纳有关如何改进整体查询的建议。我对 Oracle 比较陌生。
ORDS 端点
BEGIN
get_categoryprods2(:commasepproductids, :commasepproductskus, :pcategoryid, :sortby);
END;
参数
commasepproductids IN STRING from URI
commasepproductskus IN STRING from URI
pcategoryid IN STRING from URI
sortby IN STRING from URI
ptopcount in INT
原始查询
create or replace PROCEDURE get_categoryprods (
commasepproductids IN VARCHAR2,
commasepproductskus in varchar2,
pcategoryid IN NUMBER,
sortby IN VARCHAR2
) AS
l_cursor SYS_REFCURSOR;
v_stmt_str STRING(5000);
s_counter NUMBER;
v_productid productnew.productid%TYPE;
v_manufacturerid productmanufacturer.manufacturerid%TYPE;
v_sename productnew.sename%TYPE;
v_name productnew.name%TYPE;
v_summary varchar(3999);
v_freeground productnew.freeground%TYPE;
v_quantitydiscountid productnew.quantitydiscountid%TYPE;
v_sku productnew.sku%TYPE;
v_price productnew.price%TYPE;
v_msrp productnew.msrp%TYPE;
v_cost productnew.cost%TYPE;
v_saleprice productnew.saleprice%TYPE;
v_weight productnew.weight%TYPE;
v_percase productnew.percase%TYPE;
v_relatedproducts varchar(3999);
v_hidepriceuntilcart productnew.hidepriceuntilcart%TYPE;
v_discontinued productnew.discontinued%TYPE;
v_discounttype productnew.discounttype%TYPE;
v_unitofmeasure productnew.unitofmeasure%TYPE;
v_replacement productnew.replacement%TYPE;
v_displayorder productcategory.displayorder%TYPE;
v_friendlyurl urlmapper.friendlyurl%TYPE;
v_sortby varchar2(300);
BEGIN
IF sortby IS NULL OR sortby = 'null' OR sortby = '' THEN
v_sortby := '"p".Discontinued, "pc".DisplayOrder ';
ELSIF sortby = 'PriceAscending' THEN
v_sortby := '"p".discontinued, "p".price ';
ELSIF sortby = 'PriceDescending' THEN
v_sortby := '"p".discontinued, "p".price DESC ';
ELSIF sortby = 'Name' THEN
v_sortby := '"p".discontinued, "p".name ';
ELSE
v_sortby := '"p".discontinued, "pc".displayorder ';
END IF;
v_stmt_str := 'SELECT
"p".productid,
"pm".manufacturerid,
"p".sename,
"p".name,
to_char(substr("p".summary, 0, 3999)),
TO_NUMBER("p".freeground),
"p".quantitydiscountid,
"p".sku,
"p".price,
"p".msrp,
"p".cost,
"p".saleprice,
"p".weight,
"p".percase,
to_char(substr("p".relatedproducts, 0, 3999)),
"p".hidepriceuntilcart,
"p".discontinued,
"p".discounttype,
"p".unitofmeasure,
"p".replacement,
"pc".displayorder,
"um".friendlyurl
FROM
productnew "p"
LEFT OUTER JOIN productcategory "pc" ON "p".productid = "pc".productid
LEFT OUTER JOIN productmanufacturer "pm" ON "p".productid = "pm".productid
LEFT OUTER JOIN urlmapper "um" ON "p".productid = "um".productid
WHERE
("pc".categoryid = ' || pcategoryid || ')
AND "p".productid in ('||commasepproductids||')
--and ("p".productid in ('||commasepproductids||') -- removed because I broke the actual original proc
AND ( "p".deleted = 0 )
AND "p".published = 1 ORDER BY '||v_sortby;
s_counter := 0;
apex_json.open_array;
OPEN l_cursor FOR v_stmt_str;
FETCH l_cursor into
v_productid,
v_manufacturerid,
v_sename,
v_name,
v_summary,
v_freeground,
v_quantitydiscountid,
v_sku,
v_price,
v_msrp,
v_cost,
v_saleprice,
v_weight,
v_percase,
v_relatedproducts,
v_hidepriceuntilcart,
v_discontinued,
v_discounttype,
v_unitofmeasure,
v_replacement,
v_displayorder,
v_friendlyurl;
loop
EXIT WHEN l_cursor%notfound;
s_counter := 1;
apex_json.open_object;
apex_json.write('ProductID', v_productid);
apex_json.write('ManufacturerID', v_manufacturerid);
apex_json.write('SEName', v_sename);
apex_json.write('Name', v_name);
apex_json.write('Summary', v_summary);
apex_json.write('FreeGround', v_freeground);
apex_json.write('QuantityDiscountID', v_quantitydiscountid);
apex_json.write('SKU', v_sku);
apex_json.write('Price', v_price);
apex_json.write('MSRP', v_msrp);
apex_json.write('Cost', v_cost);
apex_json.write('SalePrice', v_saleprice);
apex_json.write('Weight', v_weight);
apex_json.write('PerCase', v_percase);
apex_json.write('RelatedProducts', v_relatedproducts);
apex_json.write('HidePriceUntilCart', v_hidepriceuntilcart);
apex_json.write('Discontinued', v_discontinued);
apex_json.write('QuantityDiscountType', v_discounttype);
apex_json.write('UnitOfMeasure', v_unitofmeasure);
apex_json.write('Replacement', v_replacement);
apex_json.write('DisplayOrder', v_displayorder);
apex_json.write('FriendlyUrl', v_friendlyurl);
apex_json.close_object;
end loop;
IF s_counter = 0 THEN
apex_json.open_object;
apex_json.write('ProductID', 0);
apex_json.write('SEName', 'NOT FOUND');
apex_json.write('Name', 'NOT FOUND');
apex_json.write('Summary', 'NOT FOUND');
apex_json.write('FreeGround', 'NOT FOUND');
apex_json.write('QuantityDiscountID', 0);
apex_json.write('SKU', 'NOT FOUND');
apex_json.write('Price', 0);
apex_json.write('MSRP', 0);
apex_json.write('Cost', 0);
apex_json.write('SalePrice', 0);
apex_json.write('Weight', 0);
apex_json.write('PerCase', 'NOT FOUND');
apex_json.write('RelatedProducts', 'NOT FOUND');
apex_json.write('HidePriceUntilCart', 'NOT FOUND');
apex_json.write('Discontinued', 'NOT FOUND');
apex_json.write('QuantityDiscountType', 'NOT FOUND');
apex_json.write('UnitOfMeasure', 'NOT FOUND');
apex_json.write('Replacement', 'NOT FOUND');
apex_json.write('FriendlyUrl', 'NOT FOUND');
apex_json.close_object;
END IF;
apex_json.close_all;
END get_categoryprods;
**新查询 1 **
create or replace PROCEDURE GET_CATEGORYPRODS2
(
COMMASEPPRODUCTIDS IN VARCHAR2
, COMMASEPPRODUCTSKUS IN VARCHAR2
, PCATEGORYID IN NUMBER
, SORTBY IN VARCHAR2
, PTOPCOUNT IN VARCHAR2
) AS
l_cursor SYS_REFCURSOR;
v_stmt_str STRING(5000);
s_counter NUMBER;
v_productid productnew.productid%TYPE;
v_manufacturerid productmanufacturer.manufacturerid%TYPE;
v_sename productnew.sename%TYPE;
v_name productnew.name%TYPE;
v_summary varchar(3999);
v_freeground productnew.freeground%TYPE;
v_quantitydiscountid productnew.quantitydiscountid%TYPE;
v_sku productnew.sku%TYPE;
v_price productnew.price%TYPE;
v_msrp productnew.msrp%TYPE;
v_cost productnew.cost%TYPE;
v_saleprice productnew.saleprice%TYPE;
v_weight productnew.weight%TYPE;
v_percase productnew.percase%TYPE;
v_relatedproducts varchar(3999);
v_hidepriceuntilcart productnew.hidepriceuntilcart%TYPE;
v_discontinued productnew.discontinued%TYPE;
v_discounttype productnew.discounttype%TYPE;
v_unitofmeasure productnew.unitofmeasure%TYPE;
v_replacement productnew.replacement%TYPE;
v_displayorder productcategory.displayorder%TYPE;
v_friendlyurl urlmapper.friendlyurl%TYPE;
v_sortby varchar2(300);
type t_categoryprods is table of categoryprod_typ;
l_catprodrow categoryprod_typ;
l_catprods t_categoryprods;
BEGIN
IF sortby IS NULL OR sortby = 'null' OR sortby = '' THEN
v_sortby := '"p".Discontinued, "pc".DisplayOrder ';
ELSIF sortby = 'PriceAscending' THEN
v_sortby := '"p".discontinued, "p".price ';
ELSIF sortby = 'PriceDescending' THEN
v_sortby := '"p".discontinued, "p".price DESC ';
ELSIF sortby = 'Name' THEN
v_sortby := '"p".discontinued, "p".name ';
ELSE
v_sortby := '"p".discontinued, "pc".displayorder ';
END IF;
v_stmt_str := 'SELECT
"p".productid,
"pm".manufacturerid,
"p".sename,
"p".name,
to_char(substr("p".summary, 0, 3999)),
to_number("p".freeground),
"p".quantitydiscountid,
"p".sku,
"p".price,
"p".msrp,
"p".cost,
"p".saleprice,
"p".weight,
"p".percase,
to_char(substr("p".relatedproducts, 0, 3999)),
"p".hidepriceuntilcart,
"p".discontinued,
"p".discounttype,
"p".unitofmeasure,
"p".replacement,
"pc".displayorder,
"um".friendlyurl
FROM
productnew "p"
LEFT OUTER JOIN productcategory "pc" ON "p".productid = "pc".productid
LEFT OUTER JOIN productmanufacturer "pm" ON "p".productid = "pm"
.productid
LEFT OUTER JOIN urlmapper "um" ON "p".productid = "um".productid
WHERE
"pc".categoryid = :pcategoryid
AND ( ( "p".productid IN (SELECT * FROM TABLE (string_to_table_num(:commasepproductids))) OR :commasepproductids IS NULL)
AND (lower("p".sku) IN (SELECT * FROM TABLE(string_to_table_varchar2(:commasepproductskus))) OR :commasepproductskus IS NULL ) )
AND "p".deleted = 0
AND "p".published = 1
ORDER BY :sortby';
s_counter := 0;
apex_json.open_array;
OPEN l_cursor FOR v_stmt_str USING pcategoryid, commasepproductids, commasepproductids, commasepproductskus, commasepproductskus, v_sortby;
FETCH l_cursor into
v_productid,
v_manufacturerid,
v_sename,
v_name,
v_summary,
v_freeground,
v_quantitydiscountid,
v_sku,
v_price,
v_msrp,
v_cost,
v_saleprice,
v_weight,
v_percase,
v_relatedproducts,
v_hidepriceuntilcart,
v_discontinued,
v_discounttype,
v_unitofmeasure,
v_replacement,
v_displayorder,
v_friendlyurl;
loop
EXIT WHEN l_cursor%notfound;
s_counter := 1;
apex_json.open_object;
apex_json.write('ProductID', v_productid);
apex_json.write('ManufacturerID', v_manufacturerid);
apex_json.write('SEName', v_sename);
apex_json.write('Name', v_name);
apex_json.write('Summary', v_summary);
apex_json.write('FreeGround', v_freeground);
apex_json.write('QuantityDiscountID', v_quantitydiscountid);
apex_json.write('SKU', v_sku);
apex_json.write('Price', v_price);
apex_json.write('MSRP', v_msrp);
apex_json.write('Cost', v_cost);
apex_json.write('SalePrice', v_saleprice);
apex_json.write('Weight', v_weight);
apex_json.write('PerCase', v_percase);
apex_json.write('RelatedProducts', v_relatedproducts);
apex_json.write('HidePriceUntilCart', v_hidepriceuntilcart);
apex_json.write('Discontinued', v_discontinued);
apex_json.write('QuantityDiscountType', v_discounttype);
apex_json.write('UnitOfMeasure', v_unitofmeasure);
apex_json.write('Replacement', v_replacement);
apex_json.write('DisplayOrder', v_displayorder);
apex_json.write('FriendlyUrl', v_friendlyurl);
apex_json.close_object;
end loop;
IF s_counter = 0 THEN
apex_json.open_object;
apex_json.write('ProductID', 0);
apex_json.write('SEName', 'NOT FOUND');
apex_json.write('Name', 'NOT FOUND');
apex_json.write('Summary', 'NOT FOUND');
apex_json.write('FreeGround', 'NOT FOUND');
apex_json.write('QuantityDiscountID', 0);
apex_json.write('SKU', 'NOT FOUND');
apex_json.write('Price', 0);
apex_json.write('MSRP', 0);
apex_json.write('Cost', 0);
apex_json.write('SalePrice', 0);
apex_json.write('Weight', 0);
apex_json.write('PerCase', 'NOT FOUND');
apex_json.write('RelatedProducts', 'NOT FOUND');
apex_json.write('HidePriceUntilCart', 'NOT FOUND');
apex_json.write('Discontinued', 'NOT FOUND');
apex_json.write('QuantityDiscountType', 'NOT FOUND');
apex_json.write('UnitOfMeasure', 'NOT FOUND');
apex_json.write('Replacement', 'NOT FOUND');
apex_json.write('FriendlyUrl', 'NOT FOUND');
apex_json.close_object;
END IF;
apex_json.close_all;
END GET_CATEGORYPRODS2;
string_to_table_num
create or replace FUNCTION string_to_table_num (
p VARCHAR2
)
RETURN tab_number
PIPELINED IS
BEGIN
FOR cc IN (SELECT rtrim(regexp_substr(str, '[^,]*,', 1, level), ',') res
FROM (SELECT p || ',' str FROM dual)
CONNECT BY level <= length(str)
- length(replace(str, ',', ''))) LOOP
PIPE ROW(lower(cc.res));
END LOOP;
END;
string_to_table_varchar
create or replace FUNCTION string_to_table_varchar2(p VARCHAR2)
RETURN tab_varchar2
PIPELINED IS
BEGIN
FOR cc IN (SELECT rtrim(regexp_substr(str, '[^,]*,', 1, level), ',') res
FROM (SELECT p || ',' str FROM dual)
CONNECT BY level <= length(str)
- length(replace(str, ',', ''))) LOOP
PIPE ROW(lower(cc.res));
END LOOP;
END;
然后我尝试用以下函数替换 string_to_table 函数
f_convert2
create or replace FUNCTION f_convert2(p_list IN STRING)
RETURN tab_number
PIPELINED
AS
l_string LONG := p_list || ',';
l_comma_index PLS_INTEGER;
l_index PLS_INTEGER := 1;
BEGIN
LOOP
l_comma_index := INSTR(l_string, ',', l_index);
EXIT WHEN l_comma_index = 0;
PIPE ROW ( SUBSTR(l_string, l_index, l_comma_index - l_index) );
l_index := l_comma_index + 1;
END LOOP;
RETURN ;
END f_convert2;
f_convert
create or replace FUNCTION f_convert(p_list IN STRING)
RETURN tab_varchar2
PIPELINED
AS
l_string LONG := p_list || ',';
l_comma_index PLS_INTEGER;
l_index PLS_INTEGER := 1;
BEGIN
LOOP
l_comma_index := INSTR(l_string, ',', l_index);
EXIT WHEN l_comma_index = 0;
PIPE ROW ( SUBSTR(l_string, l_index, l_comma_index - l_index) );
l_index := l_comma_index + 1;
END LOOP;
RETURN;
END f_convert;
如果我从 Postman 或我的 Node 应用程序调用该过程,它会超时或给出 请检查 SQL 语句是否正确形成并执行无误。 SQL 错误代码:ORA-04036:实例使用的 PGA 内存超过 PGA_AGGREGATE_LIMIT
。如果我将 apex_json.write 替换为 dbms_output.write_line 并通过 SQL Developer 运行它,它运行良好且速度非常快。
最佳答案
每当使用表函数时,请考虑使用 DYNAMIC_SAMPLING
提示。 Oracle 不知道函数 STRING_TO_TABLE_NUM
返回了多少行,并且可能会使用 8168 行的猜测。动态采样提示指示 Oracle 花费一些额外的解析时间来计算行数,这有望比更好的执行计划节省的时间少得多。
例如,使用这个类型和函数:
create or replace type tab_number as table of number;
create or replace FUNCTION string_to_table_num (
p VARCHAR2
)
RETURN tab_number
PIPELINED IS
BEGIN
FOR cc IN (SELECT rtrim(regexp_substr(str, '[^,]*,', 1, level), ',') res
FROM (SELECT p || ',' str FROM dual)
CONNECT BY level <= length(str)
- length(replace(str, ',', ''))) LOOP
PIPE ROW(lower(cc.res));
END LOOP;
END;
/
下面的简单示例显示 Oracle 将行数高估为 8168 而不是实际值 9:
explain plan for select * from table(string_to_table_num('1,2,3,4,5,6,7,8,9'));
select * from table(dbms_xplan.display);
Plan hash value: 127161297
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| STRING_TO_TABLE_NUM | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
当我们添加动态采样提示时,Oracle 得到了完美的基数:
explain plan for select /*+ dynamic_sampling(2) */ * from table(string_to_table_num('1,2,3,4,5,6,7,8,9'));
select * from table(dbms_xplan.display);
Plan hash value: 127161297
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 18 | 11 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| STRING_TO_TABLE_NUM | 9 | 18 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
这个解决方案没有直接解决为什么 SQL 在不同上下文中执行不同的问题。但是,如果您可以解决两个巨大的基数问题,那么其他问题可能就不再重要了。
关于具有聚合函数的 Oracle 动态 SQL 过程通过 ORDS 调用需要很长时间,但在 SQL Developer 中运行速度很快,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/63174998/
Oracle 即时客户端和 Oracle 客户端有什么区别?你能给我解释一下吗?谢谢 最佳答案 Oracle 客户端带有安装程序和许多可执行文件,例如 sqlplus,tnsping,它是完整而庞大的
我正在寻找一个Delphi组件来直接连接到ORACLE数据库服务器,而无需安装oracle客户端。 我知道Oracle Data Access (ODAC)来自DevArt 。还有其他组件具有此功能吗
如何编写 Oracle 存储过程,以表 (X) 作为输入参数,并在过程内部使用表 X 来与另一个表 Y 联接? 表 X 将包含数千条记录。 不希望将表名作为 varchar 传递,然后使用动态 SQL
如何编写 Oracle 存储过程,以表 (X) 作为输入参数,并在过程内部使用表 X 来与另一个表 Y 联接? 表 X 将包含数千条记录。 不希望将表名作为 varchar 传递,然后使用动态 SQL
我要过滤COMMENTS属性为空的记录 我试过了 SELECT TABLE_NAME, COMMENTS FROM (SELECT TABLE_NAME, COMMENTS FROM (sel
我要下载 Oracle Instant Client for Linux x86-64 (64-bit)现在有一段时间了。 现在我注意到该网站在过去几个月中一直遇到技术问题。 要从 Oracle 下载
有什么方法可以将我的 Delphi 应用程序 (FireDac) 直接连接到 Oracle 数据库? 目前可以连接,但需要安装Oracle Client 在 embarcadero 站点 ( http
我有一张表,其中日期列的数据格式如下:“7/25/2014 12:14:27 AM”。我需要通过放入 where 子句来获取此日期。有人可以建议我该怎么做吗? 最佳答案 日期(存储在表中)是 repr
如果两个事务试图同时修改同一行会发生什么?通常,一旦行被修改,另一个事务等待直到第一个事务执行提交或回滚。但是,如果他们恰好在同一时刻发送更新请求怎么办? 最佳答案 答案是否定的。两个事务不能同时修改
我想知道为什么我不能在 Oracle 模式中有两个同名的索引?它抛出一个错误,指出该名称已被使用。我的印象是,由于索引在一个特定的表上,这应该不会导致任何错误,除非我们对同一个表上的两个不同列使用相同
我需要构建一个查询来按成员和到期日期检索信息组,但我需要为每个成员提供一个序列号.. 例如: 如果成员“A”有 3 条记录要过期,“B”只有 1 条,“C”有 2 条,我需要这样的结果: Number
独立程序 create procedure proc1 ( begin end; ) 存储过程 create package pkg1 ( procedure proc2 begin end; ) 最
在 Oracle 9i 中声明 FK 时遇到问题。我在这里查看了许多关于 SO 和一些在线文档(例如 http://www.techonthenet.com/oracle/foreign_keys/f
我和我的同事维护的应用程序在后端有一个 Oracle 数据库。我们正在考虑偶尔以“受限”模式运行应用程序,其中一个数据库表空间设置为只读。我们可以轻松地将必要的表和索引移动到单独的表空间,这些表空间将
我想实现一个自定义的回归聚合函数,类似于现有的 REGR_SLOPE . 我要定义的函数需要获取两列作为参数,例如 select T.EMPLOYEE_ID, CUSTOM_REGR_SL
我已经尝试解决这个问题一段时间了,我认为是时候寻求帮助了。我正在构建一个架构配置脚本,我想添加一些脚本输出和错误处理。这个想法是脚本输出窗口只会向我显示关键消息而没有所有噪音。 Create Temp
在旧的 Oracle 服务器(我被告知是 8i)上使用 JDBC 时,我遇到了一个非常令人困惑和奇怪的问题。我在那里准备了一个表,其中包含大约 10 列、数字、varchars、一个 raw(255)
我有一张 table Customer_Chronics在 Oracle 11g 中。 该表具有三个关键列,如下所示: 分支代码 客户 ID 期 我已按 branch_code 列表按表分区,现在我进
是否有存储用户自定义异常的oracle表? 最佳答案 没有。 与其他变量一样,用户定义的异常在 PL/SQL block 中定义,并且具有 PL/SQL 变量将具有的任何范围。所以,例如 DECLAR
在 oracle 中使用序列并使用 Before insert trigger 自动递增列或使用标识列是否更好,因为它在 Oracle 12 c 中可用? 最佳答案 无论哪种方式,您都将使用序列。 1
我是一名优秀的程序员,十分优秀!