portnov

create table tst_tree (
	node_id int primary key,
	parent_id int null references tst_tree (node_id)
);

create index idx_tst_tree_parent on tst_tree (parent_id);

drop procedure fill_tree;
create or replace procedure fill_tree(avg_children int, max_levels int, max_items int) as $$
declare

portnov

create table tst_t1 (
	id int not null primary key,
	name text
);

create table tst_a (
	id int not null primary key,
	t1id int references tst_t1 on delete cascade,
	name text
);

portnov

explain (analyze,buffers)
select count(1)
from bigtable

QUERY PLAN                                                                                                                                                            |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Finalize Aggregate  (cost=254096.62..254096.65 rows=1 width=8) (actual time=9956.068..9976.581 rows=1 loops=1)                                                        |
  Buffers: shared hit=158026 read=25297 dirtied=1871                                                                                                                  |
  I/O Timings: shared/local read=23218.641                                                                                                                            |
  ->  Gather  (cost=254096.38..254096.61 rows=2 width=8) (actual time=9956.062..9976.576 rows=3 loops=1)                       

portnov

SELECT banner FROM v$version;

/*
BANNER                                                          |
----------------------------------------------------------------+
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release|
*/

DROP TABLE IF EXISTS tst_correlation;

portnov

create table tst_bool (
	id bigint primary key generated always as identity,
	value numeric,
	isgood boolean
);

insert into tst_bool (value, isgood)
select random()*7, round(random()*4) = 1
from generate_series(1, 1000*1000);

portnov

drop table if exists tst_stats;

create table tst_stats (
	id bigserial primary key,
	value text
);

insert into tst_stats (value)
select 'value ' || (i*i)

portnov

CREATE TABLE tstc (value varchar2(4000)) COMPRESS;

INSERT INTO tstc (value)
SELECT substr(rpad(to_char(LEVEL), 3999, to_char(LEVEL)), 0, 4000)
FROM dual
CONNECT BY LEVEL <= 1000*1000;


select segment_name,segment_type, sum(bytes/1024/1024/1024) GB
 from dba_segments

portnov

QUERY PLAN                                                                                                                                    |
----------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop  (cost=0.72..3031.12 rows=6190 width=38) (actual time=0.184..3.439 rows=6038 loops=1)                                             |
  Buffers: shared hit=58 read=22                                                                                                              |
  ->  Index Scan using idx_tst_entry_t on tst_entry w  (cost=0.42..2098.59 rows=6190 width=26) (actual time=0.143..1.387 rows=6038 loops=1)   |
        Index Cond: (parentid = 3)                                                                                                            |
        Buffers: shared hit=49 read=22                                                                                                        |
  ->  Memoize  (

portnov

drop table if exists tst_entry;
drop table if exists tst_child;
drop table if exists tst_parent;

CREATE TABLE tst_parent (
	id int NOT null primary key,
	title text NULL
);

insert into tst_parent

portnov

drop table PartTest1;
drop table PartTest2;

create table PartTest1 (
	id bigint not null primary key,
	value1 text
) partition by range (id);

create table PartTest2 (
	id bigint not null primary key,