optimization of SQL optimization for count, table connection order, conditional order, in and exist _oracle

In this paper, the optimization of count, table connection order, conditional order, in and exist in SQL optimization is very practical. Details are as follows:

One, about count

Have you seen some online articles about count (*) and count (columns), is the Count (column) more efficient than count (*)?

In fact, the individual feels that count (*) and Count (column) are simply not comparable, COUNT (*) counts the total number of columns in the table, and Count (column) counts the number of Non-null records in the column.

But we can use the experiment to compare:

First create the test table:

drop table test Purge;
CREATE TABLE Test as SELECT * from Dba_objects;

Update test set object_id =rownum;
Set timing on 
set linesize 1000
set autotrace on 

Perform

Select COUNT (*) from test;
Select COUNT (object_id) from test;

Discovering that time consuming is the same, is their efficiency actually the same?

Let's try to create an index on the column object_id.

CREATE INDEX idx_object_id on test (object_id);

And then execute it again.

Select COUNT (*) from test;
Select COUNT (object_id) from test;

The speed of finding count (object_id) is significantly higher than count (*), is it because count (object_id) can be used to index, so the efficiency is improved a lot?

We'll modify the column properties for the next object_id.

ALTER TABLE test modify OBJECT_ID NOT null;

And then execute it again.

Select COUNT (*) from test;
Select COUNT (object_id) from test;

It turns out that their speed is just as fast, and COUNT (*) can also be used to index.
In fact, the premise of efficiency comparison is two statements to be equivalent, the two kinds of writing is not equivalent, and therefore not comparable.

For the Oracle optimizer, we can see from the experiment that thestatistics of different columns, the time is not the same, the general trend is that the more the column, the greater the cost of access, the column's offset determines the performance of access. The cost of Count (*) is independent of the offset. As a result, count (*) is the fastest on some occasions .

Ii. about in and exist

Most of the statements about in and exist are that in efficiency is higher than exist, so there must be a place in where you have to replace exist and so on. But is that really the case?

Now let's do a trial:

In Oracle 10g;

SELECT * FROM dept where Deptno isn't in (select Deptno from emp);
SELECT * FROM dept where not exists (select Deptno from emp where Emp.deptno=dept.deptno);

We find that exist is indeed more efficient than in. This statement seems to be the establishment of AH.

But we'll execute the following statement

SELECT * FROM dept where deptno. (select Deptno from emp where deptno are NOT null) and DEPTNO are NOT null;

You will find that the efficiency of in and exist is the same when you add a Non-empty constraint.

View execution plans for three statements you will find that in and exist statements without non-null constraints are all anti, so the efficiency is the same, and the in statement without the non-null constraint is filter instead of the anti algorithm, so the efficiency is worse.

So we can draw the conclusion that in Oracle 10g, if you can ensure non-null, the in constraint can use the anti algorithm, and the efficiency is the same as the exist.

In Oracle 11g:

SELECT * FROM dept where Deptno isn't in (select Deptno from emp);
SELECT * FROM dept where not exists (select Deptno from emp where Emp.deptno=dept.deptno);

We found that the two statements were of the same efficiency, as was the view of the execution plan. Oracle has been optimized in 11g, so the efficiency of in and exist is the same.

From this we can conclude that in 11g, the efficiency of using in and exist is the same, because they are going to be more efficient anti algorithm .

The connection order of the size table

We can see a lot of such articles on the Internet, in the form of multiple table query, with a small table or crosstab to do the underlying table, put in the back, the large table placed in the back of the position, because the table access order is from the right to the left.

But is that really the case?

We can do experiments to verify that (here the test environment is Oracle 11g):

CREATE TABLE Tab_big as SELECT * from Dba_objects where rownum<=30000;
CREATE TABLE Tab_small as SELECT * from Dba_objects where rownum<=10;
Set Autotrace traceonly
set linesize 1000
set timing on 
select COUNT (*) from Tab_big,tab_small; 
Select COUNT (*) from Tab_small,tab_big;

We looked at the execution plan and found that the efficiency of the two statements was the same, is it not a multiple-table query, and the order of the tables has nothing to do with efficiency?

We are executing the following statement:

Select/*+rule*/Count (*) from Tab_big,tab_small; 
Select/*+rule*/Count (*) from Tab_small,tab_big;

We can clearly find that the small table in the right, large table in the left statement, query efficiency is much higher.

In fact, in the rule-based era, query efficiency is related to the table's connection order, small table or crosstab on the left, large table on the right to perform a higher efficiency. But now it's basically a cost-free era, so the order and efficiency of the size tables are irrelevant, and the Oracle optimizer automatically optimizes efficiency.

Iv. Join condition Order in the WHERE clause

In the rule-based era, Oracle uses a bottom-up sequence to parse the WHERE clause, and based on this principle, we typically place the table with the least number of rows in the last face, and the clause with the filter condition in the WHERE clause is put on the last side.

But in the current era of cost, this optimization has been optimized by the Oracle optimizer, so the order of the tables and the ordering of the conditions will not affect our query efficiency.