| Full scan even index being added [message #404440] |
Thu, 21 May 2009 19:14  |
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 #404472 is a reply to message #404455] |
Thu, 21 May 2009 21:38  |
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 #404512 is a reply to message #404481] |
Fri, 22 May 2009 01:13 |
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
