WITH E AS (
  SELECT ID
  FROM A
  WHERE type = 2
      AND DELETE_FLAG = 0
      AND uid = 41
  UNION
  SELECT a.ID
  FROM A a, B b, A c
  WHERE a.type = 3
      AND a.DELETE_FLAG = 0
      AND a.uid = 41
      AND a.ID = b.ID
      AND b.parent_id = c.ID
)
SELECT COUNT(1)
FROM D d, E e, A t, F f
WHERE d.DELETE_FLAG = 0
  AND d.ID_ = e.ID
  AND e.ID = t.ID
  AND d.aid = f.ID
  AND f.DELETE_FLAG = 0

其中 D 表最大，有 37M(37,000,000)的数据，A 表 410k 数据，F 表 165k 数据

SQL 语句是查询一个数量，在原来 Oracle 下 15s 左右就可以查询；迁移 其他数据库后需要 100s 。请问各路大神有什么好的解决方法？ 目前 D 表已经是分表了，再分表也不现实； D 表的（ DELETE_FLAG,ID,aid ）也加了联合索引。 请问有没有其他的优化方法。 执行计划如下：

PLAN_TABLE_OUTPUT
Plan hash value: 244456078

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |     1 |    43 |       |   420K  (1)| 01:24:03 |
|   1 |  SORT AGGREGATE                          |                           |     1 |    43 |       |            |          |
|*  2 |   HASH JOIN                              |                           |    22M|   906M|  3104K|   420K  (1)| 01:24:03 |
|*  3 |    TABLE ACCESS FULL                     | FULL_ATTRIBUTE_RELATION   |   151K|  1327K|       |   548   (1)| 00:00:07 |
|*  4 |    HASH JOIN                             |                           |    21M|   707M|  7312K|   371K  (1)| 01:14:24 |
|   5 |     INDEX FAST FULL SCAN                 | SYS_C0019687              |   415K|  2436K|       |   344   (1)| 00:00:05 |
|*  6 |     HASH JOIN                            |                           |    21M|   582M|       |   329K  (1)| 01:05:59 |
|   7 |      VIEW                                |                           | 69509 |   882K|       |  5236   (1)| 00:01:03 |
|   8 |       SORT UNIQUE                        |                           | 69509 |  1177K|  1784K|  5236   (1)| 00:01:03 |
|   9 |        UNION-ALL                         |                           |       |       |       |            |          |
|* 10 |         VIEW                             | index$_join$_001          | 64314 |  1004K|       |  3146   (1)| 00:00:38 |
|* 11 |          HASH JOIN                       |                           |       |       |       |            |          |
|* 12 |           HASH JOIN                      |                           |       |       |       |            |          |
|* 13 |            HASH JOIN                     |                           |       |       |       |            |          |
|  14 |             BITMAP CONVERSION TO ROWIDS  |                           | 64314 |  1004K|       |    11   (0)| 00:00:01 |
|* 15 |              BITMAP INDEX SINGLE VALUE   | NODE_PDUID_INDEX          |       |       |       |            |          |
|  16 |             BITMAP CONVERSION TO ROWIDS  |                           | 64314 |  1004K|       |    37   (0)| 00:00:01 |
|* 17 |              BITMAP INDEX SINGLE VALUE   | NODE_DELETE_FLAG_INDEX    |       |       |       |            |          |
|  18 |            BITMAP CONVERSION TO ROWIDS   |                           | 64314 |  1004K|       |    38   (0)| 00:00:01 |
|* 19 |             BITMAP INDEX SINGLE VALUE    | NODE_TYPE_INDEX           |       |       |       |            |          |
|  20 |           INDEX FAST FULL SCAN           | SYS_C0019687              | 64314 |  1004K|       |  1579   (1)| 00:00:19 |
|* 21 |         HASH JOIN                        |                           |  5195 |   172K|       |  1743   (1)| 00:00:21 |
|* 22 |          HASH JOIN                       |                           |  5637 |   154K|       |  1397   (1)| 00:00:17 |
|* 23 |           VIEW                           | index$_join$_002          |  5627 | 90032 |       |  1329   (1)| 00:00:16 |
|* 24 |            HASH JOIN                     |                           |       |       |       |            |          |
|* 25 |             HASH JOIN                    |                           |       |       |       |            |          |
|* 26 |              HASH JOIN                   |                           |       |       |       |            |          |
|  27 |               BITMAP CONVERSION TO ROWIDS|                           |  5627 | 90032 |       |     4   (0)| 00:00:01 |
|* 28 |                BITMAP INDEX SINGLE VALUE | NODE_TYPE_INDEX           |       |       |       |            |          |
|  29 |               BITMAP CONVERSION TO ROWIDS|                           |  5627 | 90032 |       |    11   (0)| 00:00:01 |
|* 30 |                BITMAP INDEX SINGLE VALUE | NODE_PDUID_INDEX          |       |       |       |            |          |
|  31 |              BITMAP CONVERSION TO ROWIDS |                           |  5627 | 90032 |       |    37   (0)| 00:00:01 |
|* 32 |               BITMAP INDEX SINGLE VALUE  | NODE_DELETE_FLAG_INDEX    |       |       |       |            |          |
|  33 |             INDEX FAST FULL SCAN         | SYS_C0019687              |  5627 | 90032 |       |  1579   (1)| 00:00:19 |
|  34 |           TABLE ACCESS FULL              | FULL_REL_SPECNODE         | 26489 |   310K|       |    68   (0)| 00:00:01 |
|  35 |          INDEX FAST FULL SCAN            | SYS_C0019687              |   415K|  2436K|       |   344   (1)| 00:00:05 |
|* 36 |      VIEW                                | index$_join$_005          |    37M|   530M|       |   324K  (1)| 01:04:54 |
|* 37 |       HASH JOIN                          |                           |       |       |       |            |          |
|* 38 |        HASH JOIN                         |                           |       |       |       |            |          |
|  39 |         BITMAP CONVERSION TO ROWIDS      |                           |    37M|   530M|       |  1687   (1)| 00:00:21 |
|* 40 |          BITMAP INDEX SINGLE VALUE       | FULL_ITEMSINFO_41_INDEX_3 |       |       |       |            |          |
|  41 |         INDEX FAST FULL SCAN             | FULL_ITEMSINFO_41_INDEX_1 |    37M|   530M|       |   115K  (1)| 00:23:06 |
|  42 |        BITMAP CONVERSION TO ROWIDS       |                           |    37M|   530M|       | 12843   (1)| 00:02:35 |
|  43 |         BITMAP INDEX FULL SCAN           | FULL_ITEMSINFO_41_INDEX_2 |       |       |       |            |          |
------------------------------------------------------------------------------------------------------------------------------

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

"   2 - access(""I"".""ATTRRID_""=""A"".""ID"")"
"   3 - filter(""A"".""ATTRR_DELETEFLAG""=0)"
"   4 - access(""N"".""NODE_ID""=""T"".""NODE_ID"")"
"   6 - access(""I"".""NODE_ID_""=""N"".""NODE_ID"")"
"  10 - filter(""NODE_PDUID""=41 AND ""NODE_TYPE""=2 AND ""NODE_DELETEFLAG""=0)"
11 - access(ROWID=ROWID)
12 - access(ROWID=ROWID)
13 - access(ROWID=ROWID)
"  15 - access(""NODE_PDUID""=41)"
"  17 - access(""NODE_DELETEFLAG""=0)"
"  19 - access(""NODE_TYPE""=2)"
"  21 - access(""B"".""NODE_PARENTID""=""C"".""NODE_ID"")"
"  22 - access(""A"".""NODE_ID""=""B"".""NODE_ID"")"
"  23 - filter(""A"".""NODE_TYPE""=3 AND ""A"".""NODE_PDUID""=41 AND ""A"".""NODE_DELETEFLAG""=0)"
24 - access(ROWID=ROWID)
25 - access(ROWID=ROWID)
26 - access(ROWID=ROWID)
"  28 - access(""A"".""NODE_TYPE""=3)"
"  30 - access(""A"".""NODE_PDUID""=41)"
"  32 - access(""A"".""NODE_DELETEFLAG""=0)"
"  36 - filter(""I"".""ISDELETED""=0)"
37 - access(ROWID=ROWID)
38 - access(ROWID=ROWID)
"  40 - access(""I"".""ISDELETED""=0)"