Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Posts
    130

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

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    130
    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.
    Last edited by cy163; 12-11-08 at 01:11.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2007
    Posts
    130
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cy163
    I just wonder if it is possible to create an index in 'create temporary table as select ...' statement.
    i'm just wondering why you need a temporary table at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2004
    Posts
    480
    You don't need a temporary table, what you need is a self join on your table, exactly what you have been shown in this thread.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •