Questions tagged [scan]
The scan tag has no usage guidance.
20 questions
1
vote
2
answers
76
views
When is a regular index scan faster than a bitmap index + heap scan in Postgres?
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 ...
- 119
0
votes
0
answers
294
views
SQL search for a pattern in the middle of a string (infix search) without full table scan
With SQL, I need to select rows which have a pattern like %hello% (i.e. <anything>hello<anything>) in a column, with potentially 100 millions of rows or more.
In the answer Infix search in ...
- 171
1
vote
1
answer
495
views
How do I bulk export data from an RDBMS without killing performance?
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 ...
- 113
2
votes
2
answers
5k
views
PostgreSQL select from table (without ORDER BY) comes out in different order every time
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 ...
- 503
1
vote
2
answers
2k
views
How to connect to Oracle RAC from HOST
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, now I just need to program an ...
- 113
3
votes
2
answers
1k
views
How does a column-oriented DBMS filter on multiple columns?
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 ...
- 171
0
votes
2
answers
946
views
Why Are Table Scans Bad?
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"? ...
- 170
0
votes
2
answers
437
views
How does MySQL skip ranges (nodes) in a loose index scan?
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 ...
- 390
0
votes
1
answer
2k
views
MSSQL Join between big and empty tables
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)
...
- 3
3
votes
1
answer
7k
views
Very high (99%) insertion cost in estimated execution plan
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 ...
- 31
0
votes
2
answers
206
views
DB2 huge IREF and lots of scans
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 ...
2
votes
1
answer
210
views
Vulnerability in Server from SQL Server 2008 R2
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 ...
- 69
4
votes
1
answer
2k
views
Number of full table scans
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 ...
- 153
1
vote
1
answer
4k
views
Bitmap Heapscan is slow
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 ...
1
vote
2
answers
1k
views
Delete cascade produce scan intead of index seek
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 ...
- 45
7
votes
1
answer
518
views
STATISTICS IO for parallel index scan
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 (...
- 2,384
1
vote
0
answers
965
views
Performance issue in 12c - Additional Full table scan after index range scan
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 ...
- 11
0
votes
1
answer
2k
views
Oracle - Scan listener and multiple databases
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 ...
- 1,926
1
vote
1
answer
7k
views
Connect to Oracle RAC from SQL Developer?
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 ...
- 71
1
vote
1
answer
5k
views
SCAN listener redirects connection to another port
I have setup RAC. I have two listeners LISTENER and LISTENER_DG.
LISTENER is listening port 1521
LISTENER_DG is listening 1530
When client connects to the database using SHARED connection mode even ...
- 1,717