Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2004
    Posts
    74

    Unanswered: Indexing and Query Speed

    Hey,

    I am creating several queries against tables with 2+ millions rows in each. The queries are calculating various sums and totals with groupings. I am wondering the best way to maximize the speed of the queries. From what I have read proper indexing is the way to go for speed, but I am not totally sure of what indexes to create.

    For example:

    If one of my queries is like this

    select
    a,
    sum(b)
    from
    table q
    where
    x = something
    and y = something
    group by a

    should I create an index on x and y separately or should I create a composite index on x and y together.

    Another example is

    select
    q.a,
    sum(w.b)
    from
    table q, table w
    where
    q.1 = w.1 (inner join)
    and q.x = something
    and w.y = something
    group by q.a

    should I create an index an on what columns are used in the inner join?


    Right now my queries are taking minutes to run and I would like things to run much faster. Maybe indexing is not the best way to do this, I am not sure. Any help would be appreciated.

    Thanks

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    1st query: first try an index on x and y together, analyze the table
    and indexes and see if the query comes back faster

    2nd query: index q.1, q.x together another index on w.1, w.y together

    always remember to analyze the tables and indexes after index creation.
    always remeber to test, test, test

    as queries get more complicated you will need to experiment more.
    learn TRACE and TKPROF and what to look for.

    you don't mention PKs but I always start there and then move forward.
    ie: can I use the PK index in the query? no? do I need an index? what columns?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Aalways index your Primary and Foreign keys. Primary Keys are automatically indexed when you designate them. Foreign Keys require a separate CREATE INDEX step.
    -cf

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Apart from using TRACE and TKPROF you can start with running EXPLAIN PLAN to verify that your indexes are actually beeing used (EXPLAIN PLAN is a bit easier to use than TKPROF) and you also might want to check the results of your index creation (and analyze) using autotrace in SQL*Plus

  5. #5
    Join Date
    Dec 2004
    Posts
    74
    Thanks for the responses.

    I just have a couple more things to ask.

    In general, would it be a good practice to index all of the columns in the where clause together, so for example if I have:

    where w = something
    and x = something
    and y = something
    and z = something

    would I create a composite index like (w, x, y z) together instead of each seperate? I know I need to test things, but would that be the best way to start?

    Also, if I have a where clause like:

    where x not in (select y from another table)

    would an index on x and/or y help in that case?

    Thanks again

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    where w = something 
    and x = something
    and y = something
    and z = something
    only index all four if they consistenly appear together in a query. Otherwise you can index them individually, or in smaller composite structures, like only fields "w" and "x". Composite indexes are the best choice when both criteria are always included.

    And, if all 4 appear together always, but "w" is almost UNIQUE as far as it's range of possible values go, you could get away with only indexing that one field and saving space in your index (ie, in the extreme case, if "w" was the PK, then the rest of the fields wouldn't matter in the index since only one row, based on the PK, qualifies).

    -cf

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Spence23
    In general, would it be a good practice to index all of the columns in the where clause together [...]
    would an index on x and/or y help in that case?
    When it comes to index creation you can very seldom state something "in general".

    What I usually do, check the the execution plan without index, then add one index and re-check the execution plan and autotrace. Then compare the figures.

    In your case of a composite index it could very well be that one, two or three additional columns actually don't speed up the query because the first column is already selective enough (i.e. the value for the first column of the index already reduces the possible number of rows to say a 100, then the additional columns will not really speed up the index usage). But this can't be answered "in general" without knowing the data and without looking at the execution plan.

    If there is "a general" rule of thumb, then yes I'd index as many columns of my where clause as possible. Although I have seen very rare situation where more than three columns were needed.

    And don't forget that each index will slow down INSERTs! So if you have a high INSERT rate be careful with too many indexes. UPDATE and DELETE can make use of indexes.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You should also consider what other queries are being executed against your base tables as you often have to compromise on what columns your going to index and also very importantly what order your columns appear in your index. A quick easy way of doing this is to search in v$sqlarea for all sql referencing your tables.

    So dont just try all the column combinations but also column ordering (also take into account index compression as this might lead you to a different ordering).

    And most important of all you have to do it on your PROD environment unless your TEST environment is absolutely identical in every way to your PROD environment.

    Alan

    P.S. one last thing is remember indexing is sometimes slower than a full table scan

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by AlanP
    So dont just try all the column combinations but also column ordering (also take into account index compression as this might lead you to a different ordering).
    Always attempt to order the columns in your where clauses the same across
    the board (where applicable). This can save you a lot of headaches down the
    road where some stupid query is NOT using the nicely created index because
    a nimcompoop ordered the columns in the where clause incorrectly.

    Also, if I have a where clause like:
    where x not in (select y from another table)
    do a search on this board on the EXISTS clause. You would probably benefit
    from using EXISTS and NOT EXISTS rather than NOT IN, etc.

    also you would consider indexing the inner select or verify that it uses an index.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Duck, I was refering to the order of columns in the index not the where clause.

    Besides that when did ordering of predicates in the where clause effect which index it would use??? You can have predicates in any order but if the columns referenced match the leading part of the index they will be used (if the cost is less).

    Alan

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by AlanP
    Duck, I was refering to the order of columns in the index not the where clause.

    Besides that when did ordering of predicates in the where clause effect which index it would use??? You can have predicates in any order but if the columns referenced match the leading part of the index they will be used (if the cost is less).

    Alan
    hrmmm ... although oracle professes that to be true, I believe in the past I
    could not prove it. Granted, that was a while ago.

    Also, maybe I got confused between the two or both at the same time.
    It always seemed to me that the order between the indexed columns and the
    where clause columns produced different results.

    Perhaps that was due to not listing the order of the columns in the index properly.

    So, I'll say you are right and I am confused. Indexed column order is key.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Might be a rule based issue (or an ordered_predicates hint). Also having index columns in different orders can effect the index's clustering factor which can be a big issue sometimes.

    Alan

  13. #13
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by shammat
    ...
    In your case of a composite index it could very well be that one, two or three additional columns actually don't speed up the query because the first column is already selective enough (i.e. the value for the first column of the index already reduces the possible number of rows to say a 100, then the additional columns will not really speed up the index usage).
    ....
    shammat, care to explain a litle bit more about this ?

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by JMartinez
    shammat, care to explain a litle bit more about this
    OK, assume you have an index with four columns. Now when Oracle takes the values from e.g. the WHERE clause and matches them with the index, it starts with the first column. Let's assume the value for the first column reduces the number of possible rows to 1000, then Oracle takes the value for the second columns and scans the 1000 rows. Now this column's value reduces the number of possible rows to let's say 1. In this case any additional column will not speed up the lookup of further rows (obviously). This is not the case if the first column(s) aren't that selective. Say the first reduces a table of 1 million rows to 800000, the second to 600000 and so on. Then the additional columns do make sense.

    This is why you can't say in general that more columns will speed up the query. It depends (as always) on your data.

    The only time when this does make sense, is when all the columns in the SELECT list are index columns. In this case the index can return the full result that you need and there is no need to read the actual data blocks. But if there is at least one column in the SELECT list that is not part of the index, Oracle will need to read the whole block for the record(s). One more reason to not put more columns into the SELECT clause as actually needed.

    Btw: when having high-selective columns it might also be a good idea to use single column bitmap indexes on them, because they are usually more efficient and can be combined during retrieval (as opposed to the "regular" indexes where Oracle cannot combine two index lookups in one step)

  15. #15
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I agree now on your explanation. It just didn't seem quite like that in the paragraph I quoted. Also, it is not that Oracle avoid reading data blocks if only the indexed columns are on the select list, it will always read data blocks, but rather the select list dictates whether it will need to touch the table or not.

    Good stuff, thanks.

Posting Permissions

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