performance tuning -index on big table on date column

4714

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

OrionNet

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

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

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

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

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