Re: Insider #60: Highlights: Logo contest, Cool SMI queries, IDS Training, Chat wit the lab, Calendar
"IIUG Emailer" <emailer@iiug.org> writes:
> IUG Insider (Issue #60) June 2005
>
> Highlights: Logo contest, Cool SMI queries, IDS Training, Chat wit the lab, Calendar
[...] (snipped)
> - Identifying temp tables
>
> Ever wonder what temp tables are out there and how much space they are taking up in your server? Try this one.
>
> SELECT n.dbsname AS database,
> n.owner AS owner,
> n.tabname AS temp_tabname,
> case
> when BITVAL(i.ti_flags, "0x0020") = 1
> then "System temp table"
> when BITVAL(i.ti_flags, "0x0040") = 1
> end AS temp_type,
> COUNT(*) AS num_fragments,
> SUM(i.ti_nptotal) AS total_pages,
> SUM(i.ti_nrows) AS total_rows
> FROM systabnames n,
> systabinfo i
> WHERE (BITVAL(i.ti_flags, "0x0020") = 1
> OR BITVAL(i.ti_flags, "0x0040") = 1)
> AND i.ti_partnum = n.partnum
> GROUP BY 1, 2, 3, 4;
There is a syntax error in this SQL. The 'then'-part of the last 'when'-clause
is missing.
Besides, on my system (9.40.FC5) bitval(i.ti_flags, "0x0020") = 1 is true
in all the cases when bitval(i.ti_flags, "0x0040") = 1 is also true, so
every temp table is a "System temp table".
What is wrong here?
--
'yvind
On Fri, 01 Jul 2005 11:49:04 +0200, ogj@tollpost.no wrote:
>"IIUG Emailer" <emailer@iiug.org> writes:
>
>> IUG Insider (Issue #60) June 2005
>>
>> Highlights: Logo contest, Cool SMI queries, IDS Training, Chat wit the lab, Calendar
>
>[...] (snipped)
>
>> - Identifying temp tables
>>
>> Ever wonder what temp tables are out there and how much space they are taking up in your server? Try this one.
>>
>> SELECT n.dbsname AS database,
>> n.owner AS owner,
>> n.tabname AS temp_tabname,
>> case
>> when BITVAL(i.ti_flags, "0x0020") = 1
>> then "System temp table"
>> when BITVAL(i.ti_flags, "0x0040") = 1
>> end AS temp_type,
>> COUNT(*) AS num_fragments,
>> SUM(i.ti_nptotal) AS total_pages,
>> SUM(i.ti_nrows) AS total_rows
>> FROM systabnames n,
>> systabinfo i
>> WHERE (BITVAL(i.ti_flags, "0x0020") = 1
>> OR BITVAL(i.ti_flags, "0x0040") = 1)
>> AND i.ti_partnum = n.partnum
>> GROUP BY 1, 2, 3, 4;>
>There is a syntax error in this SQL. The 'then'-part of the last 'when'-clause
>is missing.
>
>Besides, on my system (9.40.FC5) bitval(i.ti_flags, "0x0020") = 1 is true
>in all the cases when bitval(i.ti_flags, "0x0040") = 1 is also true, so
>every temp table is a "System temp table".
>
>What is wrong here?
The query should also have
case
when BITVAL(i.ti_flags, "0x0020") = 1
then "System temp table"
when BITVAL(i.ti_flags, "0x0040") = 1
then "User temp table"
end AS temp_type,
0x0020 = system temp table, such as temp tables for sorting, etc.
0x0040 = user temp table (CREATE TABLE or SELECT....INTO TEMP
JWC
ogj@tollpost.no wrote:
> "IIUG Emailer" <emailer@iiug.org> writes:
>
>
>>IUG Insider (Issue #60) June 2005
>>
>>Highlights: Logo contest, Cool SMI queries, IDS Training, Chat wit the lab, Calendar
>
>
> [...] (snipped)
>
>
>>- Identifying temp tables
>>
>>Ever wonder what temp tables are out there and how much space they are taking up in your server? Try this one.
>>
>>SELECT n.dbsname AS database,
>> n.owner AS owner,
>> n.tabname AS temp_tabname,
>> case
>> when BITVAL(i.ti_flags, "0x0020") = 1
>> then "System temp table"
>> when BITVAL(i.ti_flags, "0x0040") = 1
>> end AS temp_type,
>> COUNT(*) AS num_fragments,
>> SUM(i.ti_nptotal) AS total_pages,
>> SUM(i.ti_nrows) AS total_rows
>>FROM systabnames n,
>> systabinfo i
>>WHERE (BITVAL(i.ti_flags, "0x0020") = 1
>> OR BITVAL(i.ti_flags, "0x0040") = 1)
>> AND i.ti_partnum = n.partnum
>>GROUP BY 1, 2, 3, 4;>
>
> There is a syntax error in this SQL. The 'then'-part of the last 'when'-clause
> is missing.
>
> Besides, on my system (9.40.FC5) bitval(i.ti_flags, "0x0020") = 1 is true
> in all the cases when bitval(i.ti_flags, "0x0040") = 1 is also true, so
> every temp table is a "System temp table".
>
> What is wrong here?
You're correct. Actually that 'then' and another whole 'when' apparently
went AWOL when I moused the query into WORD from sqlcmd. My apologies, and
please, count Gary blameless. I've fixed it and expanded the descriptions.
Here's the complete query:
SELECT n.dbsname AS database,
n.owner AS owner,
n.tabname AS temp_tabname,
case
when BITVAL(i.ti_flags, "0x0020") = 1
then "System created temp table"
when BITVAL(i.ti_flags, "0x0040") = 1
then "User created temp table"
when BITVAL(i.ti_flags, "0x4000") = 1
then "Special function temp table"
end AS temp_type,
COUNT(*) AS num_fragments,
SUM(i.ti_nptotal) AS total_pages,
SUM(i.ti_nrows) AS total_rows
FROM systabnames n,
systabinfo i
WHERE (BITVAL(i.ti_flags, "0x0020") = 1
OR BITVAL(i.ti_flags, "0x0040") = 1)
AND i.ti_partnum = n.partnum
GROUP BY 1, 2, 3, 4;
As to you seeing all of your temp tables with both 0x0020 & 0x0040 set, I
dunno. 0x0020 in ti_flags indicates a temp table automatically created by
the engine to satisfy a join condition or to support a SCROLL CURSOR or
REPEATABLE READ isolation query. If ti_flags contains the bit 0x0040 set
that indicates a temp table created explicitely by a user with CREATE TEMP
TABLE... or SELECT...INTO TEMP... Then final value, 0x4000, is only
described as a special function temp table which does not require bitmap
maintenance. Anyone out there know why 'yvind is seeing combinations of
these flags set? John? Madison?
Also missing is an attribution. I believe Jake Solomon first showed a
version of this one to me.
Art S. Kagel