Home » SQL & PL/SQL » SQL & PL/SQL » Avoiding Full table scan
Avoiding Full table scan [message #247022] Fri, 22 June 2007 20:11 Go to next message
ankita_db
Messages: 4
Registered: June 2007
Junior Member
Hi,
We have a DWH on Oracle 10g, in 3 NF form.While querying Sales Fact table (having 4 billion records) joining 4 tables it always going for full table scan, even though indexes are part of join clause. Tables are also analyzed daily.
Using PARALLEL_INDEX hints I am getting better performance.But I have following questions,
1. Why Oracle optimizer is not able to choose the best access path ? Why is it going for full table scan instead of using indexes ?

2. If we use hints and get better performance, does it ensure that it will not degrade the performance in future ? This table will grow heavily in future.

Please suggest.

Thanks in advance !
Ankita
Re: Avoiding Full table scan [message #247025 is a reply to message #247022] Fri, 22 June 2007 21:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Indexes on join columns are not enough. Here are 3 cases where the join index is useless:

SELECT *
FROM   ten_billion
JOIN   twenty USING (join_key)

If join_key is indexed on both tables, it's not going to help because we still have to join all 10B rows.

What if we have a selective WHERE clause on the small table?
SELECT *
FROM   ten_billion
JOIN   twenty USING (join_key)
WHERE  twenty.primary_key IN (1,2)

Assuming even distribution of rows across the join, we will now have to read only 10% of the table. Non-intuitively, this is almost always faster to do via a Full Scan rather than an index scan.

What if we now have a selective WHERE clause on the big table?
SELECT *
FROM   ten_billion
JOIN   twenty USING (join_key)
WHERE  ten_billion.primary_key BETWEEN 1 AND 100


Now we're only reading 100 rows from the big table assuming we have an index on the primary_key. This will be really fast, but in this case we don't need the join index.

What you need is an index on the columns that will help you FILTER the unwanted rows out of the big table.

You also need to gather statistics on all indexes and tables.

If you need more help, you need to give us:
- the query
- the Explain Plan
- the table descriptions and indexes
- the number of rows in each table
- the number of rows matching each filter clause
- the number of rows matching each join
- the number of rows returned by the query.

Ross Leishman
Re: Avoiding Full table scan [message #247419 is a reply to message #247025] Mon, 25 June 2007 18:08 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
You may use hints to optimize the query.....
Re: Avoiding Full table scan [message #247423 is a reply to message #247022] Mon, 25 June 2007 19:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>You may use hints to optimize the query.....
There are around 60 DIFFERENT hints in V10.2, other than trial & error or BENCHMARKING each & every hint, how do you know/decide which hint to use?

Is the use of hint a symptom of Compulsive Tuning Disorder?

[Updated on: Mon, 25 June 2007 19:14] by Moderator

Report message to a moderator

Re: Avoiding Full table scan [message #247921 is a reply to message #247423] Wed, 27 June 2007 09:15 Go to previous message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member

He wants to avoid full table scan, So you may provide external index hint to use indexes...


Dont you think that? It depneds how you are trying ... else you will end up in bad situation... you have to use common sense.
Previous Topic: Sql Query
Next Topic: Creating Function using NEXTVAL
Goto Forum:
  


Current Time: Thu Dec 08 23:55:20 CST 2016

Total time taken to generate the page: 0.07896 seconds