[GitHub] [spark] huaxingao commented on pull request #33754: [SPARK-36526][SQL] DSV2 Index Support: Add supportsIndex interface

[GitBox <gi...@apache.org>]

huaxingao commented on pull request #33754:
URL: https://github.com/apache/spark/pull/33754#issuecomment-909939872


   @cloud-fan 
   Here are the ALTER INDEX syntax from major DBMS:
   
   Oracle:
   https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_1008.htm
   ```
   ALTER INDEX [ schema. ]index
     { { deallocate_unused_clause
       | allocate_extent_clause
       | shrink_clause
       | parallel_clause
       | physical_attributes_clause
       | logging_clause
       }
         [ deallocate_unused_clause
         | allocate_extent_clause
         | shrink_clause
         | parallel_clause
         | physical_attributes_clause
         | logging_clause
         ]...
     | rebuild_clause
     | PARAMETERS ('ODCI_parameters')
     | { ENABLE | DISABLE }
     | UNUSABLE
     | RENAME TO new_name
     | COALESCE
     | { MONITORING | NOMONITORING } USAGE
     | UPDATE BLOCK REFERENCES
     | alter_index_partitioning
     } ;
   ```
   
   DB2 
   https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0054932.html
   
   <img width="563" alt="Screen Shot 2021-08-31 at 10 23 48 PM" src="https://user-images.githubusercontent.com/13592258/131616459-8bade861-4abc-4759-8202-84a49b5eb31f.png">
   
   MS SQL server:
   https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15
   ```
   -- Syntax for SQL Server and Azure SQL Database
     
   ALTER INDEX { index_name | ALL } ON <object>  
   {  
         REBUILD {  
               [ PARTITION = ALL ] [ WITH ( <rebuild_index_option> [ ,...n ] ) ]   
             | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]  
         }  
       | DISABLE  
       | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]  
       | SET ( <set_index_option> [ ,...n ] )   
       | RESUME [WITH (<resumable_index_options>,[...n])]
       | PAUSE
       | ABORT
   }  
   [ ; ]  
   ```
   Postgres
   https://www.postgresql.org/docs/13/sql-alterindex.html
   
   ```
   ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
   ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
   ALTER INDEX name ATTACH PARTITION index_name
   ALTER INDEX name [ NO ] DEPENDS ON EXTENSION extension_name
   ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter [= value] [, ... ] )
   ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
   ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number
       SET STATISTICS integer
   ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
       SET TABLESPACE new_tablespace [ NOWAIT ]
   ```
   
   No ALTER INDEX in MySQL
   
   The ALTER INDEX syntaxes are very different, but for multiple columns index created by 
   `CREATE INDEX index_name ON table_name (column1, column2, column3,...)`, it seems that we can't alter property for certain columns.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org