sql not using index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: sql not using index

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if you use alias for your table you have to use alias inside your hint

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    can u please give an example

    regards
    anandkl
    anandkl

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select /*+ index(e emp_pk) */ *
    from emp e
    where empno=7934

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width