Inner Join Performance

Question

This is my first question on this forum, so be patient... My question is about the most efficient way to build a query on the following table:

create table OpenTrades( AccountNumber number, SnapshotTime date, Ticket number, OpenTime date, TradeType varchar2(4), TradeSize number, TradeItem char(6), OpenPrice number, TradeSL number, TradeTP number, TradeSwap number, TradeProfit number
); alter table OpenTrades add constraint OpenTrades_PK Primary Key (AccountNumber, SnapshotTime, Ticket) using index tablespace MyNNIdx;

This table is populated every 15 minutes, and the timestamp of the insert is saved in SnapshotTime column. The sought query should group records by Week number (based on SnapshotTime), and filter the records so that it would return those with the latest (SnapshotTime) within the same week.

So far, I've tried the following:

select max(ot2.SnapshotTime) from OpenTrades ot1, OpenTrades ot2 where to_date(ot1.SnapshotTime,'IW')=to_date(ot2.SnapshotTime,'IW');.

However, this takes way longer than expected (15 minutes for less than 1500 rows); I'm sure there's a much better way to write this, but so far, it has eluded me. Any idea? Thanks!

Answer 1

Try something like:

select AccountNumber, SnapshotTime, Ticket, ...
from (
    select AccountNumber, SnapshotTime, Ticket, ...
         , row_number() over (partition by AccountNumber, Ticket
                              order by SnapshotTime desc) as rn
    from OpenTrades
) T
where rn = 1;

I just reread the post and realized that I gave a solution for something different than the OP asked for, I guess something like:

select AccountNumber, SnapshotTime, Ticket, ...
from (
    select AccountNumber, SnapshotTime, Ticket, ...
         , row_number() over (to_date(SnapshotTime,'IW')
                              order by SnapshotTime desc) as rn
    from OpenTrades
) T
where rn = 1;

is closer to what the OP wants.

Answer 2

Try adding Virtual Column (11gR1+), plus an index on the virtual column (equivalent to a functional index).

create table OpenTrades(
  AccountNumber   number,
  SnapshotTime    date,
  Ticket          number,
  OpenTime        date,
  TradeType       varchar2(4),
  TradeSize       number,
  TradeItem       char(6),
  OpenPrice       number,
  TradeSL         number,
  TradeTP         number,
  TradeSwap       number,
  TradeProfit     number,
  WeekNumber      GENERATED ALWAYS AS (TRUNC(SnapshotTime, 'IW')) VIRTUAL,
  PRIMARY KEY (AccountNumber, SnapshotTime, Ticket)
);

CREATE INDEX openTrades_weeknumber_idx ON OpenTrades (AccountNumber, Ticket, WeekNumber);

And then load your data and try out this query. Hope it performs quickly and gives you the expected result.

SELECT *
  FROM OpenTrades ot1
  WHERE SnapshotTime = (SELECT max(SnapshotTime)
                        FROM OpenTrades
                        WHERE AccountNumber = ot1.AccountNumber
                          AND Ticket         = ot1.Ticket
                          AND WeekNumber     = ot1.WeekNumber);