There are Information tables:

information.tables
information.indexes
information.temporarytables

but no:
information.temporaryindexes

Is there a way I can determine if a temporary table has an index made from certain column names? For regular tables I can easily do this with information.indexes, but for temporary tables I'm at a loss.

TIA
Barry

Barry

>Is there a way I can determine if a temporary table has an index made from certain column names? For regular tables I can easily do this with information.indexes, but for temporary tables I'm at a loss.

In its present incarnation ElevateDB's CREATE TEMPORARY TABLE does not create indices (at least I don't think so) so I presume the idea is that any indices that do exist you will have had to create specifically and will know about.

Roy Lambert [Team Elevate]

Roy,

<< In its present incarnation ElevateDB's CREATE TEMPORARY TABLE does not create indices >>

It does if there are primary, unique or foreign key constraints defined.
Apart from those, it's how you have just said, we have to keep track of the indexes we create because there is no Information.TemporaryIndexes table, but it would be a nice additional feature .

--
Fernando Dias
[Team Elevate]

Fernando


><< In its present incarnation ElevateDB's CREATE TEMPORARY TABLE does not create indices >>
>
>It does if there are primary, unique or foreign key constraints defined.
>Apart from those, it's how you have just said, we have to keep track of the indexes we create because there is no Information.TemporaryIndexes table, but it would be a nice additional feature .

That surprises me since its relying on an sql statement. Is that in the manual anywhere?

Roy

Roy,

How would the constraints be enforced without an index - it would be terribly inefficient.
I can't remember if it's in the manual or not... but you can check it yourself, just create a temporary table with a primary key and then look in the query plans when you select from the temp table.

--
Fernando Dias
[Team Elevate]

Fernando


So it does

But only if there's no JOIN in there (may be more conditions but that was the only quick test I could think of)

Must have been one of the enhancements my memory hasn't retained. I'm trying to decide if I like it. One part of me says "that's convenient" and another part says "what if I don't want the primary key or constraints. Is there a syntax for that - can't see one in the manual.

Roy Lambert

Roy,

The syntax is the same syntax you would use with persistent tables, except for the use of the TEMPORARY word. If you don't want any constraints, just don't mention them in the CREATE statement.

For the automatically created indexes, and as far as I know, these are the rules:

- CREATE ... (LIKE ...) : it's not even possible to add any constraints, and no indexes are created;

- CREATE ... AS <Query> : there will be no constraints in the temp table, but an index is going to be automatically created, based on the primary key of the source table if it's a single table; with joins I don't know what the rules are, it still creates an index on the primary key of the driving table, I think.

- CREATE ... <Column Definitions> ... : there will be constraints and the corresponding indexes, when specified in the create statement.


--
Fernando Dias
[Team Elevate]

Is there a way to get a list of Information tables that are available instead of relying on those documented in the manual?

As we all know, there is information.tables that shows the user tables used in the database, but is there an information.informationtables that shows the internal tables (information tables) that are available?

Barry
P.S. I know this is kind of deep (recursive) for a Monday morning, but a fresh pot of coffee may help snap us out of our paradoxical dilemma.

Barry,


SELECT * FROM TimYoung.InformationTables

--
Fernando Dias
[Team Elevate]

Fernando

>The syntax is the same syntax you would use with persistent tables, except for the use of the TEMPORARY word. If you don't want any constraints, just don't mention them in the CREATE statement.

OK I misunderstood your first post - I though you meant that the CREATE TEMPORARY TABLE AS created them automagically.

Roy