indexing

Can Multiple Indexes Work Together?

Suppose I have a database table with two fields, "foo" and "bar". Neither of them are unique, but each of them are indexed. However, rather than being indexed together, they each have a separate index. Now suppose I perform a query such as SELECT * FROM sometable WHERE foo='hello' AND bar='world'; My table a huge number of rows for w...

What are hypothetical indexes?

Does anybody know what hypothetical indexes are used for in sql server 2000? I have a table with 15+ such indexes, but have no idea what they were created for. Can they slow down deletes/inserts? ...

Do you put your indexes in source control?

And how do you keep them in synch between test and production environments? When it comes to indexes on database tables, my philosophy is that they are an integral part of writing any code that queries the database. You can't introduce new queries or change a query without analyzing the impact to the indexes. So I do my best to keep m...

How do I estimate SQL Server index sizes

While estimating straight row and table sizes are fairly simple math, we find it challenging to guess just how much space each index will occupy (for a given table size). What areas can we learn to calculate a better estimate and growth rate for indexes? ...

Could you recommend an unstructured data indexing software?

I am collecting logs from several custom made applications. Each application has it's own log format. What I'm looking for is a central tool which would allow me to search through all of my logs. This means the tool would have to be able to define a different regex (or alike) for each log file (marking where a record begins, ends, and wh...

Multiple Indexes vs Multi-Column Indexes

I've just been adding an Index to a table in SQL Server 2005 and it got me thinking. What is the difference between creating 1 index and defining multiple columns over having 1 index per column you want to index. Are there certain reasons why one should be used over the other? For example Create NonClustered Index IX_IndexName On Tabl...

Maintaining an index of a filesystem

I need to record in some kind of file, the specific time at which blocks are written to in a filesystem, over a number of physical disks (RAID). I intend to do this by periodically by interrogating the writing device to find out which block it is writing to at that specific time. At some later date it will be necessary to locate and r...

SQL Server STATISTICS

So for this one project, we have a bunch of queries that are executed on a regular basis (every minute or so. I used the "Analyze Query in Database Engine " to check on them. They are pretty simple: select * from tablex where processed='0' There is an index on processed, and each query should return <1000 rows on a table with 1MM reco...

How to set session variable skip_unusable_indexes to true in a PL/SQL package to speed up a table delete/insert?

I'm trying to speed up a data load which is controlled via a PL/SQL stored procedure. I've programmatically altered the indexes for the table I want to refresh to be unusable. I want Oracle to ignore these unusable indexes. I can issue the statement... ALTER SESSION SET skip_unusable_indexes = TRUE ...but I subsequently get the error...

Using LIMIT when searching by a unique field

Given a table structure like this: CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(32) NOT NULL, `username` varchar(16) NOT NULL, `password` char(32) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ); Is there any use in using the LIMIT keyword when searching by usernam...

How to create a unique index on a NULL column?

I am using SQL Server 2005. I want to constrain the values in a column to be unique, while allowing NULLS. My current solution involves a unique index on a view like so: CREATE VIEW vw_unq WITH SCHEMABINDING AS SELECT Column1 FROM MyTable WHERE Column1 IS NOT NULL CREATE UNIQUE CLUSTERED INDEX unq_idx ON vw_unq (Column...

Copy a table (including indexes) in postgres

I have a postgres table. I need to delete some data from it. I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need. I can't delete data from the original table, because this original table is the source of data. In one case I need to get some results that depends on deleting X, in ...

Best practices for grails index page

What is the right way to populate the model for the index page in a grails app? There is no IndexController by default, is there some other mechanism for getting lists of this and that into the model? ...

Individual indexes vs multiple field indexes

Currently we have a table that we use to track inivitations. We have an email field that is indexed but we also have three optional keys that the user can specify when adding new record emails. We don't allow duplicates so we have to query if the email plus the optional keys already exists. Currently the keys are only added to the select...

How to choose and optimize oracle indexes ?

I would like to know if there are general rules for creating an index or not. How do I choose which fields I should include in this index or when not to include them? I know its always depends on the environement and the amount of data, but I was wondering if we could make some globally accepted rules about making indexes in Oracle. ...

What's the difference between creating a UNIQUE index as "index" or as "constraint" in SQL Server?

When creating an index over a column that is going to be UNIQUE (but not the primary key of the table), SQL server let's me choose a few options: 1) I can choose for it to be a Constraint or an Index. I'm guessing this means that if I set it as constraint, it won't use it when querying, only when writing. However, the only efficient way...

Importing new database table

Where I'm at there is a main system that runs on a big AIX mainframe. To facility reporting and operations there is nightly dump from the mainframe into SQL Server, such that each of our 50-ish clients is in their own database with identical schemas. This dump takes about 7 hours to finish each night, and there's not really anything we...

How To Create A 'Two-Sided' Unique Index On Two Fields?

How can I efficiently create a unique index on two fields in a table like this: create table t (a integer, b integer); where any unique combination of two different numbers cannot appear more than once on the same row in the table. In order words if a row exists such that a=1 and b=2, another row cannot exist where a=2 and b=1 or a=1 a...

Is there a clean T-SQL query I can use to verify an index has the right columns?

I am writing a DB upgrade script that will check to see if an index has the right two columns defined. If it doesn't, or if it only has one of them, then I will DROP it (is there a way to ALTER an index?) and then recreate it with both. ...

Should I index a bit field in SQL Server?

I remember reading at one point that indexing a field with low cardinality (a low number of distinct values) is not really worth doing. I admit I don't know enough about how indexes work to understand why that is. So what if I have a table with 100 million rows in it, and I am selecting records where a bit field is 1? And let's say tha...