Home > Archive > SQL Anywhere database > April 2005 > Create index on temporary table









You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

 

Author Create index on temporary table
ava3v

2005-04-24, 9:23 am

Hello all!

Is this Ok, to make a COMMIT when creating an index on local temporary
table?
I think, it's not (DROP statements don't do this):
in script below, I expect no rows in #tmp table, but there are 10.
ASA 9.0.2 ebf 3044.

Best regards,
Venedict

CREATE TABLE "baseTable" (
"ID" integer NOT NULL default autoincrement,
"Name" char(40) NOT NULL,
"isActive" bit NOT NULL,
PRIMARY KEY ( "ID" )
);

begin
declare i int;
set i = 1;
while i <= 10 loop
insert into baseTable ("Name", isActive) values (cast(now() as time),
1);
select * into #tmp from baseTable;
create index idx_1 on #tmp ("ID"); -- this causes COMMIT
set i = i + 1;
drop table #tmp;
end loop;
rollback;
select * from baseTable;
end

--drop table baseTable;


Joshua Savill

2005-04-25, 9:23 am

The CREATE INDEX statement has a side effect of an automatic commit. Please
see the following:

ASA SQL Reference
SQL Statements
CREATE INDEX statement

Online:
http://www.ianywhere.com/developer/...n9/00000365.htm

create index idx_1 on #tmp ("ID"); -- this causes COMMIT
--->This statement will cause the commit of the rows into the baseTable. The
rollback comes after the commit and does not have an effect on the 10 rows.

--
Joshua Savill
iAnywhere Solutions - Product Support Analyst


"ava3v" <ava3v@svitonline.com> wrote in message
news:426b96fd@forums
-1-dub...
> Hello all!
>
> Is this Ok, to make a COMMIT when creating an index on local temporary
> table?
> I think, it's not (DROP statements don't do this):
> in script below, I expect no rows in #tmp table, but there are 10.
> ASA 9.0.2 ebf 3044.
>
> Best regards,
> Venedict
>
> CREATE TABLE "baseTable" (
> "ID" integer NOT NULL default autoincrement,
> "Name" char(40) NOT NULL,
> "isActive" bit NOT NULL,
> PRIMARY KEY ( "ID" )
> );
>
> begin
> declare i int;
> set i = 1;
> while i <= 10 loop
> insert into baseTable ("Name", isActive) values (cast(now() as time),
> 1);
> select * into #tmp from baseTable;
> create index idx_1 on #tmp ("ID"); -- this causes COMMIT
> set i = i + 1;
> drop table #tmp;
> end loop;
> rollback;
> select * from baseTable;
> end
>
> --drop table baseTable;
>
>


ava3v

2005-04-25, 8:24 pm

Hello!

As for me, there is no need to call a COMMIT when creating an index on local
temporary tables!
There's no COMMIT when dropping an index on temporary table or when dropping
a temporary table.
Also there's no COMMIT when creating a local temporary table from select
(select ... into #tbl from ...).
So, why there's a COMMIT when creating an index on _local temporary table_?
I know, that create index statement has a side effect of a COMMIT, but don't
understand why this should take place when working with local temporary
tables. IMHO, an automatic COMMIT should take place when creating an index
on BASE tables as in case of DROP statement.

Best regards,
Venedict

"Joshua Savill" <jsavill@ianywhere.com> wrote in message
news:426cf27f@forums
-1-dub...
> The CREATE INDEX statement has a side effect of an automatic commit.
Please
> see the following:
>
> ASA SQL Reference
> SQL Statements
> CREATE INDEX statement
>
> Online:
>
http://www.ianywhere.com/developer/...e/0902/en/html/
dbrfen9/00000365.htm
>
> create index idx_1 on #tmp ("ID"); -- this causes COMMIT
> --->This statement will cause the commit of the rows into the baseTable.
The
> rollback comes after the commit and does not have an effect on the 10
rows.
>
> --
> Joshua Savill
> iAnywhere Solutions - Product Support Analyst
>
>
> "ava3v" <ava3v@svitonline.com> wrote in message
> news:426b96fd@forums
-1-dub...
>
>


Joshua Savill

2005-04-26, 8:23 pm

Venedict,

The DROP TABLE and DROP INDEX also have side effects of an automatic commit.
This is included on temporary tables.

If you run:

drop table #tmp;
select * into #tmp from baseTable;
rollback;

The #tmp table will still exist even after the rollback.

--
Joshua Savill
iAnywhere Solutions - Product Support Analyst


"ava3v" <ava3v@svitonline.com> wrote in message
news:426d4d2d@forums
-2-dub...
> Hello!
>
> As for me, there is no need to call a COMMIT when creating an index on
> local
> temporary tables!
> There's no COMMIT when dropping an index on temporary table or when
> dropping
> a temporary table.
> Also there's no COMMIT when creating a local temporary table from select
> (select ... into #tbl from ...).
> So, why there's a COMMIT when creating an index on _local temporary
> table_?
> I know, that create index statement has a side effect of a COMMIT, but
> don't
> understand why this should take place when working with local temporary
> tables. IMHO, an automatic COMMIT should take place when creating an index
> on BASE tables as in case of DROP statement.
>
> Best regards,
> Venedict
>
> "Joshua Savill" <jsavill@ianywhere.com> wrote in message
> news:426cf27f@forums
-1-dub...
> Please
> http://www.ianywhere.com/developer/...e/0902/en/html/
> dbrfen9/00000365.htm
> The
> rows.
>
>


ava3v

2005-04-26, 8:23 pm

Hello Joshua,

From Help: ASA SQL Reference -> SQL Statements -> DROP statement

Side effects
Automatic commit. Clears the Results tab in the Results pane in Interactive
SQL. DROP TABLE and DROP INDEX close all cursors for the current connection.
_Local temporary tables is an exception; no commit is performed when one is
dropped._
So, I think it would be more logical to have no COMMIT when creating index
on local temporary tables.
Best regards,

Venedict

"Joshua Savill" <jsavill@ianywhere.com> wrote in message
news:426e96e6$1@foru
ms-1-dub...
> Venedict,
>
> The DROP TABLE and DROP INDEX also have side effects of an automatic
commit.
> This is included on temporary tables.
>
> If you run:
>
> drop table #tmp;
> select * into #tmp from baseTable;
> rollback;
>
> The #tmp table will still exist even after the rollback.
>
> --
> Joshua Savill
> iAnywhere Solutions - Product Support Analyst
>
>
> "ava3v" <ava3v@svitonline.com> wrote in message
> news:426d4d2d@forums
-2-dub...
index[color=darkred]

http://www.ianywhere.com/developer/...e/0902/en/html/[color=darkred]
baseTable.[color=darkred]
temporary[color=dark
red]
>
>


begin 666 arrowdn.gif
M1TE&.#EA"P`+`/<``````/ ____________________
__________________
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
__RP`````"P`+`$ (* `!"!Q(4&" @P@1&DR8<"'#
9@PX?%IP8D6'%A@`"4-28\2'$CAX!! 0`.P``
`
end

Scott Holman

2005-04-26, 8:23 pm

Venedict,

In prior versions (6.0 that i know of) there was not a commit. It is now
the behaviour of the 9.0.x product. If you really need an index other than
PK then you should use a global temporary table that is either created by
your DB setup scripts at install or created at runtime before starting the
transaction context in which you will need to use the global temp table.


"ava3v" <ava3v@svitonline.com> wrote in message
news:426e99a5@forums
-1-dub...
> Hello Joshua,
>
> From Help: ASA SQL Reference -> SQL Statements -> DROP statement
>
> Side effects
> Automatic commit. Clears the Results tab in the Results pane in
> Interactive
> SQL. DROP TABLE and DROP INDEX close all cursors for the current
> connection.
> _Local temporary tables is an exception; no commit is performed when one
> is
> dropped._
> So, I think it would be more logical to have no COMMIT when creating index
> on local temporary tables.
> Best regards,
>
> Venedict
>
> "Joshua Savill" <jsavill@ianywhere.com> wrote in message
> news:426e96e6$1@foru
ms-1-dub...
> commit.
> index
> http://www.ianywhere.com/developer/...e/0902/en/html/
> baseTable.
> temporary
>
>
>


Joshua Savill

2005-04-26, 8:23 pm

Pardon my statement. It should have read: This is NOT included on temporary
tables.

--
Joshua Savill
iAnywhere Solutions - Product Support Analyst

"ava3v" <ava3v@svitonline.com> wrote in message
news:426e99a5@forums
-1-dub...
> Hello Joshua,
>
> From Help: ASA SQL Reference -> SQL Statements -> DROP statement
>
> Side effects
> Automatic commit. Clears the Results tab in the Results pane in
> Interactive
> SQL. DROP TABLE and DROP INDEX close all cursors for the current
> connection.
> _Local temporary tables is an exception; no commit is performed when one
> is
> dropped._
> So, I think it would be more logical to have no COMMIT when creating index
> on local temporary tables.
> Best regards,
>
> Venedict
>
> "Joshua Savill" <jsavill@ianywhere.com> wrote in message
> news:426e96e6$1@foru
ms-1-dub...
> commit.
> index
> http://www.ianywhere.com/developer/...e/0902/en/html/
> baseTable.
> temporary
>
>
>


Joshua Savill

2005-04-26, 8:23 pm

Venedict,

I will look further into the reasoning behind having the autocommit for a
CREATE INDEX statement on a temporary table.

--
Joshua Savill
iAnywhere Solutions - Product Support Analyst


"ava3v" <ava3v@svitonline.com> wrote in message
news:426e99a5@forums
-1-dub...
> Hello Joshua,
>
> From Help: ASA SQL Reference -> SQL Statements -> DROP statement
>
> Side effects
> Automatic commit. Clears the Results tab in the Results pane in
> Interactive
> SQL. DROP TABLE and DROP INDEX close all cursors for the current
> connection.
> _Local temporary tables is an exception; no commit is performed when one
> is
> dropped._
> So, I think it would be more logical to have no COMMIT when creating index
> on local temporary tables.
> Best regards,
>
> Venedict
>
> "Joshua Savill" <jsavill@ianywhere.com> wrote in message
> news:426e96e6$1@foru
ms-1-dub...
> commit.
> index
> http://www.ianywhere.com/developer/...e/0902/en/html/
> baseTable.
> temporary
>
>
>


Sponsored Links





Also available: Server administration forum archive | Web Design forum archive | Software forum archive | Hardware reviews archive | Programming forum archive

Copyright 2008 droptable.com