Discussion :

[weed]

Hello la compagnie,

Comment fais t-on pour agréger plusieurs valeurs de plusieurs tables ayant des granularité différentes sans passer par des sous requêtes? Est il possible de passer par les fonctions analytique over ?

Dans mon cas, je souhaiterais par table :
- connaitre la taille moyenne utile d'une partition et de la table
- connaitre la taille des segments
- connaitre la taille des tables spaces


Pour récupérer la taille moyenne de mes partitions, je vais utiliser la table user_tab_partitions dans laquelle je vais exploiter les champs avg_len et num_rows qui me permettront de calculer la taille utile par partition. Comme je veux avoir les tailles par table, je vais faire un regroupement par table table_name

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select table_name, avg(avg_len*num_rows) as TAILLE_PARTITION, 
sum(avg_len*num_rows) as TAILLE_TABLE_UTILISE
from user_tab_partitions
group by table_name

J'aimerais ensuite pouvoir mettre en opposition la taille utile avec la taille des tables spaces relatives aux tables. Je peux obtenir via la table dba_datafile l'information.


Si j'écris cette requête avec une simple jointure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select user_tab_partitions.table_name, avg(user_tab_partitions.avg_len*user_tab_partitions.num_rows) as TAILLE_PARTITION, 
sum(avg_len*num_rows) as TAILLE_TABLE_UTILISE, 
sum(dba_datafiles.bytes) as TAILLE_TABLESPACE
from user_tab_partitions
    inner join dba_datafiles on user_tab_partitions.tablespace_name=dba_datafiles.tablespace_name
group by user_tab_partitions.table_name

le champs dba_datafiles.bytes va être comptabilisé plusieurs fois dans l'agrégat en doublons. En effet, user_tab_partitions retourne l'ensemble des partitions par table (table_name) avec le tablespace associé. Or plusieurs partitions, j'ai le même tablespace, user_table_partition va me renvoyer en doublon. On se retrouve donc avec la table dba_datafiles avec pas mal de ligne en doublon. Le résultat n'est donc plus juste.

Pour ne plus avoir les lignes en doublons, je ne fais plus la jointure dba_datafiles. Je fais une sous jointure dans ma clause select avec ceci :

sum(dba_datafiles.bytes)
from dba_datafiles
where dba_datafiles.tablespace_name in (
select distinct user_tab_partitions.tablespace_name
from user_tab_partitions
where user_tab_partitions.table_name=<table_name de ma requête principal>)

Cependant je ne trouve cela vraiment pas propre du tout. Ne serait il pas possible d'éviter les sous requêtes et utiliser les fonctions de fenêtrage OVER ?


PS : j'écris de tête de chez moi les noms des tables. Je ne suis pas exactement du nom des champs et tables.

[skuatamad]

Je ne suis pas sûr de bien comprendre votre sous reqête scalaire, je pense que vous avez mis une étape en trop :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select p.table_name
     , avg(p.avg_len*p.num_rows) as TAILLE_PARTITION
     , sum(p.avg_len*p.num_rows) as TAILLE_TABLE_UTILISE
     , (select sum(df.bytes) from dba_data_files df where df.tablespace_name = p.tablespace_name) as TAILLE_TABLESPACE
  from user_tab_partitions p
 group by p.table_name

Ou sinon vous faites l'agrégation avant puis la jointure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
  with df as (
select tablespace_name, sum(bytes) 
  from dba_data_files
 group by tablespace_name
)
select p.table_name
     , avg(p.avg_len*p.num_rows) as TAILLE_PARTITION
     , sum(p.avg_len*p.num_rows) as TAILLE_TABLE_UTILISE
     , sum(df.bytes) as TAILLE_TABLESPACE
  from user_tab_partitions p
  join df on df.tablespace_name = p.tablespace_name
 group by p.table_name