1 2 3 Previous Next 31 Replies Latest reply on Jan 9, 2015 1:37 AM by Sekar_BLUE4EVER

Index Full scan

Sekar_BLUE4EVER Jan 7, 2015 5:19 AM

Hi Guys, I want to know how a Index Full Scan works in detail.Does it always scan from first leaf block to last leaf block(all blocks) in the index irrespective of the where condition i.e even though I have only one row satisfying the condition does it scan all the blocks of index?

1. Re: Index Full scan

John Stegeman Jan 7, 2015 5:20 AM (in response to Sekar_BLUE4EVER)

It's all answered in the lovely documentation

https://docs.oracle.com/database/121/TGSQL/tgsql_optop.htm#TGSQL237
1 person found this helpful
Like Show 0 Likes(0)

Actions
2. Re: Index Full scan

Sekar_BLUE4EVER Jan 7, 2015 5:33 AM (in response to John Stegeman)

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
Like Show 0 Likes(0)

Actions
3. Re: Index Full scan

John Stegeman Jan 7, 2015 6:28 AM (in response to Sekar_BLUE4EVER)

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
Like Show 0 Likes(0)

Actions
4. Re: Index Full scan

Sekar_BLUE4EVER Jan 7, 2015 6:35 AM (in response to John Stegeman)

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?
Like Show 0 Likes(0)

Actions
5. Re: Index Full scan

John Stegeman Jan 7, 2015 6:39 AM (in response to Sekar_BLUE4EVER)

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.
Like Show 0 Likes(0)

Actions
6. Re: Index Full scan

Galib Hussain Jan 7, 2015 6:52 AM (in response to Sekar_BLUE4EVER)

index fast full scan tips
its help u
Like Show 0 Likes(0)

Actions
7. Re: Index Full scan

Sekar_BLUE4EVER Jan 7, 2015 7:04 AM (in response to John Stegeman)
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.
Like Show 0 Likes(0)

Actions
8. Re: Index Full scan

John Stegeman Jan 7, 2015 7:08 AM (in response to Sekar_BLUE4EVER)

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
Like Show 0 Likes(0)

Actions
9. Re: Index Full scan

Sekar_BLUE4EVER Jan 7, 2015 7:18 AM (in response to John Stegeman)

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.
Like Show 0 Likes(0)

Actions
10. Re: Index Full scan

John Stegeman Jan 7, 2015 7:22 AM (in response to Sekar_BLUE4EVER)

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"
Like Show 0 Likes(0)

Actions
11. Re: Index Full scan

Hemant K Chitale Jan 7, 2015 8:22 AM (in response to Sekar_BLUE4EVER)

>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
Like Show 0 Likes(0)

Actions
12. Re: Index Full scan

mtefft Jan 7, 2015 11:39 AM (in response to Sekar_BLUE4EVER)

Please post the actual plan.
Like Show 0 Likes(0)

Actions
13. Re: Index Full scan

Jonathan Lewis Jan 7, 2015 11:41 AM (in response to Sekar_BLUE4EVER)

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
Like Show 0 Likes(0)

Actions
14. Re: Index Full scan

Billy~Verreynne Jan 7, 2015 11:44 AM (in response to John Stegeman)

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>
Like Show 2 Likes(2)

Actions