At first glance, your plan seems OK. However, it would be better if we also could see the index definitions as well as get some information about index selectivity. And, this list removes attachments, so please post the text of the query as part of the message.

Set

Stef van der Merwe wrote:
I am not a SQL master but do know the basics.
I am have an issue with sub select queries and setting conditions.
Attached is the query I have a problem with, see my comments on the last condition.

P.S see below is my plan I am sure the query could be done better for faster performance any advise would be greatly appreciated !!

Plan

PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (STAFF INDEX (STAFF_IDX1))
PLAN (CUSTOMER INDEX (CUSTOMER_IDX1))
PLAN (CA INDEX (CASHCUSTOMER_IDX2, CASHCUSTOMER_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN SORT (JOIN (J INDEX (JOURNAL_IDX1, JOURNAL_IDX2, JOURNAL_IDX2), I INDEX (INVOICES_IDX5)))

Adapted Plan

PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (STAFF INDEX (STAFF_IDX1)) PLAN (CUSTOMER INDEX (CUSTOMER_IDX1)) PLAN (CA INDEX (CASHCUSTOMER_IDX2, CASHCUSTOMER_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN SORT (JOIN (J INDEX (JOURNAL_IDX1, JOURNAL_IDX2, JOURNAL_IDX2), I INDEX (INVOICES_IDX5)))

Prepare time = 16ms
Execute time = 15s 828ms
Avg fetch time = 465.53 ms
Current memory = 74 375 188
Max memory = 74 391 660
Memory buffers = 8 192
Reads from disk to cache = 253
Writes from cache to disk = 0
Fetches from cache = 730 569

[Non-text portions of this message have been removed]