I have a two column table with a primary key (int) and a unique value (nvarchar(255)) When I insert a value to this table, I can use Scope_identity() to return the primary key for the value I just inserted. However, if the value already exists, I have to perform an additional select to return the primary key for a follow up operation (i...
How can I find out what, if any, indexes are set up on the oracle data dictionary tables themselves (eg on the columns of all_tables or all_source)? ...
I've got a query which has a WITH statement for a subquery at the top, and I'm then running a couple of CONNECT BYs on the subquery. The subquery can contain tens of thousands of rows, and there's no limit to the depth of the CONNECT BY hierarchy. Currently, this query takes upwards of 30 seconds; is it possible to specify indexes to put...
i want to run a query like this : CREATE TABLE newtable SELECT * FROM oldtable its work fine but the indexes not copied to the new table , how can i run the query and prevent the indexes ? thanks ...
I need to sort an array that can look like this: $array[4][0] = array('id' => 1, 'value' => 2); $array[3][2] = array('id' => 0, 'value' => 3); $array[4][1] = array('id' => 1, 'value' => 0); $array[1][3] = array('id' => 2, 'value' => 1); $array[1][1] = array('id' => 3, 'value' => 0); $array[3][0] = array('id' => 2, 'value' => 1); $array[...
Hello All, I read on MySQL Performance Blog that when tables are large, it is better to scan full tables, instead of using indexes. I have a table with tens of millions of rows. When conducting queries, if I use no indexes, then queries are 24 times slower than with indexes. I know lot of things may cause this (e.g., are rows stored se...
I have a large (40 Mo) SDF database. Some queries are very long, but can become quick after some time. I would like to try to tidy up my indexes but I only find information for SQL Server and not for SQL CE. ...
Hello, I am having trouble incrementing the indexes of my list item properties. Here is the code. Dim i As Integer = 0 For x As Integer = 1 To list.Count / 19 database.ExecuteCommand("INSERT INTO Contacts VALUES ('" + _ list.Item(i) + "', '" + _ ...
Hello All, I am trying to develop a complex textual search engine. I have thousands of textual pages from many books. I need to search pages that contain specified complex logical criterias. These criterias can contain virtually any compination of the following: A: Full words. B: Word roots (semilar to stems; i.e. all words with certa...
I have some questions on Composite Primary Keys and the cardinality of the columns. I searched the web, but did not find any definitive answer, so I am trying again. The questions are: Context: Large (50M - 500M rows) OLAP Prep tables, not NOSQL, not Columnar. MySQL and DB2 1) Does the order of keys in a PK matter? 2) If the cardinali...
We have a mid-size SQL Server based application that has no indexes defined. Not even on the the identity columns. I suggested to our moderately expensive application consultant that perhaps we might get better performance (particularly as our database grows) by creating some indexes on appropriate fields, and he said: "Indexes will ...
I'm writing a page that does very simple search queries, resulting in something like: SELECT * FROM my_table WHERE A in (a1, a2, a3) AND B in (b1, b2) AND C in (c1, c2, c3, c4) AND And so on for a variable number of columns, usually ~5. If I create a separate index for each column (one for A, one for B, one for C, not (A,B,C)), will a...
I have a MySQL table from a third-party application that has millions of rows and only one index - the timestamp of each entry. Now I want to do some heavy self-joins and queries on the data using fields other than the timestamp. Doing the query on the original table would bring the database to a crawl, adding indexes to the table is not...
Hi, I was wondering can I ORDER BY my query by node of an XML typed column? For example I have a table ID (int) | Data (XML) Where Data column stores XML in form similar to this <?xml?> <Data> <SimpleOrderedValue>1</SimpleOrderedValue> <ComplicatedInternals> ... </ComplicatedInternals> </Data> I want query this tabl...
One of the more annoying things about InnoDB is that although it supports foreign keys it requires that foreign key columns in the referencing table be indexed. While that's often a good idea it certainly isn't always one. Take for example an orders table with ~20 columns. Aside from the single column primary key and perhaps the cre...
While trying to optimize SQL scripts, I was recommended to add indexes. What is the easiest way to specify what Database the index should be on? IF EXISTS (SELECT * FROM sysindexes WHERE NAME = 'idx_TableA') DROP INDEX TableA.idx_TableA IF EXISTS (SELECT * FROM sysindexes WHERE NAME = 'idx_TableB') DROP INDEX TableB.idx_Tabl...
I'm no database expert, but I have enough knowledge to get myself into trouble, as is the case here. This query SELECT DISTINCT p.* FROM points p, areas a, contacts c WHERE ( p.latitude > 43.6511659465 AND p.latitude < 43.6711659465 AND p.longitude > -79.4677941889 AND p.longitude < -79.4477941889) ...
HI all, I recently asked this question in another thread, but was told it would be better to move it to a new question instead. What if I have an auto-increment INT as my non-clustered primary key, and there are about 15 foreign keys defined to it ? (snide comment about original designer being braindead in the original :) ) This...
Adding a seemingly perfectly index is having an unexpectedly adverse affect on a query performance... -- [Data] has a predictable structure and a simple clustered index of the primary key: ALTER TABLE [dbo].[Data] ADD PRIMARY KEY CLUSTERED ( [ID] ) -- Joins on itself looking for a certain kind of "overlapping" records SELECT DISTINCT ...
Whats the rationale for using signed numbers as indexes in .Net? In Python, you can index from the end of an array by sending negative numbers, but this is not the case in .Net. It's not easy for .Net to add such a feature later as it could break other code perhaps using special rules (yeah, a bad idea, but I guess it happens) on indexi...