well actually i removed that substr on cbs.locator and again it started using merge cartisian.. well try this..

Code:
SELECT SUM(assignments_mapped) assignments_mapped FROM
(
    SELECT /*+ index(cbs pk_cb_standard) */
            CASE WHEN CORR.content_id IN (ci.LOCATOR, ci.ancestor_locator) THEN
                      CASE WHEN ci.LOCATOR = ci.ancestor_locator THEN
                                2
                           ELSE
                                1
                      END
                 ELSE
                      0
            END  AS assignments_mapped
            FROM CLG_ASSESSMENT_RESULT car,
                 CLG_ITEM_RESULT cir,
                 CLG_ITEM ci,
                 CLG_CORRELATION CORR,
                 CLG_ASSESSMENT_INSTANCE cai,
                 CB_STANDARD cbs,
                 CLG_USER u
           WHERE car.assessment_result_id = cir.assessment_result_id
             AND car.status = 20
             AND car.completed_date >= TO_DATE ('09052006', 'mmddyyyy')
             AND car.completed_date <= TO_DATE ('10132006', 'mmddyyyy')
             AND car.assessment_instance_id = cai.assessment_instance_id
             AND cai.assignor_id = u.user_id
             AND u.org_unit_id = 21296
             AND cir.item_locator = ci.LOCATOR
             AND CORR.asset_type_id = 113
             AND CORR.cbs_id = cbs.cbs_id
             AND SUBSTR (cbs.LOCATOR, 1, 6) = '1.1026'           -- INPUT: subject Writing; grade 6
             AND BITAND (cbs.internal_grade_levels, 2) > 0
);