We have an application with range partitioned tables (Oracle 9.2.0.8).
All partitioned tables use the same partitioning key column called
YearMonth in YYYYMM format (type NUMBER). All indexes on partitioned
tables are partitioned local.
I know that partitioning key column needs to be included in unique
indexes.
However application vendor also included YearMonth column in many
non-unique indexes, so we have two- and three-column indexes
(YearMonth,AccountID), (YearMonth,TransactionID,TransactionStatus),
etc.
I suspect that including partitioning key column in non-unique indexes
isn't required, it just makes index bigger without any benefit.
We keep 2 years of data, so there are 24 partitions and 24 values of
YearMonth,
while there are millions of Accounts and Transactions.

On Aug 11, 4:37*pm, vsevolod afanassiev
<vsevolod.afanass... (AT) gmail (DOT) com> wrote:
Maybe I'm not understanding something, but wouldn't there be a benefit
of allowing index skip scans when you look at accounts across months?
I guess the way to not guess would be to try it with and without the
key column and see if you get different amounts of partition probing.

I have a vague memory of 9i having bugs with some skip scans. I also
have a vague memory of your question being discussed somewhere, but
heck if I can find it quickly. http://forums.oracle.com/forums/thre...696986#9696986
has thoughts on evaluating such things.

(Actually, I found a quote from Jonathan, but seem to be having
trouble with google advanced search to link to it: "Global indexes
allow maximum efficiency of access along the index path - but local
indexes introduce access overheads for all queries that do NOT include
the partitioning key, as every partition of
the index will have to be probed." Subject: Re: Question About
Indexes on a Partitioned Table in this group 06-23-2007)

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...ine-developer/

On Aug 11, 7:37*pm, vsevolod afanassiev
<vsevolod.afanass... (AT) gmail (DOT) com> wrote:
Does the SQL used by the application include the YearMonth column in
lookups ( in the WHERE clause )?

Yes, it does.

Let's say we have SQL statement SELECT * FROM ACCOUNTS WHERE YearMonth
= :b1 and TransactionID = :b2.

Case#1: Non-unique local index on (YearMonth,TransactionID): Oracle
will use RANGE SCAN on one partition.
Case#2: Non-unique local index on (TransactionID): As index is local
and YearMonth is Partitioning Key Oracle will do partition pruning and
then use RANGE SCAN on one partition.

So result is exactly the same. In fact Single-column index seems
better as it will also perform range scan for statement where there is
no condition on YearMonth: SELECT * FROM ACCOUNTS WHERE TransactionID
= :b1.

Indexes need to be local as this is 24x7 system and we need to be able
to drop partition without outage.

On 13.08.2011 02:51, vsevolod afanassiev wrote:
Did you mean INDEX RANGE SCAN? Since YearMonth is in the index Oracle
can do an INDEX RANGE SCAN (after partition pruning of course).

Here Oracle can do pruning and then an INDEX RANGE SCAN. It must then
look at all records found to determine whether YearMonth matches.
Alternatively it might revert to a FULL SCAN of the partition (probably
if it thinks the index range scan wasn't selective enough with regard to
YearMonth values).

If the range partitioning ensures only one value of YearMonth per
partition then in theory the INDEX RANGE SCAN would be be sufficient to
determine all matching records. I don't know whether Oracle 9 does this.

I don't think results are "exactly the same" as shown above. Did you
look at real plans? What do they look like?

Btw, key compression will reduce the size overhead so the size
difference between (YearMonth compressed, TransactionID) and
(TransactionID) is probably not too big.

http://download.oracle.com/docs/cd/B...schem.htm#4602

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

I think Oracle doesn't allow compression of partitioned indexes

On 15.08.2011 09:51, vsevolod afanassiev wrote:
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

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

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/

On 15.08.2011 16:33, Robert Klemme wrote:
PS: Compression works in that version with a global index as well.

PPS: Also I wouldn't know any reason which prohibits key compression for
indexes on partitioned tables.

Did I miss something?

Cheers

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Thanks, I didn't know that partitioned indexes can be compressed.
As per manual (Oracle Database SQL Reference 10g Release 2 (10.2)
B14200-02 December 2005, page 14-67):

"Oracle Database compresses only nonpartitioned indexes that are
nonunique or unique indexes of at least two columns."

What would be syntax for compressing existing partitioned index?

I tried in 10.2.0.4:

SQL> alter index foo_ym_tx rebuild compress;
alter index foo_ym_tx rebuild compress
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole



SQL> alter index foo_ym_tx rebuild partition p201101 compress;
alter index foo_ym_tx rebuild partition p201101 compress
*
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first



SQL> alter index foo_ym_tx modify default attributes compress;
alter index foo_ym_tx modify default attributes compress
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

OK, found Metalink Note 312843.1. The only way to compress partitioned
index is to drop and rebuild. Great.