Re: Should one include partitioning key column as first column of non-unique local index
Date: Mon, 15 Aug 2011 16:33:13 +0200
Message-ID: <9asp2nFqkqU1_at_mid.individual.net>
On 15.08.2011 09:51, vsevolod afanassiev wrote: > I think Oracle doesn't allow compression of partitioned indexes
I don't know about 9.* but I am sure that 10 does.
robert
Note: you can see the following also here if the formatting is garbled: http://pastie.org/2375400
Raw script is here:
http://pastie.org/2375412
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 15 16:32:09 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> select * from PRODUCT_COMPONENT_VERSION
2 ;
PRODUCT
VERSION
STATUS
NLSRTL
10.2.0.4.0
Production
Oracle Database 10g Enterprise Edition
10.2.0.4.0
Prod
PL/SQL
10.2.0.4.0
Production
TNS for Linux:
10.2.0.4.0
Production
SQL> create table foo (
2 year_month varchar2(6 char) not null,
3 tx_id number(10) not null,
4 data varchar2(1000)
5 )
6 partition by range ( year_month )
7 (
8 partition p201101 values less than ( '201101' ), 9 partition p201102 values less than ( '201102' ), 10 partition p201103 values less than ( '201103' ), 11 partition p201104 values less than ( '201104' ),12 partition pMAX values less than ( MAXVALUE ) 13 )
14 ;
Table created.
SQL> insert into foo
2 select '2011' || substr(to_char(mod(level, 10) + 1, '00'), 2)
3 , mod(level, 7)
4 , rpad('*', 100 + mod(level, 900), '*')
5 from dual
6 connect by level <= 1000;
1000 rows created.
SQL> create index foo_ym_tx on foo ( year_month, tx_id )
2 compress 1
3 local
4 ;
Index created.
SQL> set autotrace traceonly
SQL> select year_month, tx_id
2 from foo
3 where year_month = '201103' and tx_id = 6
4 ;
14 rows selected.
Execution Plan
Plan hash value: 4181563890
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
| 0 | SELECT STATEMENT | | 14 | 378 | 2 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 14 | 378 | 2 (0)| 00:00:01 | 4 | 4 | |* 2 | INDEX RANGE SCAN | FOO_YM_TX | 14 | 378 | 2 (0)| 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("YEAR_MONTH"='201103' AND "TX_ID"=6)
Note
- dynamic sampling used for this statement
Statistics
212 recursive calls 0 db block gets 45 consistent gets 0 physical reads 0 redo size 571 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed
SQL> set autotrace off
SQL> select dbms_metadata.get_ddl('INDEX', 'FOO_YM_TX') from dual
2 ;
DBMS_METADATA.GET_DDL('INDEX','FOO_YM_TX')
CREATE INDEX "RK"."FOO_YM_TX" ON "RK"."FOO" ("YEAR_MONTH", "TX_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT) LOCAL
(PARTITION "P201101"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P201102"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P201103"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ,
PARTITION "P201104"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ,
PARTITION "PMAX"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ) COMPRESS 1
SQL> drop table foo
2 ;
Table dropped.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Mon Aug 15 2011 - 09:33:13 CDT