Questions tagged [full-table-scan]
61 questions
2
votes
1 answer
mysql performs full table scan even though index exists
so I have this table containt 100000 rows
field1 field2
now i Just added a new column field3 and moreover there is an index on field3
field1 field2 field3
so I added about 50 rows that contains field3 (the other rows have field3 as NULL)
so I do…
pillarOfLight
- 8,592
- 15
- 60
- 90
2
votes
1 answer
Two sets of indentical tables with joins using indexes in MySQL; one requires full-table scan
Im getting very confused with indexes in MySQL.
I have two tables: TableA1 and TableA2.
I created indexes on these for the joins between them and queries run really fast.
I have another 2 tables with an identical setup e.g. TableB1 and TableB2. The…
Ke.
- 2,484
- 8
- 40
- 78
2
votes
1 answer
Cassandra efficient table walk
I'm currently working on a benchmark (which is part of my bachelor thesis) that compares SQL and NoSQL Databases based on an abstract data model an abstract queries to achieve fair implementation on all systems.
I'm currently working on the…
hoffmax91
- 131
- 1
- 10
2
votes
1 answer
Oracle Index - full table scan/lock
Found this here:
In general, consider creating an index on a column in any of the following situations:
A referential integrity constraint exists on the indexed column or
columns. The index is a means to avoid a full table lock that would
otherwise…
Robotronx
- 1,728
- 2
- 21
- 43
1
vote
2 answers
Sql Server doing a full table scan when first field in PK has few distinct values
I have this table (TableA):
(
[FieldA] [int] NOT NULL,
[FieldB] [int] NOT NULL,
[Value] [float] NULL
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
(
[FieldA] ASC,
[FieldB] ASC
)
There are few distinct FieldA values, lets say…
ytoledano
- 3,003
- 2
- 24
- 39
1
vote
1 answer
Effecient MySQL SELECT for text in columns in InnoDB
I have a large large table (InnoDB) that essentially has a location name with the location's latitude and longitude. my query is something like
SELECT columns FROM table
WHERE latitude BETWEEN latMin AND latMax
AND longitude BETWEEN longMin AND…
Andrew Park
- 1,489
- 1
- 17
- 26
1
vote
2 answers
SQLite: Optimize table scans
In my table I have some columns that have no index on them, searching for a value in those columns can take very long because SQLite does a full table scan.
In my specific case the row I'm looking for (the values are unique) is almost always among…
Maestro
- 9,046
- 15
- 83
- 116
1
vote
1 answer
Python boto3 get item with specific non-parition-key attribute value
My AWS dynamoDB has id as Partition key and there is no Sort key. The following does not return the existing record from the table:
response = producttable.scan(FilterExpression=Attr('title').eq("My Product"))
response['ScannedCount'] is less than…
Kok How Teh
- 3,298
- 6
- 47
- 85
1
vote
1 answer
How Query optimizer determines when to perform full table scan or Index scan?
I have just started exploring execution plans in SQL and I am not able to figure out how the query optimizer decides when to do a full table scan or index scan.
I ran several queries on the DB available in this link.
One of the query that i ran on…
Sum
- 35
- 5
1
vote
0 answers
Scan list of maps from dynamodb with Java SDK v2
I am trying to scan a DynamoDB table that has lists of maps as attribute values.
DynamoDB table:
{
"Name": "n",
"Accounts": [
{
"address": "a",
"name": "b"
},
{
"address": "c",
"name": "d"
}
}
Target Java…
levye
- 185
- 2
- 14
1
vote
1 answer
Selecting data from Postgres foreign table via oracle_fdw does not use the index on Oracle side
in short: we are facing a problem that full table scan is performed on remote Oracle DB instead of using the index.
Setup:
Postgres 12.3 in an enriched docker container with oracle base client, connecting to a remote Oracle DB in version 19c. The…
LWn
- 11
- 3
1
vote
2 answers
indexed view update is table scanning 110 million rows for 6 actual rows found out of histogram key bounds
On SQL Server 2017(RTM-CU17) with query optimizer hot fixes on I have an indexed view that is taking a considerable amount of time to update. I am at a loss and cannot figure out why a full table scan is occurring for the update.
The indexed view…
Ben H
- 26
- 4
1
vote
0 answers
MySql View and paging is causing full table scans
During our MySQL performance review we saw that we had a few full-table scans in our code.
We created a view which spans different tables which are Master - detail records. For example, sales and saleslines.
On this master detail record we have…
Rob Van Pamel
- 734
- 1
- 8
- 23
1
vote
1 answer
MERGE INTO Performance as table grows
This is a general question about the Oracle MERGE INTO statement with a particular scenario, on Oracle RDBMS 12c.
Daily data will be loaded to StagingTableA - about 10m rows.
This will be MERGEd INTO TableA.
TableA will vary between 0 to 10m rows…
TenG
- 3,843
- 2
- 25
- 42
1
vote
0 answers
Hive Full Table Scan Issue (Partitioned Columns Used)
I have a BIG table in Hive 0.13 - it has approx 250 GB of data per day. Per hour, it is, hence, approx, 10 GB of data. I have a BI Tool which would like to access this table's data on per day or per hour basis for which I need to test the queries…
Suddhasatwa Bhaumik
- 47
- 7