Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2012
    Posts
    8

    Unanswered: Date Index working partially in Oracle table -Urgent

    Hi,

    I am completely new to oracle please solve my issue

    in my table im having 25 million records, i have created index for necessary columns. i have an date column ( + index). when i filter the data with that column its using the index for certain date range(means fr the past 3 months data). Its doing full table acess scan if the date range is more.

    All table data, index eveything is in default tablesapace.


    Thanks in advance.
    Subbiah K

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what kind of solution do you expect/desire?

    post results of following SQL

    SELECT * FROM V$VERSION;
    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
    Apr 2012
    Posts
    8

    Date Index working partially in Oracle table -Urgent

    Thanks anacedent.

    Here is the version info

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    "CORE 11.2.0.1.0 Production"
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production

    Please solve me the below issue

    Query
    Explain plan for
    select * from charge_rate_bench where charge_date >= sysdate - 30
    select plan_table_output from table(dbms_xplan.display())
    Result
    -------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 19971 | 2359K| 7877 (1)| 00:01:35 |
    | 1 | TABLE ACCESS BY INDEX ROWID| CHARGE_RATE_BENCH | 19971 | 2359K| 7877 (1)| 00:01:35 |
    |* 2 | INDEX RANGE SCAN | CRB_CHARGEDATE_HG | 19971 | | 55 (0)| 00:00:01 |


    Where as fr the below query
    Query
    Explain plan for
    select * from charge_rate_bench where charge_date >= sysdate - 365
    select plan_table_output from table(dbms_xplan.display())
    Result
    ---------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ---------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 5561K| 641M| 124K (2)| 00:24:50 |
    |* 1 | TABLE ACCESS FULL| CHARGE_RATE_BENCH | 5561K| 641M| 124K (2)| 00:24:50 |

    Searching full table acess instead of using index

    Thanks..

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select COUNT(*) from charge_rate_bench;

    post results from SQL above

    FTS are not always bad or to be avoided.
    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
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by subbiahkk View Post
    Please solve me the below issue
    And which issue is that?
    I don't see any issue with what you have posted.

  6. #6
    Join Date
    Apr 2012
    Posts
    8
    hi anacedent

    the total nr of records are 23535165


    Thanks.

  7. #7
    Join Date
    Apr 2012
    Posts
    8
    Hi shammat,

    im calculating percentile in one of the column across 25 million records, its take 30 secs to do the calculation, its not applying the index properly(frm my knowledge). So want to improve the performance.

    Thanks.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    10:20:43 SQL> select 5561000/23535165 from dual;
    
    5561000/23535165
    ----------------
          .236284725
    
    10:21:08 SQL>
    returning about 1/4 the rows so FTS is correct choice by the optimizer.
    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.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by subbiahkk View Post
    Hi shammat,

    im calculating percentile in one of the column across 25 million records, its take 30 secs to do the calculation, its not applying the index properly(frm my knowledge). So want to improve the performance.

    Thanks.
    The condition charge_date >= sysdate - 365 returns a large part of the table. In that case it's more efficient to use a full table scan than an index scan. A FTS is more effecient than an index scan if you return more than approx. 10% of all rows in the table.

    And because you are using the evil select * there also probably no index covering the data you want returned.

    Btw: what do you do with the all those rows? Do you really want to display 5 million rows to the end user?

    And please use [code] tags in the future when posting SQL code.
    For details see here: http://www.dbforums.com/oracle/1031644-guidance-resources-posters.html

  10. #10
    Join Date
    Apr 2012
    Posts
    8
    Thanks for the reply Guyz.

    Here is the actual query which takes 30 sec approximately to calculate the results, any idea to improve the performance..

    select
    timekeeper_level,
    percentile_cont(0.10) within group (order by p1) as p10,
    percentile_cont(0.25) within group (order by p1) as p25,
    percentile_cont(0.50) within group (order by p1) as p50,
    percentile_cont(0.75) within group (order by p1) as p75,
    percentile_disc(0.75) within group (order by p1) as p90
    from(
    select
    a11.timekeeper_level,
    a11.matter_id,
    (NULLIF((sum(a11.amount) / NULLIF(sum(a11.original_units), 0)), 0)) as p1
    from
    charge_rate_bench a11
    join
    matter_rate_bench a12
    on
    (a11.matter_id = a12.matter_id)
    where
    (a11.timekeeper_level in ('Paralegal','Partner','Associate','Other') and
    a11.fee_arrangement_type in ('HOURLY') and
    a11.charge_date >= to_date( '01-jan-2011', 'dd-mon-yyyy'))
    group by a11.timekeeper_level, a11.matter_id
    )
    group by timekeeper_level

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT timekeeper_level, 
           Percentile_cont(0.10) within GROUP (ORDER BY p1) AS p10, 
           Percentile_cont(0.25) within GROUP (ORDER BY p1) AS p25, 
           Percentile_cont(0.50) within GROUP (ORDER BY p1) AS p50, 
           Percentile_cont(0.75) within GROUP (ORDER BY p1) AS p75, 
           Percentile_disc(0.75) within GROUP (ORDER BY p1) AS p90 
    FROM  (SELECT a11.timekeeper_level, 
                  a11.matter_id, 
                  ( Nullif(( SUM(a11.amount) / Nullif(SUM(a11.original_units), 0) ), 
                    0) ) 
                        AS p1 
           FROM   charge_rate_bench a11 
                  join matter_rate_bench a12 
                    ON ( a11.matter_id = a12.matter_id ) 
           WHERE  ( a11.timekeeper_level IN ( 'Paralegal', 'Partner', 'Associate', 
                                              'Other' 
                                            ) 
                    AND a11.fee_arrangement_type IN ( 'HOURLY' ) 
                    AND a11.charge_date >= To_date('01-jan-2011', 'dd-mon-yyyy') ) 
           GROUP  BY a11.timekeeper_level, 
                     a11.matter_id) 
    GROUP  BY timekeeper_level
    do INDEX exist for following columns?

    a11.matter_id
    a12.matter_id
    a11.timekeeper_level
    a11.fee_arrangement_type
    a11.charge_date
    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.

  12. #12
    Join Date
    Apr 2012
    Posts
    8
    Yes anacedent, We have index for all the columns

  13. #13
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    How have you indexed these columns, witha single index or a composite index? Have you though of using bitmap indexes for fee_arrangement_type and timekeeper_level. Different combinations of fields and there order can make a big difference. On the other hand if your selecting more than say 20% of the data a FTS is probably the quickest.

    Another possibility is partitioning charge_rate_bench on the date.

    Also do you actually use any data from matter_rate_bench, if you dont remove it from the query.

    Alan

  14. #14
    Join Date
    Apr 2012
    Posts
    8
    Hi Alan - Thanks for the reply.

    we have created normal index not composite one. Going to add some filters in matter_rate_bench. In charge_rate_bench table charge_date contains 3 years data(2009,10,11).having 25 million records in this table, Which is the best way to partition the data?


    Thanks,
    Subbiah K

  15. #15
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1) Partition by year would speed it up especially if you mostly query the past year. Note you can probably use local partitioned indexes which might help a little.

    2) On columns of low cardinality like I guess fee_arrangement_type and timekeeper_level, bitmap indexes could bve a good solution. Only thing to watch out for is locking issues if these columns are being updated.

    3) A lot depends on what filters are most commonly used, you will need to experiment with indexes with different columns and see which works best but you really need a statistical analysis ofthe possible filters and their usage.

    4) Can you materialize the results, do you need the very latest data?

    Alan

Tags for this Thread

Posting Permissions

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