SQL & PL/SQL

We have a problem query which came on top as the most time consuming query, taking over 25% of the total elapsed time (for all queries). 

SELECT PZPVSTREAM AS "pzPVStream", -- BLOB COLUMN
   PZINSKEY       AS "pxInsHandle"
FROM PEGADATA_CATALYST.pc_link_attachment
WHERE ( PXLINKEDREFTO = :1 )
   AND ( pxObjClass   = :2 );

Some info. on the table:

Table size: 136 MB
Stale Stats: NO
Number of rows: 86,636 
Number of different values of pxObjClass: 1
Number of different combinations of (PxLinkedRefTo AND pxObjClass): 60,042
Maximum number of combinations of (PxLinkedRefTo AND pxObjClass): 7 (i.e. the above query will bring a maximum of 7 records only)

So, an index is perfect for the 2 column combination of PXLINKEDREFTO  and pxObjClass .

Now, the explain plan shows a full table scan with a cost of 4360.

Running the query outputs data in 1,094ms.

Then I create an index for the 2 columns.

CREATE INDEX vt_201603231646_idx ON PEGADATA_CATALYST.pc_link_attachment (PXLINKEDREFTO, pxObjClass);

Still the explain plans shows a FULL TABLE scan.

Now, I force the use of the index by giving this hint.

SELECT /*+ INDEX (t vt_201603231646_idx) */ t.PZPVSTREAM AS "pzPVStream",
  t.PZINSKEY        AS "pxInsHandle"
FROM PEGADATA_CATALYST.pc_link_attachment t
WHERE ( t.PXLINKEDREFTO = 'DATA-WORKATTACH-FILE CVD-MA-CATALYST-WORK FAE-1822!20160303T063203.712 GMT' )
AND ( t.pxObjClass      = 'Link-Attachment' );

NOW, the plan shows the use of the index, with cost of 77,021.

Now, I run the query and it executes in just 46ms.

I double-check using DBMS_XPLAN.DISPLAY_CURSOR whether the index was actually used, and it HAS been used.

So, the index method is working.

I double-check WHETHER pxObjClass  can have different values. It CAN have different values although now it has only one value for all the rows.

Now, the problem is, from what I have read on tuning, it is not advisable to put hints in SQL statements.  In order to avoid it, I create histograms for the 2 column combination of the WHERE-clause.

So, with the histograms created, I will not need to put the hint in the SQL. The optimizer will use the index depending on the histogram data. I do this.

BEGIN dbms_stats.delete_column_stats('PEGADATA_CATALYST', 'pc_link_attachment', 'pxobjclass'); END;
BEGIN dbms_stats.delete_column_stats('PEGADATA_CATALYST', 'pc_link_attachment', 'PXLINKEDREFTO'); END;
BEGIN
  dbms_stats.gather_table_stats('PEGADATA_CATALYST', 'pc_link_attachment', estimate_percent=>NULL,
  method_opt=>'FOR COLUMNS SIZE 254 PXLINKEDREFTO pxObjClass');
END;
select table_name, column_name, histogram, num_buckets from all_tab_columns where table_name =UPPER('pc_link_attachment')
  AND column_name IN ('PXOBJCLASS', 'PXLINKEDREFTO')    ORDER BY 3;

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM       NUM_BUCKETS           
------------------------------ ------------------------------ --------------- ----------------------
PC_LINK_ATTACHMENT             PXOBJCLASS                     FREQUENCY       1                     
PC_LINK_ATTACHMENT             PXLINKEDREFTO                  HEIGHT BALANCED 254                   

2 rows selected

So, now with the histograms in place, the plan for the original query, without the hint, SHOULD use the newly created index. But it does not. It is still doing a FTS.

What am I doing wrong here??