SQL & PL/SQL

Hello All,

It's a long description post - hence, first and foremost, THANK YOU IN ADVANCE FOR HELP. Any input is much appreciated.

I'm working on a PL/SQL package that is called upon by a SOA service - passing in array of acct_id's and date range. Package has 2 main SQL's:

1. RANK() OVER sql:
This query accepts array of acct_id's and date range - grab and return 'n' TRAN_ID's (which is the primary and foreign key to child tables) - pagination occurs on this Query, hence I need to grab 'n' ID's.

This SQL takes 12 - 15 seconds to complete, is RANK_OVER good solution? any other solution that can give me top N rows faster?
I have tried using 'EXISTS' instead of 'IN'. I tried ORDER_BY / ROWNUM and did not notice major difference.

2. UNION sql:
Query takes in TRAN_ID's (1000 to 5000), from above rank_over SQL, and retrieves all parent and child records. Runs in 50 - 70 seconds.
The query takes a long time to execute, is there a different solution? I think Join defeats my purpose as there is lot of data - but please correct me if I'm wrong.

Observations
1. I had to use Oracle types in Rank() Over as array's in Java need to be passed to PL/SQL code.
2. I get the results - but very slow.
3. PARENT_TABLE has over 200 columns and over 600M rows - Child tables have approx 2M - 7M rows.
4. PARENT_TABLE is partioned monthly on RECDATE and TRAN_ID (Number) is primary key (foreign key to Child tables) with indexes on SYS_ID (Number), ACCT_ID (Varchar), RECDATE (Date)
5. I changed table/column names - hence please overlook if there is a typo in SQL's. I want to give out general idea.
6. I'm not an expert in PL/SQL or DB - I'm a Java guy doing the back end part also.
~~It gets more complex - I use dynamic SQL for RANK() OVER sql as I get any combination of 10 input params. But if I can get past below SQL's performance issue, I can take care of rest ~~

================Sql's=====================
1. RANK_OVER SQL -

SELECT TRAN_ID BULK COLLECT INTO TRAN_ARRAY
FROM (SELECT TRAN_ID, RANK() OVER(ORDER BY TRAN_ID ASC) TRAN_RANK
FROM PARENT_TABLE
WHERE ACCT_ID IN
(SELECT column_value
from THE (SELECT CAST(ACCT_LIST('1234567','9876543',...) AS ACCT_LIST)
FROM DUAL))
AND RECDATE BETWEEN TO_DATE('01/01/2009', 'MM/DD/YYYY') AND TO_DATE('11/30/2010', 'MM/DD/YYYY')
AND SYS_ID = 1)
WHERE TRAN_RANK < 3001;



2. UNION SQL:

SELECT
PARENT_COLUMN1,
PARENT_COLUMN2,
NULL AS CHILD1_COLUMN1,
NULL AS CHILD1_COLUMN2,
NULL AS CHILD2_COLUMN1,
NULL AS CHILD2_COLUMN2,
NULL AS CHILD3_COLUMN1,
NULL AS CHILD3_COLUMN2,
NULL AS CHILD4_COLUMN1,
NULL AS CHILD4_COLUMN2
FROM PARENT_TABLE WHERE WHERE TRAN_ID IN
(SELECT * FROM TABLE(CAST(NUMBER_LIST(1,2,3,..) AS NUMBER_LIST)))
UNION
SELECT
NULL AS PARENT_COLUMN1,
NULL AS PARENT_COLUMN2,
CHILD1_COLUMN1,
CHILD1_COLUMN2,
NULL AS CHILD2_COLUMN1,
NULL AS CHILD2_COLUMN2,
NULL AS CHILD3_COLUMN1,
NULL AS CHILD3_COLUMN2,
NULL AS CHILD4_COLUMN1,
NULL AS CHILD4_COLUMN2
FROM CHILD1_TABLE WHERE WHERE TRAN_ID IN
(SELECT * FROM TABLE(CAST(NUMBER_LIST(1,2,3,..) AS NUMBER_LIST)))
UNION
SELECT
NULL AS PARENT_COLUMN1,
NULL AS PARENT_COLUMN2,
NULL AS CHILD1_COLUMN1,
NULL AS CHILD1_COLUMN2,
CHILD2_COLUMN1,
CHILD2_COLUMN2,
NULL AS CHILD3_COLUMN1,
NULL AS CHILD3_COLUMN2,
NULL AS CHILD4_COLUMN1,
NULL AS CHILD4_COLUMN2
FROM CHILD2_TABLE WHERE WHERE TRAN_ID IN
(SELECT * FROM TABLE(CAST(NUMBER_LIST(1,2,3,..) AS NUMBER_LIST)))
UNION
SELECT
NULL AS PARENT_COLUMN1,
NULL AS PARENT_COLUMN2,
NULL AS CHILD1_COLUMN1,
NULL AS CHILD1_COLUMN2,
NULL AS CHILD2_COLUMN1,
NULL AS CHILD2_COLUMN2,
CHILD3_COLUMN1,
CHILD3_COLUMN2,
NULL AS CHILD4_COLUMN1,
NULL AS CHILD4_COLUMN2
FROM CHILD3_TABLE WHERE WHERE TRAN_ID IN
(SELECT * FROM TABLE(CAST(NUMBER_LIST(1,2,3,..) AS NUMBER_LIST)))
UNION
SELECT
NULL AS PARENT_COLUMN1,
NULL AS PARENT_COLUMN2,
NULL AS CHILD1_COLUMN1,
NULL AS CHILD1_COLUMN2,
NULL AS CHILD2_COLUMN1,
NULL AS CHILD2_COLUMN2,
NULL AS CHILD3_COLUMN1,
NULL AS CHILD3_COLUMN2,
CHILD4_COLUMN1,
CHILD4_COLUMN2
FROM CHILD4_TABLE WHERE WHERE TRAN_ID IN
(SELECT * FROM TABLE(CAST(NUMBER_LIST(1,2,3,..) AS NUMBER_LIST)));

Edited by: user5086796 on Dec 29, 2010 2:02 PM

Edited by: user5086796 on Dec 29, 2010 2:09 PM