Partición por HASH simple.

View All Scripts Login to Run Script

Script Name Partición por HASH simple.
Description Aquí puedes ver un ejemplo sencillo de partición HASH, donde se muestra también una manera de visualizar fácilmente los datos de las particiones de la tabla creada.
Area Partitioning
Contributor Diana Laura Cruz Santiago
Created Wednesday May 10, 2017

Statement 1

CREATE TABLE materias_hash (   
             materias_id NUMBER(4) NOT NULL,    
             nombre_materia VARCHAR2(30))   
     PARTITION BY HASH(materias_id) PARTITIONS 4

Table created.

Statement 2

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='MATERIAS_HASH'


TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT MAX_SIZE PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION INTERVAL SEGMENT_CREATED INDEXING READ_ONLY INMEMORY INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION INMEMORY_DUPLICATE CELLMEMORY INMEMORY_SERVICE INMEMORY_SERVICE_NAME
MATERIAS_HASH NO SYS_P19447 0  - 0 1 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO NO ON NO DISABLED  -  -  -  -  -  -  - 
MATERIAS_HASH NO SYS_P19448 0  - 0 2 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO NO ON NO DISABLED  -  -  -  -  -  -  - 
MATERIAS_HASH NO SYS_P19449 0  - 0 3 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO NO ON NO DISABLED  -  -  -  -  -  -  - 
MATERIAS_HASH NO SYS_P19450 0  - 0 4 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO NO ON NO DISABLED  -  -  -  -  -  -  - 

4 rows selected.

Statement 3

CREATE VIEW HASH_MATERIAS 
	AS SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='MATERIAS_HASH'

View created.

Statement 4

SELECT * FROM HASH_MATERIAS


TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT MAX_SIZE PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION INTERVAL SEGMENT_CREATED INDEXING READ_ONLY INMEMORY INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION INMEMORY_DUPLICATE CELLMEMORY INMEMORY_SERVICE INMEMORY_SERVICE_NAME
MATERIAS_HASH NO SYS_P19447 0  - 0 1 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO NO ON NO DISABLED  -  -  -  -  -  -  - 
MATERIAS_HASH NO SYS_P19448 0  - 0 2 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO NO ON NO DISABLED  -  -  -  -  -  -  - 
MATERIAS_HASH NO SYS_P19449 0  - 0 3 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO NO ON NO DISABLED  -  -  -  -  -  -  - 
MATERIAS_HASH NO SYS_P19450 0  - 0 4 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO NO ON NO DISABLED  -  -  -  -  -  -  - 

4 rows selected.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

TABLE_NAME	COMPOSITE	PARTITION_NAME	HIGH_VALUE	PARTITION_POSITION	TABLESPACE_NAME	PCT_FREE	PCT_USED	INI_TRANS	MAX_TRANS	INITIAL_EXTENT	NEXT_EXTENT	MIN_EXTENT	MAX_EXTENT	MAX_SIZE	PCT_INCREASE	FREELISTS	FREELIST_GROUPS	LOGGING	COMPRESSION	COMPRESS_FOR	NUM_ROWS	BLOCKS	EMPTY_BLOCKS	AVG_SPACE	CHAIN_CNT	AVG_ROW_LEN	SAMPLE_SIZE	LAST_ANALYZED	BUFFER_POOL	FLASH_CACHE	CELL_FLASH_CACHE	GLOBAL_STATS	USER_STATS	IS_NESTED	PARENT_TABLE_PARTITION	INTERVAL	SEGMENT_CREATED	INDEXING	READ_ONLY	INMEMORY	INMEMORY_PRIORITY	INMEMORY_DISTRIBUTE	INMEMORY_COMPRESSION	INMEMORY_DUPLICATE	CELLMEMORY	INMEMORY_SERVICE	INMEMORY_SERVICE_NAME
MATERIAS_HASH	NO	SYS_P19447	-	1	LIVESQL_USERS	10	-	1	255	-	-	-	-	-	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	NO	NO	ON	NO	DISABLED	-	-	-	-	-	-	-
MATERIAS_HASH	NO	SYS_P19448	-	2	LIVESQL_USERS	10	-	1	255	-	-	-	-	-	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	NO	NO	ON	NO	DISABLED	-	-	-	-	-	-	-
MATERIAS_HASH	NO	SYS_P19449	-	3	LIVESQL_USERS	10	-	1	255	-	-	-	-	-	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	NO	NO	ON	NO	DISABLED	-	-	-	-	-	-	-
MATERIAS_HASH	NO	SYS_P19450	-	4	LIVESQL_USERS	10	-	1	255	-	-	-	-	-	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	NO	NO	ON	NO	DISABLED	-	-	-	-	-	-	-