Cannot create index at creation time for CTAS (CREATE TABLE AS SELECT )
738105Dec 1 2009 — edited Dec 1 2009Hi all,
I was trying to create a new table from existing tables and also index them along with creation.
I could make it work with MYSQL, with following query:
CREATE TABLE table_indexed (INDEX A_IDX (A_NO), INDEX B_IDX (B_NO), INDEX C_IDX (C_NO))
AS (SELECT * FROM
(SELECT DISTINCT table1.A,table1.B, table3.C
FROM table1, table2, table3
WHERE
<some condition> ) T1);
Could someone help me with a query, which will create a table with data and index them( in Oracle), preferably in both Oracle and MySQL.
Thanks in advance