Results 1 to 6 of 6

Thread: Index usage

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Index usage

    In a query which returns detail information, if a field included in the ORDER BY clause is indexed, is that index ever used:

    select ...
    from ...
    order by <indexed field>

    How about a query with a GROUP BY and/or an ORDER BY? Would an index included in either clause speed up the return of the resultset?

    select <indexed field>, count(*)
    from ...
    group by <indexed field>
    order by <indexed field>

    I'm trying to set aside some rules for Developers, so that resultsets are returned in the minimum amount of time, and there's been some confusion lately as to whether indexed fields in the above situations are used to enhance performance.

    Thanks,
    Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The proof is in the pudding.
    Enable SQL_TRACE and Oracle will show you whether or not any index is used.
    Don't depend upon others explaining their view of reality, gather facts & see for yourself.

    http://download-west.oracle.com/docs...trac.htm#17588
    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
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I second what anacedent says, you cant rely on hard and fast rules as it varies according to your environment and data. A good example is that lots of developers believe if there is a performance problem stick an index on it. But going in on an index can be slower than a full table scan depending on your data and system.

    Get you developers to try it out on their dev boxes, look at the execution plan and trace stats (always run 2 or more times to cancel the effect of caching) and then try it out on PROD aswell as (which is were many developers get it wrong). Also get your developers to do as much as possible within as few sql calls as possible, developers often write reams of code for stuff where a simple bit of sql would suffice (newish stuff like merge or analytics are often overlooked)

    Alan

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    I guess I was looking for whether or not indexes were used at all. I'm sure my memory is not crystal clear, but I remember a recent post (which I couldn't find) where someone was questioning why their sort took so long, and another poster responded that indexes wouldn't speed a sort once the data had been collected.

    I haven't had time to look into it more, I can't find the post, so I started a new thread.

    -Chuck

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    sorting takes time. no doubt about it.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am willing to bet dollar to doughnuts that for any "rule" or guideline you come up with, I could find a case where the rule is not true.

    If SQL tuning could be reduced to a handful of rules, we'd still be using the RBO.
    Since Oracle continues to develop and enhance the CBO, I conclude optimizing SQL is a non-trivial task which is impacted upon a myriad of details.

    The real answer is, "It all depends".
    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.

Posting Permissions

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