how to create index when running 'create table as select'

cy163 · #1 (**permalink**) 12-10-08, 23:34

Hello ALL,

my program has a SQL statement like

Code:

Create temporary table Temp
 AS SELECT
      Name
 FROM
     EmployeeList
 WHERE 
     Dept = 'XXX'

I would l ike to create an index on the field Name of the temporary table Temp. I wonder if I can create the index on the fly by adding sth in the above statement.

Thanks

r937 · #2 (**permalink**) 12-10-08, 23:46

what the... ???

why are you creating a temporary table here?

what could you possibly want to do with it, that you cannot do with a subquery on the EmployeeList table?

methinks thou art over-engineering...

cy163 · #3 (**permalink**) 12-11-08, 00:04

Actually, I need to use the Name field of the temporary table in the following WHERE clause of another SQL statement.

Code:

SELECT
       *
FROM
     a, Temp
WHERE
    a.Name = Temp.Name

I think it would be more efficient if an index is created on Temp.Name.

Additionally, the actual SQL statement to create the temporary table Temp is so complicated (beside the EmployeeList table, 4 other tables are involved)that it cannot be integrated with the above statement into one single SQL statement.

r937 · #4 (**permalink**) 12-11-08, 00:17

dude, compare what you have with this --

Code:

SELECT a.*
  FROM a
INNER 
  JOIN ( SELECT Name
           FROM EmployeeList
          WHERE Dept = 'XXX'
       ) AS Temp
    ON Temp.Name = a.Name

cy163 · #5 (**permalink**) 12-11-08, 07:26

sorry, what do you mean by showing my previous statement here.

I just wonder if it is possible to create an index in 'create temporary table as select ...' statement.