RE: New online alter table interface [Re: Adding new funtions tohandler interface]

• Any impact of ONLINE/OFFLINE on Replication?
• If you can access a huge table while one of its indexes is out-of-service, you could temporarily get some terrible query plans.

> -----Original Message-----
> From: Marko Mäkelä [mailto:marko.makela@oracle.com]
> Sent: Wednesday, September 19, 2007 11:36 AM
> To: Martin Skold
> Cc: internals@lists.mysql.com
> Subject: Re: New online alter table interface [Re: Adding new
> funtions tohandler interface]
>
> Martin,
>
> On Wed, Sep 19, 2007 at 04:32:22PM +0200, Martin Skold wrote:
> > Hi!
> >
> > The new on-line alter table interface is now in mysql-5.2
> source tree, see
> > below.
> > Note also that there is a syntax extension to ALTER TABLE:
> > ALTER [ONLINE | OFFLINE] TABLE ...
> > where ALTER ONLINE will fail if the storage engine doesn't
> support this
> > operation on-line, and ALTER OFFLINE will always copy the table.
>
> Online index creation usually means that the data in the table may be
> updated while the index is being created. InnoDB does not
> support that,
> but it also does not need to copy the table. It locks the table in
> share mode when creating indexes. (The table will be locked
> in exclusive
> mode when creating a PRIMARY KEY.)
>
> In the code excerpt that you attached, I could not find anything that
> would temporarily disable an index. In online index
> creation, one would
> probably want to disable the use of the index until it has been
> fully created.
>
> Also, InnoDB will have to disable a newly created index from old
> transactions, because it does not copy the history (delete-marked
> records and such) when creating an index. We asked for a virtual
> method in class handler, but it wasn't implemented in 5.1:
>
> /** Check if an index can be used by this transaction.
> * @param keynr key number to check
> * @return true if available, false if the index
> * does not contain old records that exist
> * in the read view of this transaction */
> virtual bool is_index_available(uint keynr) { return true; }
>
> Can you please implement this or a similar method in 5.2 and make the
> query optimizer use it?
>
> Best regards,
>
> Marko Mäkelä
> Innobase Oy/Oracle Corp.
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:
> http://lists.mysql.com/internals?unsub=rjames@yahoo-inc.com
>
>

-- 
MySQL Internals Mailing List
For list archives: 
http://lists.mysql.com/internals
To unsubscribe:    
http://lists.mysql.com/internals?unsub=lists@pantek.com