How does Oracle optimize speed for multiple large table association operations? ____oracle

The first step is to establish an appropriate index. SQL does not add a function in the index field to ensure that the cable is effective. If the composite index is noted in the Order of SQL. If an index is already present, it is recommended that you rebuild the index first, since the index of the large data table is in a chaotic state and is generally recommended for reconstruction. The establishment of a good general can be achieved dozens of times times the speed of ascension. The table with the maximum amount of data is placed at the top, and the smallest table is on the last side. The SQL is parsed from the back. The second is to put the most effective narrowing of the scope of the conditions to the end of the SQL. In particular, the criteria for the primary key or index field. Ensure that your SQL algorithm is reasonable. Ensure the rationality of complexity and space degree. Use stored procedures when necessary. The speed of 30% to 40% is recommended for paging reading. Don't read all the data. (using rownum), too much data will make the memory less available.

If all of this is not satisfactory, you can consider building a few table spaces, and then according to an algorithm for each table data, the average in the table space (partition), in the Select database will use multithreading to the table space index data, this is not usually the Tens table is not. Not everyone can use it. Oracle Multi-table connectivity for improved efficiency and performance optimization

Execution path: This feature of Oracle greatly improves SQL performance and saves memory usage: we find that the statistics of single table data are two concepts of the speed of multiple table statistics. Single table statistics can take only 0.02 seconds, But 2-table joint statistics may take dozens of tables. This is because Oracle only provides high speed buffering for simple tables (cache buffering), and this feature does not apply to multiple table join queries. The database administrator must set the appropriate parameters for the area in the Init.ora, and the larger the memory area, the more statements can be retained and the greater the likelihood of being shared.

When you submit an SQL statement to Oracle, Oracle first looks for the same statement in this block of memory.
What needs to be noted here is that Oracle is a strict match between the two, and to achieve sharing, the SQL statement must
Exactly the same (including spaces, line wraps, etc.).

The shared statement must meet three criteria:
A. Character-level comparisons:
The statement that is currently executed and the statements in the shared pool must be identical.
For example:

     SELECT * from EMP;

is different from each of the following

           SELECT * from EMP;
           Select * from EMP;
           SELECT       * from      EMP;

the objects referred to in B. Two statements must be exactly the same:

How user object names are accessed

Jack   sal_limit                private synonym
work_city public                       synonym
plant_detail public                    synonym
Jill   sal_limit                private synonym
work_city public                       synonym
plant_detail                    table Owner

Consider whether the following SQL statements can be shared among these two users.

Whether SQL can share the reason

Select Max (sal_cap) from Sal_limit; You cannot have a private synonym-sal_limit for each user, they are different objects
select COUNT (*) from work_city where Sdesc like ' new% '; two users access the same pair Like public synonym-work_city
select A.sdesc,b.location from Work_city A, plant_detail b where a.city_id = b.city_id cannot 
user Jack accesses plant_detail through private synonym and Jill is the owner of the table, and the objects are different.

C. Two binding variables that must use the same name in SQL statements (bind variables)
For example, the first group of two SQL statements is the same (can be shared), and the two statements in the second group are different (even at run time, given the same values as different binding variables)

A.
Select PIN, name from people where pin =: Blk1.pin;
Select PIN, name from people where pin =: Blk1.pin;
B.
Select PIN, name from people where pin =: blk1.ot_ind;
Select PIN, name from people where pin =: blk1.ov_ind;

focus on 1: Select the most efficient table name order (valid only in the Rule-based optimizer) Focus on
The Oracle parser processes the table names in the FROM clause in Right-to-left order, so the last table in the FROM clause (the underlying table driving tables) is processed first. In cases where multiple tables are included in the FROM clause, you must select a table with the fewest number of records to use as the base table. When Oracle processes multiple tables, it uses sorting and merging to connect them. First, scan the first table (the last table in the FROM clause) and order the records, and then scan the second table ( The last second table in the FROM clause, and finally merges all the records retrieved from the second table with the appropriate records in the first table.

Example: Table TAB1 16,384 Records
Table TAB2 1 Records

Select TAB2 as the base table (best Practice)

Select COUNT (*) from tab1,tab2    execution time 0.96 seconds

Select TAB2 as the base table (Bad method)

Select COUNT (*) from TAB2,TAB1    execution time 26.09 seconds

If you have more than 3 table join queries, you need to select the Crosstab table (intersection table) as the underlying table, which is the table referenced by the other tables.

For example, the EMP table describes the intersection of the location table and the category table.

SELECT *
from LOCATION L, CATEGORY C, EMP E
WHERE e.emp_no BETWEEN 1000 and
    e.cat_no = C.cat_no
    A ND E.LOCN = L.LOCN

will be more efficient than the following SQL

SELECT *
from EMP E, LOCATION L, CATEGORY C
WHERE e.cat_no = c.cat_no and
    e.locn = L.LOCN and
    e.emp_no BET Ween 1000 and 2000

focus on the order of joins in the 2:where clause. Focus on

ORACLE parses the WHERE clause in a bottom-up order, according to which the connection between the tables must be written before the other where conditions , and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
For example:

--(inefficient, execution time 156.3 seconds)
SELECT ...
From EMP E
WHERE   SAL > 50000
and     JOB = ' MANAGER '     < (SELECT COUNT (*) from emp
  where mgr=e.empno);

--(efficient, execution time 10.6 seconds)
SELECT ...
From EMP E
where < (SELECT COUNT (*) from EMP
              where mgr=e.empno) and     SAL > 50000
and
  job = ' MANAGER ';

focus on avoiding the use of ' * ' in the 3:select clause. Focus on

When you want to list all columns in the SELECT clause, it is a convenient way to use the dynamic SQL column reference ' * '. Unfortunately, this is a very inefficient approach. In fact, Oracle converts ' * ' to all column names in the parsing process, which is done by querying the data dictionary, which means more time will be spent.

reduce the number of accesses to the database

When executing every SQL statement, Oracle does a lot of work internally: parsing SQL statements, estimating index utilization, binding variables, reading chunks, and so on. Thus, reducing the number of accesses to the database can actually reduce Oracle's workload.
For example
Here are three ways to retrieve employees with an employee number equal to 0342 or 0291.

Method 1 (lowest effect)

     SELECT Emp_name, SALARY, GRADE from
     EMP
     WHERE emp_no = 342;
      SELECT Emp_name, SALARY, GRADE from
     EMP
     WHERE emp_no = 291;

Method 2 (secondary low efficiency)

        DECLARE
         CURSOR C1 (e_no number) is
         SELECT emp_name,salary,grade from
EMP
         WHERE emp_no = e_no;
     BEGIN
         OPEN C1 (342);
         FETCH C1 into ...,..,.. ;
                 OPEN C1 (291);
        FETCH C1 into ...,..,.. ;
          Close C1;
       End;

Method 3 (efficient)

     SELECT A.emp_name, A.salary, A.grade,
             b.emp_name, B.salary, b.grade from
     EMP a,emp B
     WHERE a.emp_no = 342< C4/>and   b.emp_no = 291;

Attention:
The arraysize parameters are reset in Sql*plus, Sql*forms, and pro*c to increase the amount of retrieved data per database access, with a recommended value of 200.

focus on 4: Use the DECODE function to reduce processing time. Focus on
Use the Decode function to avoid repeatedly scanning the same record or repeating the same table.
For example:

SELECT COUNT (*), SUM (SAL) from the
EMP
WHERE dept_no = ename like ' SMITH% ';

SELECT COUNT (*), SUM (SAL) from the
EMP
WHERE dept_no = and ename like ' SMITH% ';

You can use the Decode function to get the same results efficiently.

SELECT COUNT (DECODE (Dept_no, 0020, ' x ', null)) as D0020_count
    , COUNT (DECODE (dept_no, 0030, ' x ', null)) as D0030_count
    , SUM (DECODE (Dept_no, 0020, Sal, NULL)) as D0020_sal
    , sum (DECODE (dept_no, 0030, Sal, NULL)) as D0030_sal
FRO M EMP
WHERE ename like ' SMITH% ';

Similarly, the Decode function can also be applied to the group BY and ORDER BY clauses.

focus on 5: Delete duplicate records. Focus on

The most efficient way to delete a duplicate record (because ROWID is used)

DELETE from emp E
where E.rowid > (SELECT MIN (x.rowid) from EMP X 
                where x.emp_no = E.emp_no);

Focus on 6: replace Delete with truncate. Focus on

When you delete records in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not commit a transaction, Oracle restores the data to the state it was in before it was deleted (or, to be exact, the condition before the delete command was executed)
When using truncate, the rollback segment no longer holds any recoverable information. When the command runs, the data cannot be recovered. Therefore, few resources are invoked and execution time is short.
(Translator: Truncate only applies when deleting full table, truncate is DDL not DML)

Focus on 7: Use commit as much as possible. Focus on

Whenever possible, use a commit in the program as much as possible, so that the performance of the program is improved and the requirements are reduced by the resources released by the commit:

Resources released by commit:

A. The information used to recover data on the rollback segment.
B. Locks obtained by program statements
Space in the C.redo log buffer
D.oracle to manage the internal costs of these 3 resources

(Translator: In the use of commit must pay attention to the integrity of the transaction, in reality, efficiency and transactional integrity is often the fish and bear the cake can not be)

focus on 8: Reduce the query to the table. Focus on

In SQL statements that contain subqueries, you should pay special attention to reducing the query to the table.

For example:

--Inefficient
select Tab_name from
TABLES
where tab_name = (
        select Tab_name from
        tab_columns
        where VERSION = 604
    ) and
    db_ver = (
        SELECT db_ver from
        tab_columns
        WHERE VERSION = 604
    )

--Efficient
Select Tab_name from the
TABLES
WHERE (tab_name,db_ver)
= (select Tab_name,db_ver)
from Tab_columns WHERE VERSION = 604)

Update multiple column Examples:

-Inefficient:
            UPDATE EMP
            SET emp_cat = (select Max (CATEGORY) from emp_categories),
               sal_range = (select Max (sal_range From emp_categories)
            WHERE emp_dept = 0020;

-Efficient:
            UPDATE EMP
            SET (emp_cat, Sal_range) = (SELECT max (CATEGORY), Max (Sal_range) from emp_categories)
            WHERE emp_dept = 0020;

Focus on 9: Replace in with exists, focus on

In many queries based on the underlying table, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) usually increases the efficiency of the query.

-Inefficient:
select * from
EMP (underlying table)
where EMPNO >0 and
DEPTNO in (select DEPTNO from
DEPT where LOC = ' Melb ')

-Efficient:
select * from
EMP (underlying table)
where EMPNO >0 and
EXISTS (select ' X ' from
DEPT
where DEPT. DEPTNO = EMP. DEPTNO and
LOC = ' Melb ')

(Translator: Relatively speaking, replacing not in with not exists will significantly increase efficiency, as noted in the next section)

focus on 10: Use NOT exists instead of in. Focus on

In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least efficient (because it performs a full table traversal of the table in the subquery). In order to avoid using not in, we can rewrite it as an outer join (Outer joins) or not EXISTS.
For example:

SELECT ...
From the EMP
where Dept_no not in (SELECT dept_no from DEPT where dept_cat= ' A ');

To improve efficiency. Rewrite as:

--(Method one: Efficient) SELECT ... from
EMP a,dept B
WHERE a.dept_no = b.dept (+) and
B.dept_no is NULL and
B.dept_c at (+) = ' A '

--(Method two: Most efficient) Select .... from the
EMP E
WHERE not EXISTS (select ' X ' from
                     DEPT D
                     where d.dept_no = e.dept _no and 
                     dept_cat = ' A ');

Of course, the most efficient way is to have a table association. Direct two-table relationship couplet speed is the fastest!

Focus on 11: Identify the ' inefficient execution ' of the SQL statement. Focus on

Use the following SQL tools to find inefficient sql:

--executions the execution of all child cursors--disk_reads the number of read disks that are caused by all child cursors running this statement--buffer_gets The number of read memory that is caused by running this statement by all child cursors--hit_radio hit rate--reads_per_run each time a read-write disk is executed SELECT executions, disk_reads, Buffer_gets, ROUND (    (buffer_gets-disk_reads)/buffer_gets,2) Hit_radio, ROUND (disk_reads/executions,2) Reads_per_run,sql_text from V$sqlarea WHERE executions>0 and Buffer_gets>0 and (buffer_gets-disk_reads)/buffer_gets < 0.8 order BY 4 DE SC;