In the [docs](https://learn.microsoft.com/en-us/sql/relational-databases/reading-pages?view=sql-server-ver16) it says we can share index scans (Advanced Scanning). Now [here](https://www.mssqltips.com/sqlservertip/4472/sql-server-enterprise-advanced-scan-aka-merrygoround-scan/) they used wait stats and statistics and i did not find an extended events session that could directly identify it. The question is - how can i know that an advanced scan has been used? I know about ordered false property, but that just says that it did not use the order in the index key?

So as far as I understand, with a regular index scan (not index-only scan), Postgres will read the index and immediately fetch the referenced rows from the heap. For a bitmap index scan + bitmap heap scan (which I will just call bitmap scan, taken together), Postgres reads the index and collects the list of relevant heap pages and tuple offsets in a bitmap. Then it reads all the relevant heap pages in order. The advantage for a bitmap scan is: - every heap page is only fetched once, while an index scan may need to read a heap page multiple times - heap pages are read in physical order, which is faster than random access, especially on magnetic disks. With that, I would expect a bitmap scan to always be faster than a normal index scan, except maybe if a very small number of rows is needed, or if the bitmap doesn't fit into working memory. But now I'm optimizing a query that uses bitmap scans by default, but switches to regular index scans when I set random_page_cost to 1 (i.e, tell postgres that random page access costs the same as sequential page access). The db runs on an SSD, so random_page_cost==1 is warranted. But the regular index scan is almost twice as fast as the bitmap index scan, which I didn't expect. This is a pair of tables with timeseries data for multiple sensors. Selecting all the data for one sensor takes ± 20 seconds with an index scan, and ± 40 seconds with a bitmap scan. That is for reading 75k rows from tables containing either 50M or 15M rows in total. So when and why is a regular index scan ever faster than a bitmap scan? EDIT: EXPLAIN plans ----------- By request, here are EXPLAIN (ANALYZE,BUFFERS) plans with track_io_timing enabled. However I am not too interested in the specific performance of this query, this question is about bitmap scans vs index scans in general. There are two tables, both with the same columns: timestamp timestamp with timezone PRIMARY KEY station_id character varying(50) PRIMARY KEY value numeric(10) NOT NULL Both have an additional index on station_id. The query: SELECT * FROM -- This first part doesn't do anything in this example, -- but this query was reduced from a more complex one -- with parameters. This part is needed to make Postgres -- choose a hash join in both cases. If you forcibly disable -- merge join and nested loop join, running just the second -- subquery should give similar results. (select * from unnest(array['station_1']) stations(station_id) where station_id = 'station_1' ) as stations join lateral ( SELECT * FROM series_measurements_gauge AS gauge INNER JOIN series_measurements_radar AS radar USING (timestamp, station_id) WHERE station_id = stations.station_id AND (gauge.value > 0 OR radar.value > 0) AND gauge.value != 'NaN'::NUMERIC AND radar.value != 'NaN'::NUMERIC order by timestamp desc ) windowed using (station_id) The explain plan with SET random_page_cost TO 1.0;: "Nested Loop (cost=108636.01..108672.92 rows=1638 width=46) (actual time=21093.479..21095.271 rows=8063 loops=1)" " Buffers: shared hit=2440 read=98925 written=52" " I/O Timings: read=20491.173 write=0.407" " -> Function Scan on unnest stations (cost=0.00..0.05 rows=1 width=32) (actual time=0.007..0.010 rows=1 loops=1)" " Filter: (station_id = 'station_1'::text)" " -> Sort (cost=108636.01..108640.11 rows=1638 width=27) (actual time=21093.469..21094.032 rows=8063 loops=1)" " Sort Key: gauge.""timestamp"" DESC" " Sort Method: quicksort Memory: 822kB" " Buffers: shared hit=2440 read=98925 written=52" " I/O Timings: read=20491.173 write=0.407" " -> Result (cost=34932.45..108548.56 rows=1638 width=27) (actual time=3243.803..21086.255 rows=8063 loops=1)" " One-Time Filter: (stations.station_id = 'station_1'::text)" " Buffers: shared hit=2440 read=98925 written=52" " I/O Timings: read=20491.173 write=0.407" " -> Hash Join (cost=34932.45..108548.56 rows=1638 width=27) (actual time=3243.801..21083.338 rows=8063 loops=1)" " Hash Cond: (gauge.""timestamp"" = radar.""timestamp"")" " Join Filter: ((gauge.value > '0'::numeric) OR (radar.value > '0'::numeric))" " Rows Removed by Join Filter: 69061" " Buffers: shared hit=2440 read=98925 written=52" " I/O Timings: read=20491.173 write=0.407" " -> Index Scan using series_measurement_gauge_station_id_idx on series_measurements_gauge gauge (cost=0.56..73416.75 rows=76164 width=24) (actual time=0.457..17735.138 rows=77253 loops=1)" " Index Cond: ((station_id)::text = 'station_1'::text)" " Filter: (value 'NaN'::numeric)" " Rows Removed by Filter: 92" " Buffers: shared hit=428 read=33054" " I/O Timings: read=17474.535" " -> Hash (cost=34430.41..34430.41 rows=40118 width=24) (actual time=3242.824..3242.825 rows=77216 loops=1)" " Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 5247kB" " Buffers: shared hit=2012 read=65871 written=52" " I/O Timings: read=3016.639 write=0.407" " -> Index Scan using series_measurement_radar_station_id_idx on series_measurements_radar radar (cost=0.43..34430.41 rows=40118 width=24) (actual time=2.383..3210.465 rows=77216 loops=1)" " Index Cond: ((station_id)::text = 'station_1'::text)" " Filter: (value 'NaN'::numeric)" " Rows Removed by Filter: 10" " Buffers: shared hit=2012 read=65871 written=52" " I/O Timings: read=3016.639 write=0.407" "Planning:" " Buffers: shared hit=48" "Planning Time: 0.447 ms" "Execution Time: 21095.783 ms" The explain plan with SET random_page_cost TO 4.0;: "Nested Loop (cost=272182.23..272219.14 rows=1638 width=46) (actual time=57709.314..57711.121 rows=8063 loops=1)" " Buffers: shared hit=1041 read=100324 written=1189" " I/O Timings: read=55283.990 write=16.970" " -> Function Scan on unnest stations (cost=0.00..0.05 rows=1 width=32) (actual time=0.007..0.010 rows=1 loops=1)" " Filter: (station_id = 'station_1'::text)" " -> Sort (cost=272182.23..272186.32 rows=1638 width=27) (actual time=57709.303..57709.851 rows=8063 loops=1)" " Sort Key: gauge.""timestamp"" DESC" " Sort Method: quicksort Memory: 822kB" " Buffers: shared hit=1041 read=100324 written=1189" " I/O Timings: read=55283.990 write=16.970" " -> Result (cost=80099.49..272094.78 rows=1638 width=27) (actual time=38642.951..57701.677 rows=8063 loops=1)" " One-Time Filter: (stations.station_id = 'station_1'::text)" " Buffers: shared hit=1041 read=100324 written=1189" " I/O Timings: read=55283.990 write=16.970" " -> Hash Join (cost=80099.49..272094.78 rows=1638 width=27) (actual time=38642.950..57698.167 rows=8063 loops=1)" " Hash Cond: (gauge.""timestamp"" = radar.""timestamp"")" " Join Filter: ((gauge.value > '0'::numeric) OR (radar.value > '0'::numeric))" " Rows Removed by Join Filter: 69061" " Buffers: shared hit=1041 read=100324 written=1189" " I/O Timings: read=55283.990 write=16.970" " -> Bitmap Heap Scan on series_measurements_gauge gauge (cost=1053.96..192849.32 rows=76164 width=24) (actual time=11.281..18961.484 rows=77253 loops=1)" " Recheck Cond: ((station_id)::text = 'station_1'::text)" " Filter: (value 'NaN'::numeric)" " Rows Removed by Filter: 92" " Heap Blocks: exact=33368" " Buffers: shared hit=440 read=33041 written=1189" " I/O Timings: read=18250.155 write=16.970" " -> Bitmap Index Scan on series_measurement_gauge_station_id_idx (cost=0.00..1034.92 rows=79781 width=0) (actual time=5.150..5.151 rows=77392 loops=1)" " Index Cond: ((station_id)::text = 'station_1'::text)" " Buffers: shared hit=113" " -> Hash (cost=78544.05..78544.05 rows=40118 width=24) (actual time=38629.094..38629.096 rows=77217 loops=1)" " Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 5247kB" " Buffers: shared hit=601 read=67283" " I/O Timings: read=37033.835" " -> Bitmap Heap Scan on series_measurements_radar radar (cost=451.37..78544.05 rows=40118 width=24) (actual time=46.071..38527.975 rows=77217 loops=1)" " Recheck Cond: ((station_id)::text = 'station_1'::text)" " Filter: (value 'NaN'::numeric)" " Rows Removed by Filter: 10" " Heap Blocks: exact=67821" " Buffers: shared hit=601 read=67283" " I/O Timings: read=37033.835" " -> Bitmap Index Scan on series_measurement_radar_station_id_idx (cost=0.00..441.34 rows=40121 width=0) (actual time=31.258..31.258 rows=77227 loops=1)" " Index Cond: ((station_id)::text = 'station_1'::text)" " Buffers: shared hit=19 read=44" " I/O Timings: read=19.450" "Planning:" " Buffers: shared hit=22" "Planning Time: 0.393 ms" "Execution Time: 57712.895 ms" Environment ---------- After posting this question initially, I found out that this query is quite slow because it runs on Amazon RDS with 3000 IOPS and 125 MB/s throughput. When I run the same query on my local laptop, the query times drop from 20s/50s to 0.7s/1.4s (after a few repeats, so the data is mostly cached. The first (uncached) run took 16s instead of 0.7s). So RDS is an order of magnitude slower than my local machine, most likely due to the IOPS limit and limited caching. My own conclusions from the explain plans ------------ Both plans read about the same number of rows in their respective scans, and also the same number of blocks. The main difference is that one of the bitmap heap scans also writes 1189 blocks, and strangely enough the other bitmap heap scan doesn't even though it reads a lot more blocks. But one thousand is small compared to the 100k total blocks read, so I don't think that can explain a 2x slowdown. **Reiterating:** I am not so interested in optimizing this query itself, I want to understand the performance trade offs between index scans and bitmap scans and how a bitmap scan can be twice as slow as an index scan.

With SQL, I need to select rows which have a pattern like %hello% (i.e. hello) in a column, with potentially 100 millions of rows or more. In the answer [Infix search in MySQL with an index](https://stackoverflow.com/questions/76121624/infix-search-in-mysql-search-with-pattern-in-the-middle-with-an-index/76121736#76121736) , it is said that: * an index won't help * even an *FTS index* won't help either Question: **are there RDBMS (e.g. PostgreSQL) that have features that can speed up queries like** select * from t where key like "%hello%" **and avoid a full table scan?** With specific data structures (tries, etc.), or bigrams, trigrams, etc. TL;DR: I have a MySQL InnoDB like this: create table t (id int primary key auto_increment, key varchar(200), value varchar(200)); create index key_index on t(key) using BTREE; create index value_index on t(value) using BTREE; and I would like to do this without a full table scan: select * from t where key like "%hello%" Notes: * the strings are not words separated by spaces. Columns key and value have typically 10 to 50 characters, with no spaces. They can be abcdefhelloworldblablabla. * see also https://dba.stackexchange.com/questions/187658/what-is-it-called-when-you-search-the-middle-of-a-string-instead-of-the-beginnin

I'm building an app that lets the user export their data. When they click the Export button, we need to do a SELECT * WHERE user_id =, save it to a CSV file, and download it. There may be hundreds of thousands of entries. If the user edits their data while the export is in progress, the export can include the edits, or not. Either is fine, as long as it's a consistent snapshot taken at any point in time during the export. The question is how do I do it without killing performance for all other queries? I don't mind if the export takes longer. Can I tell the database to somehow run this query at a low priority? Assume a MySQL or Postgres database. The only solution I know is to set up a read replica just for exports. Exports can slow down the replica, but won't slow down the primary, thus ensuring fast performance for other queries. Is there a better solution to this problem?

I have a modestly big table of about a million rows. With static where clauses it filters down to 200,000 rows. With a typical user search criterion it delivers about 100 rows. This is for a web service hit millions of times per hour. I want to keep the database load as small as possible. So cannot afford an order by. But I need to be able to scroll through the result set, with OFFSET / LIMIT clauses. However, for the first time in my life, I notice that for this query the results come out in a different order every single time I run it (on a system with no other activity). I know that there is no guarantee of any particular ordering in SQL, but I am used to the order being at least stable when the criteria don't change and we only use OFFSET and LIMIT. First I thought it was the parallel query, but even after I turned that off, single threaded table scan Limit (cost=0.00..316982.62 rows=25 width=471) -> Seq Scan on mytable (cost=0.00..316982.62 rows=25 width=471) Filter: ... I suppose it's a bad idea that I don't use any indexes but only filters. Perhaps that's the anomaly here. But even so, what is the special optimization that PostgreSQL uses that makes it produce a different result all the time? And that seems to be the reason. But regardless, is there a way I can make sure whatever table scan uses the same whatever random but stable ordering from its table scan?

For a college project, I have to set up a RAC Instance and an application, I already managed to configure my RAC lab by using virtual machines by using [this video guide](https://www.youtube.com/watch?v=NnZEQ-aaGMM) , now I just need to program an application to connect to my RAC instance but... I'm not sure how to do it! I suppose I have to connect to the SCAN network? But I had configured it as a private network in each machine's network adapter... I didn't configure DNS (I was stuck for 4 days and the video guide didn't actually show how to configure it), so now I'm stuck trying to connect to this database from DBeaver before I program my node app. Here's some information regarding my lab. I have two instances: node1 and node. Service status:

$ /crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

Scan config?

$ ./srvctl config scan
SCAN name: node-scan, Network: 1
Subnet IPv4: 192.168.24.0/255.255.255.0/enp0s3, static
Subnet IPv6: 
SCAN 1 IPv4 VIP: 192.168.24.41
SCAN VIP is enabled.
SCAN 2 IPv4 VIP: 192.168.24.42
SCAN VIP is enabled.
SCAN 3 IPv4 VIP: 192.168.24.43
SCAN VIP is enabled.

Scan listener

$ ./srvctl config scan_listener
SCAN Listeners for network 1:
Registration invited nodes: 
Registration invited subnets: 
Endpoints: TCP:1521
SCAN Listener LISTENER_SCAN1 exists
SCAN Listener is enabled.
SCAN Listener LISTENER_SCAN2 exists
SCAN Listener is enabled.
SCAN Listener LISTENER_SCAN3 exists
SCAN Listener is enabled.

Since there is no DNS I had to configure /etc/host like so for both nodes.

# Default

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1     	localhost localhost.localdomain localhost6 localhost6.localdomain6
# Public
192.168.24.1 node1.localdomain node1
192.168.24.2 node2.localdomain node2
# Private
192.168.10.1 node-priv.localdomain node1-priv
192.168.10.2 node2-priv.localdomain node2-priv
# Virtual
192.168.24.31 node1-vip.localdomain node1-vip
192.168.24.32 node2-vip.localdomain node2-vip
# SCAN
192.168.24.41 node-scan.localdomain node-scan
192.168.24.42 node-scan.localdomain node-scan
192.168.24.43 node-scan.localdomain node-scan

I'm learning how column-oriented DBMS / "columnars" work for OLAP situations. Let's say we have a log of millions of transactions with 3 columns: timestamp, shop, product, and we want to know the products sold in the shop A in a certain time range: SELECT DISTINCT product FROM data WHERE timestamp BETWEEN 1600010000 AND 1602678400 AND shop = 'A' This will be stored like this (admittedly, this is more or less an abstraction): timestamp [1600000000, 1600000005, 1600000005, 1600000017, 1600000018, ... ] shop [A, A, B, D, C, ...] product [X153, S76D, TYA6, LKJ6, SDH7, ...] For this query: * I totally get how we can achieve fast lookup *by timestamp*, since this column is sorted: with 2 [dichotomic searches](https://en.wikipedia.org/wiki/Dichotomic_search) we can find the **index** for which timestamp=1600010000 and 1602678400. With **less than 30 read operations of a few bytes**, it's done, we have rowid_start, rowid_end (I don't know if it's still called rowid in the context of a columnar) that make the boundaries of this time-range. The key thing is that we *haven't* had to read megabytes of data, but just a few bytes. * **Question: then, how can a columnar filter by shop = 'A'? Do we have to read **each entry** of the column shop in the range rowid_start .. rowid_end to test if it's A or not?** This could potentially be hundreds of MB or GB of data. TL;DR: once we have filtered by one column, how can a columnar do a second-column-filtering, without doing a FULL SCAN?

At my work, I am often told that I shouldn't do a table scan because it could negatively impact the database and the app using the database. What exactly do they mean by "negatively impact"? One negative impact I can think of is that a table scan would use up a lot of the disk's time, blocking other requests from using the disk. Is there a way to do a table scan without any negative impacts? Sometimes I need to do a table scan for one off checks (for routine things, I would of course make an index).

I have two tables in my database. TableA : 1427877 rows TableB : 339939000 rows TableB has a foreignkey column in TableB (TableB.foreignId). It has Delete On Cascade ON. The problem is when I delete a row in TableA. DELETE TableA WHERE id = @id The cascade reference, produce an index scan on TableB (300Million rows). While exits a non-clustered index on foreignId column. I test with a procedure CREATE OR ALTER PROCEDURE #tempDelete(@id INT) AS DELETE TableA WHERE id = @id; After cleaning cache plan. The plan is the same, index scan. If I did DELETE TableB WHERE ForeignId = @id It use an index seek on 'indexB' Plan for both query I checked this post about missing index. I think the index are rigth. Also this post about correct data type. foreignId is Int, the same that use on my .Net client and also in the store procedure. How I could fix the Delete operation? The plan is same for production and development. Thanks, Every index on TableB have ForeignId as first column. Indexc is the only that has ForeignId as second column.

I received a task from one of my colleagues to check why he always receives high CPU load alerts. I don't have too much experience with databases and I need some clarifications. I created a script to capture the first 5 threads that are using most of the CPU when the alerts are triggered. The order is almost always the same, first three are db2agents and the other 2 are db2pfchr. From my knowledge, I found nothing wrong with the SQL statements so I checked for table scans and IREF. What I noticed immediately was the difference between rows read and rows selected which looks like this for example: Rows deleted = 0 Rows inserted = 0 Rows updated = 1329 Rows selected = 714 Rows read = 3903430587518 Rows written = 3471 First 3 biggest table scan values are 26586532, 15538513 and 942177. The first table has one index that was never used, the second table has 1 index that was used around 70k times and the third table has no index. So, my obvious guess is that indexes need to be added in order to avoid table scans Since I never added an index in my life and must read more about it, I needed to be sure about my assumptions and maybe there something else to check? Also, all values in the queries are "?"... hidden? Like UPDATE X SET VALUE_INT = ?, VALUE_STRING = ?, Y = CURRENT TIMESTAMP WHERE AB = ? This is an OLTP DB, and it's about DB2 V10.5 on RHEL IREF - Index Read Efficiency = ROWS READ/ROWS RETURNED

I have 2 tables: *mailings* and *form_responses*. The *form_responses* table has a foreign key with the *mailings* table, so a *mailing* has *one-to-many* *form_responses*. What I want to do is calculate *mailing* stats: per *mailing* get total *form_responses* and when the last *form_response* was received. I want to return the first 20 *mailings* that have last received a *form_response*. But when it has to query 50k+ rows, the query gets really slow. **mailings** (~100k rows) Column | Type | Modifiers --------------+-----------------------------+--------------------------------------------------- id | integer | pkey project_id | integer | not null deleted | boolean | active | boolean | created_at | timestamp with time zone | Indexes: "mailings_pkey" PRIMARY KEY, btree (id) "mailing_on_project_id_where_not_deleted_and_active" btree (project_id) (WHERE deleted IS NOT true AND active IS TRUE) + individual indexes on almost all columns **form_responses** (~6m rows) Column | Type | Modifiers --------------+-----------------------------+--------------------------------------------------- id | integer | pkey project_id | integer | not null mailing_id | integer | deleted | boolean | is_unanswered| boolean | created_at | timestamp with time zone | Indexes: "form_responses_pkey" PRIMARY KEY, btree (id) "form_responses_on_pid_cr_time_where_not_del_not_is_unans" btree (project_id, created_time) (WHERE deleted IS NOT true AND is_uanswered IS NOT TRUE) + individual indexes on almost all columns I want to get the mailing stats of a project (id=1), within a date range. I only want the stats of mailings that are not deleted and active and only count non deleted responses. So this gives me the following query EXPLAIN (ANALYZE, BUFFERS) SELECT count(DISTINCT form_responses.id) AS total, max(form_responses.created_time) AS form_response_last_received, (SELECT mailings.name FROM mailings WHERE id = form_responses.mailing_id) AS name, form_responses.mailing_id AS mailing_id FROM form_responses JOIN mailings ON mailings.id = form_responses.mailing_id WHERE form_responses.deleted IS NOT true AND form_responses.is_unanswered IS NOT true AND form_responses.created_time >= '2017-08-02 00:00:00' AND form_responses.created_time Sort (cost=308997.69..308998.19 rows=197 width=16) (actual time=7762.552..7762.557 rows=20 loops=1) Sort Key: (max(form_responses.created_time)) Sort Method: top-N heapsort Memory: 26kB Buffers: shared hit=74535 read=13513 dirtied=1 -> GroupAggregate (cost=307039.18..308992.45 rows=197 width=16) (actual time=7673.523..7762.278 rows=686 loops=1) Group Key: form_responses.mailing_id Buffers: shared hit=74535 read=13513 dirtied=1 -> Sort (cost=307039.18..307111.58 rows=28961 width=16) (actual time=7673.277..7702.783 rows=137542 loops=1) Sort Key: form_responses.mailing_id Sort Method: quicksort Memory: 12592kB Buffers: shared hit=71790 read=13512 dirtied=1 -> Hash Join (cost=7376.42..304892.90 rows=28961 width=16) (actual time=44.576..7609.818 rows=137542 loops=1) Hash Cond: (form_responses.mailing_id = mailings.id) Buffers: shared hit=71790 read=13512 dirtied=1 -> Bitmap Heap Scan on form_responses (cost=3815.27..299862.55 rows=134811 width=16) (actual time=41.439..7499.305 rows=137543 loops=1) Recheck Cond: ((project_id = 1) AND (created_time >= '2017-08-02 00:00:00+00'::timestamp with time zone) AND (created_time Bitmap Index Scan on form_responses_on_pid_cr_time_where_not_del_not_is_unans (cost=0.00..3781.57 rows=134811 width=0) (actual time=23.892..23.892 rows=137543 loops=1) Index Cond: ((project_id = 1) AND (created_time >= '2017-08-02 00:00:00+00'::timestamp with time zone) AND (created_time Hash (cost=3286.82..3286.82 rows=21946 width=4) (actual time=3.093..3.093 rows=2866 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 101kB Buffers: shared hit=758 dirtied=1 -> Bitmap Heap Scan on mailings (cost=874.50..3286.82 rows=21946 width=4) (actual time=0.403..2.145 rows=2866 loops=1) Recheck Cond: ((project_id = 1) AND (deleted IS NOT TRUE) AND (active IS TRUE)) Heap Blocks: exact=732 Buffers: shared hit=758 dirtied=1 -> Bitmap Index Scan on mailing_on_project_id_where_not_deleted_and_active (cost=0.00..869.01 rows=21946 width=0) (actual time=0.315..0.315 rows=2866 loops=1) Index Cond: (project_id = 1) Buffers: shared hit=26 SubPlan 1 -> Index Scan using mailings_pkey on mailings mailings_1 (cost=0.42..8.44 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=686) Index Cond: (id = form_responses.mailing_id) Buffers: shared hit=2745 read=1 Planning time: 0.865 ms Execution time: 7764.951 ms In this example it queries ~135k rows, this can go up to almost ~600k. As you can see 99% of the time is spend on the Bitmap heap scan. I run a db.t2.large RDS database at AWS with 100gb (SSD) of storage, which runs postgres 9.4.7 - Is there anyway to avoid doing the bitmap heap scan? - Or a way to improve the speed of this? - Can I create a covering index, so it doesn't have to read the form responses table itself? - Should I cluster the *form_responses* table on *project_id* and/or *mailing_id*? - Should I upgrade my hardware? - Am I asking too much of my Database?

I've read the MySQL loose index scan, and it makes sense as optimization, however, when using an example B+tree, I don't understand how nodes can be efficiently skipped, due to potential matches causing many traverses to be performed. Take this B+-tree:

+-------------+
                                                                 |A            |
                       +-----------------------------------------+ (5,2) (7,0) +----------------------------------------+
                       |                                         |             |                                        |
                       |                                         +------+------+                                        |
                       |                                                |                                               |
                       |                                                |                                               |
                +------v------+                                  +------v------+                                 +------v------+
                |B            +---------------------------------->C            +--------------------------------->D            |
       +--------+ (2,0) (2,2) +----------+             +---------+ (5,2) (5,4) +---------+              +--------+ (7,0) (8,1) +---------+
       |        |             |          |             |         |             |         |              |        |             |         |
       |        +------+------+          |             |         +------+------+         |              |        +------+------+         |
       |               |                 |             |                |                |              |               |                |
       |               |                 |             |                |                |              |               |                |
+------v------+ +------v------+  +-------v-----+ +-----v-------+ +------v------+ +-------v-----+ +------v------+ +------v------+ +-------v-----+
|E            | |F            |  |G            | |H            | |I            | |L            | |M            | |N            | |O            |
| (0,0) (1,0) +-> (2,0) (2,1) +--> (2,2) (3,0) +-> (4,0) (5,1) +-> (5,2) (5,3) +-> (5,4) (5,5) +-> (6,0) (6,1) +-> (7,0) (8,0) +-> (8,1) (8,2) |
|             | |             |  |             | |             | |             | |             | |             | |             | |             |
+-------------+ +-------------+  +-------------+ +-------------+ +-------------+ +-------------+ +-------------+ +-------------+ +-------------+

and the query SELECT (c1), MIN(c2) ... GROUP BY c1. Now, based on my understanding, a loose index scan will skip nodes when it is certain that a subtree doesn't include values that won't affect the (current) aggregate result. With the tree above, I reckon the traversal will be: - A - B - E - find (0,0) (1,0) - backtrack - find (2,0) - skip F - G - find (3,0) - backtrack - H - find (4,0) (5,1) - backtrack - skip I - L - backtrack - D - M - find (6,0) - backtrack - find (7,0) - N - find (8,0) - O Assuming that a backtrace has no cost, isn't in this example less expensive to perform a tight index scan (that is, navigate all the leaves directly)? Is there any mistake in the traversal logic above? Or is this an excessively pessimistic (therefore, non representative) example?

I've got an issue with estimated cost and join predicate : SELECT c.Id FROM Table_A a LEFT JOIN Table_B b ON b.Id_A = a.Id LEFT JOIN Table_C c ON c.Id_B = b.Id WHERE a.Id = 2500 OPTION (RECOMPILE) Table_A has 50k rows, Table_B is empty, Table_C has 2M rows The PK and FK indexes exists and the stats are up to date. But for some reason, SQL Server use a Clustred Index Scan on Table_C. https://www.brentozar.com/pastetheplan/?id=r1G_LnpeU This is a sample my problem, the prod tables are fare bigger and use too much estimated grant memory because of this issue. Using a FORCESEEK or change the join to LEFT JOIN Table_C c ON c.Id_B = b.Id AND c.Id_B IS NOT NULL resolve the issue but it's a query (mutliple queries in fact) generate by Entity Framework so I don't have so much control on it. Is there a way to seek Table_C without changing the query here ?

Assume that there is a table with clustered index create table [a_table] ([key] binary(900) unique clustered); and some data insert into [a_table] ([key]) select top (1000000) row_number() over (order by @@spid) from sys.all_columns a cross join sys.all_columns b; By inspecting storage statistics of this table select st.index_level, page_count = sum(st.page_count) from sys.dm_db_index_physical_stats( db_id(), object_id('a_table'), NULL, NULL, 'DETAILED') st group by rollup (st.index_level) order by grouping_id(st.index_level), st.index_level desc; one can see index_level page_count ----------- ---------- 8 1 7 7 6 30 5 121 4 487 3 1952 2 7812 1 31249 0 125000 NULL 166659 that table takes 166659 pages in total. However table scan set nocount on; set statistics io, time on; declare @cnt int; select @cnt = count(1) from [a_table]; set statistics io, time off; produces Table 'a_table'. Scan count 5, logical reads 484367, ... CPU time = 1757 ms, elapsed time = 460 ms. almost three times higher number of logical reads in comparison to space taken by table. When I examined query plan, I noticed that SqlServer used parallel index scan. And this is where first part of the question arises. How parallel index scan is performed that makes SqlServer to do so much logical reads? Specifying option (maxdop 1) to suppress parallelism set nocount on; set statistics io, time on; declare @cnt2 int; select @cnt2 = count(1) from [a_table] option (maxdop 1); set statistics io, time off; resulted to Table 'a_table'. Scan count 1, logical reads 156257, ... CPU time = 363 ms, elapsed time = 367 ms. Comparing statistics for parallel and non-parallel index scan in this case leads to a conclusion that sometimes it is better to avoid parallel index scan. And this is where the second part of the question arises. When should I worry about parallel index scan? When should it be avoided/suppressed? What are the best practices? ___ The above results are obtained on > Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

I have four tables with 500,000 to 4,000,000 rows. The script joins them and insert into a temporary table with some where clause. It is taking long. The execution plan shows 99% cost only for the insert whereas there are multiple scans in the plan. Snapshot of the related part of the execution plan is shown in the image. The drive is solid state. How do I interpret the plan. SQL Server execution plan

We scanned our systems and got a high vulnerability reported in a server. One of the thing that got flagged was MS12-060, which is related to MS Office 2003 Web Components that gets installed along with Analysis Services. Can we remove that component without breaking the SQL Server engine or do we need to uninstall SSAS first and then remove the component.

I would like to see information on how many times has a table been fully scanned. I was hoping for something like sys.dm_db_index_usage_stats but for table scans. There is a method described at the blog [keepitsimleandfast](http://www.keepitsimpleandfast.com/2011/11/how-to-find-table-scans-in-your.html) , but I would rather get all scans since server started. What I am looking for is also similiar to pg_stat_all_tables.seq_scan in PostgreSQL.

I am trying to improve the performance of a procedure which we recently moved from 11g to 12c. I am simply using a refcursor which gets the result set from an underlying query. In 12c I am having a significant performance hit. The query running time in 11g was less than 3 seconds while in 12c it is taking more than 2 minutes. In 12c, the stats are not gathered for the last 1 month(testing environment). When I compared the execution plan, the only difference I could see was that there is a full table scan following an index range scan happening for a table. This full table scan is not present in 11g. Following is the plan snippet where this is happening. Thanks in advance for any help. 18 STATISTICS COLLECTOR 17 VIEW VIEW SYS.VW_1 Cost: 137 Bytes: 81 Cardinality: 9 16 HASH UNIQUE Cost: 137 Bytes: 432 Cardinality: 9 15 FILTER 14 CONNECT BY WITH FILTERING (UNIQUE) 6 TABLE ACCESS BY INDEX ROWID TABLE TAB1 Cost: 2 Bytes: 27 Cardinality: 1 5 INDEX UNIQUE SCAN INDEX (UNIQUE) TAB1_PK Cost: 1 Cardinality: 1 13 HASH JOIN Cost: 5 Bytes: 312 Cardinality: 8 11 NESTED LOOPS Cost: 5 Bytes: 312 Cardinality: 8 8 STATISTICS COLLECTOR 7 CONNECT BY PUMP 10 TABLE ACCESS BY INDEX ROWID BATCHED TABLE TAB1 Cost: 3 Bytes: 216 Cardinality: 8 9 INDEX RANGE SCAN INDEX TAB1_IE2 Cost: 1 Cardinality: 8 12 TABLE ACCESS FULL TABLE TAB1 Cost: 3 Bytes: 216 Cardinality: 8

I have a two node RAC database server hosting 8 databases. I have 3 scan listeners running. 2 in node 1 and one in the 2nd node. All the 8 databases are running for different applications. Are these 3 listeners sufficient to manage the work load for different 8 databases? So only the service names will be different for the databases for the respective applications to talk to right? Are there any best practices to have more scan listeners run on different ports or or is it recommended that way?

We have set up RAC database on server. We can connect to database from sqlplus on the server using SCAN address. However, if I try to connect to RAC using SQL developer from client machine using SCAN address-based TNS entry, it shows the error 12-APR-2016 22:33:07 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=User))(SERVICE_NAME=racdb)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.227.110)(PORT=63694)) * establish * racdb * 12516 TNS-12516: TNS:listener could not find available handler with matching protocol stack Client machine is behind the firewall. Here is the TNS entry. RACDB = (DESCRIPTION = (LOAD_BALANCE = ON) (FAILOVER = ON) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.149)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.150)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.151)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = racdb) ) ) We cannot use DNS to resolve SCAN IPs from client side, so we used the IP address on the client machine. And IP addresses of SCAN are converted on client side using NAT. Below is their real addresses set on the server # SCAN #192.168.227.149 racdb-scan.esplink.mn racdb-scan #192.168.227.150 racdb-scan.esplink.mn racdb-scan #192.168.227.151 racdb-scan.esplink.mn racdb-scan If we change the IP address of TNS entry to VIP addresses, the connection is successful and I can connect to RAC database. Is it wrong to use SCAN addresses to connect to RAC?