Ask Tom HOT ARTICLES

1. Optimizing Recursive SQL Query Performance

   Problem Statement I am working with a large dataset (~11-12 million records), where I need to identify key out-of-stock (OOS) event dates for different products (MASTER_ID) across multiple marketplaces (MARKETPLACE_ID). Each record in the DETAILED_OOS_EVENTS table represents an OOS event on a particular date, and the combination of (MASTER_ID, MARKETPLACE_ID, OOS_DATE) is always unique. Goal of the Query I need to: Find the earliest OOS event (MIN(OOS_DATE)) for each (MASTER_ID, MARKETPLACE_ID). Recursively find the next OOS event, which occurs at least 7 days after the previous event. Repeat this process until no more OOS events satisfy the condition. Issue I am using a recursive CTE (WITH RECURSIVE), but since the dataset is large (~11-12M rows), the query takes too long to execute. I?m looking for ways to optimize it. I have provided table structure, sample data and current query as a livesql script. Script URL: https://livesql.oracle.com/next/library/scripts/recursive-query-for-oos-events-47UhGS

2. Parallel index creation on different table on same tablespace

   I?m having trouble with the parallel execution of an Oracle SQL query for creating indexes on <b>separate table (assume each table have single index)</b>. The performance boost isn?t even close to what I was hoping for. I therefore need your help in determining what to check for in the Oracle DB host in order to troubleshoot the issue. Although, I found all SQL queries having WAIT_CLASS as ?USER I/O?. I also did a check on IOPS through NetData UI on parallel index creation env, its max observed near ~24%. Just to notice, we already aware that PARALLEL hint may help here, but we are looking further improvement on top of that. Please let me know, whether any tunning on Oracle DB Side required or We can assume there won't be any benefit by executing index creation in parallel (even on different table) and we can only specify PARALLEL degree to enhance the performance.

3. Execution Plan sharing across containers

   If a SQL statement in pdb container A is <b>exactly</b> the same SQL statement as in pdb container B, will they share the same execution plan even though the owner of the tables and the user owning the session (including GTTs) will be different in each container?

4. Sub queries not working if db link used

   hi Tom, when a remote table is referred in sub query then it is not retrieving the data and it throws error. refer the below sql, I am trying to get latest record, so I am doing order by DATEM in sub query and taking first record in outer sql. here, OWNERDOCUMENTS used in FROM clause is a synonym created for a remote table. The below sql not working! <code>select ownercode,img_path_back,img_path_front,DELETED_FLG,useridm,DATEM FROM ( select ownercode,img_path_back,img_path_front,customer_name,NVL(DELETED_FLG,'N') DELETED_FLG,useridm,DATEM from OWNERDOCUMENTS where OWNERCODE=NVl('xxxx',OWNERCODE) Order By DATEM DESC ) WHERE ROWNUM=1;</code> The sql below is working!! <code>select ownercode,img_path_back,img_path_front,customer_name,NVL(DELETED_FLG,'N') DELETED_FLG,useridm,DATEM from OWNERDOCUMENTS where OWNERCODE=NVl('xxxx',OWNERCODE) Order By DATEM DESC; </code> what could be the issue? please advice.

5. Solving a logical problem using analytical functions

   (https://livesql.oracle.com/ords/livesql/s/da34i74lkmxt2mqrtp0k4xsk6 ) (I was able to format better with screenshots on the Oracle forums - https://forums.oracle.com/ords/apexds/post/help-solving-a-logical-problem-using-analytical-query-3778) Hi, I am trying to solve a problem using analytical functions but I am stuck. 1. I have a list of coupons that I can use. The usage sequence is in alphabetical order of the coupon name. +---------+-------+ | Coupons | Value | +---------+-------+ | A | 100 | +---------+-------+ | B | 40 | +---------+-------+ | C | 120 | +---------+-------+ | D | 10 | +---------+-------+ | E | 200 | +---------+-------+ 2. There is a limit (cap) on the total value across all coupons that can be used in a day +----------+-----------+ | Cap Name | Cap Limit | +----------+-----------+ | Cap 1 | 150 | +----------+-----------+ | Cap 2 | 70 | +----------+-----------+ 3. Each coupon is subject to 1 or 2 caps. If it is subject to 2 caps, there is a specified sequence to apply caps. +--------+--------------+----------+ | Coupon | Cap Sequence | Cap Name | +--------+--------------+----------+ | A | 1 | Cap 1 | +--------+--------------+----------+ | A | 2 | Cap 2 | +--------+--------------+----------+ | B | 1 | Cap 2 | +--------+--------------+----------+ | C | 1 | Cap 2 | +--------+--------------+----------+ | C | 2 | Cap 1 | +--------+--------------+----------+ | D | 1 | Cap 1 | +--------+--------------+----------+ | E | 1 | Cap 1 | +--------+--------------+----------+ | E | 2 | Cap 2 | +--------+--------------+----------+ 4. I have to now find how much coupon value could be utilized before my daily cap was reached. i.e. find "coupon usage" and ?Cap Remaining? below. So, If I join the tables above +---+--------+-------+----------+--------------+-----------+--------------+---------------+ | # | Coupon | Value | Cap Name | Cap Sequence | Cap Limit | Coupon Usage | Cap Remaining | +---+--------+-------+----------+--------------+-----------+--------------+---------------+ | 1 | A | 100 | Cap 1 | 1 | 150 | 100 | 50 | +---+--------+-------+----------+--------------+-----------+--------------+---------------+ | 2 | A | 100 | Cap 2 | 2 | 70 | 0 | 70 | +---+--------+-------+----------+--------------+-----------+--------------+---------------+ | 3 | B | 40 | Cap 2 | 1 | 70 | 40 | 30 | +---+--------+-------+----------+--------------+-----------+--------------+---------------+ | 4 | C | 120 | Cap 2 | 1 | 70 | 30 | 0 | +---+--------+-------+----------+--------------+-----------+--------------+---------------+ | 5 | C | 120 | Cap 1 | 2 | 150 | 50 | 0 | +---+--------+-------+----------+--------------+-----------+--------------+---------------+ | 6 | D | 10 | Cap 1 | 1 | 150 | 0 | 0 | +---+--------+-------+----------+--------------+-----------+--------------+---------------+ | 7 | E | 200 | Cap 1 | 1 | 150 | 0 | 0 | +---+--------+-------+----------+--------------+-----------+--------------+---------------+ | 8 | E | 200 | Cap 2 | 2 | 70 | 0 | 0 | +---+--------+-------+----------+--------------+-----------+--------------+---------------+ Explanation: Row #1 : Coupon A has a value of 100 and that is less than the first cap i.e. Cap 1 with a limit...