SelectEtoile : Base de connaissance SGBD

Posts Tagged 'partition'

Petit guide des commandes SYBASE RSE

Guide pratique des commandes système pour Replication Server SYBASE

Sur RSSD Server :

rs_helppartition : état des partitions

rs_helpuser : listing utilisateurs


Sur Replication Server :

admin disk_space : état des espace disque

add partition rrwdev01 on /dev/rwdev01’ with size 512 : ajout partition

alter connection to ‘FP_SV_TTTT’ set parallel dsi to ‘on’ : active le parallelisme

alter connection to ‘FP_SV_TTTT’ set dsi_serialization_method ‘isolation_level_3’ :

suspend connection to ‘FP_SV_TTTT’ with nowait : desactive la connexion

resume connection to ‘FP_SV_TTTT’ : active la connexion

 sysadmin dump_queue,135,0,-1,-1,-1,client : affiche le dernier block de l'outbound queue n°135

 

Sauvegarde toute la inbound queue 103 dans le fichier queue_103.log
sysadmin dump_file, queue_103.log
sysadmin dump_queue, 103, 1, -1, 1, -2
sysadmin dump_file

 

 

 

 

 

Rebuild d'indexes partitionnés parallélisés entre partitions et intra-partitions

Un package standard Oracle permet de paralléliser la reconstruction des partions d’index.

Le parallélisme est entre les partitions et au sein de chaque partition.

 

Exemple sur une table de 38 millions de lignes sur un index unique composite : 4 minutes !

 

SQL> connect / as sysdba

Connected.

SQL> alter system set job_queue_processes=16;

 

System altered.

 

SQL> connect user/pass

Connected.

 

SQL> create UNIQUE index USER.ITEM_COMP_MATCHID_ID on USER.ITEM(MATCHID,ID) LOCAL tablespace USER_ITEM_IDX storage (buffer_pool RECYCLE) unusable;

 

Index created.

 

Elapsed: 00:00:00.12

 

SQL> set serveroutput on size 10000

SQL> EXECUTE dbms_pclxutil.build_part_index(4,4,'ITEM','ITEM_COMP_MATCHID_ID',TRUE);                                                                  

INFO: Job #21 created for partition ITEM_001 with 4 slaves

INFO: Job #22 created for partition ITEM_002 with 4 slaves

INFO: Job #23 created for partition ITEM_003 with 4 slaves

INFO: Job #24 created for partition ITEM_004 with 4 slaves

INFO: Job #25 created for partition ITEM_005 with 4 slaves

INFO: Job #26 created for partition ITEM_006 with 4 slaves

INFO: Job #27 created for partition ITEM_007 with 4 slaves

INFO: Job #28 created for partition ITEM_008 with 4 slaves

INFO: Job #29 created for partition ITEM_009 with 4 slaves

INFO: Job #30 created for partition ITEM_010 with 4 slaves

INFO: Job #31 created for partition ITEM_011 with 4 slaves

INFO: Job #32 created for partition ITEM_012 with 4 slaves

INFO: Job #33 created for partition ITEM_UNMATCHED with 4 slaves

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:04:00.73

SQL Server et les partitions

Vous trouverez ici quelques scripts sql permettant de visualiser et manipuler les partitions sous SQLServer

Les tables systèmes :

  • sys.tables
  • sys.indexes
  • sys.partitions
  • sys.partition_schemes
  • sys.partition_functions
  • sys.partition_parameters

 

Lister les partitions d'une base :

select object_name(object_id) as tablename,* from sys.partitions



Lister les partitions de toutes les tables

select T.name, T.Object_id, T.Type_Desc,P.Partition_Id
from sys.tables T
inner join sys.partitions P on T.object_id = P.object_id

 

Affiche la fonction et le schéma de partition de toutes les tables :

SELECT
    tbl.name AS [Name],
    tbl.object_id AS [ID],
    ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount],
    CASE WHEN 'FG'=dsidx.type THEN dsidx.name ELSE N'' END AS [FileGroup],
    CAST(CASE WHEN 'PS'=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],
    ps.name AS [PartitionScheme],
    pf.name    AS [PartitionFunction]
FROM
    sys.tables AS tbl
    INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
    LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id
    LEFT OUTER JOIN sys.partition_schemes AS ps ON dsidx.name = ps.name    
    LEFT OUTER JOIN sys.partition_functions pf on ps.function_id=pf.function_id



Lister les caractéristiques des partitions d'une table

select object_name(object_id) as table_name, index_id, partition_number, rows
from sys.partitions  where object_name(object_id) = 'matable'

 

Afficher le détails des fonctions et schémas de partitions

-- Affiche les schémas et fonctions de partitions

select s.name as scheme_name, f.name as function_name, limit = case f.boundary_value_on_right when 0 then 'LEFT' else 'RIGHT' END
from sys.partition_schemes s
inner join sys.partition_functions f on s.function_id=f.function_id

 

-- Liste des paramètres et des valeurs de la fonction pfDate

select    *
from    sys.partition_functions pf
        inner join sys.partition_parameters pp on pf.function_id=pp.function_id
        where pf.name = 'pfDate'

select    *
from    sys.partition_functions pf
        inner join sys.partition_range_values prv on pf.function_id=prv.function_id
        where pf.name = 'pfDate'

 

--relation scheme fonction valeurs

select    s.name as scheme_name, f.name as function_name,
        limit = case f.boundary_value_on_right when 0 then 'LEFT' else 'RIGHT' END ,
        v.boundary_id, v.value
from sys.partition_schemes s
inner join sys.partition_functions f on s.function_id=f.function_id
inner join sys.partition_range_values v on v.function_id=f.function_id