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...
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? ...
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...
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? ...
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...
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...
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...
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...
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...
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...
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...
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 ...
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? ...
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...
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. ...
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...
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 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...
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. ...
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...