Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle multi-table query optimization     - CentOS static IP network configuration (Linux)

- After installing minimize RHEL / CentOS 7 we need to do (Linux)

- The Concept and Semantics of Java Memory Model (Programming)

- Linux server is how to do after the invasion (Linux)

- Oracle bug Ora-04043 (Database)

- Linux dmidecode command detail (Linux)

- Linux Mint brightness adjustment --xrandr command learning (Linux)

- Linux System Getting Started Learning: modify environment variables in Linux PATH (Linux)

- Android LayoutInflater source parsing (Programming)

- Android Activity launchMode (Programming)

- Spark SQL job of a lifetime (Server)

- The virtual memory (Linux)

- Simple Calendar C language (Programming)

- Linux gprof oprofiling and performance testing tools (Linux)

- Command line tool Tmux (Linux)

- Oracle 11g R2 RAC RMAN backup script example (Database)

- Understand the security restore accidentally deleted critical system files (Linux)

- C ++ handling text input (Programming)

- Installation Yarock 1.1.4 Music Player in Ubuntu (Linux)

- Supervisor Installation and Configuration (Server)

 
         
  Oracle multi-table query optimization
     
  Add Date : 2018-11-21      
         
         
         
  Oracle has a cache concept, the cache is stored executed SQL statements that oracle to do a lot of work in the implementation of sql statement, such as parsing sql statement, estimate index utilization, bind variables, read data block so these operations. Suppose has been stored in the cache have been executed sql statement, then execute a direct match, fewer steps, naturally fast, but after the test will find the cache works only for simple table, multi-table no circumstances effects, such as a single table in the query called a quick time, but suppose that join multiple tables, on the turtle speed.
The most important thing, ORACLE cache is full character match, what does that mean, look at the following three select

--No.1
select * from tableA;
--No.2
select * From tableA;
--No.3
select * from tableA;

At first glance, these three statements are the same, but the cache is not recognized, the whole match the characters, the index is stored in the cache in three different statements, at this point, but also leads to a habit, it is to maintain good programming practice, this is very important

ORACLE multi-table I gained some optimization, are common, introduced

A, FROM clause in the order behind the table with stress

Let me talk about why, ORACLE in parsing sql statement FROM clause of time behind the table name is parsed from right to left, it is to scan the rightmost table, and then scan the left side of the table, and then use the data to match the table to the left , successful match after the merger. So, in multi-table queries, we must take small writing table at the far right, why they think will understand. For example, the following two statements:

--No.1 TableA: 100w Records tableB: 1w records the execution speed of ten seconds
select count (*) from tableA, tableB;
  
--No.2 Execution speed of one hundred seconds or even higher
select count (*) from tableB, tableA;

This is estimated that many people know, but it is very useful to confirm.

There is also a query three tables, for example,

select count (1) from tableA a, tableB b, tableC c where a.id = b.id and a.id = c.id;

The above tableA crosstab, according to the oracle clause From right to left scanning, should be placed in the table at the end of the cross, and then the smallest table, should be written above

--tableA a Crosstab
--tabelB b 100w
--tableC c 1w
select count (1) from tableB b, tableC c, tableA a where a.id = b.id and a.id = c.id;

The wording of the large amount of data can be very useful, we remember, we are also very popular.

Two, Where clause conditions behind the filter with stress, ORACLE on the where clause conditions behind the filter upward, from right to left scan, and so much the same as the From clause from under the filter row a sequence, press filtering data size, natural conditions is that the minimum data that is written in the bottom, far right, and so on, for example,

--No.1 Undesirable poor performance
select * from tableA a where
a.id> 500
and a.lx = '2b'
and a.id <(select count (1) from tableA where id = a.id)

--No.2 High performance
select * from tableA a where
a.id <(select count (1) from tableA where id = a.id)
and a.id> 500
and a.lx = '2b'

Third, when using select * less, more knock on the keyboard, the field name written on it, because ORACLE * interrogator will convert all table column name, this will be a waste of time, so a large table with less

Fourth, make full use of rowid, rowid can be used to page, delete duplicate records inquiries, very powerful, giving two examples:

--oracle find duplicate records
select * from tableA a where a.rowid> = (select min (rowid) from tableB b where a.column = b.column)
           

--oracle delete duplicate records
delete from tableA a where a.rowid> = (select min (rowid) from tableB b where a.column = b.column)
     

- Paging start = 10 limit = 10
--end to start + limit
- 1. A query to be arranged in a table
- 2. A query to find less than the end of the table Rownum composition data table B
- 3. Query B is greater than the start table to find the data done by rownum
- Simply to filter data based on end value, and then filter data based on start
SELECT * FROM
(SELECT a. *, ROWNUM rn FROM (SELECT * FROM uim_serv_file_data ORDER BY OUID) a where ROWNUM <= 20) b
where rn> 10 order by ouid desc

Fifth, the stored procedure should be noted, the use of a commit, both free resources, but be careful.

Sixth, reduce queries to the database tables, this is very important, it can be reduced to reduce, because at the time the statement is executed oracle will do a lot of the initial work.

Seven, less in, more used to replace exists

--NO.1 IN wording
SELECT * FROM TABLEA A WHERE
A.ID IN (SELECT ID FORM TABLEB B WHERE B.ID> 1)

--NO.2 Exists wording
SELECT * FROM TABLEA A WHERE
EXISTS (SELECT 1 FROM TABLEB B WHERE A.ID = B.ID AND B.ID> 1)
     
         
         
         
  More:      
 
- MySQL 5.7.10 source code for the latest version of the installation process in detail (Database)
- To establish a secure and reliable Linux operating system (Linux)
- The method of CentOS-7.0. The installation and configuration of Tomcat-7 (Server)
- Arrow keys, backspace key garbled in Python-2.7.5 Interactive Mode under CentOS 5.8 (Linux)
- Slow update statement Performance Analysis (Database)
- Git / Github use notes (Linux)
- Rails 4.1.6 start being given Could not find a JavaScript runtime (Linux)
- Use Linux built-in firewall to improve network access control (Linux)
- How to Create a file can not be changed under Linux (Linux)
- About Samba certification process and permissions (Linux)
- Analysis of memory mapping process in Linux x86-64 mode (Linux)
- How to download video youtube-dl in Linux (Linux)
- Ubuntu 14.04 installation and configuration environment variable JDK1.8.0_25 (Linux)
- CentOS install Redis (Database)
- To install Scribus 1.4.4 under ubuntu (Linux)
- Qt shared memory interprocess communication (Programming)
- TOAD connect DB2 error SQL1460N solve (Database)
- Logging information through the web GUI (LogAnalyzer) (Server)
- independently configurable PHP environment under CentOS6.5 (Server)
- Linux raw socket (Programming)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.