⬆️ ⬇️

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.



Source: https://habr.com/ru/post/37125/



All Articles