Re: Using global index on partitioned table vs index on the same but non-partitioned table

From: Tim Gorman <tim_at_evdbt.com>
Date: Mon, 18 Apr 2016 07:30:15 -0600
Message-ID: <7b3b488e-fa32-6e28-2ed4-c8e68726fd2b_at_evdbt.com>

Global indexes use extended rowids (10 bytes) in the index leaf blocks, while indexes on non-partitioned tables and local indexes use the restricted rowid (6 bytes).

On 4/18/16 07:21, Marko Sutic wrote:
> Hello all,
>
> is there any difference if you have global index on partitioned table
> vs regular btree index on (same) non-partitioned table?
>
> I have created simple test case and noticed that index on partitioned
> table is bigger.
> Why is this?
> I would expect for both indexes the same size and same performance
> behavior.
>
> Are there any performance drawbacks if you have partitioned table with
> global index? (ignore maintenance)
>
>
> Probably I'm missing something obvious...
>
> Thanks!
>
>
> CREATE TABLE objekti_part
> ( ID number NOT NULL,
> owner VARCHAR2(128) NOT NULL,
> OBJECT_NAME VARCHAR2(128) NOT NULL,
> OBJECT_ID NUMBER,
> CREATED DATE,
> EDITION_NAME VARCHAR2(128))
> PARTITION BY RANGE (ID)
> (PARTITION p1 VALUES LESS THAN (10000) TABLESPACE users,
> PARTITION p2 VALUES LESS THAN (20000) TABLESPACE users,
> PARTITION p3 VALUES LESS THAN (30000) TABLESPACE users,
> PARTITION p4 VALUES LESS THAN (40000) TABLESPACE users,
> PARTITION p5 VALUES LESS THAN (50000) TABLESPACE users,
> PARTITION p6 VALUES LESS THAN (60000) TABLESPACE users,
> PARTITION p7 VALUES LESS THAN (70000) TABLESPACE users,
> PARTITION p8 VALUES LESS THAN (80000) TABLESPACE users,
> PARTITION p9 VALUES LESS THAN (90000) TABLESPACE users,
> PARTITION p10 VALUES LESS THAN (100000) TABLESPACE users,
> PARTITION p11 VALUES LESS THAN (110000) TABLESPACE users,
> PARTITION p12 VALUES LESS THAN (120000) TABLESPACE users,
> PARTITION p13 VALUES LESS THAN (130000) TABLESPACE users,
> PARTITION p14 VALUES LESS THAN (140000) TABLESPACE users,
> PARTITION p15 VALUES LESS THAN (150000) TABLESPACE users,
> PARTITION p16 VALUES LESS THAN (160000) TABLESPACE users,
> PARTITION p17 VALUES LESS THAN (MAXVALUE) TABLESPACE users
> );
>
> insert into objekti_part
> select rownum, owner, object_name, object_id, created, edition_name
> from dba_objects;
>
> 164.668 rows inserted.
>
> create table objekti_nopart as
> select rownum ID, owner, object_name, object_id, created, edition_name
> from dba_objects;
>
> create index idx_p_obj_id on objekti_part(object_id);
> create index idx_np_obj_id on objekti_nopart(object_id);
>
> begin
> dbms_stats.gather_table_stats('MSUTIC','OBJEKTI_PART', cascade=>TRUE,
> granularity=>'ALL',estimate_percent=>100);
> end;
> /
> begin
> dbms_stats.gather_table_stats('MSUTIC','OBJEKTI_NOPART',
> cascade=>TRUE, estimate_percent=>100);
> end;
> /
>
> select index_name, table_name, blevel, leaf_blocks, distinct_keys,
> num_rows, clustering_factor
> from dba_indexes
> where index_name in ('IDX_P_OBJ_ID','IDX_NP_OBJ_ID');
>
>
> INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS
> NUM_ROWS CLUSTERING_FACTOR
> --------------- --------------- ---------- ----------- -------------
> ---------- -----------------
> IDX_P_OBJ_ID OBJEKTI_PART 1 458 164574
> 164574 3157
> IDX_NP_OBJ_ID OBJEKTI_NOPART 1 366 164575
> 164575 3151
>
>
> select segment_name, blocks, bytes
> from dba_segments
> where segment_name in ('IDX_P_OBJ_ID','IDX_NP_OBJ_ID');
>
> SEGMENT_NAME BLOCKS BYTES
> --------------- ---------- ----------
> IDX_P_OBJ_ID 512 4194304
> IDX_NP_OBJ_ID 384 3145728
>
>
>
> select count(*) from objekti_part where object_id between 20000 and 40000;
>
> -----------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows |E-Bytes|
> Cost (%CPU)| A-Rows | A-Time | Buffers |
> -----------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | | |
> 1 (100)| 1 |00:00:00.01 | 57 |
> | 1 | SORT AGGREGATE | | 1 | 1 | 5 |
> | 1 |00:00:00.01 | 57 |
> |* 2 | INDEX RANGE SCAN| IDX_P_OBJ_ID | 1 | 10399 | 51995 |
> 1 (0)| 19974 |00:00:00.01 | 57 |
> -----------------------------------------------------------------------------------------------------------------
>
> select count(*) from objekti_nopart where object_id between 20000 and
> 40000;
>
> ------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows |E-Bytes|
> Cost (%CPU)| A-Rows | A-Time | Buffers |
> ------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | | | 1
> (100)| 1 |00:00:00.01 | 46 |
> | 1 | SORT AGGREGATE | | 1 | 1 | 5 |
> | 1 |00:00:00.01 | 46 |
> |* 2 | INDEX RANGE SCAN| IDX_NP_OBJ_ID | 1 | 10399 | 51995 |
> 1 (0)| 19974 |00:00:00.01 | 46 |
> ------------------------------------------------------------------------------------------------------------------
>
>
>
> Regards,
> Marko
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 18 2016 - 15:30:15 CEST