correlation test

correlation.sql

SELECT banner FROM v$version;

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

DROP TABLE IF EXISTS tst_correlation;

create table tst_correlation (
	id int generated always as IDENTITY (START WITH 1 INCREMENT BY 1 CACHE 200),
	regtime timestamp not null,
	value varchar(4000) not NULL
);

ALTER TABLE tst_correlation ADD CONSTRAINT pk_correlation PRIMARY KEY (id);

create index idx_tst_correlation on tst_correlation (regtime);

/*
Тут заполняем таблицу. Организуем 10 потоков и вставляем 100 тыс записей (по 10 тыс записей каждым потоком).
Вставляем одиночными инсертами, после каждого инсерта коммит и sleep на 10мс, чтобы имитировать работу OLTP-системы.
*/

BEGIN
	dbms_stats.gather_table_stats('portnov', 'tst_correlation');
	dbms_stats.gather_index_stats('portnov', 'idx_tst_correlation');
END;

SELECT index_name, blevel, leaf_blocks, clustering_factor
FROM user_indexes
WHERE table_name = 'TST_CORRELATION';

/*
INDEX_NAME         |BLEVEL|LEAF_BLOCKS|CLUSTERING_FACTOR|
-------------------+------+-----------+-----------------+
PK_CORRELATION     |     1|        267|            92545|
IDX_TST_CORRELATION|     1|        423|            87914|
*/

explain plan for
SELECT id, value
FROM TST_CORRELATION 
WHERE id BETWEEN 10000 AND 20000
ORDER BY id;

select * from table(dbms_xplan.display);

/*
PLAN_TABLE_OUTPUT                                                                             |
----------------------------------------------------------------------------------------------+
Plan hash value: 37099169                                                                     |
                                                                                              |
----------------------------------------------------------------------------------------------|
| Id  | Operation          | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     ||
----------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT   |                 | 10002 |   410K|       |   350   (1)| 00:00:01 ||
|   1 |  SORT ORDER BY     |                 | 10002 |   410K|   520K|   350   (1)| 00:00:01 ||
|*  2 |   TABLE ACCESS FULL| TST_CORRELATION | 10002 |   410K|       |   239   (1)| 00:00:01 ||
----------------------------------------------------------------------------------------------|
                                                                                              |
Predicate Information (identified by operation id):                                           |
---------------------------------------------------                                           |
                                                                                              |
   2 - filter("ID"<=20000 AND "ID">=10000)                                                    |
*/

-- Время выполнения: 7ms

------------------------------------------------------

explain plan for
SELECT /*+ index (tst_correlation pk_correlation) */  id, value
FROM TST_CORRELATION 
WHERE id BETWEEN 10000 AND 20000
ORDER BY id;

select * from table(dbms_xplan.display);

/*
PLAN_TABLE_OUTPUT                                                                              |
-----------------------------------------------------------------------------------------------+
Plan hash value: 3886292390                                                                    |
                                                                                               |
-----------------------------------------------------------------------------------------------|
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     ||
-----------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT            |                 | 10002 |   410K|  9287   (1)| 00:00:01 ||
|   1 |  TABLE ACCESS BY INDEX ROWID| TST_CORRELATION | 10002 |   410K|  9287   (1)| 00:00:01 ||
|*  2 |   INDEX RANGE SCAN          | PK_CORRELATION  | 10002 |       |    28   (0)| 00:00:01 ||
-----------------------------------------------------------------------------------------------|
                                                                                               |
Predicate Information (identified by operation id):                                            |
---------------------------------------------------                                            |
                                                                                               |
   2 - access("ID">=10000 AND "ID"<=20000)                                                     |
*/

-- Время выполнения: 1ms