Myths SQL. Continued.

A recent article on Habré Myths SQL SQL Khabar people like. And many have asked for examples of why everything works this way and not otherwise.


All written code works and is tested on

SQL > select * from v $ version;
')
BANNER
-------------------------------------------------- ------------ -
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL / SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32- bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Executed in 0.0494 seconds * This code was highlighted with Source Code Highlighter .

A free version of Orakla can be found here .

On the issue of code portability. Look, try to run and get the same results on your favorite database. All questions about portability immediately disappear.

The magic parameter is somewhere deep in the server configurations fast = true
You can take and optimize any individual query, or even part of the query, without touching anything other than it.
The very question of what needs to be done to speed up the work of the database does not make sense.
As a rule, it is necessary to revise the structure of the tables, rewrite queries, redo indexes. We need a whole range of measures. There is no magic action.

You can understand how the request works and improve it by simply looking at the request code.
Show me the query plan and then you can start thinking. Without a plan, nothing at all can be said about the optimality of work.

In temporary tables, neither the Primary Key nor the indices need to be done.
Temporary tables are no worse than ordinary ones. If we use a temporary table to run an algorithm on it, then indeed the indices will only harm. But it happens that the temporary table is used as the most common, that is, we put the data in it and then we make to it a lot of different requests. In this case, PK and indexes greatly facilitate life.

Foreign key = Index on the field in the child table
Very nice article on this topic.
The main conclusions from there:

  Problems that may arise with non-indexed foreign keys. 
 • high probability of interlocking.
 • when deleting from the main table, if the limit is set to on delete cascade, a cascade delete from the fixed table is called.  And if there is no index on the foreign key, then Orakl is forced to do a full view of the fixed table.  And this is not good - it takes a lot of resources and time consuming.
 • for a query from the main table to be repaired, if there is a key condition in the where clause, there will be a significant slowdown of work.
 Do not worry about the presence of an index on a foreign key under the following conditions: 
 • never records from the main table are deleted.
 • The value of a unique, primary key never changes.
 • Never in queries, the master and subordinate table are not joined.

Addition from me to the item “Never in requests the main and fixed table are connected.”
I mean only requests of the form

SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON.d = t2.id * This is a code highlighted with the Source Code Highlighter .

If we have all requests of the form

SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE t1.code = 'A'
AND t2.code = 'B' * This code was highlighted with Source Code Highlighter .

It will be much more efficient to create a composite index (id, code) and automatically created (id) will only interfere.

A request with a lower cost must work faster
Better Tom Kite, I will not write.
First link
Second link
The links are the same, just in case, if suddenly one of the servers disappears.

The absolute identity of the following code options
Just run this code.

- Create table
create table TABLE1
(
ID NUMBER (13) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

insert into table1
select rownum
from dual
connect by level <100000

Sql > declare
2 summ number (10);
3 i number (10);
4 cursor c is select id from table1;
5 begin
6 summ: = 0;
7 open c;
eight
9 loop
10 fetch c into i;
11 exit when c% notfound;
12
13 summ: = summ + i;
14 end loop;
15
16 close c;
17
18 dbms_output.put_line (summ);
nineteen
20 end ;
21 /

PL / SQL procedure successfully completed

Executed in 1,515 seconds

SQL > select sum (id) from table1;

SUM (ID)
-------- -
4999950000

Executed in 0.031 seconds * This code was highlighted with Source Code Highlighter .

The difference is 50 times.

Any reference to the index is better than without it.
For queries that return most of the table, access by index will be slower than a full table scan.

This is subject to a simple example:

- Create table
create table TABLE1
(
ID NUMBER (13) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
- Create / Recreate indexes
create index IX_TABLE1 on TABLE1 (ID)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

insert into table1
select rownum
from dual
connect by level <1000000;

SQL > select / * + index (table1 IX_TABLE1) * /
2 COUNT (*) from table1;

COUNT (*)
-------- -
1099998

Executed in 0.172 seconds

SQL >
SQL > select / * + no_index (table1 IX_TABLE1) * /
2 COUNT (*) from table1;

COUNT (*)
-------- -
1099998

Executed in 0.063 seconds * This code was highlighted with Source Code Highlighter .

The difference is 2.7 times.

The query is slow, you need to add an index
Adding indexes helps, but this is not a panacea. At some point, INSERT and UPDATE will start to work very slowly due to the large number of indexes, and it will be very difficult to change anything. We'll have to redo most of the requests. It is better to write right away so that such a situation does not arise.

The connection speed of the tables depends strongly on the types and number of fields by which we connect them.
Check the connection speed of the tables by id and the same tables by a pair of text fields.

Create tables

- Create table
create table TABLE1
(
ID NUMBER (13) not null ,
CODEA VARCHAR2 (1024) not null ,
CODEB VARCHAR2 (1024) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
- Create / Recreate indexes
create index IX_TABLE1 on TABLE1 (ID)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IX1_TABLE1 on TABLE1 (CODEA, CODEB)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

- Create table
create table TABLE2
(
ID NUMBER (13) not null ,
CODEA VARCHAR2 (1024) not null ,
CODEB VARCHAR2 (1024) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
- Create / Recreate indexes
create index IX_TABLE2 on TABLE2 (ID)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IX2_TABLE2 on TABLE2 (CODEA, CODEB)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
); * This source code was highlighted with Source Code Highlighter .

Fill

insert into table1
select rownum, rownum, rownum
from dual
connect by level <100000;

insert into table2
select rownum, rownum, rownum
from dual
connect by level <100000; * This source code was highlighted with Source Code Highlighter .

Check

SQL >
SQL > select count (*)
2 from table1 t1
3 INNER JOIN table2 t2 ON t2.codea = t1.codea
4 AND t2.codeb = t2.codeb;

COUNT (*)
-------- -
99999

Executed in 0.078 seconds

SQL >
SQL > select count (*)
2 from table1 t1
3 INNER JOIN table2 t2 ON t2.id = t1.id;

COUNT (*)
-------- -
99999

Executed in 0.079 seconds * This code was highlighted with Source Code Highlighter .

The spread of values ​​within the error.

When requesting a filter with 2 fields, both indexes work
Tom Kite wrote about when using multiple indexes.
First link
Second link

The speed of the query is estimated by how quickly the first 20 lines were printed.
To test this myth you need a rather complicated environment. A simpler example is welcome in comments.

Create tables:

- Create table
create table TABLE_SMALL
(
ID NUMBER (13) not null ,
D_BEG DATE not null ,
D_END DATE not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
- Create / Recreate primary, unique and foreign key constraints
alter table TABLE_SMALL
add constraint PK_SMALL primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
- Create / Recreate indexes
create index IX_SMALL on TABLE_SMALL (D_BEG, D_END)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

- Create table
create table TABLE_BIG
(
ID NUMBER (13) not null ,
D DATE not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
- Create / Recreate primary, unique and foreign key constraints
alter table TABLE_BIG
add constraint PK_BIG primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
- Create / Recreate indexes
create index IX_BIG on TABLE_BIG (D)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
); * This source code was highlighted with Source Code Highlighter .

Fill in the table

insert into table_big t
(
SELECT rownum,
TO_DATE ( '01 .01.2000 ' , ' dd.mm.yyyy ' ) + (rownum - 1) / 24 AS thour
FROM dual
CONNECT BY LEVEL <= (TO_DATE ( '01 .03.2000 ' , ' dd.mm.yyyy ' ) - TO_DATE ( '01 .01.2000' , 'dd.mm.yyyy' )) * 24
);

insert into table_small
values (1, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;

insert into table_small
values (2, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;

insert into table_small
values (3, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;

insert into table_small
values (4, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;

insert into table_small
values (5, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;

insert into table_small
values (6, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;

insert into table_small
values (7, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;

insert into table_small
values (8, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;

insert into table_small
values (9, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;

insert into table_small
values (10, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;

insert into table_small
values (11, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;

insert into table_small
values (12, TO_DATE ( '01 .01.2000 01:00 ' , ' dd.mm.yyyy HH24: MI ' ), TO_DATE ( '02 .01.2000 01:00' , 'dd.mm.yyyy HH24: MI' )) ;
* This source code was highlighted with Source Code Highlighter .

Creating Types and Functions

create or replace type t_data as object
(
id number (13),
d date
);

create or replace type t_data_table as table of t_data;

create or replace function expand_small return t_data_table
pipelined
is
begin
FOR v IN (
SELECT
s.id AS id,
hours.thour AS tdate
FROM
(
SELECT TO_DATE ( '01 .01.2000 ' , ' dd.mm.yyyy ' ) + (rownum - 1) / 24 AS thour
FROM dual
CONNECT BY LEVEL <= 10 * 24 - 10 days
) hours
INNER JOIN table_small s ON s.d_beg BETWEEN TO_DATE ( '01 .01.2000 ' , ' dd.mm.yyyy ' ) AND TO_DATE ( '10 .01.2000' , 'dd.mm.yyyy' )
OR s.d_beg BETWEEN TO_DATE ( '01 .01.2000 ' , ' dd.mm.yyyy ' ) AND TO_DATE ( '10 .01.2000' , 'dd.mm.yyyy' )
OR s.d_beg <TO_DATE ( '01 .01.2000 ' , ' dd.mm.yyyy ' ) AND s.d_end> TO_DATE ( '10 .01.2000' , 'dd.mm.yyyy' )
)
LOOP
PIPE ROW (t_data (v.id, v.tdate));
END LOOP;
RETURN ;
end expand_small;

create or replace function expand_big return t_data_table
pipelined
is
begin
FOR v IN (
SELECT
b.id
bd as tdate
FROM table_big b
WHERE
bd BETWEEN TO_DATE ( '01 .01.2000 ' , ' dd.mm.yyyy ' ) AND TO_DATE ( '10 .01.2000' , 'dd.mm.yyyy' )
)
LOOP
PIPE ROW (t_data (v.id, TO_DATE (v.tdate)));
END LOOP;
RETURN ;
end expand_big; * This source code was highlighted with Source Code Highlighter .

We are checking.
Normal behavior, which is often laid on.

SQL >
SQL > select COUNT (*) from table_big
2 where rownum <10;

COUNT (*)
-------- -
9

Executed in 0.015 seconds

SQL >
SQL > select COUNT (*) from table_big
2 where rownum <200;

COUNT (*)
-------- -
199

Executed in 0.016 seconds
* This source code was highlighted with Source Code Highlighter .

COUNT (*) costs to exclude the time required for data transfer to the client and for drawing.

The time to receive the first 10 lines and the first 200 lines differs slightly. And most of these requests.

But sometimes it happens

SQL >
SQL > select COUNT (*) from table (expand_big) t
2 where t.id not in
3 (
4 SELECT t1.id from table (expand_small) t1
five )
6 and rownum <10;

COUNT (*)
-------- -
9

Executed in 0.578 seconds

SQL >
SQL > select COUNT (*) from table (expand_big) t
2 where t.id not in
3 (
4 SELECT t1.id from table (expand_small) t1
five )
6 and rownum <200;

COUNT (*)
-------- -
199

Executed in 12.063 seconds . Source code highlighter .

The time is 24 times different. And this is not an exceptional case. This often happens with complex queries.