-
Hi friends,
This sql is not using index in spite of using an index hint..can u please help me...
select /*+ index(listing LISTINGU1 LISTING_IDX6) */ 'status', count(distinct(l.listing_id)), l.status
from listing l, noah_user_plan n where
l.company_id=n.company_id and
l.actual_end_time >= '&START_DATE' and
l.actual_end_time < '&END_DATE' and
l.status not in ('N', 'EM', 'X') and
-- nvl(l.imported_from_mkt, 'N') = 'N' and
l.run_postsale = 'Y' and
l.marketplace_id < 5 and
n.activation_date <= l.date_added and
n.expiration_date >= l.date_added and
n.plan_component_id in (500, 110000, 120000, 130000, 150000)
group by l.status;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=310279 Card=1 Bytes=
52)
1 0 SORT (GROUP BY) (Cost=310279 Card=1 Bytes=52)
2 1 NESTED LOOPS (Cost=310271 Card=1 Bytes=52)
3 2 TABLE ACCESS (FULL) OF 'LISTING' (Cost=310268 Card=1 B
ytes=30)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'NOAH_USER_PLAN' (Cos
t=3 Card=128929 Bytes=2836438)
5 4 INDEX (RANGE SCAN) OF 'NOAHUSERPLAN_INDEX1' (NON-UNI
QUE) (Cost=2 Card=128929)
regards
anandkl
-
if you use alias for your table you have to use alias inside your hint
-
can u please give an example
regards
anandkl
-
select /*+ index(e emp_pk) */ *
from emp e
where empno=7934
