Questions tagged [table-statistics]
50 questions
14
votes
6 answers
concurrent statistics gathering on Oracle 11g partiitioned table
I am developing a DWH on Oracle 11g. We have some big tables (250+ million rows), partitioned by value. Each partition is a assigned to a different feeding source, and every partition is independent from others, so they can be loaded and processed…
Francesco
- 161
- 1
- 10
14
votes
3 answers
Any way to compute statistics on a hive table for all partitions with a single analyze command?
The syntax I see for computing statistics in hive seems to indicate the answer to the title question would be 'no':
ANALYZE TABLE [TABLENAME] PARTITION(parcol1=…, partcol2=….) COMPUTE STATISTICS
However, I wanted to throw it out here, since it i…
WestCoastProjects
- 58,982
- 91
- 316
- 560
6
votes
1 answer
Why is Oracle is locking the statistics of my schema after import?
My problem is that I have a schema where the statistics of all tables are locked.
I found on the Internet that I can unlock using the DBMS_STATS.UNLOCK_TABLE_STATS (SCHEMA_NAME) procedure.
What I need to know is the how Oracle determines when the…
Aramillo
- 3,176
- 3
- 24
- 49
4
votes
1 answer
What is the format of TSQL STATS_STREAM (undocumented feature)? Does it contain all the Statistics Data?
I have a huge table on one server, and need to copy the table to the production server. I can do this, but then I need to generate statistics, which will take a long time on a system that I don't want to ask to handle a huge job. If I move the…
David Manheim
- 2,553
- 2
- 27
- 42
3
votes
4 answers
Table Stats gathering for Oracle
When and how should table stats gathering be performed for Oracle, version 9 and up? How would you go about gathering stats for a large database, where stats gathering would collide with "business hours".
Matthew Watson
- 14,083
- 9
- 62
- 82
2
votes
0 answers
Azure Postgres AUTOVACUM AND ANALYZE THRESHOLD - How to change it?
I am coming again with another Postgres question. We are using the Managed Service from Azure that uses autovacuum. Both vacuum and statistics are automatic.
The problem I am getting is that for a specific query, when it is running at specific…
Roberto Hernandez
- 8,231
- 3
- 14
- 43
2
votes
1 answer
Do EXTENDED STATISTICS have any trade offs in PostgreSQL?
Extended Statistics help the planner with evaluating the query complexity in order to choose the correct algorithm for processing the query. Creating new statistics on a couple of dependant columns in a table probably creates some complex structure…
toddddos
- 321
- 2
- 11
2
votes
1 answer
SQL Server optimal query based on value distribution
I have a table that stores dynamic user data in a key-value pair format. Something like this:
UserId | Key | Value
---------------------------------
1 | gender | male
1 | country | Australia
2 | gender | male
2 |…
Diego Jancic
- 7,280
- 7
- 52
- 80
1
vote
1 answer
Auto Update Statistics and FULLSCAN in SQL Server 2008 R2
Is it possible to force FULLSCAN when statistics are updated automatically by SQL Server 2008 R2?
If not, is a planned UPDATE STATISTICS WITH FULLSCAN the best way to keep statistics up-to-date?
note: The need for the FULLSCAN comes by proved…
CAFxX
- 28,060
- 6
- 41
- 66
1
vote
1 answer
SQL Server: Cardinality estimation for table variable
SQL Server does not maintain statistics for table variable and it always estimates 1 output row. Microsoft changed this behavior post release of SQL Server 2019. Though I am not able to find much information about those changes.
Below is my sample…
KnowledgeSeeeker
- 620
- 1
- 9
- 14
1
vote
1 answer
SQLServer query blocking itself, how to solve?
I have this query
;WITH --first CTE is your data set example
CTE
AS
(
SELECT *
FROM (VALUES
('2023-04-06', 0029, 'D', 'ABCD', 1, 100),
('2023-04-06', 0027, 'D', 'ABCD', 1, 200),
…
Ron
- 21
- 2
1
vote
0 answers
ANALYZE TABLE showing NULLs for all statistics in Spark
I was trying to compute statistics and get the statistics for individual columns. And I'm seeing that all the statistics are NULL here for all the columns. Not sure what mistake I may be doing here.
ordersSchemaDDL = "orderid Int, ordertime…
Praveen Kumar B N
- 85
- 2
- 5
1
vote
1 answer
Netezza: Generate FULL Statistics using SQL / force
Is there a way to force netezza to generate full statistics on a table using SQL. I know it is possible to do via command line but that require nzsql client to be installed on the machine that initiates this command.
According to the details…
Kausty
- 899
- 2
- 10
- 22
1
vote
1 answer
What is the implication of getting "No stats" for certain columns in hive?
I get the following in my log file, but don't know what is the implication:
query:
create table mydb.new as
select a.col1,b.col4,a.col3 from
sampledb.table1 a
left join sampledb.table2 b
on a.col3=b.col3
;
No Stats for sampledb@table1, Columns:…
DS R
- 235
- 2
- 13
1
vote
2 answers
Why does my table_summary looks weird in r
total beginner and very hopeful someone can help me(:
Wrote the following code to create a statistics summary table grouped by city, but my table turned out weird looking (instead of looking like a table, it displayed the table spacers).
What should…
Net_D
- 33
- 4