notebook/IT/SQL/SCRIPTS SQL QSA ORSYS/EXO 7 indexation.sql

/***************************
****************************
* LES REQU<EFBFBD>TES A OPTIMISER *
****************************
***************************/
/*
SET STATISTICS IO OFF
SET STATISTICS TIME ON
--> 32867 IO
DROP INDEX X ON T_EMPLOYEE_EMP;
CREATE INDEX X ON T_EMPLOYEE_EMP (EMP_SEXE); --> 32867
CREATE INDEX X ON T_EMPLOYEE_EMP (EMP_SERVICE); --> 32867
CREATE INDEX X ON T_EMPLOYEE_EMP (EMP_SEXE) INCLUDE (EMP_SERVICE); --> 4535
CREATE INDEX X ON T_EMPLOYEE_EMP (EMP_SEXE, EMP_SERVICE); --> 82
CREATE INDEX X ON T_EMPLOYEE_EMP (EMP_SERVICE, EMP_SEXE); --> 77
CREATE INDEX X ON T_EMPLOYEE_EMP (EMP_SERVICE) INCLUDE (EMP_SEXE); --> 77
CREATE INDEX X ON T_EMPLOYEE_EMP (EMP_SEXE) WHERE EMP_SERVICE = 'RH';--> 54
CREATE INDEX X ON T_EMPLOYEE_EMP (EMP_SEXE) WHERE EMP_SERVICE = 'RH' WITH (DATA_COMPRESSION = ROW); --> 48
CREATE INDEX X ON T_EMPLOYEE_EMP (EMP_SEXE) WHERE EMP_SERVICE = 'RH' WITH (DATA_COMPRESSION = PAGE); --> 31
CREATE INDEX X ON T_EMPLOYEE_EMP (EMP_SEXE) INCLUDE (EMP_SERVICE) WITH (DATA_COMPRESSION = PAGE);--> 1410
CREATE INDEX X ON T_EMPLOYEE_EMP (EMP_SERVICE) INCLUDE (EMP_SEXE) WITH (DATA_COMPRESSION = PAGE); --> 36 (UC = 15, TE = 20)

DROP INDEX XC ON T_EMPLOYEE_EMP
CREATE COLUMNSTORE INDEX XC ON T_EMPLOYEE_EMP (EMP_SERVICE, EMP_SEXE); --> 3 segments (UC = 500, TE = 300)
                                                                                      (UC = 0, TE = 4 ms

-- vue index<65>e
CREATE VIEW V_EMP_SEXE_SERVICE
WITH SCHEMABINDING
AS
SELECT EMP_SERVICE, EMP_SEXE, COUNT_BIG(*) AS NOMBRE
FROM   [dbo].[T_EMPLOYEE_EMP]
GROUP  BY EMP_SERVICE, EMP_SEXE;
GO

CREATE UNIQUE CLUSTERED INDEX XV ON V_EMP_SEXE_SERVICE (EMP_SERVICE, EMP_SEXE);
GO

Si version Standard alors utiliser le tag NOEXPAND et la vue
*/

-- 1
SELECT COUNT(*), EMP_SEXE
FROM   T_EMPLOYEE_EMP
WHERE  EMP_SERVICE = 'RH'
GROUP  BY EMP_SEXE;

-- 2
SELECT COUNT(*) AS NOMBRE, 'Homme' AS SEXE
FROM   T_EMPLOYEE_EMP
WHERE  EMP_SERVICE = 'RH'
  AND  EMP_SEXE = 'Homme'
UNION
SELECT COUNT(*) AS NOMBRE, 'Femme' AS SEXE
FROM   T_EMPLOYEE_EMP
WHERE  EMP_SERVICE = 'RH'
  AND  EMP_SEXE = 'Femme';
GO
 
-- 3
SELECT COUNT(*) AS HOMME, 
       (SELECT COUNT(*) 
        FROM   T_EMPLOYEE_EMP
        WHERE  EMP_SERVICE = 'RH'
          AND  EMP_SEXE = 'Femme') AS FEMME
FROM   T_EMPLOYEE_EMP
WHERE  EMP_SERVICE = 'RH'
  AND  EMP_SEXE = 'Homme';
GO

-- 4
SELECT COUNT(*) - (SELECT COUNT(*) 
                   FROM   T_EMPLOYEE_EMP E2
                   WHERE  E2.EMP_SERVICE = E1.EMP_SERVICE
                      AND  EMP_SEXE = 'Femme') AS HOMME,
       (SELECT COUNT(*) 
        FROM   T_EMPLOYEE_EMP E3
        WHERE  E3.EMP_SERVICE = E1.EMP_SERVICE
          AND  EMP_SEXE = 'Femme') AS FEMME
FROM   T_EMPLOYEE_EMP E1
WHERE  EMP_SERVICE = 'RH'
GROUP  BY EMP_SERVICE;
GO
   

-- 5
SELECT SUM(CASE EMP_SEXE
             WHEN 'Homme' THEN 1
             WHEN 'Femme' THEN 0
           END) AS NOMBRE_HOMME,
       SUM(CASE EMP_SEXE
             WHEN 'Homme' THEN 0
             WHEN 'Femme' THEN 1
           END) AS NOMBRE_FEMME
FROM  dbo.T_EMPLOYEE_EMP
WHERE EMP_SERVICE= 'RH';
GO
 
-- 6
SELECT COUNT(EMP_SEXE) AS NOMBRE,
       CASE EMP_SEXE
	  WHEN 'Femme' THEN 'Femme'
	  WHEN 'Homme' THEN 'Homme'
	  ELSE 'Unknown'
       END AS SEXE
FROM   dbo.T_EMPLOYEE_EMP
WHERE  EMP_SERVICE= 'RH'
GROUP  BY EMP_SEXE;
GO


-- 7 
 SELECT COUNT(*) AS Nombre, 'Femme' AS Sexe 
 FROM  dbo.T_EMPLOYEE_EMP 
 WHERE EMP_ID NOT IN (SELECT EMP_ID 
                       FROM   dbo.T_EMPLOYEE_EMP 
                      WHERE  EMP_SERVICE <> 'RH' 
                         OR  EMP_SEXE = 'Homme') 
 UNION ALL 
 SELECT COUNT(*) AS Nombre, 'Homme' AS Sexe 
 FROM  dbo.T_EMPLOYEE_EMP 
 WHERE EMP_ID NOT IN (SELECT EMP_ID 
                      FROM   dbo.T_EMPLOYEE_EMP 
                      WHERE  EMP_SERVICE <> 'RH' 
                         OR  EMP_SEXE = 'Femme');
GO