Home » RDBMS Server » Performance Tuning » Full scan even index being added
Full scan even index being added [message #404440] Thu, 21 May 2009 19:14 Go to next message
greentea
Messages: 12
Registered: May 2009
Junior Member
I have the following sql script, and I have done the index as well

SELECT a.text1, a.text3, a.text3, a.text4, a.text5, a.text6, a.text7,
b.text8, b.text9, b.text10, b.text11
FROM remark1 a, remark2 b
WHERE (a.text1 = b.text1)
AND (a.text2 = b.text2)
AND (a.text3 = b.text3)
AND (b.text4 = '0001')

I have add the index:
create index index1 on remark1(text1, text2, text3)
create index index2 on remark2(text1, text2, text3, text4)

but when i run this script still will come out with
remark1 table access (by index rowid)
index1 index (Full Scan)

and show that the cost id is so high.

any idea on how to fine tune?
Re: Full scan even index being added [message #404455 is a reply to message #404440] Thu, 21 May 2009 20:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You might get better results with individual indexes on each of the columns.
Of course if CBO knows tables are small, it might choose FTS anyways.

Do tables & indexes have current statistics?
Re: Full scan even index being added [message #404471 is a reply to message #404455] Thu, 21 May 2009 21:36 Go to previous messageGo to next message
greentea
Messages: 12
Registered: May 2009
Junior Member
which statistic should I refer?
Re: Full scan even index being added [message #404472 is a reply to message #404455] Thu, 21 May 2009 21:38 Go to previous messageGo to next message
greentea
Messages: 12
Registered: May 2009
Junior Member
"You might get better results with individual indexes on each of the columns."

do you mean I have to index column by column?
create index index1 on remark1(text1)
create index index1 on remark1(text2)
create index index1 on remark1(text3)

Re: Full scan even index being added [message #404481 is a reply to message #404472] Thu, 21 May 2009 23:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What percentage of rows have b.text4 = '0001'?

If more than 10% of rows match that predicate, an index might not help.

Ross Leishman
Re: Full scan even index being added [message #404512 is a reply to message #404481] Fri, 22 May 2009 01:13 Go to previous messageGo to next message
greentea
Messages: 12
Registered: May 2009
Junior Member
for text4, it might more than 10% of record will be match from table "remark2", but text1, text2, text3, text4 in table remark2 is the primary key. for table remark1, text1 is primary key, and text2, text3 are not primary key.

Will it be the main reason of causing that full scan on the index?

Re: Full scan even index being added [message #404528 is a reply to message #404512] Fri, 22 May 2009 02:01 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Assuming there is generally matching rows in the two tables, an index is not going to help.

Oracle can read data many, many times faster in a Full Scan than it can from an index, so you can afford to read lots of redundant data in a FTS and then discard it, and it will still be faster than an index scan.

Your SQL has only one filter condition. Based on your observation that the filter is not very selective, it would be faster to full-scan the table.

Similarly, finding matching rows in the second table is going to be faster with a full scan - providing it is performing a HASH join.

Ross Leishman
Previous Topic: setting up performance monitoring on my schema
Next Topic: Difference between awr metrics
Goto Forum:
  


Current Time: Sat Dec 10 13:06:58 CST 2016

Total time taken to generate the page: 0.04527 seconds