| Index on Global temp tables [message #461118] |
Wed, 16 June 2010 12:14  |
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
|
Member |
|
|
all,
I am trying to use Global temporary tables, and index on this table to get my results faster. I can see even if I run any query on this table, it does full table scan and not Index scan.., any idea why this is the case?
create global temporary table abc_tab on commit preserve rows
as select a,b,c from xyz;
create index lmn on abc_tab(a,b,c)
Thanksm
freakabhi
|
|
|
|
|
|
| Re: Index on Global temp tables [message #461120 is a reply to message #461118] |
Wed, 16 June 2010 12:21  |
 |
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Because there is no statistics on the GTT and so default is taken which is very few rows (don't currently remember the actual value) and so index is not useful (as it is estimated by the optimizer).
If you want this index to be used you have to use CARDINALITY hint on the query to give the optimizer a good estimation of the number of rows in the GTT.
Executing the query with autotrace on will show you this fact.
Regards
Michel
[Updated on: Wed, 16 June 2010 12:21] Report message to a moderator |
|
|
|
|
|
| Re: Index on Global temp tables [message #461123 is a reply to message #461118] |
Wed, 16 June 2010 12:27 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I get index use when I try it:create global temporary table test_030
(col_1 number, col_2 varchar2(100))
on commit preserve rows
;
create index test_030_idx on test_030(col_1);
insert into test_030 select level,'Row '||level from dual connect by level <= 10000;
commit;
explain plan for
select *
from test_030
where col_1 = 345;
select * from table(dbms_xplan.display());
Plan hash value: 3106667229
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_030 | 1 | 65 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_030_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_1"=345)
Note
-----
- dynamic sampling used for this statement |
|
|
|
|
|
| Re: Index on Global temp tables [message #461128 is a reply to message #461124] |
Wed, 16 June 2010 12:45 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Or you could gather statistics on the GTT after loading it:begin
dbms_stats.gather_table_stats(ownname => null
,tabname => 'TEST_030'
,method_opt => 'FOR ALL INDEXED COLUMNS'
,cascade => true);
end;
/
explain plan for
select col_1,col_2
from test_030
where col_1 = 567;
select * from table(dbms_xplan.display());
Plan hash value: 3106667229
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_030 | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_030_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_1"=567) |
|
|
|
| Re: Index on Global temp tables [message #461130 is a reply to message #461128] |
Wed, 16 June 2010 12:50 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Or you could be a traditionalist and just use an INDEX hint (I dropped and recreated the table for this example to get rid of the stats that were there previously):explain plan for
select /*+ dynamic_sampling(0) index */
col_1,col_2
from test_030
where col_1 = 324;
select * from table(dbms_xplan.display());
Plan hash value: 3106667229
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 5330 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_030 | 82 | 5330 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_030_IDX | 33 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_1"=324) |
|
|
|
|
|
|
|
| Re: Index on Global temp tables [message #461299 is a reply to message #461134] |
Thu, 17 June 2010 05:51 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:I see you are using 11g and the original poster is using 10g.
Not I, Ma'am. 10g all the way for me.
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
"CORE 10.2.0.4.0 Production"
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
The odd thing is I've not enabled dynamic sampling myself - it came this way out of the box.
select name,value,isdefault from v$parameter where name ='optimizer_dynamic_sampling';
NAME VALUE ISDEFAULT
--------------------------- ------------------ -------------
optimizer_dynamic_sampling 2 TRUE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
