Relational database system Codd's 12 rule

Dr. E.F. Codd, an IBM researcher, first developed the relational data model in 1970. In 1985, Dr. Codd published a list of 12 rules that concisely define an ideal relational database, which have provided a guideline for the design of all relational database systems ever since. Advertisement
On this topic


1. Information Representation
2. Guranteed Access
3. Systematic Treatment of Null Values
4. Database Description Rule
5. Comprehensive Data Sub-Language
6. View Updating
7. High-Level Update,Insert,Delete
8. Physical data independence
9. Logical Data Independence
10.The Distribution Rule
11. Non-Subversion
12. Integrity Rule




Codd's 12 rules,

Rule 1: The Information Rule
All data should be presented to the user in table form. Last week's newsletter already discussed the basics of this rule.

Rule 2: Guaranteed Access Rule
All data should be accessible without ambiguity. This can be accomplished through a combination of the table name, primary key, and column name.

Rule 3: Systematic Treatment of Null Values
A field should be allowed to remain empty. This involves the support of a null value, which is distinct from an empty string or a number with a value of zero. Of course, this can't apply to primary keys. In addition, most database implementations support the concept of a nun- null field constraint that prevents null values in a specific table column.

Rule 4: Dynamic On-Line Catalog Based on the Relational Model A relational database must provide access to its structure through the same tools that are used to access the data. This is usually accomplished by storing the structure definition within special system tables.

Rule 5: Comprehensive Data Sublanguage Rule
The database must support at least one clearly defined language that includes functionality for data definition, data manipulation, data integrity, and database transaction control. All commercial relational databases use forms of the standard SQL (Structured Query Language) as their supported comprehensive language.

Rule 6: View Updating Rule
Data can be presented to the user in different logical combinations, called views. Each view should support the same full range of data manipulation that direct-access to a table has available. In practice, providing update and delete access to logical views is difficult and is not fully supported by any current database.

Rule 7: High-level Insert, Update, and Delete
Data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.

Rule 8: Physical Data Independence
The user is isolated from the physical method of storing and retrieving information from the database. Changes can be made to the underlying architecture ( hardware, disk storage methods ) without affecting how the user accesses it.

Rule 9: Logical Data Independence
How a user views data should not change when the logical structure (tables structure) of the database changes. This rule is particularly difficult to satisfy. Most databases rely on strong ties between the user view of the data and the actual structure of the underlying tables.

Rule 10: Integrity Independence
The database language (like SQL) should support constraints on user input that maintain database integrity. This rule is not fully implemented by most major vendors. At a minimum, all databases do preserve two constraints through SQL.

No component of a primary key can have a null value. (see rule 3)
If a foreign key is defined in one table, any value in it must exist as a primary key in another table.
Rule 11: Distribution Independence
A user should be totally unaware of whether or not the database is distributed (whether parts of the database exist in multiple locations). A variety of reasons make this rule difficult to implement; I will spend time addressing these reasons when we discuss distributed databases.

Rule 12: Nonsubversion Rule
There should be no way to modify the database structure other than through the multiple row database language (like SQL). Most databases today support administrative tools that allow some direct manipulation of the datastructure.

Oracle Top 20 Parallel SQL

SELECT * FROM
(SELECT substr(sql_text,1,100) sql,
ELAPSED_TIME, CPU_TIME,
disk_reads, executions,
disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE ( hash_value, address ) in (
SELECT distinct HASH_VALUE, address
FROM v$sql_plan
WHERE DISTRIBUTION is not null )
and disk_reads > 1000
ORDER BY ELAPSED_TIME DESC)
WHERE rownum <= 20;

Warning: Product user profile information not loaded!

SQL> connect scott/XXXX
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> connect system/XXXX
Connected.


SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

Difference between v$sql v$sqltext v$sqltext_with_newlines

v$sql : if you have multiple copies of the query:

select * from x

if user A and B are executing (in shared pool ) above query then we will have 2 rows in v$sql

v$sqlarea:- aggregate of v$sql. It selects out DISTINCT sql.select * from x
will appear there.

v$sqltext:- for full text

v$sqltext_with_newlines: - without the white space replacement and tab.

Database Point-in-Time Recovery RECOVER DATABASE UNTIL TIME

oracle only accept this command at the time of time base recovery (following format, delimited by single quotation marks 'YYYY-MM-DD:HH24:MI:SS' )

Performs a incomplete recovery (=Point in time recovery).

The format of the time is 'YYYY-MM-DD:HH24:MI:SS'


in sql prompt


SQL>recover automatic database until time '2010-05-09:10:45:00';

SQL>recover database UNTIL TIME '2010-05-08:08:42:00';





Pl don't try or waste your time in following date format at the time of Point in Time Recovery

to_date," " etc ...


SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS' ;

Session altered.

SQL> RECOVER DATABASE UNTIL TIME to_date('2010-05-08:08:42:00','YYYY-MM-DD:HH24:MI:SS');
ORA-00285: TIME not given as a string constant


SQL> RECOVER DATABASE UNTIL TIME "to_date('2010-05-08:08:42:00','YYYY-MM-DD:HH24:MI:SS')";
ORA-00972: identifier is too long


SQL> RECOVER DATABASE UNTIL TIME to_date('2010-05-08:08:42:00','YYYY-MM-DD:HI24:MI:SS');

ORA-00285: TIME not given as a string constant


SQL> RECOVER DATABASE UNTIL TIME "to_date('2010-05-08:08:42:00','YYYY-MM-DD:HI24:MI:SS')";

ORA-00972: identifier is too long


SQL> RECOVER DATABASE UNTIL TIME to_date('2010-05-08:08:42:00','YYYY-MM-DD:HI24:MI:SS') ;

ORA-00285: TIME not given as a string constant


SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> recover database UNTIL TIME "TO_DATE('08/05/2010 08:42:00','DD/MM/YYYY HH24:MI:SS')";

ORA-00972: identifier is too long


SQL> recover database UNTIL TIME TO_DATE('08/05/2010 08:42:00','DD/MM/YYYY HH24:MI:SS');
ORA-00285: TIME not given as a string constant

RECOVER DATABASE UNTIL TIME 'to_date('2006-12-11:10:35:00','YYYY-MM-DD:HH24:MI:SS')'

oracle 10g current SQL Monitor Query

from Web



SELECT /* SQL Monitor Query for >= 10g */
'(' || s.SID || ',' || s.serial# || ')' "sid/serial",
s.program "pgrm", s.terminal "term", s.username "db user",
DECODE (ROUND (s.last_call_et / 60),'0', '< 1',ROUND (s.last_call_et / 60)) "rtime (mins)",
DECODE (ps."px oper cnt", '', 'N/A', ps."px oper cnt") "px opers",
DECODE (ps."px count", '', 'N/A', ps."px count") "px slaves",
si.block_changes "sess bchgs", si.physical_reads "sess preads",
pss_pr."px preads" "child px preads",
si.consistent_gets "sess cgets", pss_cg."px cgets" "child px cgets",
ss_cpu."sess cpu" "sess cpu", pss_cpu."px cpu" "child px cpu",
sa.optimizer_cost "curr sql cost", su."blocks" "temp blocks",
t."used_ublk" "undo blocks", s.event "wait",
s.seconds_in_wait "wait secs", s.state "wait state",
s.sql_id "current sql id", sa.sql_fulltext "sql text",
DBMS_XPLAN.display_cursor (s.sql_id, s.sql_child_number) "sql xplan"
FROM v$session s,
v$sess_io si,
v$sql sa,
(SELECT qcsid, COUNT (DISTINCT server_set) "px oper cnt", COUNT (*) "px count"
FROM v$px_session
WHERE NOT server_set IS NULL
GROUP BY qcsid, DEGREE) ps,
(SELECT qcsid, SUM (VALUE) "px preads"
FROM v$px_sesstat
WHERE statistic# = 54 AND SID != qcsid
GROUP BY qcsid) pss_pr,
(SELECT qcsid, SUM (VALUE) "px cgets"
FROM v$px_sesstat pss
WHERE statistic# = 50 AND SID != qcsid
GROUP BY qcsid) pss_cg,
(SELECT qcsid, SUM (VALUE) "px cpu"
FROM v$px_sesstat pss
WHERE statistic# = 12 AND SID != qcsid
GROUP BY qcsid) pss_cpu,
(SELECT ss.SID, SUM (ss.VALUE) "sess cpu"
FROM v$sesstat ss
WHERE statistic# = 12
GROUP BY ss.SID) ss_cpu,
(SELECT t.ses_addr, SUM (t.used_ublk) "used_ublk"
FROM v$transaction t
GROUP BY t.ses_addr) t,
(SELECT su.session_addr, SUM (su.blocks) "blocks"
FROM v$sort_usage su
GROUP BY su.session_addr) su
WHERE s.sql_address = sa.address
AND s.sql_hash_value = sa.hash_value
AND s.saddr = su.session_addr(+)
AND s.SID = ps.qcsid(+)
AND s.SID = si.SID(+)
AND s.saddr = t.ses_addr(+)
AND s.SID = pss_pr.qcsid(+)
AND s.SID = pss_cg.qcsid(+)
AND s.SID = pss_cpu.qcsid(+)
AND s.SID = ss_cpu.SID(+)
AND s.TYPE != 'BACKGROUND'
AND s.status = 'ACTIVE'
AND program NOT LIKE ('%(C%') --Eliminate Streams Capture
AND program NOT LIKE ('%(A%') --Eliminate Streams Apply
AND program NOT LIKE ('%(P%') --Eliminate Parallel Slaves
ORDER BY sa.optimizer_cost DESC;

oracle 10g find large full scans SQL and objects

SELECT sql_id,sql_text,
total_large_scans,
executions,
executions * total_large_scans sum_large_scans
FROM
(SELECT c.sql_id sql_id, sql_text,
count(*) total_large_scans,
executions
FROM sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
WHERE a.object_owner (+) = b.owner
AND a.object_name (+) = b.segment_name
AND b.segment_type IN ('TABLE', 'TABLE PARTITION')
AND a.operation LIKE '%TABLE%'
AND a.options = 'FULL'
AND c.hash_value = a.hash_value
AND b.bytes / 1024 > 1024
and PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
group by c.sql_id, sql_text, executions)
order by 4 desc
/





col TABLE_OWNER format a12

select sql_id,
table_owner,
table_name,
table_type,
size_kb,
statement_count,
reference_count,
executions,
executions * reference_count total_scans
from
(select c.sql_id sql_id,
a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
sum(c.executions ) executions,
count( distinct a.hash_value ) statement_count,
count( * ) reference_count
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
where a.object_owner (+) = b.owner
and a.object_name (+) = b.segment_name
and b.segment_type in ('TABLE', 'TABLE PARTITION')
and a.operation like '%TABLE%'
and a.options = 'FULL'
and a.hash_value = c.hash_value
and b.bytes / 1024 > 1024
and PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
-- and a.object_owner not in ('SYS','SYSTEM')
and a.object_owner not in ('SYS','SYSTEM','PERFSTAT','SYSMAN','WKSYS')
group by c.sql_id,a.object_owner, a.object_name, a.operation, b.bytes / 1024, b.segment_type
order by 4 desc, 1, 2 );