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 .
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.
SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON.d = t2.id * This is a code highlighted with the Source Code Highlighter .
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 .
- 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 .
- 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 .
- 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 .
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 .
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 .
- 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 .
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 .
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 .
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 .
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 .
Source: https://habr.com/ru/post/37125/