Hello,
I am running out of luck trying to improve the runtime of a query. I am on Oracle 12.1.0.2 Standard Edition (yes I know…) and this is the plan I get:
select /*+ gather_plan_statistics */ pos.* from pos where trade in (select trade from trade where (itrade = :SYS_B_0 or trade in (select itrade from trade where trade.trade = :SYS_B_1 ) and trade.trade <> :SYS_B_2));
Plan hash value: 1854865082
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:17.82 | 3447K| | | |
|* 1 | FILTER | | 1 | | 1 |00:00:17.82 | 3447K| | | |
|* 2 | HASH JOIN | | 1 | 3155K| 3214K|00:00:11.58 | 233K| 190M| 13M| 202M (0)|
| 3 | INDEX FULL SCAN | IDX3 | 1 | 3206K| 3208K|00:00:00.77 | 16526 | | | |
| 4 | TABLE ACCESS FULL| POS | 1 | 3204K| 3222K|00:00:02.48 | 217K| | | |
|* 5 | INDEX RANGE SCAN | IDX3 | 3213K| 1 | 0 |00:00:03.30 | 3213K| | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ITRADE"=SYS_OP_C2C(:SYS_B_0) OR ("TRADE"."TRADE"<>SYS_OP_C2C(:SYS_B_2) AND IS NOT NULL)))
2 - access("TRADE"="TRADE")
5 - access("TRADE"."TRADE"=SYS_OP_C2C(:SYS_B_1) AND "ITRADE"=:B1)
Table POS:
3m rows
size: 1.7GB
Table TRADE:
3m rows
primary key column: trade
size: 2.1GB
Index IDX03 on trade(trade, itrade)
I know the query could be improved by rewriting it with a union, but unfortunately the SQL code cannot be changed. Any ideas are welcome :-)
Jochen