RE: Why doesn't my hint work?

Incidentally, thanks to Mark - I was wondering if there was an optimum order to create the index where it was not dictated by the query. In the event I created all six possible indexes in a test system, and the clustering factor was very similar. This is definitely something I will bear in mind in the future.

I gathered histograms and the plan did not change. By default Oracle chose not to gather a histogram on OPRID_SEC.OPRID. When I forced it to, the estimated cardinality was unaffected. The main problem seems to be that in this case it has to loop through less data than expected, i.e. less rows come out of the join that the optimiser expects. Hinting is bad because if I pick an oprid with more rows in OPRID_SEC the plan the optimiser picked originally is the best one, but a literal is used for oprid, so the optimiser will treat each oprid as a different query meaning we should not get the problems I am concerned about with histograms and bind variables in this case.

At this stage I think I have come to the conclusion that the optimiser is probably doing the right thing. But to increase my knowledge of Oracle:

Does anyone have any idea of a way to fix this with stats? (The oprid in this query is actually a literal).

This is the plan forced with hints thanks to Gaja and Riyaj, with histograms in place as below.

  Predicate Information (identified by operation id):

   3 - access("A"."OPRID"=:1)
   4 - access("A"."CAREER"="B"."CAREER" AND "A"."ORG"="B"."ORG" AND
              "B"."LATEST_ROW"='Y')

Here is a representation of the skew in OPRID_SEC.

SQL> select cnt, count(*) from (select oprid, count(*) as cnt from OPRID_SEC group by oprid) group by cnt

       CNT COUNT(*)
---------- ----------

         1         98
         2        201
         3        405
         4        739
         5        455
         6        254
         7         10
         8        252
         9         71
        10        213
        11          1
        12        185
        14          2
        15         38
        16         53
        18          7
        20         38
        21          7
        24         17
        25          5
        27          1
        28         10
        30         22
        32         10
        33         23
        35          1
        36          7
        39          5
        40         12
        42          2
        44          8
        45          1
        48         10
        50          7
        52          3
        55          2
        56          3
        60          8
        63          1
        70          3
        72          2
        78          1
        80          1
        84          1
        85          1
        86          1
        88          2
        92         10
        96         10
        99          1
       100          2
       104          2
       110          1
       112          1
       115          1
       120          4
       130          4
       132          1
       135          3
       140          5
       144         32
       148          1
       152          1
       165          1
       180         13
       196         24
       204          1
       210          4
       213          1
       216          5
       280          7
       284          1
       304          1
       308          2
       392         74
       588         32
       784        473
       980         94
      1176        104
      1372        140
      1568         47
      1764         35

Histograms for relevant columns:

SQL> select table_name, column_name, num_distinct, histogram from all_tab_col_statistics where owner = 'SYSADM' and table_name in ('OPRID_SEC','EMP_APP_SEC') order by table_name;

  TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM

• ----------- ------------ --------------- EMP_APP_SEC EMPLID 258608 HEIGHT BALANCED EMP_APP_SEC CAREER 5 FREQUENCY EMP_APP_SEC LATEST_ROW 2 FREQUENCY EMP_APP_SEC ORG 160 FREQUENCY OPRID_SEC CAREER 9 FREQUENCY OPRID_SEC OPRID 4337 HEIGHT BALANCED OPRID_SEC ORG 196 FREQUENCY -- http://www.freelists.org/webpage/oracle-l