Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2010
    Posts
    24

    Unanswered: Create index for ORDER BY

    My query (SELECT cola, colb from tableA ORDER BY cola) is slow so I created an index

    CREATE indexa on schema.tableA (cola)

    The index was created and my query is as slow as before. Do I miss something?

    This plan works on DB2, MS SQL, MySQL. Why not on Oracle?

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >This plan works on DB2, MS SQL, MySQL. Why not on Oracle?
    post EXPLAIN PLAN from all 4 DBs.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2010
    Posts
    24
    On MS SQL Server for example, my query takes 27 seconds

    SELECT cola, colb from dbo.tableA ORDER BY cola

    After creating index:

    CREATE indexa on dba.tableA (cola)

    The same query only takes 0.1 seconds.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Proves NOTHING!

    >This plan works on DB2, MS SQL, MySQL. Why not on Oracle?
    post EXPLAIN PLAN from all 4 DBs.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Most likely this is because you created a clustered index in MS SQL. Otherwise indexes don't generally help w/ order by speed unless its a covering index.

  6. #6
    Join Date
    Feb 2010
    Posts
    24
    These 2 queries take 23 seconds on Oracle:

    (1) select invline from mrcmpower.bisales order by invline desc
    (2) select invline from mrcmpower.bisales where invline >0 order by invline desc

    They should be the same because all 2 million rows in the table have invline > 0.

    Then I added index
    create index mrcmpower.index2 on mrcmpower.bisales (invline desc)

    Now query (1) still takes 23 seconds and (2) only takes 0.01 second.

    Why query (1) does not use the index?

    Thanks

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    More likely in the MS SQL Server case, the data is being read from cache on the first run, and the second query reads the same data (does a table scan) but reads from memory.

  9. #9
    Join Date
    Aug 2010
    Location
    Bangalore
    Posts
    7
    What is load on those table?

    Try gathering the stats on the index that you have created. That might help you.

  10. #10
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Create a concatenated index:

    Code:
    create index indexA on tableA(colA, colB)
    In this case, Oracle will not access the data segments and get all the info out of the index (you save 50% of I/O this way)
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  11. #11
    Join Date
    Mar 2013
    Posts
    1
    It's because you don't have a not null constraint on that field, as Oracle treats NULL differently with MSSQL, Oracle doesn't store NULLs in the index, so that if you don't tell Oracle the field cannot be NULL, Oracle cannot use that index for sort.

Posting Permissions

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