Re: Tuning 'Alter Modify" column

Thank you Mladen. Actually I saw throughout the 4hrs of Alter query execution the current_obj# was pointing to the table but not indexes, but still the event it was noting was 'cell singleblock physical read' , longops was showing full tablescan which looks odd to me.

 This same event is showing up even i run the alter query by making the index unusable which is on that altered column. ( Though there were other indexes present in usable state on that table on other columns, which i believe should not affect this alter.). With index unusable it's finishing in 1.5hrs though

So wondering if this 'Alter' is some special case in which fullscan is happening using 'cell singleblock physical read' and which is why it's slow as compared to a normal FTS on that 24gb table. (Note- Even with exact index unusable it's taking around 1.5hrs vs with indexes it was running for around 5hrs.)

On Thu, 25 Aug 2022, 7:17 am Mladen Gogala, <gogala.mladen_at_gmail.com> wrote:

> On 8/24/22 02:46, yudhi s wrote:
>
> And also during the table scan i was seeing event 'cell singleblock
> physical read'
>
> Single block physical read is usually an index read. You cannot have index
> read if you want to do it fast. You need to find out where are those index
> reads coming from.
>
> Regards
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
> -- http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l