Re: Z-tables in star co-ordinator databases
- From: JTM <mcgrawjt@xxxxxxxxxxx>
- Date: Mon, 25 Jun 2007 13:09:13 -0700
Interesting,
So if I open a normal dB session to a non-distributed dB, and run a
create table as, and it fails I never see any of the remnants, ever?
Under what circumstances would I encounter a "zTABLE" in a non-
distributed dB?
Tyler
On Jun 25, 3:36 pm, Karl & Betty Schendel <schen...@xxxxxxxxxxxxxx>
wrote:
At 12:04 PM -0700 6/25/07, JTM wrote:
Are you sure?
:-)
Yep.
I wrote most of the DDL side for partitioned tables, and I
had to learn way more than I ever wanted to know about how
CREATE works. :)
In my mind the newly created table wouldn't exist unless the actual
select query was completed.
EXAMPLE:
create table tyler as select * from awsomeness;
"Tyler" wouldn't exist until all the rows were selected from
"awsomeness". If it can't complete the select statement, it couldn't
build the table def. or are you saying the table is created first and
then data is loaded? Not sure I believe that. (friendly disagreement)
It has to have something to load. It creates the table first
(but does not commit the create, of course!) and the loads it from
the select results. If something goes puke in the select and
causes a statement or transaction rollback, the table create is
also rolled back and you never see it.
Wanna see it? "set trace point op149" and then run your CTAS.
The first action you see is "DMU UCRT" which is server-speak
for "create a user table in a create-as-select context".
Then you see your SELECT query plan feeding a LOAD or PUT
action which is what loads the new table.
One other hint: if you take a look at the database data
directory after a CTAS before you commit it, you'll see a
recent file with a .d00 extension. That's the stub disk file
from the original create. The LOAD throws it away and
creates a new disk file. The .d00 is deleted at commit
time. (This is true only if your CTAS does a bulk-load,
of course. A CTAS into a journaled table has to do row-puts
and there's no .d00 file.)
Karl
On Jun 25, 2:51 pm, Karl & Betty Schendel <schen...@xxxxxxxxxxxxxx>
wrote:
At 11:38 AM -0700 6/25/07, JTM wrote:
Another scenario I've seen in which these tables show up is after a
"create table as select * from blah" falure. If the table create
fails for whatever reason, you're left with these zTABLES. (when
submited via a batch/cron job) This doesn't apply to STAR only. You
can get these temp "zTables" in regular dB's as well.
Only if the DB is being accessed via Star somehow. (in other words
the zNNNN tables need not show up only in the CDB.)
Of course, it might be a user doing it too.
I don't know of any way that the DBMS would create a zNNNNN temp
table outside of Star. Create table as select creates the real
table and then does a rebuild/recreate load into the table - no
temps involved.
Karl
_______________________________________________
Info-Ingres mailing list
Info-Ing...@xxxxxxxxxxxxxxxxxxxxxxxxx
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres
.
- Follow-Ups:
- Re: [Info-Ingres] Z-tables in star co-ordinator databases
- From: Karl & Betty Schendel
- Re: [Info-Ingres] Z-tables in star co-ordinator databases
- References:
- [Info-Ingres] Z-tables in star co-ordinator databases
- From: martin . bowes
- Re: Z-tables in star co-ordinator databases
- From: JTM
- Re: [Info-Ingres] Z-tables in star co-ordinator databases
- From: Karl & Betty Schendel
- Re: Z-tables in star co-ordinator databases
- From: JTM
- Re: [Info-Ingres] Z-tables in star co-ordinator databases
- From: Karl & Betty Schendel
- [Info-Ingres] Z-tables in star co-ordinator databases
- Prev by Date: Re: [Info-Ingres] Z-tables in star co-ordinator databases
- Next by Date: Re: [Info-Ingres] Z-tables in star co-ordinator databases
- Previous by thread: Re: [Info-Ingres] Z-tables in star co-ordinator databases
- Next by thread: Re: [Info-Ingres] Z-tables in star co-ordinator databases
- Index(es):
Relevant Pages
|
|
