SQL Oracle Counting Clusters

SQL Oracle Counting Clusters

Using LEAD and LAG functions in ORACLE you can built these queries:

1.Number of shutdowns:

WITH IntTable AS
( SELECT * FROM
  (
   SELECT dt b_date,value,LEAD(dt) OVER (ORDER BY dt) e_date  FROM
     (
        select "Date" dt,"Value" value,
               LAG("Value") OVER (ORDER BY "Date") pvalue,
               LEAD("Value") OVER (ORDER BY "Date") nvalue
        from T
     ) T1
     WHERE pvalue is NULL or value<>pvalue or nvalue is NULL 
   )
WHERE E_DATE is NOT NULL
)
SELECT COUNT(*) FROM IntTable where value = 0 

SQLFiddle demo

2.Period Between every shut down

WITH IntTable AS
( SELECT * FROM
  (
   SELECT dt b_date,value,LEAD(dt) OVER (ORDER BY dt) e_date  FROM
     (
        select "Date" dt,"Value" value,
               LAG("Value") OVER (ORDER BY "Date") pvalue,
               LEAD("Value") OVER (ORDER BY "Date") nvalue
        from T
     ) T1
     WHERE pvalue is NULL or value<>pvalue or nvalue is NULL 
   )
WHERE E_DATE is NOT NULL
)
SELECT b_date,e_date, (e_date-b_date) * 60 * 24 FROM IntTable where value = 1 

SQLFiddle demo

SQL query for displaying clusters with their size

select [Cluster Size], Count(*) as [No of Clusters]
from (
    select count(*) as [Cluster Size]
    from Table1
    group by ID
) a
group by [Cluster Size]

SQL Fiddle Example

Output:

| CLUSTER SIZE | NO OF CLUSTERS |
---------------------------------
|            2 |              2 |
|            3 |              1 |
|            4 |              1 |

Is it possible to tell which Oracle cluster node was called?

Assuming you are able to connect via sqlplus or another tool and assuming you are using TNS names.....

If the assumptions above are correct your TNS names will look somthing like the below

(DESCRIPTION=
  (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))

You can then simply remove all nodes except 1 (backing up file first of course) then connect to via SQLPULS or your tool of choice and do..

select sysdate from dual;

Rinse and repeat for each of the nodes in the TNSNames original file, until you find which are the problematic nodes, although I am unsure of any reason that different nodes would give a different sysdate.

Read Oracle Cluster name from Oracle RAC using SQL query

Finally, I found out that there is no way to do that :(.

Oracle SQL: How to SELECT N records for each group / cluster

I recommend an analytical function such as rank() or row_number(). You could do this with hard-coded unions, but the analytical function does all the hard work for you.

select *
from 
(
    select
      bt.col_a,
      bt.col_b,
      bt.process_type_cod,
      row_number() over ( partition by process_type_cod order by col_a nulls last ) rank
    from small_table st
    inner join big_table bt
      on st.process_type_cod = bt.process_type_cod
)
where rank < 11
;

You may not even need that join since big_table has all of the types you care about. In that case, just change the 'from clause' to use big_table and drop the join.

What this does is performs the query and then sorts the records using the 'order by' operator in the partition statement. For a given group (here we grouped by col_a), a numerical row number (i.e. 1, 2, 3, 4, 5, n+1...) is applied to each record consecutively. In the outer where clause, just filter by the records with a number lower than N.

How to optimize the SQL query to the Oracle database

Using analytic functions to determine the max avg_cnt might help speed things up, e.g.:

insert into t_clusters (transaction_id,
                        cluster_id,
                        word_id,
                        connection)
select 72,
       cluster_id,
       word_id,
       avg_cnt
 from  (select   word_id,
                 cluster_id,
                 avg(cnt) avg_cnt,
                 max(avg(cnt)) over (partition by word_id) max_avg_cnt
        from     (select   a.word_id,
                           b.word_id bword,
                           c.cluster_id,
                           count (*) cnt
                  from     t_semantic a,
                           t_words a1,
                           t_semantic b,
                           t_clusters c
                  where    a.transaction_id = 72
                  and      a.word_id = a1.word_id
                  and      a1.checked = 0
                  and      cluster_id <> 0
                  and      a.link_id = b.link_id
                  and      b.word_id = c.word_id
                  group by a.word_id, b.word_id, c.cluster_id)
        group by word_id, cluster_id
        having   avg(cnt) >= 3)
where  avg_cnt = max_avg_cnt;

---

Related Topics