Re: Performance with temporary table

In article <9phla5$1bar$1_at_josh.sovintel.ru>, "Dmitry says...
>
>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
>
>

the problem is that you cannot analyze a temp table. You can analyze a real table. So the optimizer "guesses".

Use dbms_stats to set the table stats to your liking, eg:

exec dbms_stats.set_table_stats( user, 'T', numRows => 15, numBlks => 1 );

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp