Forum Stats

  • 3,783,352 Users
  • 2,254,762 Discussions
  • 7,880,372 Comments

Discussions

performance tuning -index on big table on date column

4714
4714 Member Posts: 106
edited Dec 9, 2008 5:58PM in General Database Discussions
Hi,

I am working on Oracle 10g with Oracle Apps 11i on Sun.

we have a large non-partition table "GL_JE_HEADERS" with 7 million rows.

Now we want to run the query for selecting rows using between clause on date column.

I have created Btree index on the this table.

Now how can I tune the query? Which hint should I use for the query?

Thanks,
rane
Tagged:

Answers

  • OrionNet
    OrionNet Member Posts: 4,542
    edited Dec 8, 2008 9:54PM
    DGrane,

    You can try something similar to this, but in long term if you have rely on hints then its time to focus on redesigning the tables.


    SELECT /*+ INDEX(myindex) */ *
    FROM mytable
    WHERE mycondition = 'test';

    There are whole list of hints are available ,check following links for entire list

    http://www.psoug.org/reference/hints.html
    Regards

    Edited by: OrionNet on Dec 8, 2008 9:53 PM
  • 669771
    669771 Member Posts: 826
    Hi,

    you can use SQL tunning advisor in grid control.
    There you can see execution plan and possible botlenecks.

    Regards,
    Tom
    http://oracledba.cz
  • 469753
    469753 Member Posts: 208
    Hm...
    You created the Btree index but the optimizer is NOT choosing to use it?
    Perhaps it ought not be used....
    How selective are the dates and wide of a range of dates are you looking for?
    If you need to retrieve, say 30% of the table, you would be better off with a full table scan than an index range scan.

    Sometimes a full table scan is faster than an index.
  • 108476
    108476 Member Posts: 2,184
    edited Dec 9, 2008 10:49AM
    Hi Rane,
    Now how can I tune the query?
    Indexes on DATE datatypes are tricky, as the SQL queries must match the index!

    For example, an index on ship_date would NOT match a query:

    WHERE trunc(ship_date) > trunc(sysdate-7);

    WHERE to_char(ship_date,’YYYY-MM-DD’) = ‘2004-01-04’;

    You may need to create an function-basd index, so that the DATE reference in your SQL matches the index:

    http://www.dba-oracle.com/oracle_tips_index_scan_fbi_sql.htm

    To start testing, go into SQL*Plus and "set autotrace on" and run the queries.

    Then confirm that your index is being used.
    Which hint should I use for the query?
    Hints are a last resort!

    Your query is fully tuned when it fetches the rows you need with a minimum of block touches (logical reads, consistent gets).

    See here for details:

    http://www.dba-oracle.com/art_sql_tune.htm

    Hope this helps . . .

    Donald K. Burleson
    Oracle Press author
    Author of "Oracle Tuning: The Definitive Reference"
    http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,852 Gold Crown
    dgrane wrote:

    I am working on Oracle 10g with Oracle Apps 11i on Sun.
    we have a large non-partition table "GL_JE_HEADERS" with 7 million rows.
    Now we want to run the query for selecting rows using between clause on date column.
    I have created Btree index on the this table.
    Can you supply the SQL you used to create the index, the call to dbms_stats you used to gather statistics on the index, and an example of the SQL you want to use against the table.

    Please use the "code" tag (in curly brackets - see FAQ at top right of page) to make the SQL come out in
    fixed font
    as this makes it rather easier to read.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    "Science is more than a body of knowledge; it is a way of thinking" Carl Sagan
This discussion has been closed.