Oracle Database Discussions

It's all answered in the lovely documentation

https://docs.oracle.com/database/121/TGSQL/tgsql_optop.htm#TGSQL237

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

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

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?

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.

index fast full scan tips

its help u

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.

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.

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"

>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

Please post the actual plan.

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

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>

<gingerly pokes the database with the 11-foot pole>

Nope, still looks like roadkill

Question. How many DBAs does it take to eat roadkill?

Answer. At least two, as one needs to watch for cars.

But in DBA land, seeing it coming doesn't stop it happening.

Regards

Jonathan Lewis

Jonathan Lewis a écrit:

One obvious feature that MIGHT explain the choice is that your order by clause will be satisfied by a full scan, 
Regards
Jonathan Lewis
  

Particularly since he is using a first_rows mode

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. 

https://hourim.wordpress.com/2012/03/14/order-by-and-first_rows/

Best regards

Mohamed Houri

Mohamed,

... when you've indicated that you're only planning to fetch one row.

I was leaving it to the reader to notice that I was interpreting the requirement of the hint.

Regards

Jonathan Lewis

Hi Jonathan,     The plan just shows a Full Index Scan,there are no access predicates just filter predicates.I tried modifying the query by removing the OR clause and the query does a range scan now.As pointed out probably its the OR clause fooling the optimizer

Since (a='123') is common to all of the clauses, what happens if you factor it out:

where a = '123' and ((b>=.....) or (b=) or (etc))

Will check it out.Thanks

I didn't want to hear a description of the bit you thought was relevant from the thing you thought you were seeing, I wanted to see what Oracle was actually supplying.

Please remember when you ask future questions to add a note that if anyone asks for further information you're not going to supply them with the information they've requested. This will reduce the time that people waste trying to help you.

Jonathan Lewis

Hi sorry.. Here is the plan

  select /*+ FIRST_ROWS gather_plan_statistics scanned */ count(1)      FROM  XXX
  where  (((((COL1 = '003' and COL2 >= '20150120') and COL3 >= '00000000') and COL4>= '000000000000' )
  or ((COL1 = '003' and COL2 >= '20150120') and COL3> '00000000' )) or (COL1= '003' and COL2> '20150120'))
  order by COL1,COL2,COL3,COL4




Plan hash value: 919851669


---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Starts | E-Rows | A-Rows |  A-Time  | Buffers | Reads  |


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |      1 |        |  18533 |00:01:47.04 |    156K|  70286 |
|  1 |  TABLE ACCESS BY INDEX ROWID| XXX    |      1 |  7886K|  18533 |00:01:47.04 |    156K|  70286 |
|*  2 |  INDEX FULL SCAN          | XXXXPK |      1 |  7886K|  18533 |00:01:30.36 |    131K|  61153 |
---------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


  2 - filter((("COL2">:B2 AND "COL1"=:B1 AND
              SYS_OP_DESCEND("COL2")<SYS_OP_DESCEND(:B2)) OR ("COL1"=:B1 AND "COL2">=:B2
              AND "COL3">:B3 AND SYS_OP_DESCEND("COL2")<=SYS_OP_DESCEND(:B2)) OR
              ("COL1"=:B1 AND "COL2">=:B2 AND "COL3">=:B3 AND "COL4">=:B4 AND
              SYS_OP_DESCEND("COL2")<=SYS_OP_DESCEND(:B2))))

I'm sure Jonathan can comment more on SYS_OP_DESCEND, but is the index XXXXPK a descending index? That sure would have been useful information...

No it is not a descending index.All four columns in the index are in ascending order

Getting better - although the SQL you've posted doesn't come from the output to the call to dbms_xplan, since you've got literals in the SQL and bind variables in the predicate section, and they're not the correct form to have been generated by messing about with the cursor_sharing parameter.

There's also the problem that you said you should be returning only one row, but the A-rows column shows you returning 18,533

Finally there's an odd sys_op_descend in the predicate which suggests an index with a descending column (which is a little odd if the index is supporting the primary key), and that would contribute to the bad estimate of cardinality.

Which version of Oracle are you running ?

Regards

Jonathan Lewis

John,

Our timelines seem to have crossed, somehow - I'm sure your comment wasn't here when I made my comment which has a timestamp one hour later.

I've not found out (possibly) why that sys_op_descend() appears.  As you can see below I assumed it had to be an index with a descending column, but in fact it's an odd little Oracle bug that's still present in 11g and even in 12c - though with a special twist in the latter.  I've set up a data set with an index (c1, c2, c3, c4) and a second index (c1, c2 desc, c3, c4), and then found that I can get an execution plan for a predicate like the one used by the OP that uses the index with the ascending column, but does a sys_op_descend() on the c2 and the values it's compared with ! 

I've actually got a blog note about it from 3 years ago (https://jonathanlewis.wordpress.com/2011/12/30/fbi-bug-2/ ) which says it was fixed in 10.2.0.3, but perhaps it's (a) returned, or (b) a variant for multi-column indexes in some cases.

Regards

Jonathan Lewis

Thanks, Jonathan - I saw your posting on that bug already, which is why I said that Jonathan may have something to say about this

John,

Applying your suggestion with a little more cunning the optimum predicate is probably:

where

        (COL1 = :B1 and COL2 >= :B2)

and    (

            COL2 > :B2

        or  COL3 > :B3

        or (COL3 >= :B3 and COL4 > :B4)

        )

In the original form the optimizer cannot assume that :B1 is the same value at every occurrency, similarly B2 and so on, so it either has to use OR expansion to a concatentation of three indexed access, or do an index full scan and filter every row.  By factoring out the COL1 predicate we limit it to a single index range scan; by factoring out the COL2 predicate (and handling the > vs. >= operators carefully) we limit the range scan to the minimum we could get away with.  (It's possible that the optimizer in 12c with the IN-MEMORY option might manage to do this to produce an in-memory predicate - but I haven't tested that yet).

Regards

Jonathan Lewis

Thanks Jonathan