locked
Creating an Index RRS feed

  • Question

  • User-373571878 posted

    When creating an index should the column order start with the most entries in a table and work down or start with the least and work up?

     

    so if a table holds records for years 2011, 2012 and 2013 and all of the records contain a Period Number, and Account Id and a Userid

    and you need to bring back the records for a certain Year in a Period Number Range for certain Account ID's, what should the index look like?

     

    Thursday, August 15, 2013 1:40 PM

Answers

  • User269602965 posted

    FROM dmclient, dmuser, zsalespersonaccountsbilled

    This FROM statement is a Cartesian Product JOIN which is very costly and creates all possible combinations of all three tables,

    some of which are not even involved in the analysis.

    It is better to do INNER JOINS, LEFT OUTER JOINS, RIGHT OUTER JOINS

    which may then move some of your where conditions into the JOIN and limit number of return rows with enhanced performance.

    Cannot say more since I do not know the entity relationships and key definitions of these tables.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 16, 2013 5:29 PM

All replies

  • User269602965 posted

    I would still post the query because sometimes indexing is not the problem with performance in Oracle SQL.

    Thursday, August 15, 2013 3:07 PM
  • User269602965 posted

    Indexing in Oracle is a bit of an art, perhaps trial and error as well, but predictable in some ways.

    By default, an index created on a column uses B-TREE index method, a rather standard indexing method.

    Sometimes, indexing is helpful, and sometimes it is not.

    Let's say you index each column in your WHERE clause.  One index for each column (year, period number, account id, userid).

    If your query returns more than 50% of rows in the table, indexing will not likely add much performance.

    If your WHERE clause uses NOT operators !=, NOT IN, NOT LIKE, indexing will not likely add much performance.

    Indexes are more helpful when you are returning small number of rows in proportion to the total number of rows

    and when your WHERE clause uses the operators >, <, IN, LIKE, BETWEEN.

    Sometimes doing a full table scan is faster than doing a partial table scan AND four index scans.

    For non-transactional tables, like study data, I find proper ordering of variables on table creation can be just as useful as indexing,

    since often for Oracle analytics there is much sorting and full table scans going on anyway... index then gets in the way.

    Back in the 1990s, some databases advised using cols that were integer numbers for best indexing performance,

    but with Oracle since Oracle 9i, for me, it does not seem to matter if the col is text or a number.

    Another strategy is using multi-column indexes, which is allowed in Oracle.  That will not likely help performance unless the multiple cols form a unique key unto itself, which may not be the case with your data.

    An outstanding references on Performance Tuning.

    http://www.amazon.com/Expert-Oracle-Database-Architecture-Programming/dp/1430229462/ref=sr_1_1?ie=UTF8&qid=1376616461&sr=8-1&keywords=Oracle+kyte

    Having said that about indexes, SQL query design is just as important in performance considerations.

    Thursday, August 15, 2013 9:42 PM
  • User-373571878 posted

    Thanks for the information Lannie. 

     I tried breaking down query into smaller segements, figuring if I can speed up these little repeated segments the whole query should improve. So here is one piece:

    This little segment takes about 27 seconds to run, add that to three other like segments and the query comes back in about two minutes.

    Very long when waiting for results to come back on web page.

    SELECT dmclient.namelast_bsn   AS Accountname,
        dmclient.accountnumber_adbase AS Accountnum,
        CASE
          WHEN zsalespersonaccountsbilled.fiscalyear = 2013
          THEN zsalespersonaccountsbilled.revenue
          ELSE 0
        END AS current_revenue,
        CASE
          WHEN zsalespersonaccountsbilled.fiscalyear = 2012
          THEN zsalespersonaccountsbilled.revenue
          ELSE 0
        END AS previous_revenue,
        dmuser.namelast_user
      FROM dmclient, dmuser, zsalespersonaccountsbilled
      WHERE zsalespersonaccountsbilled.fiscalyear = ANY(2012,2013)
      AND zsalespersonaccountsbilled.period      >= 1
      AND zsalespersonaccountsbilled.period      <= 1
      AND zsalespersonaccountsbilled.accountid    = dmclient.id
      AND zsalespersonaccountsbilled.userid       = dmuser.id
      AND dmuser.salesteam_user                   = 'Team1';

    I have an index built on the zsalespersonaccountsbilled table as Fiscalyear, period, AccountId and UserId.

    I ran an Explain on the query and it shows that it is using the index.

     

     

    Friday, August 16, 2013 12:49 PM
  • User269602965 posted

    FROM dmclient, dmuser, zsalespersonaccountsbilled

    This FROM statement is a Cartesian Product JOIN which is very costly and creates all possible combinations of all three tables,

    some of which are not even involved in the analysis.

    It is better to do INNER JOINS, LEFT OUTER JOINS, RIGHT OUTER JOINS

    which may then move some of your where conditions into the JOIN and limit number of return rows with enhanced performance.

    Cannot say more since I do not know the entity relationships and key definitions of these tables.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 16, 2013 5:29 PM