Materialized Views

REFRESH FAST Categories

There are three ways to categorize a materialized view’s ability to be fast refreshed.

  1. It can never be fast refreshed.
  2. It can always be fast refreshed.
  3. It can be fast refreshed after certain kinds of changes to the base table but not others.

For the first case Oracle will raise an error if you try to create such a materialized view with its refresh method defaulted to REFRESH FAST. In the example below table T does not have a materialized view log on it. Materialized views based on T cannot therefore be fast refreshed. If we attempt to create such a materialized view we get an error.

create materialized view MV REFRESH FAST as select * from t2 ; as select * from t2 * ERROR at line 3: ORA-23413: table "SCOTT"."T2" does not have a materialized view log

Source: Materialized Views: REFRESH FAST Categories

Oracle SQL: LAG Oracle 11g is analytic function

Very usefully new features in Oracle 11g is analytic Oracle SQL function LAG.

SQL query LAG

This code find change in address post code in journal table:

  SELECT  *
    FROM  (
              SELECT  t1.SITE_NUMBER
                     ,t1.JN_DATETIME
                     ,t1.ADR_POSTCODE
                     ,LAG (t1.ADR_POSTCODE, 1) OVER (PARTITION BY t1.SITE_NUMBER ORDER BY t1.jn_datetime) AS PREV_ADR_POSTCODE
                FROM  t1 
          )
   WHERE      adr_postcode = '00-001'
          AND PREV_ADR_POSTCODE <> '00-001'
          AND PREV_ADR_POSTCODE <> '00-000'
ORDER BY  SITE_NUMBER;

Test query data

CREATE TABLE T1
(
  SITE_NUMBER   VARCHAR2(16 BYTE),
  JN_DATETIME   DATE,
  ADR_POSTCODE  VARCHAR2(20 BYTE)
)
LOGGING 
NOCOMPRESS 
MONITORING;

SET DEFINE OFF;
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('08/01/2017 15:01:31', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20002', TO_DATE('04/20/2017 11:59:29', 'MM/DD/YYYY HH24:MI:SS'), '00-002');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('06/15/2017 04:02:41', 'MM/DD/YYYY HH24:MI:SS'), '00-003');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20004', TO_DATE('06/20/2017 20:15:13', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('12/10/1929 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('09/25/2091 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-003');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('09/14/1914 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('03/25/1911 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('11/22/2029 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
COMMIT;

SQL result

site_number jn_datetime adr_postcode prev_adr_postcode
20001 2017-08-01 15:01:31 00-001 00-003

How it works together

http://sqlfiddle.com/#!4/9a676b/1

PL/SQL Errors: ORA-04061: existing state of package has been invalidated

I suspect you’re only reporting the last error in a stack like this:

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "schema.package" has been invalidated
ORA-04065: not executed, altered or dropped package body "schema.package"
ORA-06508: PL/SQL: could not find program unit being called: "schema.package"

If so, that’s because your package is stateful:

The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.

When you recompile the state is lost:

If the body of an instantiated, stateful package is recompiled (either explicitly, with the “ALTER PACKAGE Statement”, or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.

After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it…

You can’t avoid this if your package has state. I think it’s fairly rare to really need a package to be stateful though, so you should revisit anything you have declared in the package, but outside a function or procedure, to see if it’s really needed at that level. Since you’re on 10g though, that includes constants, not just variables and cursors.

But the last paragraph from the quoted documentation means that the next time you reference the package in the same session, you won’t get the error and it will work as normal (until you recompile again).

As of Oracle Database 11g Release 2 (11.2.0.2), Oracle Database treats a package as stateless if its state is constant for the life of a session (or longer). This is the case for a package whose items are all compile-time constants.

A compile-time constant is a constant whose value the PL/SQL compiler can determine at compilation time. A constant whose initial value is a literal is always a compile-time constant. A constant whose initial value is not a literal, but which the optimizer reduces to a literal, is also a compile-time constant. Whether the PL/SQL optimizer can reduce a nonliteral expression to a literal depends on optimization level. Therefore, a package that is stateless when compiled at one optimization level might be stateful when compiled at a different optimization level. For information about the optimizer, see “PL/SQL Optimizer”.

Each session that references a package item has its own instantiation of that package. If the package is stateful, the instantiation includes its state. The package state persists for the life of a session, except in these situations:

  • The package is SERIALLY_REUSABLE.For details, see “SERIALLY_REUSABLE Packages”.
  • The package body is recompiled.If the body of an instantiated, stateful package is recompiled (either explicitly, with the “ALTER PACKAGE Statement”, or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it. Therefore, previous changes to the package state are lost. (For information about initialization, see “Package Instantiation and Initialization”.)
  • Any of the session’s instantiated packages are invalidated and revalidated.All of a session’s package instantiations (including package states) can be lost if any of the session’s instantiated packages are invalidated and revalidated. For information about invalidation and revalidation of schema objects, see Oracle Database Advanced Application Developer’s Guide.

It sounds like what you want is to be able to list all packages that may potentially have state.

What you’re looking for is just packages that have any global variables or constants. For a single package, this is quite simple by inspection. To look across all packages in a schema, however, you could use PL/Scope:

First, log in as the schema owner, turn on PL/Scope in your session:

alter session set plscope_settings='IDENTIFIERS:ALL';

Then, recompile all your package bodies.

Then, run this query to find all the variables and constants declared at the package level:

select object_name AS package,
       type,
       name AS variable_name
from user_identifiers
where object_type IN ('PACKAGE','PACKAGE BODY') and usage = 'DECLARATION' and type in ('VARIABLE','CONSTANT') and usage_context_id in ( select usage_id
  from user_identifiers
  where type = 'PACKAGE' );

I’d suggest the resulting list of packages will be your target.

If you’re on 11gR2, constants no longer cause this problem, so you’d use this query instead:

select object_name AS package,
       type,
       name AS variable_name
from user_identifiers
where object_type IN ('PACKAGE','PACKAGE BODY') and usage = 'DECLARATION' and type = 'VARIABLE' and usage_context_id in ( select usage_id
  from user_identifiers
  where type = 'PACKAGE' );

Source

  1. Annals of Oracle’s Improbable Errors: ORA-04061: existing state of package has been invalidated
  2. https://stackoverflow.com/questions/19376440/ora-06508-pl-sql-could-not-find-program-unit-being-called/19380746

PLSQL corner

PLSQL loop inexed by letters

declare
  k_ascii_a constant pls_integer := ascii('A');
  l_index varchar2(1);
begin
  for i in 1 .. 10 loop -- Change 10 for whatever is your limit
    l_index := chr(k_ascii_A + i - 1);
    dbms_output.put_line('Index is ' || l_index);
  end loop;
end;

But this is not portable across character sets. I think a better solution is to declare up front your index variables like this:

declare
  k_index_char constant varchar2(26) := 'ABCDEF...Z';
  l_index varchar2(1);
begin
  for i in 1 .. 10 loop -- Change 10 for whatever is your limit
    l_index := substr(k_index_char, i, 1);
    dbms_output.put_line('Index is ' || l_index);
  end loop;
end;

That way if you change character sets (e.g. to utf8) you are not reliant on a collating sequence that doesn’t match your char set.

Narzędzia to projektowania baz danych

GNU-GPL

Multiple versions/licenses (each has a “free” and a “not-free” version)

  • DBVisualizer
  • Toad http://www.toadsoft.com/

Not-free

  • Microsoft Visio – (If you already have it, it does work quite well)
  • DataStudio – 30 day trial http://www.aquafold.com
  • DBWrench – 30 day trial http://www.dbwrench.com/

ER diagram tool

  • http://druid.sf.net/
  • http://schemaspy.sourceforge.net/
  • http://uml.sourceforge.net/index.php

Generic DataSource on CDHDR and CDPOS Tables

    data: act type i, &amp;quot;aktualna liczba kroków
          cal type i, &amp;quot;całkowita liczba kroków
          key_x like cdpos-tabkey,
          it_t161f like t161f,
          i_frgzu2 like eban-frgzu,
          begin of cdhdr_x_l,
             udate like cdhdr-udate,
          end of cdhdr_x_l,
          cdhdr_x like cdhdr_x_l occurs 0 with header line,
          begin of cdpos_x_l,
             changenr like cdpos-changenr,
          end of cdpos_x_l,
          cdpos_x like cdpos_x_l occurs 0 with header line.

    select * from t161f into it_t161f where frgst = j_eban-frgst.
    endselect.

      cal = sy-dbcnt.
      act = strlen( j_eban-frgzu ).

      if sy-subrc ne 0.
        t_items-badat = j_eban-badat.
      elseif ( cal eq act ).

        concatenate &amp;#039;030&amp;#039; j_eban-banfn j_eban-bnfpo into key_x.

        select changenr from cdpos into table cdpos_x
          where objectclas = &amp;#039;BANF&amp;#039; and
              objectid = j_eban-banfn and
              tabkey = key_x and
              fname = &amp;#039;FRGZU&amp;#039;.

        sort cdpos_x by changenr descending.

        read table cdpos_x into cdpos_x_l index 1.

        select single udate from cdhdr into cdhdr_x_l-udate
          where objectclas = &amp;#039;BANF&amp;#039; and
                objectid = j_eban-banfn and
                changenr = cdpos_x_l-changenr.

         t_items-badat = cdhdr_x_l-udate.
      endif. 

Pomiar wydajności bazy danych

Przy wyborze hostingu warto sprawdzić jak prezentuje się wydajność zakupionych serwerów może w tym pomóc skrypt http://www.linux.pl

Po przeróbkach skryptu zainstalowałem go na serwerze http://ultimasolution.pl/projects/bechmark_phpmysql/?iterations=50000&amp;amp;tabletype=MYISAM

i oto co wyszło:

Testing a(n) MYISAM table using 50000 rows.

Successfully created database speedtestdb
Sucessfully created table speedtesttable

This MySQL instance does NOT support MYISAM tables
Table Type Verified: ..

Done. 50000 inserts in 1.98958 seconds or

25131 inserts per second.

Done. 50000 row reads in 6.0E-5 seconds or

833333333 row reads per second.

Done. 50000 updates in 6.0E-5 seconds or

833333333 updates per second.

Inne pomysły na zgadanie wydajności:

 

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑