Archives du mot-clé sql

How to find tables analyzed by dbms_stats job

Sometime it can be usefull to know what have done the automatic stats jobs

Here is a statment that will let you know the objects that have been analyzed by the job

As SYS execute the following

SELECT /*+ dynamic_sampling(4) dynamic_sampling_est_cdn */
               CASE
                  WHEN o.type# IN (1, 20, 35)
                     THEN 'INDEX'
                  WHEN o.type# IN (2, 19, 34)
                     THEN 'TABLE'
                  ELSE 'UNKNOWN'
               END object_type,
               u.NAME owner, o.NAME object_name,
               CASE
                  WHEN o.type# IN (19, 20)
                     THEN o.subname
                  WHEN o.type# IN (34, 35)
                     THEN (SELECT po.subname
                             FROM SYS.obj$ po
                            WHERE po.obj# = st.bo#)
                  ELSE NULL
               END PARTITION,
               CASE
                  WHEN o.type# IN (34, 35)
                     THEN o.subname
                  ELSE NULL
               END SUBPARTITION,
               CASE st.STATUS
                  WHEN 0
                     THEN 'PENDING'
                  WHEN 1
                     THEN 'BEING PROCESSED'
                  WHEN 2
                     THEN 'COMPLETED'
                  WHEN 3
                     THEN DECODE (BITAND (st.flags, 8),
                                  8, 'TIMEOUT',
                                  'ERROR'
                                 )
                  ELSE NULL
               END STATUS,
               CASE
                  WHEN st.staleness <= -99
                     THEN 'MISSING'
                  ELSE 'STALE'
               END reason
          FROM SYS.stats_target$ st, SYS.obj$ o, SYS.user$ u
         WHERE st.obj# = o.obj#
           AND o.owner# = u.user#
           AND st.STATUS != 4
           AND (st.SID, st.serial#) IN (
                  SELECT /*+ no_unnest */
                         REGEXP_REPLACE (session_id, ',.*') SID,
                         REGEXP_REPLACE (session_id, '.*,') serial#
                    FROM (SELECT   CASE
                                      WHEN REGEXP_REPLACE
                                                      (session_id,
                                                       '[0-9,]'
                                                      ) IS NULL
                                         THEN session_id
                                      ELSE NULL
                                   END session_id
                              FROM dba_scheduler_job_run_details
                             WHERE job_name = 'GATHER_STATS_JOB'
                          ORDER BY actual_start_date DESC)
                   WHERE ROWNUM < 2)

Rotating table with Oracle UNPIVOT

I had recently faced a SQL question about rotating a table

The question was how to rotate transform a table gettint multiple columns into rows

Here is a small sample to create the table

CREATE TABLE test_tab(productm VARCHAR2(30),
                      leg1 VARCHAR2(30),
                      legval1 INTEGER,
                      leg2 VARCHAR2(30),
                      legval2 INTEGER,
                      leg3 VARCHAR2(30),
                      legval3 INTEGER);

INSERT INTO test_tab
VALUES ('prod 1', 'subprod 1-1', 11, 'subprod 1-2', 12, 'subprod 1-3', 13);
INSERT INTO test_tab
VALUES ('prod 2', 'subprod 2-1', 21, 'subprod 2-2', 22, 'subprod 2-3', 23);
INSERT INTO test_tab
VALUES ('prod 3', 'subprod 3-1', 31, 'subprod 3-2', 32, 'subprod 3-3', 33);
INSERT INTO test_tab
VALUES ('prod 4', 'subprod 4-1', 41, 'subprod 4-2', 42, NULL, NULL);
INSERT INTO test_tab
VALUES ('prod 5', 'subprod 5-1', 51, NULL, NULL, NULL, NULL);
COMMIT;

SELECT * FROM test_tab;

PRODUCTM  LEG1         LEGVAL1  LEG2         LEGVAL2  LEG3         LEGVAL3
--------- ------------ -------- ------------ -------- ------------ --------
prod 1    subprod 1-1        11 subprod 1-2        12 subprod 1-3        13
prod 2    subprod 2-1        21 subprod 2-2        22 subprod 2-3        23
prod 3    subprod 3-1        31 subprod 3-2        32 subprod 3-3        33
prod 4    subprod 4-1        41 subprod 4-2        42                 
prod 5    subprod 5-1        51                                          

And the result needed was something like that

PRODUCTM         PRODNAME         PRODVAL
---------------- ---------------- ---------------
prod 1           subprod 1-1                   11
prod 1           subprod 1-2                   12
prod 1           subprod 1-3                   13
prod 2           subprod 2-1                   21
prod 2           subprod 2-2                   22
prod 2           subprod 2-3                   23
prod 3           subprod 3-1                   31
prod 3           subprod 3-2                   32
prod 3           subprod 3-3                   33
prod 4           subprod 4-1                   41
prod 4           subprod 4-2                   42
prod 5           subprod 5-1                   51

After some researchs i finally found that Oracle 11G got the solution for me

SELECT productm, prodname, prodval
FROM   test_tab unpivot((prodname, prodval) 
FOR    prod IN( (leg1, legval1),
                (leg2, legval2),
                (leg3, legval3)));