---------------------------------------------------
2 - filter("A">50000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3441 consistent gets
99 physical reads
0 redo size
920044 bytes sent via SQL*Net to client
37132 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from hr.t04310_b where a > 50000;
50000 rows selected.
Elapsed: 00:00:00.47
Execution Plan
----------------------------------------------------------
Plan hash value: 4149340864
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49996 | 195K| 43 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T04310_B | 49996 | 195K| 43 (5)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A">50000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3518 consistent gets
181 physical reads
0 redo size
920044 bytes sent via SQL*Net to client
37132 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed
2.分区表的索引
SQL> conn /as sysdba
Connected.
SQL> select count(*) from dba_indexes i where i.owner='HR' and i.table_name='T04310_A';
COUNT(*)
----------
0
SQL> create index i_t04310_a on t04310_a(a) local;
Index created.
SQL> select segment_type from dba_extents e where e.owner='HR'
2 and e.segment_name='I_T04310_A' and rownum <= 3;
SEGMENT_TYPE
-----------------
INDEX PARTITION
INDEX PARTITION
INDEX PARTITION
3：哈希分区表，哈希表适合于倾斜表场合，即大部分值分布在同一个区间，小部分值在另外的区间；等值查询效果比较明显
SQL> CREATE TABLE HR.T04310_C
2 ( A NUMBER, B DATE) PARTITION BY HASH (A)
3* (PARTITION T04310_C_P1 TABLESPACE USERS, PARTITION T04310_C_P2 TABLESPACE EXAMPLE)
Table created.
SQL> create table hr.t04310_d(a number,b date);
Table created.
SQL> select table_name,partitioning_type,partition_count from dba_part_tables pt
2* where pt.owner='HR' and pt.table_name='T04310_C'
TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
---------- --------------------- ---------------
T04310_C HASH 2
SQL> begin
2 for i in 1..5000
3 loop
4 insert into hr.t04310_c values (1,sysdate);
5 end loop;
6 commit;
7* end;
PL/SQL procedure successfully completed
SQL> begin
2 for i in 1..5000
3 loop
4 insert into hr.t04310_d values (1,sysdate);
5 end loop;
6 commit;
7* end;
PL/SQL procedure successful