What are the Storage Index features and BRIN indexes in Oracle and PostgreSQL
This article introduces the knowledge of "what are the Storage Index features and BRIN indexes in Oracle and PostgreSQL?". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
Exadata's Storage Index won't talk about it, because it's not a database-wide solution, and Zone Maps, the new feature in Oracle Database 12.1.0.2, made me very excited, but I was disappointed to find that it could only be enabled in Oracle running on Exadata.
In the same way of thinking, when the minimum and maximum values of a column in a certain range of data blocks are stored in a class index structure, when the filter conditions of the column are included in the query statement, the data blocks that definitely do not contain the qualified column values will be automatically ignored, thus reducing the amount of IO reading and improving the query speed.
The following examples are borrowed from Pg wiki to illustrate the power of BRIN indexes.
-- create a test table orders
CREATE TABLE orders (
Id int
Order_date timestamptz
Item text)
-- insert a large number of records into the table, and Pg's function generate_series is very useful.
INSERT INTO orders (order_date, item)
SELECT x, 'dfiojdso'
FROM generate_series ('2000-01-01 00 seconds'::interval 2015-03-01 00 seconds'::interval)
At present, the watch has the size of 13GB, which can be regarded as a large watch.
#\ dt+ orders
List of relations
Schema | Name | Type | Owner | Size | Description
-+-
Public | orders | table | thom | 13 GB |
(1 row)
-- query the records within two days by full table scan. Note that it is expected to take 30 seconds here, which is a Pg database stored on SSD, so the speed is ideal.
# EXPLAIN ANALYSE SELECT count (*) FROM orders WHERE order_date BETWEEN '2012-01-04 09 and' 2014-01-04 14 and 30
QUERY PLAN
- -
Aggregate (cost=5425021.80..5425021.81 rows=1 width=0) (actual time=30172.428..30172.429 rows=1 loops=1)
-> Seq Scan on orders (cost=0.00..5347754.00 rows=30907121 width=0) (actual time=6050.015..28552.976 rows=31589101 loops=1)
Filter: (order_date > = '2012-01-04 09:00:00+00'::timestamp with time zone) AND (order_date Bitmap Heap Scan on orders (cost=316863.99..2539600.80 rows=30907121 width=0) (actual time=36.366..4686.634 rows=31589101 loops=1))
Recheck Cond: (order_date > = '2012-01-04 09:00:00+00'::timestamp with time zone) AND (order_date Bitmap Index Scan on idx_order_date_brin (cost=0.00..309137.21 rows=30907121 width=0) (actual time=35.567..35.567 rows=2323200 loops=1))
Index Cond: (order_date > = '2012-01-04 09:00:00+00'::timestamp with time zone) AND (order_date
