我什么时候需要重建索引。
我们在数据仓库位图索引中使用。如果我截断表并且没有行,然后插入新行,据我所知应该重建索引,因为这是有道理的。
当更新或插入或删除新行时总是如此,无论是使用单个语句还是使用合并语句然后 Oracle 自动更新所有类型的索引?
我什么时候需要重建索引。
我们在数据仓库位图索引中使用。如果我截断表并且没有行,然后插入新行,据我所知应该重建索引,因为这是有道理的。
当更新或插入或删除新行时总是如此,无论是使用单个语句还是使用合并语句然后 Oracle 自动更新所有类型的索引?
我们有一个主表,它通过许多列来驱动我们产品中的大部分工作。
索引 1 的键包括:A 列、B 列、C 列、D 列
索引 2 的键包括:Column D、Column A、Column B
包含几乎相同,除了索引 2 将列 C 作为包含而索引 1 将其包含在键中。
Index 2 会成为放弃支持 Index 1 的候选者吗?
我有一个 jump\terminal 服务器来管理 100 个 SQL Server,并且想使用中央管理服务器评估我在所有服务器上本地创建的策略,但没有:
我需要它与 Powershell 或 T-SQL 一起运行,这并不重要。我试图通过为包含主机名的每一行遍历 CSV 文件或通过查询msdb.dbo.sysmanagement_shared_registered_servers
系统表来实现这一点,但到目前为止还没有成功。有什么办法可以完成这个壮举?
我计划将我的数据库从本地迁移到 AWS RDS for Oracle。但是,我不确定 Amazon RDS 是否支持 Automatic Fragment Advisor 或 Oracle 的 Automatic Segment Space Management 功能等工具来控制碎片问题。我曾尝试在 AWS 文档和谷歌上进行搜索,但没有找到任何官方信息来证实这一点。
我有一张这样的桌子:
CREATE TABLE Sessions (
SessionId int(11) NOT NULL AUTO_INCREMENT,
ExternalId int(11) NOT NULL,
Active bit(1) NOT NULL DEFAULT b'0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
ALTER TABLE Sessions
ADD PRIMARY KEY (SessionId),
ADD KEY ExternalId (ExternalId),
ADD KEY Active (Active);
该表包含超过 4000 万行,只有 2k 行设置为活动行,并且每个 ExternalId 仅 3 行。
我想获得所有活动会话以获得精确的ExternalId
. 所以,我基本上做了:
SELECT * FROM Sessions WHERE Active = 1 AND ExternalId = myId;
但是,这需要很多时间(超过 3 秒)。经过一番调查,我发现这个更快:
SELECT * FROM (SELECT * FROM Sessions WHERE Active = 1) x WHERE x.ExternalId = myId;
这样,大约需要 0.005 秒。
我想知道为什么制作临时表比使用 2 个参数进行基本 where 检查更快?
我有一张小桌子,只有几行
create table dbo.p(i int);
insert dbo.p(i)
values (1), (2), (3), (4), (4);
我创建了统计数据;没有索引,只有统计数据。
create statistics p_c on dbo.p(i) with fullscan;
我正在调查各种事情,所以我尝试将 ROWCOUNT 设置为 bigint 的上限。
update statistics dbo.p p_c with rowcount = 9223372036854775807;
此操作失败并显示错误消息
消息 3739,级别 11,状态 3,第 346 行
无法更新索引“p_c”,因为它不是统计信息集合。
ROWCOUNT = 1;
尽管成功了,但它也失败了update statistics dbo.p p_c
。
该错误不在文档中。我在网上找不到任何相关的内容。
与普通的表统计数据相比,什么是统计数据收集?为什么设置 ROWCOUNT 可能会在这里失败?
SQL Server 2017 (RTM-CU31-GDR) (KB5021126)
MySQL 8.0.33
我有一个存储函数来计算给定日期一系列事件中所有时间间隔的总和。此函数使用窗口函数 LEAD(...) OVER() 来确定一个事件结束与下一个事件开始之间的间隔。
如果我多次运行查询本身,结果总是一样的。如果使用该函数,第一次执行有正确的值,其余返回NULL。
为什么???
编辑:
我已经更正了下面块中代码中的一些错误并制作了一个Fiddle。
现在我的问题变成了:“为什么它在 Fiddle 中可以正常工作,但在我的机器上却不能?什么设置会影响它?”
编辑 2:
在两个函数调用之间添加FLUSH TABLES events;
可以解决我服务器上的问题。再一次,为什么会这样?
架构:
CREATE TABLE `events` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`start` datetime DEFAULT NULL,
`end` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `start` (`start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `events` (`id`, `start`, `end`) VALUES
(1, '2023-02-06 08:14:00', '2023-02-06 10:30:00'),
(2, '2023-02-06 11:57:00', '2023-02-06 12:25:00'),
(3, '2023-02-06 14:00:00', '2023-02-06 14:15:00'),
(4, '2023-02-06 15:00:00', '2023-02-06 16:49:00');
CREATE FUNCTION fx_timegap (dt DATE)
RETURNS DECIMAL(4,2) NOT DETERMINISTIC READS SQL DATA
RETURN (
WITH
C1 AS (
SELECT
(UNIX_TIMESTAMP(LEAD(start) OVER (ORDER BY start ASC)) - UNIX_TIMESTAMP(end)) / 3600 AS gap
FROM events
WHERE DATE(start) = dt
)
SELECT SUM(gap)
FROM C1
);
这有效:
WITH
C1 AS (
SELECT
(UNIX_TIMESTAMP(LEAD(start) OVER (ORDER BY start ASC)) - UNIX_TIMESTAMP(end)) / 3600 AS gap
FROM events
WHERE DATE(start) = '2023-02-06'
)
SELECT SUM(gap)
FROM C1;
>>> 3.7833
WITH
C1 AS (
SELECT
(UNIX_TIMESTAMP(LEAD(start) OVER (ORDER BY start ASC)) - UNIX_TIMESTAMP(end)) / 3600 AS gap
FROM events
WHERE DATE(start) = '2023-02-06'
)
SELECT SUM(gap)
FROM C1;
>>> 3.7833
这不会:
SELECT fx_timegap('2023-02-06');
>>> 3.78
SELECT fx_timegap('2023-02-06');
>>> NULL
对于设置为的表,(fillfactor = 75)
我试图确保每个数据页只能有 3 行。
select ((8192 * 0.75 - 24) / 3)- 4 - 24; -- returns 2012.
前 24 位是页眉元字节。
最后 24 是 t_hoff(每行的行元字节)
最后 4 是每个插入行的行指针。
2012 % 8 = 4。所以我想确保一页只有 3 行我只能使用 2008 字节?
CREATE TABLE hot (hotid int,s char(2000))WITH (fillfactor = 75,autovacuum_enabled = OFF);
INSERT INTO hot VALUES (1, 'A');
SELECT
lp,lp_off,lp_len,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2
FROM
heap_page_items (get_raw_page ('hot', 0));
回报
lp | lp_off | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+--------+------------------+-------------
1 | 6160 | 2032 | 24 | (0,1) | 0000100000000010 | 2
(1 row)
我对为什么lp_len = 2032的猜测。2032 = 8 + 2000 + 24。
第一个 4 字节 int 将其填充到 8 字节。2000 是列s
,最后 24 个字节是行元页字节。
对于下表,我预计lp_len
是 2032。但事实并非如此。我错过了什么?
CREATE TABLE hot_test (
hotid int, hotid1 int,s char(2000)
)WITH (fillfactor = 75,autovacuum_enabled = OFF);
INSERT INTO hot_test VALUES (1, 1, 'A');
SELECT
lp,lp_off,lp_len,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2
FROM
heap_page_items (get_raw_page ('hot_test', 0));
回报
lp | lp_off | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+--------+------------------+-------------
1 | 8128 | 59 | 24 | (0,1) | 0000100000000010 | 3
(1 row)
更新:
drop table hot_test;
CREATE TABLE hot_test (
hotid int, hotid1 int,s char(1999)
)WITH (fillfactor = 75,autovacuum_enabled = OFF);
ALTER TABLE hot_test ALTER s SET STORAGE PLAIN;
INSERT INTO hot_test VALUES (1, 1, 'A');
SELECT
lp,lp_off,lp_len,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2
FROM
heap_page_items (get_raw_page ('hot_test', 0));
回报
lp | lp_off | lp_len | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+--------+------------------+-------------
1 | 6152 | 2035 | 24 | (0,1) | 0000100000000010 | 3
(1 row)
2035 = 4 + 4 + 1999 + 4 + 24 所以我猜 varlena 文本需要 4 个字节的填充?
drop table paddling;
CREATE TABLE paddling (
hotid int, hotid1 int,s char(127)
)WITH (fillfactor = 75,autovacuum_enabled = OFF);
ALTER TABLE paddling ALTER s SET STORAGE PLAIN;
INSERT INTO paddling VALUES (1, 1, 'ASDFGHJKLQWERTYUI');
SELECT
lp,lp_off,lp_len,lp_len - 24 - 8,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2
FROM
heap_page_items (get_raw_page ('paddling', 0));
回报
lp | lp_off | lp_len | ?column? | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+----------+--------+--------+------------------+-------------
1 | 8024 | 163 | 131 | 24 | (0,1) | 0000100000000010 | 3
(1 row)
drop table paddling;
CREATE TABLE paddling (
hotid int, hotid1 int,s char(126)
)WITH (fillfactor = 75,autovacuum_enabled = OFF);
ALTER TABLE paddling ALTER s SET STORAGE PLAIN;
INSERT INTO paddling VALUES (1, 1, 'ASDFGHJKLQWERTYUI');
SELECT
lp,lp_off,lp_len,lp_len - 24 - 8,t_hoff,t_ctid,t_infomask::bit(16),t_infomask2
FROM
heap_page_items (get_raw_page ('paddling', 0));
回报
lp | lp_off | lp_len | ?column? | t_hoff | t_ctid | t_infomask | t_infomask2
----+--------+--------+----------+--------+--------+------------------+-------------
1 | 8032 | 159 | 127 | 24 | (0,1) | 0000100000000010 | 3
(1 row)
似乎对于char(x)
,如果 x <= 126,填充是一个字节。x > 126 则填充为 4 字节。经过测试,也适用于text
数据类型。
我在 Postgresql-11 数据库中有数百个具有相同结构的表,我需要对每个表执行相同的统计。
由于真正的统计逻辑非常复杂,为了简化我在这里的问题,假设我需要计算每个的 MAX/MIN/MEAN/STDDEV。
我不想为每个单独的表一个一个地编辑单独的 SQL,因为如果是这样,我将编写数百行非常相似的 SQL,唯一不同的是表名。
所以我在一个函数中使用动态 SQL 一次对一个表进行计算,如下所示:
DROP FUNCTION IF EXISTS get_features_for;
CREATE OR REPLACE FUNCTION get_features_for(
IN table_name VARCHAR,
OUT result_ RECORD ) LANGUAGE 'plpgsql'
AS $func$
DECLARE sql_string VARCHAR :=
format('
SELECT ''%s'' table_name, -- just for validating
MAX(feature) max_feature,
MIN(feature) min_feature,
AVG(feature) avg_feature,
STDDEV(feature) std_feature
FROM "%s";',
table_name, table_name );
BEGIN
EXECUTE sql_string INTO result_;
END
$func$;
然后在查询中用数据库中的每个表名调用它,如下所示:
SELECT tbs."table_name", get_features_for( tbs."table_name" )
FROM information_schema."tables" tbs
WHERE tbs.table_schema = 'public' AND tbs.table_type = 'BASE TABLE'
AND tbs."table_name" LIKE 'analy%'
ORDER BY tbs."table_name";
它有效,但结果类似于以下内容:
"table_name", "get_features_for"
“analy_001”,“(analy_001,-3,2,0,2.5)”
“analy_002”,“(analy_002,-3,2,-1,2)”
“analy_003”,“(analy_003,-3,2,0,2)”
你可以看到所有的结果列都被包装成一个字符串列,让我不能方便地使用它们。
我希望它们类似于:
表名、最大特征、最小特征、平均特征、标准特征
"analy_001", "analy_001", -3, 2, 0, 2.5
"analy_002", "analy_002", -3, 2, -1, 2
"analy_003", "analy_003", -3, 2, 0, 2
我应该怎么得到它?
顺便说一句,我正在使用 pgAdmin。
谢谢!!!