SQL Myths

Many developers with considerable development experience in any imperative languages ​​firmly believe that SQL is the same. Only the syntax is different. After writing a few requests to pull data into your application server, myths about the database begin to be born.


I have written and verified examples for Orakla, it’s just closer to me.
But the same is valid for any DBMS.

UPD: They asked to add a description of what and why. Added by .
')
1. The magic parameter is somewhere deep in the server configurations fast = true.
2. The speed of the query is estimated by how quickly the first 20 lines were printed.
3. You can take and optimize any single query, or even a part of the query, without touching anything other than it.
4. To understand how the request works and to improve it, you can simply look at the request code.
5. In temporary tables, neither the Primary Key nor the indices need be made.
6. Foreign key = Index on the field in the child table
7. A request with a lower cost must work faster.
8. The absolute identity of the following code options.

declare
summ number (10);
i number (10);
cursor c is select a from test_pk;
begin
summ: = 0;
open c;

loop
fetch c into i;
exit when c% notfound;

summ: = summ + i;
end loop;

close c;

dbms_output.put_line (summ);

end ;

and

select SUM (a) from table1

After all, the main explanation for both there and there is simply to summarize what difference it makes.

9. Any reference to the index is better than without it.
9a. And the associated Request is slow, you need to add an index.
In the Full Table Scan query, there are 100 entries, of which 95 are needed, and a number of Full Index Scan, along with a million entries, of which 95 are also needed. Everyone will 'optimize' the Full Table Scan
10. The speed of the connection of the tables strongly depends on the types and number of fields by which we connect them.
10a. The speed depends on the number of tables involved in the query.
11. In requests of the form

SELECT *
FROM table t
Where ta = 123
AND tb = 321

There will always be two separate indices for fields a and b.