-
1. Re: Index Full scan
1 пользователь считает эту информацию полезнойIt's all answered in the lovely documentation
https://docs.oracle.com/database/121/TGSQL/tgsql_optop.htm#TGSQL237
-
2. Re: Index Full scan
Hi John, Thanks for the link.What I would like to know is ,assume there query selects only 10 records which are in leaf block 10 and 11 of the index and the query has chose a full index scan,does the scanning start from leaf block 1 and continue till it reaches leaf block 1000 which is the final leaf block? Or does it scan only leaf block 11 and 12
-
3. Re: Index Full scan
FULL means FULL - it will scan the entire index.
If the query was such that the index covered the where clause properly, you might perhaps see an index range scan instead
-
4. Re: Index Full scan
Strangely enough its doing INDEX FULL SCAN even the first two columns of the Index are present in where clause.So I guess it's better to put a INDEX hint.Am I right?
-
5. Re: Index Full scan
No, you are not right. INDEX hints are in most cases NOT the right thing to do.
Have you made sure your statistics are up-to-date?
Perhaps if you posted some more information (table structure, indexes, and your query) someone might be able to offer more insight.
-
-
7. Re: Index Full scan
Statistics are up to date.Have been clearing shared pool and it is repeatedly taking INDEX FULL SCAN .Histograms are present as well I cant post actual details as its confidential.Here's similar one
table
xyz (a char(3) not null, b char(8) not null, c char(8) not null, d char(12) not null , .... ..... ) The four not null columns are primary key in the same order. Query is Select /*+ FIRST_ROWS */ * from xyz where (a='123' and b>='12345678' and c>='12345678' and d>='000000000000') or (a='123' and b>='12345678' and c>'12345678') or (a='123' and b>'12345678') order by a,b,c,d.
Strangely enough I have never been able to replicate the problem when I execute the query manually even using the same bind variable used by application.
-
8. Re: Index Full scan
Are you really storing what appear to be numbers in a char column?
My guess is that possibly the OR is making the optimiser think that it cannot do an index range scan
-
9. Re: Index Full scan
Yes...Thanks to the application Architects in lates 90's,numbers are stored as characters and even dates as well... So since my select criteria is selecting around 10 percent rows of table I have to somehow force the query to use INDEX RANGE SCAN instead of FULL INDEX SCAN.I was thinking of using hint to do that job.
-
10. Re: Index Full scan
Not going to touch that one with a ten-foot pole.
Storing numbers and dates as strings (fixed-length ones, at that) is a recipe for "I wish I could do that, but because of stupid decisions, I cannot"
-
11. Re: Index Full scan
>Strangely enough I have never been able to replicate the problem when I execute the query manually even using the same bind variable used by application.
Possibly the optimizer environment is set differently for the application.
Also, check if you are using binds or literals. What you show are literals, not binds.
Execution with binds may suffer / use bind_peeking without a reparse even when the values are changed.
Hemant K Chitale
-
-
13. Re: Index Full scan
Show us the output you get by using dbms_xplan.display_cursor() to pull the execution plan from memory.
One obvious feature that MIGHT explain the choice is that your order by clause will be satisfied by a full scan, which the optimizer might think is a better strategy than finding all the data and paying the cost of sorting it when you've indicated that you're only planning to fetch one row.
Regards
Jonathan Lewis
-
14. Re: Index Full scan
John Stegeman wrote:
Not going to touch that one with a ten-foot pole.
Storing numbers and dates as strings (fixed-length ones, at that) is a recipe for "I wish I could do that, but because of stupid decisions, I cannot"
Here you go John.. <handing John a 11 foot pole>
I'll be standing over here. <expectant expression on face>
Join the world’s largest interactive community dedicated to Oracle technologies.
- Learn from thousands of community experts
- Get answers to your technical questions
- Share your knowledge with peers
1,060,000
Active Users1,100,000
Answered Questions
