Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 13 total |
Why is there no Information.TemporaryIndexes table? |
Sun, Mar 10 2013 12:05 AM | Permanent Link |
Barry | 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 |
Sun, Mar 10 2013 4:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Sun, Mar 10 2013 10:43 AM | Permanent Link |
Fernando Dias 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] |
Sun, Mar 10 2013 11:24 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
Sun, Mar 10 2013 2:22 PM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Mon, Mar 11 2013 4:52 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
Mon, Mar 11 2013 11:47 AM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Mon, Mar 11 2013 11:48 AM | Permanent Link |
Barry | 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. |
Mon, Mar 11 2013 11:55 AM | Permanent Link |
Fernando Dias Team Elevate | Barry,
SELECT * FROM TimYoung.InformationTables -- Fernando Dias [Team Elevate] |
Mon, Mar 11 2013 12:08 PM | Permanent Link |
Roy Lambert NLH Associates 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |