Need help in SQL tunning

                          from ng_structure_txn
                           where b.wi_name = :"SYS_B_0"
                           and APPLICANT_TYPE = :"SYS_B_1")

| Id |                Operation                 |             Name

|    |                                          |
   | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes
| (Max) |   (%)    |     (# samples)      |


===================================================================================================================================================================================================


|  0 | SELECT STATEMENT                         |
   |         |       |           |        |     1 |          |      |
|       |          |                      |
|  1 |   COUNT STOPKEY                          |
   |         |       |           |        |     1 |          |      |
|       |          |                      |
|  2 |    FILTER                                |
   |         |       |           |        |     1 |          |      |
|       |          |                      |
|  3 |     NESTED LOOPS SEMI                    |
   |       1 | 23561 |           |        |     1 |          |      |
|       |          |                      |
|  4 |      FILTER                              |
   |         |       |         1 |     +4 |     1 |        0 |      |
|       |          |                      |
|  5 |       HASH JOIN RIGHT OUTER              |
   |       1 | 23558 |         3 |     +2 |     1 |       2M |      |
|  150M |    66.67 | Cpu (2)              |
|  6 |        TABLE ACCESS FULL                 | NG_VID_STATUS
 |      2M |  4914 |         1 |     +4 |     1 |       2M |      |       |
      |          |                      |
|  7 |        TABLE ACCESS FULL                 | NG_VID_D_VERIFY
 |      2M |  8768 |         2 |     +3 |     1 |       2M | 2520 | 312MB |
      |    33.33 | direct path read (1) |
|  8 |      TABLE ACCESS BY INDEX ROWID BATCHED | NG_STRUCTURE_TXN
    |      4M |     3 |           |        |       |          |      |
  |       |          |                      |
|  9 |       INDEX RANGE SCAN                   | CID_STRUCTURE_TXN_IDX
   |       1 |     2 |           |        |       |          |      |
|       |          |                      |


===================================================================================================================================================================================================

Currently index is present on WI_NAME column of both tables ie NG_VID_STATUS and NG_VID_D_VERIFY

I manually tried to tune it by adding hint of index and making NG_VID_STATUS as leading with use_nl hint but index was not picked up

However i was trying something as below ,and query was optimised without any hint

select a.status as docstatus ,
b.status as vstatus
 from ng_vid_d_verify a left join ng_vid_status b on b.wi_name = a.wi_name
and b.wi_name = :"SYS_B_0"
 where a.c_id in (select id

                          from ng_structure_txn
                           where
                            APPLICANT_TYPE = :"SYS_B_1")

Plan of above was close to plan provided in below , which was generated by tuning advisior

But recommending this needs code change . finally i used tuning advisor and it generated optimised plan similar to rewritten code , lio was 7 from 70K time being i have used it but i don't know how tuning advisor was able to generate optimal plan .

TUNING ADVISOR PLAN :

| Id  | Operation                       | Name                          |
Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                               |
  1 |   106 |    10   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                  |                               |
    |       |            |          |
|   2 |   NESTED LOOPS SEMI             |                               |
  1 |   106 |    10   (0)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN         |                               |
  1 |    88 |     7   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | NG_VID_STATUS               |
1 |    36 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN           | NGI_VID_STATUS              |
1 |       |     3   (0)| 00:00:01 |
|   6 |     BUFFER SORT                 |                               |
  1 |    52 |     3   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| NG_VID_D_VERIFY           |     1
|    52 |     3   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | NGI_VID_D_VERIFY          |     1
|       |     2   (0)| 00:00:01 |
|*  9 |    TABLE ACCESS BY INDEX ROWID  | NG_STRUCTURE_TXN             |
 3948K|    67M|     3   (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN            | CID_STRUCTURE_TXN_IDX         |
  1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Later i done further modifcation from Meger cartesian to USE_NL and used the same plan .
It would be very helpfull if some one will assist me to understand what advisor done to generate plan above plan .

Regards,
Krishna

--
http://www.freelists.org/webpage/oracle-l