[20130812]12c Partial Indexes For Partitioned Tables Part I.txt

[20130812]12c Partial Indexes For Partitioned Tables Part I.txt

参考链接：
http://richardfoote.wordpress.com/2013/07/08/12c-partial-indexes-for-partitioned-tables-part-i-ignoreland/

更多的是重复作者的测试，加深理解:

1.测试环境: 

SQL> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

2.测试建立：
create table pink_floyd (id number, status varchar2(6), name varchar2(30))
INDEXING OFF
partition by range (id)
(partition pf1 values less than (1000001),
partition pf2 values less than (2000001) INDEXING OFF,
partition pf3 values less than (maxvalue) INDEXING ON);

--说明：利用indexing off可以定义在那个分区建立索引，前面的INDEXING OFF表示缺省参数，这样pf1分区上相当于INDEXING OFF。

SQL> insert into pink_floyd select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level insert into pink_floyd select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level             *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation
--报错，估计机器配置没有作者的好。修改如下：

SQL> insert into pink_floyd
select rownum, 'CLOSED', 'DAVID BOWIE' from
(select 1 from dual connect by level (select 1 from dual connect by level
3000000 rows created.

SQL> commit;
Commit complete.

SQL> update pink_floyd set status = 'OPEN' where id > 2000000 and mod(id,10000)=0;
100 rows updated.

SQL> commit ;
Commit complete.

SQL> create index pink_floyd_status_i on pink_floyd(status);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'PINK_FLOYD', estimate_percent=>null, cascade=> true, method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS STATUS SIZE 5');
PL/SQL procedure successfully completed.

SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'PINK_FLOYD_STATUS_I';
INDEX_NAME                       NUM_ROWS LEAF_BLOCKS INDEXIN
------------------------------ ---------- ----------- -------
PINK_FLOYD_STATUS_I               3000000        9203 FULL

3.开始测试1： 

SQL> set autot traceonly ;
SQL> select * from pink_floyd where status = 'OPEN';
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3984357026
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                     |   100 |  2500 |     4   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD          |   100 |  2500 |     4   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                         | PINK_FLOYD_STATUS_I |   100 |       |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='OPEN')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        104  consistent gets
          0  physical reads
          0  redo size
       3315  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed
SQL> select * from pink_floyd where status = 'OPEN' and id > 2000001;
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4284033138
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                     |    99 |  2475 |     4   (0)| 00:00:01 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD          |    99 |  2475 |     4   (0)| 00:00:01 |     3 |     3 |
|*  2 |   INDEX RANGE SCAN                         | PINK_FLOYD_STATUS_I |   100 |       |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">2000001)
   2 - access("STATUS"='OPEN')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        104  consistent gets
          0  physical reads
          0  redo size
       1641  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

--可以发现逻辑读一样，实际上我们仅仅需要扫描pf3分区。

4.建立部分索引看看： 

SQL> drop index pink_floyd_status_i;
Index dropped.

--使用关键字indexing partial就可以部分索引。 

SQL> create index pink_floyd_status_i on pink_floyd(status) indexing partial;
Index created.
SQL> set autot off
SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'PINK_FLOYD_STATUS_I';
INDEX_NAME                       NUM_ROWS LEAF_BLOCKS INDEXIN
------------------------------ ---------- ----------- -------
PINK_FLOYD_STATUS_I               1000000        3068 PARTIAL

--这样就实现仅仅对分区pf3的status建立索引，其他分区不需要建立索引。

SQL> select * from pink_floyd where status = 'OPEN' and id >= 2000001;
100 rows selected.


Execution Plan


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


Plan hash value: 4284033138


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


| Id  | Operation                                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |


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


|   0 | SELECT STATEMENT                           |                     |   100 |  2500 |     4   (0)| 00:00:01 |       |       |


|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD          |   100 |  2500 |     4   (0)| 00:00:01 |     3 |     3 |


|*  2 |   INDEX RANGE SCAN                         | PINK_FLOYD_STATUS_I |    33 |       |     3   (0)| 00:00:01 |       |       |


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


Predicate Information (identified by operation id):


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


   1 - filter("ID">=2000001)


   2 - access("STATUS"='OPEN')




Statistics


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


          1  recursive calls


          0  db block gets


        104  consistent gets


          0  physical reads


          0  redo size


       1641  bytes sent via SQL*Net to client


        544  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


        100  rows processed


SQL> select * from pink_floyd where status = 'OPEN';


100 rows selected.


Execution Plan


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


Plan hash value: 4221394394


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


| Id  | Operation                                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |


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


|   0 | SELECT STATEMENT                             |                     |   100 |  2500 |  2475   (1)| 00:00:01 |       |       |


|   1 |  VIEW                                        | VW_TE_2             |    99 |  3465 |  2475   (1)| 00:00:01 |       |       |


|   2 |   UNION-ALL                                  |                     |       |       |            |          |       |       |


|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD          |    33 |   825 |     4   (0)| 00:00:01 | ROWID | ROWID |


|*  4 |     INDEX RANGE SCAN                         | PINK_FLOYD_STATUS_I |   100 |       |     3   (0)| 00:00:01 |       |       |


|   5 |    PARTITION RANGE ITERATOR                  |                     |    66 |  1650 |  2471   (1)| 00:00:01 |     1 |     2 |


|*  6 |     TABLE ACCESS FULL                        | PINK_FLOYD          |    66 |  1650 |  2471   (1)| 00:00:01 |     1 |     2 |


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


Predicate Information (identified by operation id):


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


   3 - filter("PINK_FLOYD"."ID">=2000001 OR "PINK_FLOYD"."ID" IS NULL)


   4 - access("STATUS"='OPEN')


   6 - filter("STATUS"='OPEN')




Statistics


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


          0  recursive calls


          0  db block gets


       8333  consistent gets


          0  physical reads


          0  redo size


       1641  bytes sent via SQL*Net to client


        544  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


        100  rows processed


--这样查询，分区pf1与pf2全表扫描，而pf3使用索引。
--另外从逻辑读上select * from pink_floyd where status = 'OPEN' and id >= 2000001;依旧是104，没有改进。


5.再看看下面的例子：


SQL> drop table PINK_FLOYD purge ;
Table dropped.


create table pink_floyd (id number, status varchar2(6), name varchar2(30))
indexing off
partition by range (id) subpartition by list(status)
subpartition template
(subpartition closed values ('CLOSED') indexing off, subpartition open values ('OPEN') indexing on)
(partition pf1 values less than (1000001),
partition pf2 values less than (2000001),
partition pf3 values less than (maxvalue))
enable row movement;


--分区采用组合：range+list。仅仅对status的字段建立索引，其他不建立。


SQL> select subpartition_position, subpartition_name, num_rows, indexing from dba_tab_subpartitions where table_name = 'PINK_FLOYD';


SUBPARTITION_POSITION SUBPARTITION_NAME      NUM_ROWS IND


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


                    1 PF1_CLOSED                      OFF


                    2 PF1_OPEN                        ON


                    1 PF2_CLOSED                      OFF


                    2 PF2_OPEN                        ON


                    1 PF3_CLOSED                      OFF


                    2 PF3_OPEN                        ON


6 rows selected.


SQL> insert into pink_floyd 
select rownum, 'CLOSED', 'DAVID BOWIE' from 
(select 1 from dual connect by level (select 1 from dual connect by level
3000000 rows created.


SQL> commit;
Commit complete.


SQL> update pink_floyd set status = 'OPEN' where id > 2000000 and mod(id,10000)=0;
100 rows updated.


SQL> commit ;
Commit complete.


SQL> create index pink_floyd_status_i on pink_floyd(status) indexing partial;
Index created.


SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'PINK_FLOYD_STATUS_I';


INDEX_NAME                       NUM_ROWS LEAF_BLOCKS INDEXIN


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


PINK_FLOYD_STATUS_I                   100           1 PARTIAL


--仅仅100行在索引中，


SQL> select * from pink_floyd where status = 'OPEN';


100 rows selected.


Execution Plan


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


Plan hash value: 3984357026


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


| Id  | Operation                                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |


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


|   0 | SELECT STATEMENT                           |                     |   100 |  3500 |     2   (0)| 00:00:01 |       |       |


|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD          |   100 |  3500 |     2   (0)| 00:00:01 | ROWID | ROWID |


|*  2 |   INDEX RANGE SCAN                         | PINK_FLOYD_STATUS_I |   100 |       |     1   (0)| 00:00:01 |       |       |


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


Predicate Information (identified by operation id):


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


   2 - access("STATUS"='OPEN')


Note


-----


   - dynamic statistics used: dynamic sampling (level=2)


Statistics


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


          0  recursive calls


          0  db block gets


          4  consistent gets


          0  physical reads


          0  redo size


       3315  bytes sent via SQL*Net to client


        544  bytes received via SQL*Net from client


          2  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


        100  rows processed


--这样逻辑读仅仅4个。善于利用这个特性，既可以减少索引大小，有可以提高检索速度。
				


				
时间： 2024-07-29 04:56:27

		
		


		


	

	
	

		


		
[20130812]12c Partial Indexes For Partitioned Tables Part I.txt的相关文章


								

		

			

                [20130812]12c Partial Indexes For Partitioned Tables Part II.txt
			

		

		

									

				[20130812]12c Partial Indexes For Partitioned Tables Part II.txt 参考链接:http://richardfoote.wordpress.com/2013/07/12/12c-partial-indexes-for-partitioned-tables-part-ii-vanishing-act/ 更多的是重复作者的测试,加深理解: 1.测试环境: SQL> @ver BANNER                           			

		

	

				

		

			

                ORACLE 12C Partial Global/Local Indexes for Partitioned Tables
			

		

		

									

				以前我有个想法,我只想对其中的某个或者某几个分区上创建索引,其他分区不想创建,在12C之前的版本,无论是Local还是Global index,都不能实现该需求,但是从ORACLE 12C开始引进了Partial Global/Local Indexes for Partitioned Tables,解决了该问题,可以在指定的分区上创建本地索引或者全局索引,主要语法是在表或者分区,子分区级别设置[INDEXING { ON | OFF }]创建测试表  代码如下 复制代码 CDB_PDB@CHF			

		

	

				

		

			

                [20131019]12c Global Temporary table 统计信息的收集.txt
			

		

		

									

				[20131019]12c Global Temporary table 统计信息的收集.txt 摘要:twp-optimizer-with-oracledb-12c-1963236.pdf Optimizer with Oracle Database 12c Session level statistics on Global Temporary Tables P22 Global temporary tables are often used to store intermediate re			

		

	

				

		

			

                [20141116]12c下增加字段与缺省值.txt
			

		

		

									

				[20141116]12c下增加字段与缺省值.txt --前一段时间写了一篇表增加字段与缺省值的blog. --链接如下: http://blog.itpub.net/267265/viewspace-1257035/ --12G 增加字段带缺省值,可以很快完成,不需要update表.实际上是增加一个隐含字段,通过位与的方式确定取值方式. 当时的总结如下: --增加字段带缺省值,在12c下很快完成,不需修改表,但是以后的插入要多付出2个字节的代价(不会增加9个字段带缺省值的吧^_^) --它通过			

		

	

				

		

			

                [20131116]12c的EXTENDED VARCHAR2与隐含参数_scalar_type_lob_storage_threshold.txt
			

		

		

									

				[20131116]12c的EXTENDED VARCHAR2与隐含参数_scalar_type_lob_storage_threshold.txt 参考链接:http://space.itpub.net/267265/viewspace-776806/ google查询了一些资料: 发现:SYS@test01p> @hide _scalar_type_lob_storage_threshold;NAME                                     DESCRIPTI			

		

	

				

		

			

                [20131116]12c的EXTENDED VARCHAR2与_scalar_type_lob_storage_threshold之2.txt
			

		

		

									

				[20131116]12c的EXTENDED VARCHAR2与_scalar_type_lob_storage_threshold之2.txt 参考链接:http://space.itpub.net/267265/viewspace-776807/ google查询了一些资料: 发现:SYS@test01p> @hide _scalar_type_lob_storage_threshold;NAME                                     DESCRIPTION			

		

	

				

		

			

                [20130924]12c dbms_stats包的一些缺省参数.txt
			

		

		

									

				[20130924]12c dbms_stats包的一些缺省参数.txt 11G下: SQL> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production SELECT DBMS_STATS.get_param ('AU			

		

	

				

		

			

                MongoDB 部分索引(Partial Indexes)
			

		

		

									

				MongoDB部分索引只为那些在一个集合中,满足指定的筛选条件的文档创建索引.由于部分索引是一个集合文档的一个子集,因此部分索引具有较低的存储需求,并降低了索引创建和维护的性能成本.部分索引通过指定过滤条件来创建,可以为MongoDB支持的所有索引类型使用部分索引. 一.语法描述 创建部分索引语法 db.collection.createIndex(keys, options) options可以使用partialFilterExpression,即部分过滤表达式,其类型为文档类型 过滤表达式通			

		

	

				

		

			

                VLDB and Partitioning Guide  读书摘要
			

		

		

									

				1. If a global index partition contains data, dropping the partition causes the next highest partition to be  marked unusable. You cannot drop the highest partition in a global index.   p2-14 2. The highest partition of a global index must have a par