"Simple" SQL Query

Simplifying a complex SQL query into a simple query

Dynamic SQL it is.

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     l_size  NUMBER;
  3  BEGIN
  4     FOR cur_r
  5        IN (SELECT table_name,
  6                   column_name,
  7                   data_type,
  8                      'select (max(length('
  9                   || COLUMN_NAME
 10                   || '))/(1024)) as "Size in KB" from '
 11                   || owner
 12                   || '.'
 13                   || TABLE_NAME querytogetlobsize
 14              FROM all_tab_cols
 15             WHERE     owner = 'SCOTT'
 16                   AND data_type IN ('CLOB', 'BLOB', 'NCLOB'))
 17     LOOP
 18        EXECUTE IMMEDIATE cur_r.querytogetlobsize
 19           INTO l_size;
 20
 21        DBMS_OUTPUT.put_line (
 22              RPAD (cur_r.table_name, 20, ' ')
 23           || ' - '
 24           || RPAD (cur_r.column_name, 20, ' ')
 25           || ': '
 26           || TO_CHAR (l_size, '999G990D00'));
 27     END LOOP;
 28  END;
 29  /

which results in

DUGOTRAJNO       - KRAJ_BLOB           :    1.708,98
DUGOTRAJNO       - POCETAK_BLOB        :    2.596,62
OSOBA            - PHOTO               :      390,32
OSOBA            - FAKSIMIL            :       23,18
ZAHTJEV_PUTNI_NA - NALOG_BLOB          :   16.286,69
ZAHTJEV_PUTNI_NA - POZIV_BLOB          :   25.609,50

PL/SQL procedure successfully completed.

SQL>

(Simple?) SQL Query: Display all customer information for customers with 2+ orders

You can try to use subquery to get count by cust# then do inner join to make it.

SELECT c.*
FROM (
    SELECT cust# , COUNT(*) cnt
    FROM order 
    GROUP BY cust#
) o INNER JOIN customer c ON c.cust# = o.cust#
WHERE o.cnt > 2

Simple SQL query but huge table - how to optimize?

For this query, you want the following index:

create index myindex on mytable(year, goal_id, target)

This gives you a covering index: all columns that come into play in the query are part of the index, so this gives the database a decent chance to execute the query by looking at the index only (without actually looking at the data).

The ordering of columns in the index is important: the first two columns correspond to the where predicates, and the last column is the column comes into play in the select clause.

Depending on the cardinality of your data, you might also want to try to invert the first two columns:

create index myindex on mytable(goal_id, year, target)

The base idea is that you want to put the more restrictive criteria first.

---

Related Topics