1
-- 先收集统计信息
2
EXEC dbms_stats.gather_index_stats(ownname => 'SCHEMA', indname => 'INDEX_NAME');
3

4
-- 查询 CF 异常索引（CF > 行数/2 且 CF > 块数）
5
SELECT *
6
  FROM (
7
    SELECT d.clustering_factor, d.table_name, d.index_name,
8
           d.num_rows, d.last_analyzed
9
      FROM dba_indexes d
10
     WHERE d.clustering_factor > d.num_rows / 2
11
       AND d.clustering_factor > (
12
             SELECT t.blocks FROM dba_tables t
13
              WHERE t.table_name = d.table_name
14
                AND t.owner = d.table_owner
15
           )
16
     ORDER BY 1 DESC
17
  )
18
 WHERE rownum <= 10;

1
-- DELETE 操作
2
DELETE FROM mrp_schedule_dates
3
 WHERE schedule_level = 3
4
   AND (schedule_designator, organization_id) IN (
5
         SELECT input_designator_name, input_organization_id
6
           FROM mrp_plan_schedules_v
7
          WHERE organization_id = :b0
8
            AND compile_designator = :b1
9
            AND input_designator_type = 1
10
       )
11
   AND rownum <= :b2;
12

13
-- INSERT 操作（紧随其后）
14
INSERT INTO mrp_schedule_dates (
15
  inventory_item_id, reference_schedule_id, organization_id,
16
  schedule_designator, schedule_level, ...
17
)
18
SELECT inventory_item_id, reference_schedule_id, organization_id,
19
       schedule_designator, 3, ...
20
  FROM mrp_schedule_dates
21
 WHERE schedule_level = 2
22
   AND supply_demand_type = 1
23
   AND (organization_id, schedule_designator) IN (
24
         SELECT input_organization_id, input_designator_name
25
           FROM mrp_plan_schedules_v
26
          WHERE organization_id = :b2
27
            AND compile_designator = :b3
28
            AND input_designator_type = 1
29
       );

1
SELECT RCT.TRANSACTION_TYPE  TRANS_TYPE,
2
       RCT.TRANSACTION_ID    TRANS_ID,
3
       RCT.PARENT_TRANSACTION_ID PARENT_TRANS_ID,
4
       RCT.PRIMARY_QUANTITY  TRANS_QTY
5
  FROM RCV_SHIPMENT_LINES RSL,
6
       RCV_TRANSACTIONS   RCT
7
 WHERE RCT.SOURCE_DOCUMENT_CODE = 'PO'
8
   AND RSL.ITEM_ID = :B3
9
   AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
10
   AND RCT.TRANSACTION_TYPE = 'DELIVER'
11
   AND RCT.TRANSACTION_DATE BETWEEN :B2 AND :B1
12
   AND EXISTS (
13
         SELECT 1 FROM PO_HEADERS_ALL POH
14
          WHERE RSL.PO_HEADER_ID = POH.PO_HEADER_ID
15
            AND NVL(POH.VENDOR_SITE_ID, -99) = NVL(:B5, -99)
16
            AND POH.VENDOR_ID = :B4
17
            AND ROWNUM = 1
18
       )

1
SELECT MAX(rev.revision), items.inventory_item_id, items.organization_id
2
  FROM mtl_item_revisions       rev,   -- ← 此处为视图，含 TL 多语言表 JOIN
3
       mrp_system_items         items,
4
       mrp_plan_organizations_v mpo
5
 WHERE trunc(rev.effectivity_date) = (
6
         SELECT trunc(MAX(rev2.effectivity_date))
7
           FROM mtl_item_revisions rev2   -- ← 相关子查询自关联
8
          WHERE rev2.implementation_date IS NOT NULL
9
            AND rev2.effectivity_date <= (trunc(SYSDATE) + .99999)
10
            AND rev2.organization_id = rev.organization_id
11
            AND rev2.inventory_item_id = rev.inventory_item_id
12
       )
13
   AND rev.organization_id = items.organization_id
14
   AND rev.inventory_item_id = items.inventory_item_id
15
   AND items.organization_id = mpo.planned_organization
16
   AND items.compile_designator = mpo.compile_designator
17
   AND mpo.organization_id = :b0
18
   AND mpo.compile_designator = :b1
19
 GROUP BY items.inventory_item_id, items.organization_id