Oracle subquery performance -

so have huge table ss(someid, somedate, ...). need join subset of table other table. subset determined by: select * ss someid in (select someid ss somedate between date1 , date2).

when running on oracle xa data server in parallel, execution takes long time , temp space, though oracle can cell offloading efficiency of 99% on ss table, subset query still bring large amount of data database server in joining other table.

is there anyway make more efficient? such oracle doesn't have send data , utilize more of cell offloading efficiency?

below query plan

plan_table_output plan hash value: 3198983388  --------------------------------------------------------------------------------------------------------------------------------------- | id  | operation                               | name           | rows  | bytes | cost (%cpu)| time     |    tq  |in-out| pq distrib | --------------------------------------------------------------------------------------------------------------------------------------- |   0 | select statement                        |                |  1044k|   589m| 46101   (1)| 00:01:33 |        |      |            | |   1 |  px coordinator                         |                |       |       |            |          |        |      |            | |   2 |   px send qc (random)                   | :tq10003       |  1044k|   589m| 46101   (1)| 00:01:33 |  q1,03 | p->s | qc (rand)  | |*  3 |    hash join buffered                   |                |  1044k|   589m| 46101   (1)| 00:01:33 |  q1,03 | pcwp |            | |   4 |     px receive                          |                |       |       |            |          |  q1,03 | pcwp |            | |   5 |      px send hash                       | :tq10001       |       |       |            |          |  q1,01 | p->p | hash       | |   6 |       nested loops                      |                |       |       |            |          |  q1,01 | pcwp |            | |   7 |        nested loops                     |                |   523k|   135m| 38264   (1)| 00:01:17 |  q1,01 | pcwp |            | |   8 |         sort unique                     |                | 29402 |   401k| 13751   (1)| 00:00:28 |  q1,01 | pcwp |            | |   9 |          px receive                     |                | 29402 |   401k| 13751   (1)| 00:00:28 |  q1,01 | pcwp |            | |  10 |           px send hash                  | :tq10000       | 29402 |   401k| 13751   (1)| 00:00:28 |  q1,00 | p->p | hash       | |  11 |            px block iterator            |                | 29402 |   401k| 13751   (1)| 00:00:28 |  q1,00 | pcwc |            | |* 12 |             index storage fast full scan| superset_idx1  | 29402 |   401k| 13751   (1)| 00:00:28 |  q1,00 | pcwp |            | |* 13 |         index range scan                | xu_superset_01 |    18 |       |     1   (0)| 00:00:01 |  q1,01 | pcwp |            | |  14 |        table access index rowid      | superset       |    18 |  4644 |     2   (0)| 00:00:01 |  q1,01 | pcwp |            | |  15 |     px receive                          |                |  2886k|   880m|  7834   (2)| 00:00:16 |  q1,03 | pcwp |            | |  16 |      px send hash                       | :tq10002       |  2886k|   880m|  7834   (2)| 00:00:16 |  q1,02 | p->p | hash       | |  17 |       px block iterator                 |                |  2886k|   880m|  7834   (2)| 00:00:16 |  q1,02 | pcwc |            | |  18 |        table access storage full        | pol_dtl        |  2886k|   880m|  7834   (2)| 00:00:16 |  q1,02 | pcwp |            | ---------------------------------------------------------------------------------------------------------------------------------------  predicate information (identified operation id): ---------------------------------------------------     3 - access(ss.pol_id=pd.pol_id)   12 - storage(impt_dt<=to_date(' 2014-11-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') , impt_dt>=to_date(' 2014-10-28                00:00:00', 'syyyy-mm-dd hh24:mi:ss'))        filter(impt_dt<=to_date(' 2014-11-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') , impt_dt>=to_date(' 2014-10-28                00:00:00', 'syyyy-mm-dd hh24:mi:ss'))   13 - access(ss.pol_id=pol_id)  note -----    - degree of parallelism 4 because of session 

there may not can improve query. execution plan looks pretty good:

1. good objects indexes seem fit query well, although it's hard tell without full definitions.
2. good cardinality estimated rows , actual rows close. implies optimizer doing job , picking near-optimal plan. if can estimate number of rows correctly make wise decisions access paths, join methods, join order, etc. time estimate close, rare. looks there table , system statistics.
3. cell offloading storage predicates , active report cell offloading imply cell offloading working expected, @ least once.
4. parallelism large objects being processed in parallel. don't see obvious parallel problems.

here ideas improvement don't expect drastic improvements:

1. full table scan force full table scan instead of index range scan hint --+ no_index(superset xu_superset_01). multiblock reads (use full scans) , cell offloading (used direct path read full scan not used index range scan uses buffer cache), full table scan reading data may more efficient index range scan reading less data.
2. covering index if full table scan doesn't work, create skinny version of table index includes returned , queried columns. gets benefits of full scans (multiblock io, cell offloading) smaller full table.
3. larger dop there's no magic number degree of parallelism (dop). in experience dop sweet-spot larger 4. may improve performance use more resources.

4. rewrite query? re-writing query may enable smart scan process join in storage cells. try changing

   select * ss someid in    (select someid ss somedate between date1 , date2) 

   to

   select distinct ss1.* ss ss1 join ss ss2     on ss1.someid = ss2.someid      , ss2.somedate between date1 , date2 

   this new version work. join returns more rows necessary, , need made distinct. work may worth if means join can happen in storage cells. can't find great source kind of processing can offloaded, @ least types of joins can be.