2009/07/28, 9:51 pm
A database administrator (DBA) is a person who is responsible for the environmental aspects of a database. The role of a database administrator has changed according to the technology of database management systems (DBMSs) as well as the needs of the owners of the databases. For example, although logical and physical database design are traditionally the duties of a database analyst or database designer, a DBA may be tasked to perform those duties.
Duties
The duties of a database administrator vary and depend on the job description, corporate and Information Technology (IT) policies and the technical features and capabilities of the DBMS being administered. They nearly always include disaster recovery (backups and testing of backups), performance analysis and tuning, data dictionary maintenance, and some database design.
Some of the roles of the DBA may include
- Installation of new software — It is primarily the job of the DBA to install new versions of DBMS software, application software, and other software related to DBMS administration. It is important that the DBA or other IS staff members test this new software before it is moved into a production environment.
- Configuration of hardware and software with the system administrator — In many cases the system software can only be accessed by the system administrator. In this case, the DBA must work closely with the system administrator to perform software installations, and to configure hardware and software so that it functions optimally with the DBMS.
- Security administration — One of the main duties of the DBA is to monitor and administer DBMS security. This involves adding and removing users, administering quotas, auditing, and checking for security problems.
- Data analysis — The DBA will frequently be called on to analyze the data stored in the database and to make recommendations relating to performance and efficiency of that data storage. This might relate to the more effective use of indexes, enabling "Parallel Query" execution, or other DBMS specific features.
- Database design (preliminary) — The DBA is often involved at the preliminary database-design stages. Through the involvement of the DBA, many problems that might occur can be eliminated. The DBA knows the DBMS and system, can point out potential problems, and can help the development team with special performance considerations.
- Data modeling and optimization — By modeling the data, it is possible to optimize the system layouts to take the most advantage of the I/O subsystem.
- Responsible for the administration of existing enterprise databases and the analysis, design, and creation of new databases.
- Data modeling, database optimization, understanding and implementation of schemas, and the ability to interpret and write complex Structured Query Language (SQL) queries
- Proactively monitor systems for optimum performance and capacity constraints
- Establish standards and best practices for SQL
- Interact with and coach developers in SQL scripting
http://metalink.oracle.com/
Share on Facebook
2009/07/09, 9:20 pm
去 Oracle 那邊 download
那個 instantclient-sqlplus-linux32-11.1.0.7.zip
放到 /usr/portage/distfiles
下 emerge -av dev-db/oracle-instantclient-sqlplus
Share on Facebook
2009/06/30, 4:35 pm
ver 2.7:
幾個查詢例:
SELECT m.* FROM wp_posts p , wp_postmeta m
where p.id=m.post_id
order by post_id desc
SELECT tr.*
FROM wp_posts p , wp_term_relationships tr
where p.id=tr.object_id
order by p.id desc
SELECT p.id , w.*
FROM wp_posts p , wp_term_relationships tr , wp_term_taxonomy t
where p.id=tr.object_id
and tr.term_taxonomy_id=t.term_taxonomy_id
order by p.id desc
SELECT p.id , w.*
FROM wp_posts p , wp_term_relationships tr , wp_term_taxonomy t , wp_terms w
where p.id=tr.object_id
and tr.term_taxonomy_id=t.term_taxonomy_id
and t.term_id = w.term_id
order by p.id desc
Share on Facebook
2009/06/24, 9:12 am
參考: http://dev.mysql.com/doc/refman/5.0/en/optimizing-database-structure.html
7.4.1. Make Your Data as Small as Possible
7.4.2. Column Indexes
7.4.3. Multiple-Column Indexes
7.4.4. How MySQL Uses Indexes
7.4.5. The MyISAM Key Cache
7.4.6. MyISAM Index Statistics Collection
7.4.7. How MySQL Opens and Closes Tables
7.4.8. Disadvantages of Creating Many Tables in the Same Database
減少 record structure 的大小 – Numeric Types 參考表
(用 MEDIUMINT 3bytes 比 INT 4bytes 好 , 若資料內容不可能有負值那加上 UNSIGNED , 則數值範圍可多一倍!)
Share on Facebook
2009/06/23, 9:40 am
http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
);
The name index is an index over the last_name and first_name columns. The index can be used for queries that specify values in a known range for last_name, or for both last_name and first_name. Therefore, the name index is used in the following queries:
SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test
WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius'
AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test
WHERE last_name='Widenius'
AND first_name >='M' AND first_name < 'N';
However, the name index is not used in the following queries, 以下的 query 用不到 index —> 多重 index 有先後區別.
SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius' OR first_name='Michael';
Share on Facebook
2009/06/10, 11:01 am
官網資料 : http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_aqadm.htm#i1015375
The DBMS_AQADM package provides procedures to manage Oracle Streams Advanced Queuing (AQ) configuration and administration information.
See Also:
This chapter contains the following topics:
Share on Facebook
2009/05/11, 10:41 pm
sqlplus account/password@172.25.0.18x/service_name
sqlplus sys/password@172.30.0.48/oradb as sysdba
或
sqlplus /nolog
conn account/password@172.25.0.18x/service_name
Share on Facebook
2009/04/23, 9:06 pm
The UNION query allows you to combine the result sets of 2 or more “select” queries. It removes duplicate rows between the various “select” statements.
select count(1) from (
— 賣家
select ctrl_rowid
from bidleader.sold_record s
where s.ctrl_date like ’200701%’
group by ctrl_rowid
union
— 買家
select p_ctrl_rowid ctrl_rowid
from bidleader.sold_record s
where s.ctrl_date like ’200701%’
group by p_ctrl_rowid
) t
Share on Facebook
