Does truncate and API Load invalidate statistics

rcamarda

As part of my data warehouse nightly build, I truncate my tables in my
target database.
As example, I find it is much quicker to do a bulk API load of 13M
records and to do an update/insert of 100K rows. I also drop the
indexes before the builds and reindex after. Thats an aside.
What I am wondering is how is this impacting the statistics? Do I need
to update them?
Not well versed on statistics and any data is welcomed.
Thanks
Rob

Aug 16 '06 #1

Subscribe Post Reply

1515

Erland Sommarskog

rcamarda (ro*****@hotmail.com) writes:

As part of my data warehouse nightly build, I truncate my tables in my
target database.
As example, I find it is much quicker to do a bulk API load of 13M
records and to do an update/insert of 100K rows. I also drop the
indexes before the builds and reindex after. Thats an aside.
What I am wondering is how is this impacting the statistics? Do I need
to update them?

When an indexes added, statistics based a full scan is added for that
index, so you are fairly safe.

Would could matter is statistics of non-indexed columns. These you lose
when you truncate the tables. These statistics are less essential than
index on indexed columns, but there are queries where they may be useful.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Aug 16 '06 #2

Similar topics

zos - exec sql - load

by: Carmine | last post by:

I'm getting this error when I submit the job: DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = LOADSQL DSNU005I DSNUGPRS - EXEC SQL DSNU082I DSNUGPRS - INVALID KEYWORD - EXEC...

DB2 Database

Bug in LOAD command?

by: JS | last post by:

EE instance DB2 v7.2.0 fixpack 3 on WIN2K. I select some data from a table A and write it to a file using the COALESCE function and whitespace as the null character: eg coalesce(col1, ' '). This...

DB2 Database

Why does 'SELECT * FROM countries' truncate characters and 'SELECT country FROM countries' does not ?

by: PRS | last post by:

Why does 'SELECT * FROM countries' truncate characters and SELECT country FROM countries does not ? mysql> SELECT * FROM countries; +-------------+-----------+ | country | capital |...

MySQL Database

truncate in db2

by: Sumanth | last post by:

Are there any implementations of truncate in db2. Is it going to be implemented in the future? Is there an alternate way of doing a truncate of a table that has a high record count without using...

DB2 Database

Utilization Statistics during data load

by: asheesh.rastogi | last post by:

Hi, I am running DB2 UDB 8.2.4 on AIX 5.3. We are in process of migrating huge chunks of data as part of benchmarking exercise. I need to collect statistics like CPU time and total time taken to...

DB2 Database

How to count how many visitor's have js enabled and how many does not?

by: damezumari | last post by:

I would like to know how many of the visitors to my site has js enabled and how many has it turned off. I haven't found a simple solution searching Google groups so I suggest the following using...

Javascript

Include with variables - why does this work ?

by: TMN | last post by:

Hi All I am new to PHP and I do not understand why the following works ?? $file=urlencode("displayIncidents.php"); echo "<a href=statistics.php?fileName=$file&delete=true>Delete...

PHP

How do you get load to reject and not truncate data

by: Eric Davidson | last post by:

I am try to find a way to get load to reject a record if the data is too large and not just truncate it. eg. c:\temp\fred3.txt ------ a23456789012345678901234567890...

DB2 Database

truncate tables in stored procs?

by: Henry J. | last post by:

I know that we can use import or load to truncate tables without filling up trans log in DB2. Is it possible to do this in a stored procedure? How about in CLI/C++? My impression is that...

DB2 Database

Cloud Servers without Credit Card and Email Registration: A Simpler Way to Get on the Cloud

by: CloudSolutions | last post by:

Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...

General

Access Europe: Command bars, the Access Shortcut Tool and a simple Audit Log - Wed 3 April

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

General

One-click Importing Excel Data into a*Database

by: ryjfgjl | last post by:

In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...

Microsoft Excel

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++