Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What are the Storage Index features and BRIN indexes in Oracle and PostgreSQL

2025-01-21 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 252

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report