SELECT

FILE_CONTROL_NO AS "FILE_CONTROL_NO" ,

FILE_ID AS "FILE_ID" ,

SUBMITTER_ID AS "SUBMITTER_ID" ,

BHD_SEQUENCE_NO AS "BHD_SEQUENCE_NO" ,

PRODUCT_SERVICE_ID AS "PRODUCT_SERVICE_ID " ,

SERVICE_PROVIDER_ID AS "SERVICE_PROVIDER_ID" ,

SERVICE_PROVIDER_ID_QUALIFIER AS "SERVICE_PROVIDER_ID_QUALIFIER" ,

FILL_NUMBER AS "FILL_NUMBER" ,

DISPENSING_STATUS AS "DISPENSING_STATUS" ,

SUBMISSION_CLARIFICATION_CODE AS "SUBMISSION_CLARIFICATION_CODE",

FILE_CONTROL_NO AS "pxInsHandle"

FROM PDE_ETL.PET005_CMS_RPT1_EXCEPTION

WHERE ( LOAD_STATUS = :1 )

Bad Selectivity

lf an index(on load_status) on a table of 7560107 records had only 2 distinct values, then the

index's selectivity is 2 / 7560107 = 0.0000002645 and in this case a query which uses the

limitation of such an index will retum 7560107/2 = 3780053.5 records for each distinct value.

It is evident that a full table scan is more efficient as using such an index where much more

I/O is needed to scan repeatedly the index and the table.

Distinct Values

---------------

2

select distinct(load_status) from PET005_CMS_RPT1_EXCEPTION;

Total Number Rows

-----------------

7560107

Selectivity = Distinct Values / Total Number Rows

= 2/7560107

= 0.0000002645