Forum Stats

  • 3,781,648 Users
  • 2,254,533 Discussions
  • 7,879,774 Comments

Discussions

Cannot create index at creation time for CTAS (CREATE TABLE AS SELECT )

738105
738105 Member Posts: 1
edited Dec 1, 2009 11:52AM in SQL & PL/SQL
Hi 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
Tagged:

Answers

  • 730185
    730185 Member Posts: 223
    You can specify indexes at table creation time to enforce primary key or unique constraints. There's some examples here:

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/indexes003.htm#i1006566

    Although, from a performance point of view, you're probably better off creating the indexes after the initial data load has been done.
  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    Hi,

    There is no such thing as CREATE INDEX as part of CREATE TABLE. (although there is implicit index creation when specifying Primary and Unique Keys)

    But why are you talking about Create Table As Select..?

    If you're just playing around with data it's fine. Else not. Don't know if that's an usual approach in MySQL, in Oracle it is not. Here options are amongst others (Materialized) Views and Global Temporary Tables.

    Regards
    Peter
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    edited Dec 1, 2009 8:48AM
    Peter Gjelstrup wrote:
    There is no such thing as CREATE INDEX as part of CREATE TABLE. (although there is implicit index creation when specifying Primary and Unique Keys)
    That's not entirely true :) You can explicitly create an index to enforce primary key or unique key constraints.

    [CREATE TABLE - using_index_clause|http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#i2159557]

    Sample:
    SQL> CREATE TABLE TEST
      2  (
      3          ID      NUMBER
      4  ,       VAL     VARCHAR2(50)
      5  ,       CONSTRAINT      TEST_PK
      6          PRIMARY KEY     (ID)
      7          USING INDEX
      8          (
      9                  CREATE UNIQUE INDEX TEST_IDX#PK
     10                  ON      TEST(ID)
     11          )
     12  )
     13  /
    
    Table created.
  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    edited Dec 1, 2009 11:52AM
    Good one!

    We have explicit index creation, as well.

    Not so long ago, I gave up on this one (wanted Index to be named differently than PK for an IOT).
    What you've shown me now comes close, but not quite there.
    create table t (x   number not null
                   ,constraint pk 
                       primary key (x) using index
                          (create index ux on t(x)))
    organization index
    Error at line 4
    ORA-25192: invalid option for an index-organized table
    Thanks for showing me :-)

    Regards
    Peter
This discussion has been closed.