Hi All



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.



Please assist me if you can.



Stef van der Merwe



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)))



------ Performance info ------

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]