Performance with temporary table

From: Dmitry Guralnik <DGuralnik_at_chat.ru>
Date: Thu, 4 Oct 2001 16:44:31 +0400
Message-ID: <9phla5$1bar$1@josh.sovintel.ru>

Hi

I have a global temporary table, which I created using clause CREATE GLOBAL TEMPORARY TABLE tmp_org_structure (   owner_id NUMBER(5) PRIMARY KEY,
  lev NUMBER(1) NOT NULL,
  typ VARCHAR2(1) NOT NULL,
  CHECK (typ IN ('>', '<', '='))
) ON COMMIT PRESERVE ROWS;
Data is inserted into the table on after logon trigger. There are only 15 rows in the table.

But for cost based optimization I have very strange explain plan for Select * from tmp_org_structure:

Operation                            Object Name
Rows Bytes   Cost TQ In/Out PStart PStop
SELECT STATEMENT                                                    8 K

16
  TABLE ACCESS FULL TMP_ORG_STRUCTURE 8 K 223 K 16 So much cost and so many rows?!

When I create usual table with same columns and data I get this explain plan

Operation                         Object Name     Rows Bytes Cost TQ In/Out
PStart PStop
SELECT STATEMENT                            15                 1
  TABLE ACCESS FULL CUSTTEST     15       90       1

This plan looks right. There are 15 rows in fact.

Why temporary table has this plan? What should I do to get 15 rows in plan for temporary table?
My Oracle version is Oracle8i Release 8.1.6.1

Thank you.

Sincerely,
Dmitry Guralnik

EMail: DGuralnik_at_chat.ru Received on Thu Oct 04 2001 - 07:44:31 CDT