Link to home
Create AccountLog in
Avatar of NewPrgmr
NewPrgmr

asked on

DB performance issue with top N rows and Union SQL

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;

Open in new window




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

Open in new window

SOLUTION
Avatar of Ora_Techie
Ora_Techie

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of NewPrgmr
NewPrgmr

ASKER

@riazpk - Thanks for the tip. I'll use the hints and get the explain plans before and after.
1. Regarding your suggestion for pagination, is it faster? I can paginate with my RANK OVER sql but of course its slower.
2. What about UNION sql? Any suggestions for that? Any good way of passing 1000's of TRAN_ID's and retrieve parent and child records?

Thanks.
@NewPrgmr

Here are answers to your questions:

(1) For me, it worked perfectly but your mileage may vary, i would say. Test, Test, Test. It is all about testing. In your query (using rank), it will have to scan whole table to assign rank, while in rownum query, it will stop at maxrow.

But, most importantly, you need to make sure that the resultsets are same since they MIGHT  (I stress, MIGHT) return different resultsets. So you need to be careful.

(2) After applying cardinality hint, it should not be a performance problem provided there is nice index on trans_id.

To minimize processing, you can do something like:
WITH data as
(SELECT /*+cardinality(t 10) */ FROM
(SELECT * FROM TABLE(CAST(NUMBER_LIST(1, 2, 3, ..) AS NUMBER_LIST) t)
)
SELECT < Columns>
FROM <Table>
WHERE tran_id IN (SELECT * FROM data)
UNION
SELECT < Columns>
FROM <Table>
WHERE tran_id IN (SELECT * FROM data)
.....

Open in new window

One thing i forgot to mention: To pass 1000's of tran_ids, you can use global temporary tables (provided your java & PL/SQL share the same sessions).
Looking at your UNION query makes me to think that you have distinct sets in each UNION-ed query. Therefore you don't need UNION (make the set distinct) but rather UNION ALL. This will greatly improve your performance.
@ Venkotch - Instead of 'UNION' in my query, I did try 'UNION ALL' and 'order by..' at the end but that did not throw up spectacular numbers.

@riazpk - Thank you for the hints. I'm skeptic on your paging solution - but I'll try it (doesn't hurt). I've been on the issue for the past few months so at this point, I'm going to try everything.
I never tried cardinality hint before, I'll use that too.

Because of performance issues, DBA is working on re-designing tables. So it will take me at least 1 day to get back to you guys with results. So please stay tuned. I will come back with results.

Just some fodder in the meantime - does below tkprof (with my current RANK n UNION) give out any clues? I believe the issue, instead of PL/SQL logic, could also be in DB and how tables are designed.

Let me know and I can provide table/index sizes.

**************************************************************************************************
RANK() OVER
**************************************************************************************************
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.23          3         12          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      1.16      70.33       3202       3215          0        1001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      1.17      70.57       3205       3227          0        1001

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64 (recursive depth: 1)

_Rows     Row Source Operation_
   1001  VIEW  (cr=3215 pr=3202 pw=0 time=71465216 us)
  12087   WINDOW SORT PUSHED RANK (cr=3215 pr=3202 pw=0 time=75263413 us)
  12087    TABLE ACCESS BY LOCAL INDEX ROWID PARENT_TABLE (cr=3215 pr=3202 pw=0 time=63101654 us)
  12089     NESTED LOOPS  (cr=73 pr=68 pw=0 time=7107975 us)
      1      VIEW  VW_NSO_1 (cr=0 pr=0 pw=0 time=603 us)
      1       HASH UNIQUE (cr=0 pr=0 pw=0 time=598 us)
      1        COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=17 us)
      2         FAST DUAL  (cr=0 pr=0 pw=0 time=3 us)
  12087      PARTITION RANGE ITERATOR PARTITION: 13 35 (cr=73 pr=68 pw=0 time=151932 us)
  12087       INDEX RANGE SCAN PARENT_TABLE_LCL_IDX01 PARTITION: 13 35 (cr=73 pr=68 pw=0 time=1913677 us)(object id 61343)

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
.........................................   Waited  ........................................
  db file sequential read                      3202        1.53         64.28

********************************************************************************************
UNION
********************************************************************************************

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.04       0.09          0         11          0           0
Fetch        3      2.57      69.38      11425      19199          0        1525
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      2.62      69.49      11425      19210          0        1525
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 63  
 
Rows     Row Source Operation
-------  ---------------------------------------------------
   1525  SORT UNIQUE (cr=19199 pr=11425 pw=0 time=62134287 us)
   1525   UNION-ALL  (cr=19199 pr=11425 pw=0 time=41972936 us)
   1000    NESTED LOOPS  (cr=4002 pr=1105 pw=0 time=10231964 us)
   1000     COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=5026 us)
   1000     TABLE ACCESS BY GLOBAL INDEX ROWID TRANSACTION PARTITION: ROW LOCATION ROW LOCATION (cr=4002 pr=1105 pw=0 time=16492325 us)
   1000      INDEX UNIQUE SCAN TRANSACTION_GLBL_PK (cr=3002 pr=1105 pw=0 time=16425714 us)(object id 13560468)
      0    TABLE ACCESS BY GLOBAL INDEX ROWID TRANSACTION_APPLIED PARTITION: ROW LOCATION ROW LOCATION (cr=2008 pr=425 pw=0 time=2213555 us)
   1001     NESTED LOOPS  (cr=2008 pr=425 pw=0 time=5035031 us)
   1000      COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=2027 us)
      0      INDEX RANGE SCAN TRANSACTION_APPLIED_GLBL_PK (cr=2008 pr=425 pw=0 time=2210233 us)(object id 13560470)
      0    HASH JOIN  (cr=7415 pr=7316 pw=0 time=7268768 us)
   1000     COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=24 us)
 600150     PARTITION RANGE ALL PARTITION: 1 42 (cr=7415 pr=7316 pw=0 time=8591112 us)
 600150      TABLE ACCESS FULL TRANSACTION_INSURANCE PARTITION: 1 42 (cr=7415 pr=7316 pw=0 time=8427470 us)
      0    TABLE ACCESS BY GLOBAL INDEX ROWID TRANSACTION_SMA PARTITION: ROW LOCATION ROW LOCATION (cr=2002 pr=921 pw=0 time=15765833 us)
   1001     NESTED LOOPS  (cr=2002 pr=921 pw=0 time=17549033 us)
   1000      COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=4026 us)
      0      INDEX RANGE SCAN TRANSACTION_SMA_GLBL_PK (cr=2002 pr=921 pw=0 time=15756168 us)(object id 13560472)
    525    TABLE ACCESS BY GLOBAL INDEX ROWID TRANSACTION_STREET_TRADE PARTITION: ROW LOCATION ROW LOCATION (cr=3772 pr=1658 pw=0 time=31050187 us)
   1526     NESTED LOOPS  (cr=3002 pr=988 pw=0 time=12087189 us)
   1000      COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=5021 us)
    525      INDEX RANGE SCAN TRANSACTION_STRT_TRD_GLBL_PK (cr=3002 pr=988 pw=0 time=11575519 us)(object id 13560473)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       5        0.00          0.00
  SQL*Net more data to client                   671        2.88          7.18
  SQL*Net message from client                     5        0.85          2.49
  SQL*Net more data from client                   3        0.00          0.00
  db file sequential read                      4160        0.28         54.57
  db file scattered read                        541        0.27          6.09

Open in new window

NewPrgmr,

1) You should use UNION ALL. You may not see huge difference if your results-sets you are trying to union are small but in any case UNION forces oracle to do extra work and eliminate the duplicates between the result sets.

2) I am curious how your TRANS_ID is maintained. You may be using the analytic for a large populations (all trans_ids within the query) and they are not partitioned in any way. This could be a performance problem - your WINDOW to sort is too large. Here is what I mean. If you have TRANS_IDs created withing a date range (the one you partiion table by) or within accounts, you should you the same partiotion for tha analytic, i.e. (example for 1 month).
    RANK() OVER(PARTITION BY TRUNC(RECDATE, 'MM') ORDER BY TRAN_ID ASC)
or
    RANK() OVER(PARTITION BY ACCT_ID ORDER BY TRAN_ID ASC)

You cound be sorting in a window with 3 MIL columns to rank them and at the end just using first 3000.

I certainly don't know your application but what I see does not makes sense to me. You are pretty much using the rank() as stop-key on the whole query but the way rank() is calculated is not clear. You can get unpredictable (different) results. RANK() will grade the same TRANS_IDs within your query the same way. So "3000" does not mean anything.

I hope I explained in a way you understand what I am trying to say. If you give more details of how TRANS_ID is generated and how it lives accross the dates, accounts and etc. AND what you are trying to achieve by stop-keying on 3000, I might be able to help more.

But this is all about the first query.

About the second - how long it takes to one union-ed query to complete?
I see explain plan is using nested loops - is the result set you get comparable to the inner-table row count (3000 in this case I guess)? Try to hint the query to use hash join and see if any improvement. cardinality(3) is bad hint and may force CBO for nested loops. you cardinality is 3000.
Guys - sorry it took this long to get back but DB was having issues and finally got some explain plans:

@riazpk -
1. As you suggested, I tried your RANK solution (also found the same solution in asktom.com and modified it accordingly "http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:76812348057") below are the explain plans for them

Notes: I did not notice big gains - there is index on TRANS_SRGT_ID - Tried 'With clause in UNION' but did not notice significant gains.
 
SELECT TRANS_SRGT_ID
  FROM (SELECT TRANS_SRGT_ID,
               RANK() OVER(ORDER BY TRANS_SRGT_ID ASC) TRAN_RANK
          FROM TRANSACTION
         WHERE SRC_SYS_ACCT_NBR IN
               (SELECT column_value
                  from THE (SELECT /*+cardinality(t 3) */ CAST(ACCT_LIST('1','11','111') AS ACCT_LIST) FROM DUAL))
           AND RECDATE BETWEEN TO_DATE('01/01/2009', 'MM/DD/YYYY')
           AND TO_DATE('07/30/2010', 'MM/DD/YYYY')
           AND SRC_SYS_ID = 1)
 WHERE TRAN_RANK < 1000;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3989989181

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                      | 84543 |  2146K|       |  6208   (1)| 00:00:35 |       |       |
|*  1 |  VIEW                                       |                      | 84543 |  2146K|       |  6208   (1)| 00:00:35 |       |       |
|*  2 |   WINDOW SORT PUSHED RANK                   |                      | 84543 |  4128K|    10M|  6208   (1)| 00:00:35 |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID       | TRANSACTION          | 84543 |  2311K|       |  6169   (1)| 00:00:35 | ROWID | ROWID |
|   4 |     NESTED LOOPS                            |                      | 84543 |  4128K|       |  6206   (1)| 00:00:35 |       |       |
|   5 |      VIEW                                   | VW_NSO_1             |  8168 |   175K|       |    35   (0)| 00:00:01 |       |       |
|   6 |       HASH UNIQUE                           |                      |     1 | 16336 |       |            |          |       |       |
|   7 |        COLLECTION ITERATOR CONSTRUCTOR FETCH|                      |       |       |       |            |          |       |       |
|   8 |         FAST DUAL                           |                      |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|*  9 |      INDEX RANGE SCAN                       | TRANSACTION_ACC_GLBL | 84543 |       |       |    57   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TRAN_RANK"<1000)
   2 - filter(RANK() OVER ( ORDER BY "TRANS_SRGT_ID")<1000)
   3 - filter("SRC_SYS_ID"=1)
   9 - access("SRC_SYS_ACCT_NBR"="$nso_col_1" AND "RECDATE">=TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "RECDATE"<=TO_DATE(' 2010-07-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

25 rows selected.

select * 
  from ( select a.*, rownum r 
           from ( SELECT TRANS_SRGT_ID
                    from TRANSACTION
                   WHERE SRC_SYS_ACCT_NBR IN
               (SELECT column_value
                  from THE (SELECT /*+cardinality(t 3) */ CAST(ACCT_LIST('2','22','222') AS ACCT_LIST) FROM DUAL))
           AND RECDATE BETWEEN TO_DATE('01/01/2009', 'MM/DD/YYYY')
           AND TO_DATE('07/30/2010', 'MM/DD/YYYY')
           AND SRC_SYS_ID = 1 
           ORDER BY TRANS_SRGT_ID) a
          where rownum < 1001 )
 where r > 0;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 586384496

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                      |  1000 | 26000 |       |  6208   (1)| 00:00:35 |       |       |
|*  1 |  VIEW                                         |                      |  1000 | 26000 |       |  6208   (1)| 00:00:35 |       |       |
|*  2 |   COUNT STOPKEY                               |                      |       |       |       |            |          |       |       |
|   3 |    VIEW                                       |                      | 84543 |  1073K|       |  6208   (1)| 00:00:35 |       |       |
|*  4 |     SORT ORDER BY STOPKEY                     |                      | 84543 |  4128K|    10M|  6208   (1)| 00:00:35 |       |       |
|*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID       | TRANSACTION          | 84543 |  2311K|       |  6169   (1)| 00:00:35 | ROWID | ROWID |
|   6 |       NESTED LOOPS                            |                      | 84543 |  4128K|       |  6206   (1)| 00:00:35 |       |       |
|   7 |        VIEW                                   | VW_NSO_1             |  8168 |   175K|       |    35   (0)| 00:00:01 |       |       |
|   8 |         HASH UNIQUE                           |                      |     1 | 16336 |       |            |          |       |       |
|   9 |          COLLECTION ITERATOR CONSTRUCTOR FETCH|                      |       |       |       |            |          |       |       |
|  10 |           FAST DUAL                           |                      |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|* 11 |        INDEX RANGE SCAN                       | TRANSACTION_ACC_GLBL | 84543 |       |       |    57   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R">0)
   2 - filter(ROWNUM<1001)
   4 - filter(ROWNUM<1001)
   5 - filter("SRC_SYS_ID"=1)
  11 - access("SRC_SYS_ACCT_NBR"="$nso_col_1" AND "RECDATE">=TO_DATE(' 2009-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "RECDATE"<=TO_DATE(' 2010-07-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

28 rows selected.

Open in new window

@ venkotch -
I understood your comments on UNION-cardinality hint but lost on RANK comments...
Table is partitioned on RECDATE (monthly). TRAN_ID is the Primary key and foreign key on child table. The reason we partitioned on TRAN_ID is due to fact that my input can be: (ACCT_ID, RECDATE's) or (TRAN_TYPE, RECDATE's) or some other combination - DB package is designed to accept 10 input parameters (I'm constructing dynamic SQL's with the input parameters).
 At present I'm dealing with (ACCT_ID, RECDATE) combination and trying to solve the performance issue. If I can get over this bump, I'm pretty sure I can deal with other combinations - granted I understand that table structure with indexes might NOT solve all the combinations.
Let me know if above explanation does not help. In any case, I've forwarded your comments to DBA, perhaps he can throw some light.

I've used UNION ALL vs UNION and below are explain plans for them:

UNION ALL
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3217175846

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                               |   824 | 79505 |    76   (2)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                        |                               |   824 | 79505 |    75  (80)| 00:00:01 |       |       |
|   2 |   UNION-ALL                           |                               |       |       |            |          |       |       |
|   3 |    INLIST ITERATOR                    |                               |       |       |            |          |       |       |
|   4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION                   |   100 | 34400 |    15   (0)| 00:00:01 | ROWID | ROWID |
|*  5 |      INDEX UNIQUE SCAN                | TRANSACTION_GLBL_PK           |   100 |       |    15   (0)| 00:00:01 |       |       |
|   6 |    INLIST ITERATOR                    |                               |       |       |            |          |       |       |
|   7 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_APPLIED           |   127 |  8001 |    15   (0)| 00:00:01 | ROWID | ROWID |
|*  8 |      INDEX RANGE SCAN                 | TRANSACTION_APPLIED_GLBL_PK   |   127 |       |    15   (0)| 00:00:01 |       |       |
|   9 |    INLIST ITERATOR                    |                               |       |       |            |          |       |       |
|  10 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_INSURANCE         |   399 | 23541 |    15   (0)| 00:00:01 | ROWID | ROWID |
|* 11 |      INDEX RANGE SCAN                 | TRANSACTION_INSURANCE_GLBL_PK |   399 |       |    14   (0)| 00:00:01 |       |       |
|  12 |    INLIST ITERATOR                    |                               |       |       |            |          |       |       |
|  13 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_SMA               |    99 |  5148 |    15   (0)| 00:00:01 | ROWID | ROWID |
|* 14 |      INDEX RANGE SCAN                 | TRANSACTION_SMA_GLBL_PK       |    99 |       |    15   (0)| 00:00:01 |       |       |
|  15 |    INLIST ITERATOR                    |                               |       |       |            |          |       |       |
|  16 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_STREET_TRADE      |    99 |  8415 |    15   (0)| 00:00:01 | ROWID | ROWID |
|* 17 |      INDEX RANGE SCAN                 | TRANSACTION_STRT_TRD_GLBL_PK  |    99 |       |    15   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------


UNION
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4193038666

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                               |   832 | 80285 |    81  (82)| 00:00:01 |       |       |
|   1 |  SORT UNIQUE                          |                               |   832 | 80285 |    81  (82)| 00:00:01 |       |       |
|   2 |   UNION-ALL                           |                               |       |       |            |          |       |       |
|   3 |    INLIST ITERATOR                    |                               |       |       |            |          |       |       |
|   4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION                   |   101 | 34744 |    15   (0)| 00:00:01 | ROWID | ROWID |
|*  5 |      INDEX UNIQUE SCAN                | TRANSACTION_GLBL_PK           |   101 |       |    15   (0)| 00:00:01 |       |       |
|   6 |    INLIST ITERATOR                    |                               |       |       |            |          |       |       |
|   7 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_APPLIED           |   128 |  8064 |    15   (0)| 00:00:01 | ROWID | ROWID |
|*  8 |      INDEX RANGE SCAN                 | TRANSACTION_APPLIED_GLBL_PK   |   128 |       |    15   (0)| 00:00:01 |       |       |
|   9 |    INLIST ITERATOR                    |                               |       |       |            |          |       |       |
|  10 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_INSURANCE         |   403 | 23777 |    15   (0)| 00:00:01 | ROWID | ROWID |
|* 11 |      INDEX RANGE SCAN                 | TRANSACTION_INSURANCE_GLBL_PK |   403 |       |    15   (0)| 00:00:01 |       |       |
|  12 |    INLIST ITERATOR                    |                               |       |       |            |          |       |       |
|  13 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_SMA               |   100 |  5200 |    15   (0)| 00:00:01 | ROWID | ROWID |
|* 14 |      INDEX RANGE SCAN                 | TRANSACTION_SMA_GLBL_PK       |   100 |       |    15   (0)| 00:00:01 |       |       |
|  15 |    INLIST ITERATOR                    |                               |       |       |            |          |       |       |
|  16 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_STREET_TRADE      |   100 |  8500 |    15   (0)| 00:00:01 | ROWID | ROWID |
|* 17 |      INDEX RANGE SCAN                 | TRANSACTION_STRT_TRD_GLBL_PK  |   100 |       |    15   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------

Open in new window

@ venkotch - to further fill you in.
Records are inserted into DB and TRAN_ID is a unique (DB) sequence value generated to identify each record.
For example a record might have ACCT_ID, RECDATE(sysdate), TRANTYPE, QTY, etc.... when inserted into DB, a TRAN_ID is generated and inserted along with it:
TRAN_ID, ACCT_ID, RECDATE(sysdate), TRANTYPE, QTY, etc....
In a month 50M records are inserted - so 50M TRAN_ID's are inserted along with the records - partitioned for that month. So across dates, TRAN_ID's will be incrementing.

PS: For simplicity sake, I used TRAN_ID - but in actually, TRAN_ID refers to TRAN_SRGT_ID, ACCT_ID refers to SRC_SYS_ACCT and so on...
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Guys - I closed the question and assigned relevant points. There have been lot of changes on my end. I tried several different approaches (some of them listed by you also). Lot of experts, in our company, chipped in with resolution also. In the end, our application scope was changed. We have changed our DB design and now retrieving less results - but faster.
Thank you for the help and support.