Re: Slow SQL, too many logical reads ?

On May 4, 8:07 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Peter" <peterny..._at_gmail.com> wrote in message
>
> news:1178030834.571357.275300_at_e65g2000hsc.googlegroups.com...
> 8 - access("IA"."IA_IACT2"="AA"."AA__ICODE" AND
> "IA"."IA_REASON"='#EPM#Int2Int'
> AND
> ("IA"."IA_SEQUE"=0 OR "IA"."IA_SEQUE"=1 OR
> "IA"."IA_SEQUE"=2 OR "IA"."IA_SEQUE"=3
> OR
> "IA"."IA_SEQUE"=4 OR "IA"."IA_SEQUE"=5 OR
> "IA"."IA_SEQUE"=6 OR "IA"."IA_SEQUE"=9
> OR
> "IA"."IA_SEQUE"=10 OR "IA"."IA_SEQUE"=11 OR
> "IA"."IA_SEQUE"=17)
> AND
>
> "IA"."IA__DELETED"='N')
>
> Peter,
>
> Can you tell us the definition of the four-column
> index - the column order may have some bearing
> on the resource usage.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

Jonathan, the ordering of the index was given above:

select * from user_ind_columns
where index_name = 'ACURE_A_INDACT_1';

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH

CHAR_LENGTH     DESCEND
ACURE_A_INDACT_1        A_INDACT        IA_IACT2        1
12      12      ASC
ACURE_A_INDACT_1        A_INDACT        IA_REASON       2
12      12      ASC
ACURE_A_INDACT_1        A_INDACT        IA_SEQUE        3
22      0       ASC
ACURE_A_INDACT_1        A_INDACT        IA__DELETED     4
1       1       ASC

I am answering this from home, without access to the database etc. right now.
Anyway, I still lack a real understanding of how my original query is processed, and if/how it could possibly be improved.

Is this what goes on:

• for each of the 5906 rows in sub-select: -- for each of the 11 elements in the inlist: --- retrieve the corresponding ROWIDs from the index ?

Assuming 3 reads to get to an index leaf block, if the the third step would always visit only one index leaf block, we get: 5906 x 11 x 3 = 194898 logical reads on the index, close to the actual 195945 reads reported from tkprof, the difference being that it few times need to visit more than one index leaf block ?

Could the second step (inlist) be replaced with something more efficient (filter?) ?
What would be the most efficient plan ?

• Peter