Questions tagged [hashing]
For questions about the usage of hashing in a database context
55
questions
0
votes
2
answers
55
views
SQL Server - Finding Parent Source from T-SQL Snippet
The environments for which I'm responsible have some pretty substantial plan non-reuse challenges. I've run across the following query (h/t Brent Ozar blog commenter Michael J Swart) that does a fine ...
3
votes
1
answer
52
views
Why does an index on the join predicate does not improve the performance of a hash join?
Various references, such as join-strategies-and-performance-in-postgresql, say that an index on the join predicate does not improve the performance of a hash join.
However, couldn't a B+ tree index ...
- 33
0
votes
0
answers
16
views
3DES Decryption Returning Encrypted Characters at End of Decrypted Password
I'm having a problem with password decryption that has worked correctly for some 10 years in a VB6 DLL but now on newer, faster servers, it's returning the last few encrypted characters of the stored ...
- 11
0
votes
1
answer
80
views
Oracle generating hash_val from a clob
I have the following table, where I would like the value of hash_val to be generated from clob_val whether clob_val is inserted or updated.
Can this be done perhaps by making the column hash_val a ...
- 103
0
votes
2
answers
189
views
Postgresql 12 Custom Hash Function
I have a large table that I would like to partition. The field I want to partition against is a string that contains a coded value, but appears as an integer stored as a string. The "number" ...
2
votes
1
answer
275
views
PostgreSQL: what are purpose of functions uuid_hash(uuid) and uuid_hash_extended(uuid, bigint)
Functions uuid_hash and uuid_hash_extended
Exploring postgresql I discovered 2 functions that looks interesting:
SELECT
p.proname ,
obj_description(p.oid) AS func_desc,
...
- 203
1
vote
1
answer
238
views
Several Postgres partitioning questions (hierarchical partitioning, HASH, PK order)
I'm pretty new to Postgres and to RDBMS partitioning. I have a few related questions about the performance benefits of partitioning.
Background: I am trying to fix slow batch queries on a 4 column ...
- 173
4
votes
1
answer
117
views
In PostgreSQL, what area in memory does HashSetOp use, work_mem or shared_buffer?
I want to know the difference between the hash table in a hash join and the hash table in HashSetOp Except.
Here is the PostgreSQL script I wrote to explain my question.
drop table if exists t1 ...
- 41
3
votes
0
answers
176
views
Recent degradation/variability of HashBytes performance
Up until a few days ago, T-SQL code we have running in SQL Azure that generates an MD5 for temp table values was fast (a few seconds per hundred thousand on a Business Critical Gen 5, 2 VCores DB). ...
- 145
3
votes
2
answers
116
views
Conflict resolution for hash-backed unique constraint of large text bodies
So we've just run into
ERROR: index row size 2736 exceeds maximum 2712 for index "foo__bar__un"
Hint: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index ...
- 132
0
votes
1
answer
248
views
Is there a cross-platform way to compare data in one columnd on each sitde of replicated data, like a checksum or hash?
I have an Oracle 12 database with lots of tables, and I am replicating several of the tables (a subset of rows) into a SQL Server 2016 database. The subset of rows can be established with a WHERE ...
0
votes
1
answer
460
views
How to create automatic partitions on a non-time based column in Postgres?
I want to create a partition on a column which is of type serial. A hash based partition seems appropriate here. But from what I understand I will need to create all the partitions myself (based on ...
- 202
-1
votes
1
answer
178
views
Does Oracle guarentee that ORA_HASH is used to determine which hash partition a row is inserted, and will this be honored in the future?
I use hash partitioning for a few of my very large tables, and occasionally I have a use case where it would be convenient to have a mechanism that would return the partition name that a row would be ...
- 155
8
votes
3
answers
4k
views
Is left hash join always better than left outer join?
I have a query that runs quite slow (see below). While searching for a way to improve it we found that the query runs about ten times faster if we replaced the LEFT OUTER JOIN with LEFT HASH JOIN
The ...
- 199
5
votes
2
answers
298
views
SQL Server hashbytes seed
I'm trying to compare hashes generated by SQL Server HASHBYTES() with hashes generated with python's hash() (or hashlib) function. For both functions, the same algorithm is used. The problem is I don'...
- 161
1
vote
2
answers
442
views
oracle - hashing method, excluding DBMS_CRYPTO
BACKGROUND:
DBAs have hidden DBMS_CRYPTO package to any role, grantee, and I wonder why. I have to use wrapper instead :(
Is there any way to get the same hash for the text field with the same text? ...
- 155
0
votes
3
answers
42
views
Index of which columns are NOT empty
I am trying to enhance the search performance of a table based on whether certain columns contain values.
I have the following columns :
id |
link |
text |
date |
lat |
long |
type
If a record ...
- 103
0
votes
0
answers
112
views
Hash matches with collisions
I'm trying to improve certain matches that happen often, on many many dimension columns. So my choices as I understand are:
Match on many columns
Hash the columns, store the hash, match on that.
If ...
- 287
1
vote
2
answers
714
views
MSSQL Hashbytes produces different output, string vs column value
The following SQL
SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', 'test'), 2) as 'From String'
produces the following hash:
...
- 227
3
votes
2
answers
4k
views
Efficient key value store in Postgres
I want to use Postgres(11 in CloudSQL) as an efficient key value store. I have about 200GB of dictionaries (average size is 10kB, the structure can be different and nested). I'm thinking about ...
- 131
0
votes
1
answer
507
views
How to optimize query for text array in PostgreSQL using `@>` operator
I have a column of type text[] and I want to search over this column using this SQL operator @>
Note: @> operator, it filters the data as such ['a','b'] in ['a','b','c']
The ...
- 159
10
votes
1
answer
400
views
Hash aggregate bailout
A question that arose in a chat discussion:
I know hash join bailout switches internally to a sort of nested loops thing.
What does SQL Server do for a hash aggregate bailout (if it can happen at ...
- 69.2k
0
votes
1
answer
325
views
HashBytes Computed Column: Exclude Column
We want to create Hashbytes computed column Non-Persisted. Table has twenty columns, and would like to exclude IdentityId column. How could I do this in computed column? Is there a SQL library ...
user172734
3
votes
1
answer
166
views
Definition of indexing, datatype configuration, and DBMS for SHA3-256 hashes
I am designing a system within which I will need to be able to search a database using a file or string SHA3-256 hash.
For example the hash of test is
...
- 33
4
votes
1
answer
2k
views
Should I use the deprecated MD5 function in SQL Server?
We'd like to use MD5 for our hashing function instead of SHA_256 but as of SQL Server 2016, MD5 is deprecated. We're using this for hashing (comparing which records have changed). We now have this ...
- 1,222
5
votes
2
answers
1k
views
Byte ordering for multibyte characters in SQL Server versus Oracle
I am currently in the process of migrating data from Oracle to SQL Server and I'm encountering an issue trying to validate the data post-migration.
Environment Details:
Oracle 12 - AL32UTF8 ...
- 9,232
31
votes
4
answers
2k
views
What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?
As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded.
The comparison is ...
- 30.3k
11
votes
1
answer
888
views
What is the algorithm behind the EXCEPT operator?
What is the internal algorithm of how the Except operator works under the covers in SQL Server? Does it internally take a hash of each row and compare?
David Lozinksi ran a study, SQL: Fastest way to ...
user162241
1
vote
1
answer
317
views
Specify build and probe tables to hash join?
I am observing a situation in AdventureWorks database where the hash join operator is building hash table using a larger input (296 rows) and then probing with a much smaller input (3 rows). I'm not ...
- 113
14
votes
3
answers
8k
views
How is it possible for Hash Index not to be faster than Btree for equality lookups?
For every version of Postgres that supported hash indexing, there is a warning or note that hash indexes are "similar or slower" or "not better" than btree indexes, at least up to version 8.3. From ...
- 245
0
votes
1
answer
1k
views
How to convert hex chars to byte array in mysql
Got sha1 checksum in hexadecimal format(40 characters).
Need help to convert hex chars to byte array in mysql.
Thanks.
0
votes
1
answer
865
views
Insert PBKDF2 hash into PostgreSQL container
I am using the PostgreSQL docker image with the following Dockerfile:
FROM postgres:10.1
COPY init-db.sh /docker-entrypoint-initdb.d/init-db.sh
and an init-db.sh script with the following content:
...
-1
votes
1
answer
1k
views
Use Sha vs md5 or Hash in Snowflake-db
Let me preface by saying that I am not using this for storing passwords or any other sensitive info -- I simply want a row-level sha/hash that I can use later or to check for unique records. My tables ...
- 111
8
votes
1
answer
16k
views
Best Data Type to Store Result of HASHBYTES('MD5', ...)
What would be the best data type to store the results of the HASHBYTES('MD5', ...)?
It outputs 16 bytes of binary as follows: e.g.
0x5CFCD77F9FF836189D2F647EBCEA183E
I could store it in the ...
- 2,487
2
votes
1
answer
786
views
SQL Server Latching from Multithreading/Parallel Inserts and Hash Partitioning
We are conducting parallel, multithreading inserts in SQL server tables, and want to reduce latching.
What are the disadvantages of utilizing hash partitioning to reduce latching? Is it reduced ...
user129291
1
vote
1
answer
3k
views
MySQL passwords SHA512 using AES_ENCRYPT instead of ENCRYPT?
A tutorial is asking me to encrypt passwords using SHA512 in the following manner:
INSERT INTO `vir_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
('1', '1', ENCRYPT('pw1', CONCAT('$6$', ...
8
votes
3
answers
2k
views
Does Microsoft release its SQL Query Hash Algorithm..?
We use SQL Spotlight in our environment. it's pretty handy.
we use particularly the output of sys.dm_exec_requests and sys.dm_exec_query_stats.
Spotlight pulls the query plan from the plan cache for ...
- 83
7
votes
1
answer
8k
views
Using HASHBYTES() yields different results for nvarchar and a variable
I'm using server-side hashing to transmit passwords, then running PBKDF2 in the database to store the hashed password + salt combination.
Hashing nvarchar(max) and a @variable holding the same value ...
- 305
3
votes
0
answers
57
views
What is a cost of search for records in a table with an equality predicate on a non key attribute A and a static hash organization?
To speed up the search for records in a table with an equality predicate on a non key attribute A and selectivity factor fs, is preferable:
A sequential organization on a key attribute ?
A static ...
1
vote
1
answer
633
views
SQL Server Query Plan Hash Collision
I'm wondering if anyone can shed light on a worry of mine that a query_plan_hash collision could cause a query to be executed as an entirely different query.
The hash is a 16 digit Hex that sp_help ...
- 303
1
vote
0
answers
82
views
MySQL partitioning by hash not repeatable
I have a MySQL (5.6.17) table with a few million records that I wish to partition. In order to do this, I use this query:
ALTER TABLE tablename
PARTITION BY HASH(id)
PARTITIONS 8;
Seems to ...
- 131
4
votes
1
answer
2k
views
Determine the size of a hashed varchar column
I am using SQL Server.
I have this table:
create table MyTable (
ID int not null ,
Name varchar(500) )
and I am adding a name_hash column to store hash values for the name column:
alter myTable
...
- 187
2
votes
2
answers
2k
views
Using the hash of text instead of text itself to address a row in Postgres
I have a project for a website where I have to address thousands or maybe tens of thousands of objects in a postgres table.
Initially I had the url to be:
example.com/object/{ID}/{TEXT SLUG}/
where ...
- 123
1
vote
0
answers
605
views
Using hashed value as quick comparison
I have a postgres table that will be used to store data pulled from several remote sources / APIs. I will be syncing the data several times a day.
Since the data will vary wildly depending on the ...
- 219
1
vote
0
answers
261
views
Performance of Long Primary Keys
There are plenty of discussions on the benefits and drawbacks of using hashes as primary keys, but in my case I have a database of webpages that I need to look up by address, and these addresses are ...
- 111
2
votes
1
answer
1k
views
Use a scalar hashing function in a computed column - non-deterministic?
I have a scalar function
select [dbo].[fn_get_hash_fields] ('blah','dbo')
to which is passed a table and a schema
and which returns an expression representing a concatenation of all column values in ...
- 93
3
votes
1
answer
216
views
How does Hash Join works with relational operators other than equality operator?
I have been reading about Hash Join and how does it work in physical level. However, there is some thing that I don't understand (because of my lack of knowledge).
This is the algorithm of Hash Join ...
- 133
9
votes
1
answer
4k
views
Hash Join vs Hash Semi Join
PostgreSQL 9.2
I'm trying to understand the difference between Hash Semi Join and just Hash Join.
Here're two queries:
I
EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT
customerid ...
- 1,055
2
votes
1
answer
941
views
How to get back value passed to HASHBYTES()?
I want to use the SQL Server HASHBYTES() function.
But I don't know how to get back the value passed to HASHBYTES...
0
votes
1
answer
3k
views
Mysql How to insert a variable as field value in a table
I have created a variable named hash as a password hash. How can this variable be inserted a a field value in a table?
hash = pbkdf2_sha256.encrypt("password", rounds=200000, salt_size=16)
cur....