About Database Development Fundamentals
- Design Basics
- Connection Strategies for Database Applications
- Performance and Scalability
- Designing Applications for Oracle Real-World Performance
- Security
- High Availability
- Advanced PL/SQL Features
1 Design Basics
1.1 Design for Performance
The key to database and application performance is design, not tuning. While tuning is quite valuable, it cannot make up for poor design. Your design must start with an efficient data model, well-defined performance goals and metrics, and a sensible benchmarking strategy. Otherwise, you will encounter problems during implementation, when no amount of tuning will produce the results that you could have obtained with good design. You might have to redesign the system later, despite having tuned the original poor design.
1.2 Design for Scalability
Scalability is the ability of a system to perform well as its load increases. Load is a combination of number of data volumes, number of users, and other relevant factors. To design for scalability, you must use an effective benchmarking strategy, appropriate application development techniques (such as bind variables), and appropriate Oracle Database architectural features like shared server connections, clustering, partitioning, and parallel operations.
1.3 Design for Extensibility
Extensibility is the ease with which a database or database application accommodates future growth. The more extensible the database or application, the easier it is to add or change functionality with minimal impact on existing functionality.
1.3.1 Data Cartridges
1.3.2 External Procedures
1.3.3 User-Defined Functions and Aggregate Functions
1.3.4 Object-Relational Features
1.4 Design for Security
Database security involves a wide range of potential activities, including:
-
Designing and implementing security policies to protect the data of an organization, users, and applications from accidental, inappropriate, or unauthorized actions
-
Creating and enforcing policies and practices of auditing and accountability for inappropriate or unauthorized actions
-
Creating, maintaining, and terminating user accounts, passwords, roles, and privileges
-
Developing applications that provide desired services securely in a variety of computational models, leveraging database and directory services to maximize both efficiency and ease of use
1.5 Design for Availability
Availability is the degree to which an application, service, or function is accessible on demand. A system designed for high availability provides uninterrupted computing services during essential time periods, during most hours of the day throughout the year, with minimal downtime for operations such as upgrading the system's hardware or software. The main characteristics of a highly available system are:
-
Reliability
-
Recoverability
-
Timely error detection
-
Continuous operation
1.6 Design for Portability
PL/SQL stored procedures provide some application portability across multiple databases. Although using stored procedures written in the language of a given vendor may seem to tie you to that vendor to some extent, stored procedures make the application's visual component (user interface) and application logic portable. The data logic is encoded optimally for the database on which the application runs. Because the data logic is hidden in stored procedures, you can use the vendor's extensions and features to optimize the data layer.
When developed and deployed on a database, the application can stay deployed on that database forever. If the application is moved to another database, the visual component and application logic can move independently of the data logic in the stored procedures, which simplifies the move. (Reworking the application in combination with the move complicates the move.)
1.7 Design for Diagnosability
Oracle Database includes a fault diagnosability infrastructure for preventing, detecting, diagnosing, and resolving database problems. Problems include critical errors such as code bugs, metadata corruption, and customer data corruption. The goals of the diagnosability infrastructure are to detect problems proactively, limit damage and interruptions after a problem is detected, reduce the time required to diagnose and resolve problems, and simplify any possible interaction with Oracle Support.
Automatic Diagnostic Repository (ADR) is a file-based repository that stores database diagnostic data such as trace files, the alert log, and Health Monitor reports. ADR is located outside the database, which enables Oracle Database to access and manage ADR when the physical database is unavailable.
1.8 Design for Special Environments
1.8.1 Data Warehousing
A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but can include data from other sources. Data warehouses separate analysis workload from transaction workload and enable an organization to consolidate data from several sources. This strategy helps the organization maintain historical records and analyze the data to better understand and improve its business.
In addition to a relational database, a data warehouse environment can include:
-
An extraction, transportation, transformation, and loading (ETL) solution
-
Statistical analysis
-
Reporting
-
Data mining capabilities
-
Client analysis tools
-
Applications that manage the process of gathering data; transforming it into useful, actionable information; and delivering it to business users
Data warehousing systems typically:
-
Use many indexes
-
Use some (but not many) joins
-
Use denormalized or partially denormalized schemas (such as a star schema) to optimize query and analytical performance
-
Use derived data and aggregates
-
Have workloads designed to accommodate ad hoc queries and data analysis
Because you might not know the workload of your data warehouse in advance, you must optimize the data warehouse to perform well for a wide variety of possible query and analytical operations.
-
Are updated regularly (nightly or weekly) by the ETL process, using bulk data modification techniques
1.8.2 Online Transaction Processing (OLTP)
Online transaction processing (OLTP) systems are optimized for fast and reliable transaction handling. Compared to data warehouse systems, most OLTP interactions involve a relatively small number of rows, but a larger group of tables. In OLTP systems, performance requirements require that historical data be frequently moved to an archive.
OLTP systems typically:
-
Use few indexes.
-
Use many joins.
-
Use fully normalized schemas to optimize update, insert, and delete performance, and to guarantee data consistency.
-
Rarely use derived data and aggregates.
-
Have workloads consisting of predefined operations.
-
Have users routinely issuing individual data modification statements to the database, so that the OLTP database always reflects the current state of each transaction.
1.9 Features for Special Scenarios
1.9.1 SQL Analytic Functions
A SQL analytic function computes an aggregate value based on a group of rows. A SQL analytic function differs from an aggregate function in that it returns multiple rows for each group. For each row, a window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
SQL analytic functions bring to set-oriented SQL the ability to use array semantics on result sets. They provide coding efficiency, because they enable concise, straightforward coding of logic that is otherwise cumbersome or impossible. They also provide processing efficiency, because they are integral to Oracle Database and use internal optimizations.
A typical use of analytic functions is to retrieve the most current information in a table. For example, a query of the following form returns information from the row with the most recent update time for each customer with records in a table:
SELECT ... FROM my_table t1
WHERE upd_time = ( SELECT MAX(UPD _TIME)
FROM my_table t2
WHERE t2.cust_id = t1.cust_id );
The preceding query uses a correlated subquery to find the MAX(UPD _TIME) by cust _id, record by record. Therefore, the correlated subquery could be evaluated once for each row in the table. If the table has very few records, performance may be adequate; if the table has tens of thousands of records, the cumulative cost of repeatedly executing the correlated subquery is high.
The following query makes a single pass on the table and computes the maximum UPD_TIME during that pass. Depending on various factors, such as table size and number of rows returned, the following query may be much more efficient than the preceding query:
SELECT ...
FROM ( SELECT t1.*,
MAX(UPD_TIME) OVER (PARTITION BY cust _id) max_time
FROM my_table t1
)
WHERE upd_time = max_time;The available analytic functions are:
AVG
CORR
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE
LAG
LAST
LAST_VALUE
LEAD
LISTAGG
MAX
MIN
NTH_VALUE
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions
ROW_NUMBER
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE1.9.2 Materialized Views
Materialized views are query results that have been stored ("materialized") as schema objects. Like tables and views, materialized views can appear in the FROM clauses of queries.
Materialized views are used to summarize, compute, replicate, and distribute data. They are useful for pre-answering general classes of questions—users can query the materialized views instead of individually aggregating detail records. Some environments where materialized views are useful are data warehousing, replication, and mobile computing.
Materialized views require time to create and update, and disk space for storage, but these costs are offset by dramatically faster queries. In these respects, materialized views are like indexes, and they are called "the indexes of your data warehouse." Unlike indexes, materialized views can be queried directly (with SELECT statements) and sometimes updated with DML statements (depending on the type of update needed).
A major benefit of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables. The transformation is transparent to the end user or application, requiring no intervention and no reference to the materialized view in the SQL statement. Because query rewrite is transparent, materialized views can be added or dropped like indexes without invalidating the SQL in the application code.
he following statement creates and populates a materialized aggregate view based on three master tables in the SH sample schema:
CREATE MATERIALIZED VIEW sales_mv AS
SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id
AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;
1.9.3 Partitioning
Partitioning is the database ability to physically break a very large table, index, or materialized view into smaller pieces that it can manage independently. Partitioning is similar to parallel processing, which breaks a large process into smaller pieces that can be processed independently.
Each partition is an independent object with its own name and, optionally, its own storage characteristics. Partitioning is useful for many different types of database applications, particularly those that manage large volumes of data. Benefits include increased availability, easier administration of schema objects, reduced contention for shared resources in OLTP systems, and enhanced query performance in data warehouses.
To partition a table, specify the PARTITION BY clause in the CREATE TABLE statement. SELECT and DML statements do not need special syntax to benefit from the partitioning.
A common strategy is to partition records by date ranges. The following statement creates four partitions, one for records from each of four years of sales data (2008 through 2011):
CREATE TABLE time_range_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')),
PARTITION SALES_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')),
PARTITION SALES_2010 VALUES LESS THAN (TO_DATE('01-JAN-2011','DD-MON-YYYY')),
PARTITION SALES_2011 VALUES LESS THAN (MAXVALUE)
); 1.9.4 Temporal Validity Support
Temporal Validity Support lets you associate one or more valid time dimensions with a table and have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of the period for which a given record is considered valid. Examples of time-based validity are the hire and termination dates of an employee in a Human Resources application, the effective date of coverage for an insurance policy, and the effective date of a change of address for a customer or client.
Temporal Validity Support is typically used with Oracle Flashback Technology, for queries that specify the valid time period in AS OF and VERSIONS BETWEEN clauses. You can also use the DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME procedure to specify an option for the visibility of table data: all table data (the default), data valid at a specified time, or currently valid data within the valid time period at the session level.
Temporal Validity Support is useful in Information Lifecycle Management (ILM) and any other application where it is important to know when certain data becomes valid (from the application's perspective) and when it becomes invalid (if ever).
The following example creates a table with Temporal Validity Support, inserts rows, and issues queries whose results depend on the valid start date and end date for individual rows.
CREATE TABLE my_emp(
empno NUMBER,
last_name VARCHAR2(30),
start_time TIMESTAMP,
end_time TIMESTAMP,
PERIOD FOR user_valid_time (start_time, end_time));
INSERT INTO my_emp VALUES (100, 'Ames', '01-Jan-10', '30-Jun-11');
INSERT INTO my_emp VALUES (101, 'Burton', '01-Jan-11', '30-Jun-11');
INSERT INTO my_emp VALUES (102, 'Chen', '01-Jan-12', null);
-- Valid Time Queries --
-- AS OF PERIOD FOR queries:
-- Returns only Ames.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Jun-10');
-- Returns Ames and Burton, but not Chen.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Jun-11');
-- Returns no one.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP( '01-Jul-11');
-- Returns only Chen.
SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Feb-12');
-- VERSIONS PERIOD FOR ... BETWEEN queries:
-- Returns only Ames.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN
TO_TIMESTAMP('01-Jun-10') AND TO_TIMESTAMP('02-Jun-10');
-- Returns Ames and Burton.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN
TO_TIMESTAMP('01-Jun-10') AND TO_TIMESTAMP('01-Mar-11');
-- Returns only Chen.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN
TO_TIMESTAMP('01-Nov-11') AND TO_TIMESTAMP('01-Mar-12');
-- Returns no one.
SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN
TO_TIMESTAMP('01-Jul-11') AND TO_TIMESTAMP('01-Sep-11');SQL> conn scott/tiger@PDB1;
Connected.
SQL>
SQL>
SQL> show user;
USER is "SCOTT"
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL> CREATE TABLE my_emp(
2 empno NUMBER,
3 last_name VARCHAR2(30),
4 start_time TIMESTAMP,
5 end_time TIMESTAMP,
6 PERIOD FOR user_valid_time (start_time, end_time));
Table created.
SQL> INSERT INTO my_emp VALUES (100, 'Ames', '01-Jan-10', '30-Jun-11');
INSERT INTO my_emp VALUES (101, 'Burton', '01-Jan-11', '30-Jun-11');
1 row created.
SQL>
1 row created.
SQL> INSERT INTO my_emp VALUES (102, 'Chen', '01-Jan-12', null);
1 row created.
SQL> SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Jun-10');
EMPNO LAST_NAME START_TIME
---------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
100 Ames 01-JAN-10 12.00.00.000000 AM
30-JUN-11 12.00.00.000000 AM
SQL> set pagesize 200 linesize 200
SQL> SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Jun-10');
EMPNO LAST_NAME START_TIME
---------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
100 Ames 01-JAN-10 12.00.00.000000 AM
30-JUN-11 12.00.00.000000 AM
SQL> SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Jun-11');
EMPNO LAST_NAME START_TIME
---------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
100 Ames 01-JAN-10 12.00.00.000000 AM
30-JUN-11 12.00.00.000000 AM
101 Burton 01-JAN-11 12.00.00.000000 AM
30-JUN-11 12.00.00.000000 AM
SQL> SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP( '01-Jul-11');
no rows selected
SQL> SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Feb-12');
EMPNO LAST_NAME START_TIME
---------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
102 Chen 01-JAN-12 12.00.00.000000 AM
SQL> SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN
2 TO_TIMESTAMP('01-Jun-10') AND TO_TIMESTAMP('02-Jun-10');
EMPNO LAST_NAME START_TIME
---------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
100 Ames 01-JAN-10 12.00.00.000000 AM
30-JUN-11 12.00.00.000000 AM
SQL> SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN
2 TO_TIMESTAMP('01-Jun-10') AND TO_TIMESTAMP('01-Mar-11');
EMPNO LAST_NAME START_TIME
---------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
100 Ames 01-JAN-10 12.00.00.000000 AM
30-JUN-11 12.00.00.000000 AM
101 Burton 01-JAN-11 12.00.00.000000 AM
30-JUN-11 12.00.00.000000 AM
SQL> SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN
2 TO_TIMESTAMP('01-Nov-11') AND TO_TIMESTAMP('01-Mar-12');
EMPNO LAST_NAME START_TIME
---------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
102 Chen 01-JAN-12 12.00.00.000000 AM
SQL> SELECT * from my_emp VERSIONS PERIOD FOR user_valid_time BETWEEN
2 TO_TIMESTAMP('01-Jul-11') AND TO_TIMESTAMP('01-Sep-11');
no rows selected
SQL> ALTER TABLE my_emp ADD PERIOD FOR user_valid_time;
ALTER TABLE my_emp ADD PERIOD FOR user_valid_time
*
ERROR at line 1:
ORA-55603: invalid flashback archive or valid time period command
SQL> INSERT INTO my_emp VALUES (103, 'Davis', '01-Jan-12', null);
1 row created.
SQL> UPDATE my_emp SET end_time = '01-Feb-12' WHERE empno = 103;
1 row updated.
SQL> INSERT INTO my_emp VALUES (103, 'Smith', '02-Feb-12', null);
1 row created.
SQL> SELECT * from my_emp AS OF PERIOD FOR user_valid_time SYSDATE WHERE empno = 103;
EMPNO LAST_NAME START_TIME
---------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
103 Smith 02-FEB-12 12.00.00.000000 AM
SQL> SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('01-Jul-12')
2 WHERE empno = 103;
EMPNO LAST_NAME START_TIME
---------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
103 Smith 02-FEB-12 12.00.00.000000 AM
SQL> SELECT * from my_emp AS OF PERIOD FOR user_valid_time TO_TIMESTAMP('20-Jan-12')
2 WHERE empno = 103;
EMPNO LAST_NAME START_TIME
---------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
103 Davis 01-JAN-12 12.00.00.000000 AM
01-FEB-12 12.00.00.000000 AM
SQL>
相关文章
- 通过pycharm的database设置进行数据库的可视化
- sqlite database browser的使用「建议收藏」
- DBA 面对的几种 数据库“问题” 制造者 (Database killer)
- 如何运维多集群数据库？58 同城 NebulaGraph Database 运维实践
- ORA-01138: database must either be open in this instance or not at all ORACLE 报错 故障修复 远程处理
- ORA-47997: Database Vault protected schema does not exist ORACLE 报错 故障修复 远程处理
- ORA-00309: log belongs to wrong database ORACLE 报错 故障修复 远程处理
- ORA-01228: SET DATABASE option required to install seed database ORACLE 报错 故障修复 远程处理
- ORA-12336: cannot login to database (link name string) ORACLE 报错 故障修复 远程处理
- ORA-16747: logical standby database guard could not be turned on ORACLE 报错 故障修复 远程处理
- a databaseMySQL: An Excellent Database Solution（mysqlendas）
- MySQL PDO: Mastering the Power of Database Access”（mssqlpdo）